mysql存储过程笔记

时间：2022-09-22 17:26:43
http://blog.csdn.net/wangchao0605/article/details/5935988

基本语法

创建存储过程 create procedure sp_name()
begin
.........
end
调用存储过程 call sp_name()
删除存储过程 drop procedure sp_name//
不能再存储过程中删除存储过程,只能调用
常用命令 show procedure status
后面可以加where限定字段条件
show create procedure sp_name
显示存储过程详细信息
delimiter mysql默认;为执行符号,为防止输入多行时没输入完就执行,临时将执行符号改成别的delimiter //等,防止执行

数据类型和运算符

变量 DECLARE a INT;SET a=100;
定义 赋值
DECLARE a INT DEFAULT 100;
定义并初始化
变量分为用户变量和系统变量
系统变量分为会话和全局变量
  1. DECLARE l_int INT unsigned default 4000000; 
  2. DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95; 
  3. DECLARE l_date DATE DEFAULT '1999-12-31'; 
  4. DECLARE l_datetime DATETIME DEFAULT '1999-12-31 23:59:59';
  5. DECLARE l_varchar VARCHAR(255) DEFAULT 'This will not be padded';
用户变量 一般以@开头

1、 在mysql客户端使用用户变量

  1. mysql> SELECT 'Hello World' into @x;  
  2. mysql> SELECT @x;
  3. mysql> SET @y='Goodbye Cruel World';
  4. mysql> select @y;
  5. mysql> SET @z=1+2+3;
  6. mysql> select @z;

select起与SQL语句相同的作用

赋值都是SET

2、 在存储过程中使用用户变量

  1. mysql> CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
  2. mysql> SET @greeting='Hello';
  3. mysql> CALL GreetWorld( );

这段意思 创建greetworld存储过程,功能是连接@greeting和world

3、 在存储过程间传递全局范围的用户变量

  1. mysql> CREATE PROCEDURE p1( )   SET @last_procedure='p1';
  2. mysql> CREATE PROCEDURE p2( ) SELECT CONCAT('Last procedure was ',@last_procedure);
  3. mysql> CALL p1( );
  4. mysql> CALL p2( );
运算符 关于赋值:
 :=是真正的赋值操作
 =作为赋值只在 set var=value; 和 update的update tn set var=value where... 才是赋值,其他都是比较操作符

1.算术运算符
+     加   SET var1=2+2;       4
-     减   SET var2=3-2;       1
*      乘   SET var3=3*2;       6
/     除   SET var4=10/3;      3.3333
DIV   整除 SET var5=10 DIV 3; 3
%     取模 SET var6=10%3 ;     1
2.比较运算符
>            大于 1>2 False
<            小于 2<1 False
<=           小于等于 2<=2 True
>=           大于等于 3>=2 True
BETWEEN      在两值之间 5 BETWEEN 1 AND 10 True
NOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 False
IN           在集合中 5 IN (1,2,3,4) False
NOT IN       不在集合中 5 NOT IN (1,2,3,4) True
=             等于 2=3 False
<>, !=       不等于 2<>3 False
<=>          严格比较两个NULL值是否相等 NULL<=>NULL True
LIKE          简单模式匹配 "Guy Harrison" LIKE "Guy%" True
REGEXP       正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" False
IS NULL      为空 0 IS NULL False
IS NOT NULL 不为空 0 IS NOT NULL True
3.逻辑运算符
and or not xor
4.位运算符
|   或
&   与
<< 左移位
>> 右移位
~   非(单目运算，按位取反)
注释 -- 双横杠有空格
# 有空格单行注释
c风格/**/

流程控制

 
分支 if 条件 then
语句
else
语句
end if;
  1. if mobile='13911113222' and psw='720717' then
  2. set useridx = 10008888;
  3. else if mobile='13911113333' and psw='720717' then
  4.                set useridx = 10006666;
  5. else
  6. select UserID into useridx from User_ID order by rand() limit 1;
  7. end if;
CASE语句 类似switch case
  1. CASE case_value
  2.     WHEN when_value THEN statement_list
  3.     [WHEN when_value THEN statement_list] ...
  4.     [ELSE statement_list]
  5. END CASE
Or:
  1. CASE
  2.     WHEN search_condition THEN statement_list
  3.     [WHEN search_condition THEN statement_list] ...
  4.     [ELSE statement_list]
  5. END CASE
  1. CASE
  2.     WHEN (sale_value>200) THEN
  3.         CALL free_shipping(sale_id);
  4.         CASE customer_status
  5.             WHEN 'PLATINUM' THEN
  6.                 CALL apply_discount(sale_id,20);
  7.             WHEN 'GOLD' THEN
  8.                 CALL apply_discount(sale_id,15);
  9.             WHEN 'SILVER' THEN
  10.                 CALL apply_discount(sale_id,10);
  11.             WHEN 'BRONZE' THEN
  12.                 CALL apply_discount(sale_id,5);
  13.         END CASE;
  14. END CASE;
循环 leave [label];就是break;
iterate [label]; 就是continue;
while循环
[label:] while 表达式 do
语句
end while [label];
  1. WHILE (id is not null ) DO 
  2. if(month='01'||month='02'||month='03') THEN set season='1'; 
  3. end if; 
  4. if(month='04'||month='05'||month='06') THEN set season='2'; 
  5. end if; 
  6. if(month='07'||month='08'||month='09') THEN set season='3'; 
  7. end if; 
  8. if(month='10'||month='11'||month='12') THEN set season='4'; 
  9. end if; 
  10. update t_industry_finance_instance set season_=season where ID_=id; 
  11. FETCH cur1 INTO id,month; 
  12. END WHILE; 

loop循环
[label:] loop
语句
end loop [label];

  1. CREATE PROCEDURE ABC()
  2.    BEGIN
  3.       DECLARE a INT Default 0 ;
  4.       simple_loop: LOOP
  5.          SET a=a+1;
  6.          select a;
  7.          IF a=5 THEN
  8.             LEAVE simple_loop;
  9.          END IF;
  10.    END LOOP simple_loop;
  11. END
  1. create procedure pro()
  2. begin
  3.  declare a int default 1;
  4.  label1: loop
  5.     if a<6 then
  6.      select a;
  7.      set a=a+1; 
  8.   iterate label1;
  9.     end if;
  10.     leave label1;
  11.    end loop label1;
  12. end

repeat until循环
[label:] repeat
语句
until 表达式
end repeat [label];

  1. create procedure pro
  2. begin
  3.   declare a int default 3;
  4.   repeat
  5.    select a;
  6.    set a=a+1;
  7.   untile a>5 end repeat;
  8. end
区块定义 begin
end;
还可以加标签
label:begin
end label;
用leave label;跳出区块

输入输出

参数定义 共三种类型 IN OUT INOUT
注意参数后要加类型
IN 输入参数
表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

IN参数例子：

  1. CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
  2. BEGIN
  3. SELECT p_in; --查询输入参数
  4. SET p_in=2; --修改
  5. select p_in;--查看修改后的值
  6. END;
  7. 执行结果:
  8. mysql> set @p_in=1
  9. mysql> call sp_demo_in_parameter(@p_in)
  10. mysql> select @p_in;

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

就是值传递

OUT 输出参数
该值可在存储过程内部被改变，并可返回

OUT参数例子
创建:

  1. mysql> CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
  2. BEGIN
  3. SELECT p_out;/*查看输出参数*/
  4. SET p_out=2;/*修改参数值*/
  5. SELECT p_out;/*看看有否变化*/
  6. END;
  7. 执行结果:
  8. mysql> SET @p_out=1
  9. mysql> CALL sp_demo_out_parameter(@p_out)
  10. 略
  11. mysql> SELECT @p_out;
  12. 略

这玩应只作为输出,所以读数据是NULL,赋值之后才有内容,相当于参数列表写返回值...就是RETURN

INOUT 输入输出参数
调用时指定，并且可被改变和返回

INOUT参数例子：

  1. mysql> CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
  2. BEGIN
  3. SELECT p_inout;
  4. SET p_inout=2;
  5. SELECT p_inout;
  6. END;
  7. 执行结果：
  8. set @p_inout=1
  9. call sp_demo_inout_parameter(@p_inout) //
  10. 略
  11. select @p_inout;
  12. 略

这个玩应相当于引用传递

函数列表

字符串类 CHARSET(str) //返回字串字符集
返回的是编码.utf-8啥的
CONCAT (string2 [,… ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
mysql字符串第一个字符索引为1
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格
数值类型 ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
SIGN (number2 ) //返回符号,正负或0
SQRT(number2) //开平方
日期类型 ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分

游标

游标就是遍历数据库查询返回的记录的 只读
不能滚动,只能单向移动
避免在已经打开游标的表上更改数据
游标的使用 1.声明游标
  1. DECLARE cursor_name CURSOR FOR SELECT_statement;
2.使用OPEN打开游标
  1. OPEN cursor_name;
3.使用FEICH语句获得下一行数据
  1. FETCH cursor_name INTO variable list;
4.最后释放游标
  1. CLOSE cursor_name;
 
使用示例
  1. DELIMITER $$
  3. DROP PROCEDURE IF EXISTS `test`.`CursorProc` $$
  4. CREATE PROCEDURE `test`.`CursorProc` ()
  5. BEGIN
  6. DECLARE no_more_products, quantity_in_stock INT DEFAULT 0;
  7. DECLARE prd_code VARCHAR(255);
  8. DECLARE cur_product CURSOR FOR SELECT code FROM products; /*First: Delcare a cursor,首先这里对游标进行定义*/
  9. DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_products = 1; /*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/
  11. /* for loggging information 创建个临时表格来保持*/
  12. CREATE TEMPORARY TABLE infologs (
  13. Id int(11) NOT NULL AUTO_INCREMENT,
  14. Msg varchar(255) NOT NULL,
  15. PRIMARY KEY (Id)
  16. );
  18. OPEN cur_product; /*Second: Open the cursor 接着使用OPEN打开游标*/
  19. FETCH cur_product INTO prd_code; /*Third: now you can Fetch the row 把第一行数据写入变量中,游标也随之指向了记录的第一行*/
  21. REPEAT
  23. SELECT quantity INTO quantity_in_stock
  24. FROM products
  25. WHERE code = prd_code;
  27. IF quantity_in_stock < 100 THEN
  28. INSERT INTO infologs(msg)
  29. VALUES (prd_code);
  30. END IF;
  31. FETCH cur_product INTO prd_code;
  33. UNTIL no_more_products = 1
  34. END REPEAT;
  35. CLOSE cur_product; /*Finally: cursor need be closed 用完后记得用CLOSE把资源释放掉*/
  36. SELECT * FROM infologs;
  37. DROP TABLE infologs;
  38. END $$
  40. DELIMITER ;
 
 
 
 

条件处理/错误.异常处理

DECLARE HANDLER
  2. DECLARE handler_action HANDLER
  3. FOR condition_value [, condition_value] ...
  4. statement
  6. handler_action:
  7. CONTINUE
  8. | EXIT
  9. | UNDO
  11. condition_value:
  12. mysql_error_code # sql错误代码
  13. | SQLSTATE [VALUE] sqlstate_value # 标准错误代码
  14. | condition_name # 条件名称,需要declare condition定义
  15. | SQLWARNING # SQLTATE中以01起始的错误
  16. | NOT FOUND # SQLTATE中以02起始的错误,如游标尾巴
  17. | SQLEXCEPTION # SQLTAIE中不是00 01 02开头的错误,其中00是成功执行
1、对于SQLWARNING和NOT FOUND的处理方法就是无视错误继续执行，所以在游标的例子里面如果我们没有对repeat的条件判断的那个值做个no_more_products=1的handler来处理，那么循环就会一直下去。
2、对于SQLEXCEPTION的话，其默认的处理方法是在出现错误的地方就终止掉了。
statement 多行时需要用begin和end括起来
handle_action 标识执行了statement后要执行的动作,包括
    continue 继续
    exit 退出
    undo 撤销
 
例子:这里创建一个对SQLSTATE的代码为'23000'的错误(重复的主键)进行处理的HANDLER，每次发生时我们对变量@x进行增加1
  1. DELIMITER $$
  3. DROP PROCEDURE IF EXISTS `test`.`ConditionProc` $$
  4. CREATE PROCEDURE `test`.`ConditionProc` ()
  5. BEGIN
  6. DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x = @x+1;
  7. INSERT INTO products values(1,default,default,default,default,default);
  8. INSERT INTO products values(1,default,default,default,default,default);
  9. SELECT @x;
  10. END $$
  12. DELIMITER ;
 
DECLARE CONDITION
  1. DECLARE duplicate_key CONDITION FOR SQLSTATE '23000';
  2. DECLARE CONTINUE HANDLER FOR duplicate_key
  3. BEGIN
  4. -- body of handler
  5. END;
就是给具体的 handler for 后面的内容起名字

相关文章