一、存储过程的变量
①变量的声明:
DECLARE var_name[,...] type [DEFAULT value]
这个语句被用来声明局部变量。要给变量提供一个默认值,需要包含一个DEFAULT子句。值可以被指定为一个表达式,不需要为一个常数。如果没有DEFAULT子句,初始值为NULL。
局部变量的作用范围在它被声明的BEGIN ... END块内。它可以被用在嵌套的块中,除了那些用相同名字声明变量的块。
②变量赋值:A:使用SET语句:
SET var_name = expr [, var_name = expr]...
也可以用语句代替SET来为用户变量分配一个值。在这种情况下,分配符必须为:=而不能用=,因为在非SET语句中=被视为一个比较 操作符,如下所示:
mysql> SET @t1=0, @t2=0,@t3=0;
mysql> SELECT @t1:=0,@t2:=0,@t3:=0;
对于使用 select 语句为变量赋值的情况,若返回结果为空 ,即没有记录,此时变量的值为上一次变量赋值时的值,如 果没有对变量赋过值,则为 NULL 。 B:使用SELECT....INTO语句:
SELECT col_name[,...] INTO var_name[,...]table_expr 这个SELECT语法把选定的列直接存储到变量。因此,只有单一的行可以被取回。
例:SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
二、BEGIN....END复合语句:[begin_label:] BEGIN
[statement_list]
END [end_label]
存储子程序可以使用BEGIN ... END复合语句来包含多个语句。statement_list代表一个或多个语句的列表。statement_list之内每个语句都必须用分号(;)来结尾。
复合语句可以被标记。除非begin_label存在,否则end_label不能被给出,并且如果二者都存在,他们必须是同样的。
三、流程控制:
① IF语句:
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
IF实现了一个基本的条件构造。如果search_condition求值为真,相应的SQL语句列表被执行。如果没有search_condition匹配,在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。
例: student表:
mysql> select * from student;
+---------------+---------------+----------------------+-----------+----+
| nickname | password | email | address | id |
+---------------+---------------+----------------------+-----------+----+
| elgin | 12345 | dddffg@qq.com | xinbei9-2 | 1 |
| elgin | 12345 | dddffg@qq.com | xinbei9-2 | 2 |
| seth | 12345 | dddffg@qq.com | xinbei9-2 | 3 |
| 3344 | ddff3322 | dddffg@qq.com | xinbei9-2 | 4 |
| ddddfff | procedure1234 | dddffg@qq.com | xinbei9-2 | 5 |
| jdbcName | jdbc1234 | dddffg@qq.com | xinbei9-2 | 6 |
| testProcedure | procedure1234 | procedure123@163.com | china.js | 7 |
| jdbcName | jdbc1234 | jdbc1234@gmail.com | china.js | 9 |
+---------------+---------------+----------------------+-----------+----+
8 rows in set
使用流程控制的存储过程:
mysql> create procedure testif(sid varchar(10))
-> begin
-> if sid='01' then
-> select * from student where id=1;
-> elseif sid='02' then
-> select * from student where id=2;
-> else
-> select * from student where id=7;
-> end if;
-> end;
Query OK, 0 rows affected
调用结果:
mysql> call testif('01');
+----------+----------+---------------+-----------+----+
| nickname | password | email | address | id |
+----------+----------+---------------+-----------+----+
| elgin | 12345 | dddffg@qq.com | xinbei9-2 | 1 |
+----------+----------+---------------+-----------+----+
1 row in set
Query OK, 0 rows affected
mysql> call testif('02');
+----------+----------+---------------+-----------+----+
| nickname | password | email | address | id |
+----------+----------+---------------+-----------+----+
| elgin | 12345 | dddffg@qq.com | xinbei9-2 | 2 |
+----------+----------+---------------+-----------+----+
1 row in set
Query OK, 0 rows affected
mysql> call testif(null);
+---------------+---------------+----------------------+----------+----+
| nickname | password | email | address | id |
+---------------+---------------+----------------------+----------+----+
| testProcedure | procedure1234 | procedure123@163.com | china.js | 7 |
+---------------+---------------+----------------------+----------+----+
1 row in set
Query OK, 0 rows affected
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Or:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
依旧使用student表:
mysql> create procedure testcase(sid int)
-> begin
-> declare x int;
-> set x=sid+1;
-> case
-> when 10 then select * from student where id=5;
-> when 10 then select * from student where id=1;
-> else select * from student where id=7;
-> end case;
-> end;
Query OK, 0 rows affected
调用结果:
mysql> call testcase(9);
+----------+----------+---------------+-----------+----+
| nickname | password | email | address | id |
+----------+----------+---------------+-----------+----+
| elgin | 12345 | dddffg@qq.com | xinbei9-2 | 1 |
+----------+----------+---------------+-----------+----+
1 row in set
Query OK, 0 rows affected
mysql> call testcase(10);
+----------+---------------+---------------+-----------+----+
| nickname | password | email | address | id |
+----------+---------------+---------------+-----------+----+
| ddddfff | procedure1234 | dddffg@qq.com | xinbei9-2 | 5 |
+----------+---------------+---------------+-----------+----+
1 row in set
Query OK, 0 rows affected
mysql> call testcase(6);
+---------------+---------------+----------------------+----------+----+
| nickname | password | email | address | id |
+---------------+---------------+----------------------+----------+----+
| testProcedure | procedure1234 | procedure123@163.com | china.js | 7 |
+---------------+---------------+----------------------+----------+----+
1 row in set
③ WHILE 语句:
示例:
mysql> create procedure testwhile()
-> begin
-> declare x int;
-> set x=5;
-> while x<8 do
-> insert into student(nickname,password,email,address)values('dfg',x,'dfg@gmail.com','china.js');
-> set x=x+1;
-> end while;
-> end;
Query OK, 0 rows affected
调用结果:
mysql> call testwhile();
Query OK, 1 row affected
mysql> select * from student
-> ;
+---------------+---------------+----------------------+-----------+----+
| nickname | password | email | address | id |
+---------------+---------------+----------------------+-----------+----+
| elgin | 12345 | dddffg@qq.com | xinbei9-2 | 1 |
| elgin | 12345 | dddffg@qq.com | xinbei9-2 | 2 |
| seth | 12345 | dddffg@qq.com | xinbei9-2 | 3 |
| 3344 | ddff3322 | dddffg@qq.com | xinbei9-2 | 4 |
| ddddfff | procedure1234 | dddffg@qq.com | xinbei9-2 | 5 |
| jdbcName | jdbc1234 | dddffg@qq.com | xinbei9-2 | 6 |
| testProcedure | procedure1234 | procedure123@163.com | china.js | 7 |
| jdbcName | jdbc1234 | jdbc1234@gmail.com | china.js | 9 |
| dfg | 5 | dfg@gmail.com | china.js | 10 |
| dfg | 6 | dfg@gmail.com | china.js | 11 |
| dfg | 7 | dfg@gmail.com | china.js | 12 |
+---------------+---------------+----------------------+-----------+----+
11 rows in set
可以看到插入了3条记录
④ LOOP语句:
LOOP允许某特定语句或语句群的重复执行,实现一个简单的循环构造。在循环内的语句一直重复直到循环被退出,退出通常伴随着一个LEAVE 语句。
示例:
mysql> create procedure testloop()
-> begin
-> declare x int;
-> set x=5;
-> insertloop:loop
-> insert into student(nickname,password,email,address)values('dfgloop',x,'dfg@gmail.com','china.js');
-> set x=x+1;
-> if x>=8 then
-> leave insertloop;
-> end if;
-> end loop;
-> end;
Query OK, 0 rows affected
调用结果:
mysql> call testloop();
Query OK, 1 row affected
mysql> select * from student;
+---------------+---------------+----------------------+-----------+----+
| nickname | password | email | address | id |
+---------------+---------------+----------------------+-----------+----+
| elgin | 12345 | dddffg@qq.com | xinbei9-2 | 1 |
| elgin | 12345 | dddffg@qq.com | xinbei9-2 | 2 |
| seth | 12345 | dddffg@qq.com | xinbei9-2 | 3 |
| 3344 | ddff3322 | dddffg@qq.com | xinbei9-2 | 4 |
| ddddfff | procedure1234 | dddffg@qq.com | xinbei9-2 | 5 |
| jdbcName | jdbc1234 | dddffg@qq.com | xinbei9-2 | 6 |
| testProcedure | procedure1234 | procedure123@163.com | china.js | 7 |
| jdbcName | jdbc1234 | jdbc1234@gmail.com | china.js | 9 |
| dfg | 5 | dfg@gmail.com | china.js | 10 |
| dfg | 6 | dfg@gmail.com | china.js | 11 |
| dfg | 7 | dfg@gmail.com | china.js | 12 |
| dfgloop | 5 | dfg@gmail.com | china.js | 13 |
| dfgloop | 6 | dfg@gmail.com | china.js | 14 |
| dfgloop | 7 | dfg@gmail.com | china.js | 15 |
+---------------+---------------+----------------------+-----------+----+
14 rows in set
四、异常处理 Error Handling
声明异常处理的语法:
DECLARE
{ EXIT | CONTINUE } HANDLER FOR
{ error-number | { SQLSTATE error-string } | condition } SQL statement
① CONTINUE异常处理示例:
在上述student表的基础上
存储过程:
mysql> create procedure testhandler()
-> begin
-> declare CONTINUE HANDLER FOR SQLSTATE '23000' set @x2=3;
-> set @x=1;
-> insert into student(nickname,password,email,address,id)values('dfgloop','330335','dfg@gmail.com','china.js',15);
-> set @x=3;
-> end;
Query OK, 0 rows affected
调用之后结果:
mysql> call testhandler();
Query OK, 0 rows affected
mysql> select @x,@x2;
+----+-----+
| @x | @x2 |
+----+-----+
| 3 | 3 |
+----+-----+
1 row in set
执行流程分析:
第一步执行 set @x=1; 之后尝试执行insert 语句 ,发生主键冲突错误插入失败并执行错误处理语句 set @x2=3 ,之后继续执行 set @x=3 语句。
可以看出,由于是 CONTINUE异常处理,发生错误之后,执行错误处理语句,之后继续回继续执行下面的语句 (本例为 set @x=3);
② EXIT异常处理示例:
存储过程:
mysql> create procedure testhandler1()
-> begin
-> declare EXIT HANDLER FOR SQLSTATE '23000' set @x2=3;
-> set @x=1;
-> insert into student(nickname,password,email,address,id)values('dfgloop','330335','dfg@gmail.com','china.js',15);
-> set @x=3;
-> end;
Query OK, 0 rows affected
调用之后结果:
mysql> call testhandler1();
Query OK, 0 rows affected
mysql> select @x,@x2;
+----+-----+
| @x | @x2 |
+----+-----+
| 1 | 3 |
+----+-----+
1 row in set
第一步执行 set @x=1; 之后尝试执行insert 语句 ,发生主键冲突错误插入失败并执行错误处理语句 set @x2=3 ,之后由于出错结束本次执行。
可以看出,由于是EXIT异常处理,发生错误之后,执行错误处理语句,之后退出,并不继续执行后续的语句;
五 游标声明游标
DECLARE cursor_nameCURSOR FORselect_statement
这个语句声明一个光标。也可以在子程序中定义多个光标,但是一个块中的每一个光标必须有唯一的名字。
打开游标
OPEN cursor_name
这个语句打开先前声明的光标。
游标FETCH
FETCH cursor_nameINTO var_name [, var_name]...
这个语句用指定的打开光标读取下一行(如果有下一行的话),
并且前进光标指针。
关闭游标CLOSE
CLOSE cursor_name
这个语句关闭先前打开的光标。
如果未被明确地关闭,光标在它被声明的复合语句的末尾被关闭。
游标的特性:
READ ONLY 只读,只能取值而不能赋值;
NOT SCROOLABLE 不可回滚,只能顺序读取;
ASENSITIVE 敏感,不能在已经打开游标的表上执行update事务;
mysql> create procedure testcur(out val int)
-> begin
-> declare a,b int;
-> declare cur1 cursor for select id from student;
-> declare continue handler for not found set b=1;
-> open cur1;
-> repeat fetch cur1 into a;
-> until b=1 end repeat;
-> close cur1;
-> set val=a;
-> end;
Query OK, 0 rows affected
调用并查看结果:
mysql> call testcur(@x);
Query OK, 0 rows affected
mysql> select @x;
+----+
| @x |
+----+
| 15 |
+----+
1 row in set
容易出现的错误:1328 - Incorrect number of FETCH variables
出现这个错误使用的存储过程语句:
mysql> create procedure testcur(out val int)
-> begin
-> declare a,b int;
-> declare cur1 cursor for select * from student;
-> declare continue handler for not found set b=1;
-> open cur1;
-> repeat fetch cur1 into a;
-> until b=1 end repeat;
-> close cur1;
-> set val=a;
-> end;
Query OK, 0 rows affected
上述定义游标的语句中 查询语句使用的时 select * ,即从student表中查询出所有记录 ,而在fetch的时候 fetch cur1 into a ,这里接收查询结果的变量只有a ,而查询结果却有5个字段,多以会出现上面的错误: Incorrect number of FETCH variables ,如果想要接收多个结果值,可以在此增加接收查询结果的变量