MySQL存储过程游标使用实例详解

本文章来给各位同学详细介绍关于在MySQL存储过程游标使用实例,包括简单游标使用与游标循环跳出等方法,希望此教程对各位同学有所帮助.

例1,一个简单存储过程游标实例,代码如下:

1. DELIMITER$$
2. DROPPROCEDUREIFEXISTSgetUserInfo$$
3. CREATEPROCEDUREgetUserInfo(indate_daydatetime)
4. --
5. --实例
6. --存储过程名为：getUserInfo
7. --参数为：date_day日期格式:2008-03-08
8. --
9. BEGIN
10. declare_userNamevarchar(12);--用户名
11. declare_chineseint;--语文
12. declare_mathint;--数学
13. declaredoneint;
14. --定义游标
15. DECLARErs_cursorCURSORFORSELECTusername,chinese,mathfromuserInfowheredatediff(createDate,date_day)=0;
16. DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
17. --获取昨天的日期
18. ifdate_dayisnullthen
19. setdate_day=date_add(now(),interval-1day);
20. endif;
21. openrs_cursor;
22. cursor_loop:loop
23. FETCHrs_cursorinto_userName,_chinese,_math;--取数据
25. ifdone=1then
26. leavecursor_loop;
27. endif;
28. --更新表
29. updateinfoSumsettotal=_chinese+_mathwhereUserName=_userName;
30. endloopcursor_loop;
31. closers_cursor;
33. END$$
34. DELIMITER;

例2,存储过程游标循环跳出现

在MySQL的存储过程中,游标操作时,需要执行一个conitnue的操作.众所周知,MySQL中的游标循环操作常用的有三种,LOOP,REPEAT,WHILE.三种循环,方式大同小异.以前从没用过,所以记下来,方便以后查阅.

1.REPEAT,代码如下:

1. REPEAT
2. Statements;
3. UNTILexpression
4. ENDREPEAT
5. demo
6. DECLAREnumINT;
7. DECLAREmy_stringVARCHAR(255);
8. REPEAT
9. SETmy_string=CONCAT(my_string,num,',');
10. SETnum=num+1;
11. UNTILnum<5
12. ENDREPEAT;

2.WHILE,代码如下:

1. WHILEexpressionDO
2. Statements;
3. ENDWHILE
4. demo
5. DECLAREnumINT;
6. DECLAREmy_stringVARCHAR(255);
7. SETnum=1;
8. SETstr='';
9. WHILEnum<span>10DO
10. SETmy_string=CONCAT(my_string,num,',');
11. SETnum=num+1;
12. ENDWHILE;

3.LOOP(这里面有非常重要的ITERATE,LEAVE),代码如下:

1. DECLAREnumINT;
2. DECLAREstrVARCHAR(255);
3. SETnum=1;
4. SETmy_string='';
5. loop_label:LOOP
6. IFnum<10THEN
7. LEAVEloop_label;
8. ENDIF;
9. SETnum=num+1;
10. IF(nummod3)THEN
11. ITERATEloop_label;
12. ELSE
13. SETmy_string=CONCAT(my_string,num,',');
14. ENDIF;
15. ENDLOOP;

PS:可以这样理解ITERATE就是我们程序中常用的contiune,而ITERATE就是break.当然在MySQL存储过程,需要循环结构有个名称,其他都是一样的.

例3,mysql 存储过程中使用多游标,先创建一张表,插入一些测试数据,代码如下:

1. DROPTABLEIFEXISTSnetingcn_proc_test;
2. CREATETABLE`netingcn_proc_test`(
3. `id`INTEGER(11)NOTNULLAUTO_INCREMENT,
4. `name`VARCHAR(20),
5. `password`VARCHAR(20),
6. PRIMARYKEY(`id`)
7. )ENGINE=InnoDB;
8. insertintonetingcn_proc_test(name,password)values
9. ('procedure1','pass1'),
10. ('procedure2','pass2'),
11. ('procedure3','pass3'),
12. ('procedure4','pass4');下面就是一个简单存储过程的例子：
13. dropprocedureIFEXISTStest_proc;
14. delimiter//
15. createproceduretest_proc()
16. begin
17. --声明一个标志done，用来判断游标是否遍历完成
18. DECLAREdoneINTDEFAULT0;
19. --声明一个变量，用来存放从游标中提取的数据
20. --特别注意这里的名字不能与由游标中使用的列明相同，否则得到的数据都是NULL
21. DECLAREtnamevarchar(50)DEFAULTNULL;
22. DECLAREtpassvarchar(50)DEFAULTNULL;
23. --声明游标对应的SQL语句
24. DECLAREcurCURSORFOR
25. selectname,passwordfromnetingcn_proc_test;
26. --在游标循环到最后会将done设置为1
27. DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
28. --执行查询
29. opencur;
30. --遍历游标每一行
31. REPEAT
32. --把一行的信息存放在对应的变量中
33. FETCHcurINTOtname,tpass;
34. ifnotdonethen
35. --这里就可以使用tname，tpass对应的信息了
36. selecttname,tpass;
37. endif;
38. UNTILdoneENDREPEAT;
39. CLOSEcur;
40. end
41. //
42. delimiter;
43. --执行存储过程
44. calltest_proc();

需要注意的是变量的声明、游标的声明和HANDLER声明的顺序不能搞错，必须是先声明变量，再申明游标，最后声明HANDLER。上述存储过程的例子中只使用了一个游标，那么如果要使用两个或者更多游标怎么办，其实很简单，可以这么说，一个怎么用两个就是怎么用的,例子如下:

1. dropprocedureIFEXISTStest_proc_1;
2. delimiter//
3. createproceduretest_proc_1()
4. begin
5. DECLAREdoneINTDEFAULT0;
6. DECLAREtidint(11)DEFAULT0;
7. DECLAREtnamevarchar(50)DEFAULTNULL;
8. DECLAREtpassvarchar(50)DEFAULTNULL;
9. DECLAREcur_1CURSORFOR
10. selectname,passwordfromnetingcn_proc_test;
11. DECLAREcur_2CURSORFOR
12. selectid,namefromnetingcn_proc_test;
13. DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
14. opencur_1;
15. REPEAT
16. FETCHcur_1INTOtname,tpass;
17. ifnotdonethen
18. selecttname,tpass;
19. endif;
20. UNTILdoneENDREPEAT;
21. CLOSEcur_1;
22. --注意这里，一定要重置done的值为0
23. setdone=0;
24. opencur_2;
25. REPEAT
26. FETCHcur_2INTOtid,tname;
27. ifnotdonethen
28. selecttid,tname;
29. endif;
30. UNTILdoneENDREPEAT;
31. CLOSEcur_2;
32. end
33. //
34. delimiter;
35. calltest_proc_1();

上述代码和第一个例子中基本一样，就是多了一个游标声明和遍历游标,这里需要注意的是，在遍历第二个游标前使用了set done = 0,因为当第一个游标遍历玩后其值被handler设置为1了,如果不用set把它设置为 0,那么第二个游标就不会遍历了,当然好习惯是在每个打开游标的操作前都用该语句,确保游标能真正遍历,当然还可以使用begin语句块嵌套的方式来处理多个游标,例如:

1. dropprocedureIFEXISTStest_proc_2;
2. delimiter//
3. createproceduretest_proc_2()
4. begin
5. DECLAREdoneINTDEFAULT0;
6. DECLAREtnamevarchar(50)DEFAULTNULL;
7. DECLAREtpassvarchar(50)DEFAULTNULL;
8. DECLAREcur_1CURSORFOR
9. selectname,passwordfromnetingcn_proc_test;
10. DECLAREcur_2CURSORFOR
11. selectid,namefromnetingcn_proc_test;
12. DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
13. opencur_1;
14. REPEAT
15. FETCHcur_1INTOtname,tpass;
16. ifnotdonethen
17. selecttname,tpass;
18. endif;
19. UNTILdoneENDREPEAT;
20. CLOSEcur_1;
21. begin
22. DECLAREdoneINTDEFAULT0;
23. DECLAREtidint(11)DEFAULT0;
24. DECLAREtnamevarchar(50)DEFAULTNULL;
25. DECLAREcur_2CURSORFOR
26. selectid,namefromnetingcn_proc_test;
27. DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
28. opencur_2; //phpfensi.com
29. REPEAT
30. FETCHcur_2INTOtid,tname;
31. ifnotdonethen
32. selecttid,tname;
33. endif;
34. UNTILdoneENDREPEAT;
35. CLOSEcur_2;
36. end;
37. end
38. //
39. delimiter;
40. calltest_proc_2();