mysql游标

太过爱你忘了你带给我的痛 2022-09-18 08:57 191阅读 0赞

Mysql从 5.0 开始支持存储过程和 trigger ，给我们喜欢用 mysql 的朋友们更喜欢 mysql 的理由了，语法    
上和PL/SQL 有差别，不过搞过编程的人都知道，语法不是问题，关键是思想，大致了解语法后，就从    
变量定义，循环，判断，游标，异常处理这个几个方面详细学习了。关于游标的用法Mysql 现在提供    
的还很特别，虽然使用起来没有PL/SQL 那么顺手，不过使用上大致上还是一样。

定义游标 ：  
declare fetchSeqCursor cursor for select seqname, value from sys\_sequence;    
使用游标 ：  
open fetchSeqCursor；    
fetch数据：   
fetch fetchSeqCursor into \_seqname, \_value;    
关闭游标：   
close fetchSeqCursor;

不过这都是针对cursor 的操作而已，和 PL/SQL 没有什么区别吧，不过光是了解到这个是根本不足以    
写出Mysql 的 fetch 过程的，还要了解其他的更深入的知识，我们才能真正的写出好的游标使用的 proc    
edure    
  
       首先fetch 离不开循环语句，那么先了解一下循环吧。 我一般使用Loop 和 while 觉得比较清楚，而且代码简单。 这里使用Loop 为例：   
fetchSeqLoop:Loop   
    fetch fetchSeqCursor into \_seqname, \_value;    
end Loop;

现在是死循环，还没有退出的条件，那么在这里和oracle 有区别， Oracle 的 PL/SQL 的指针有个隐性变量%notfound ， Mysql 是通过一个 Error handler 的声明来进行判断的，    
declare continue handler for Not found (do some action);    
在Mysql 里当游标遍历溢出时，会出现一个预定义的 NOT FOUND 的 Error ，我们处理这个 Error 并定义一个continue 的 handler 就可以叻，关于 Mysql Error handler 可以查询 Mysql 手册 定义一个flag ，在 NOT FOUND ，标示 Flag, 在 Loop 里以这个 flag 为结束循环的判断就可以叻。    
  
declare fetchSeqOk boolean; \#\# define the flag for loop judgement    
declare \_seqname varchar(50); \#\# define the varient for store the data    
declare \_value bigint(20);    
declare fetchSeqCursor cursor for select seqname, value from sys\_sequence;\# define the cursor    
declare continue handler for NOT FOUND set fetchSeqOk = true; \#define the continue handler for not found flag    
set fetchSeqOk = false;    
  
open fetchSeqCursor;    
fetchSeqLoop:Loop

fetch fetchSeqCursor into \_seqname, \_value;    
if fetchSeqOk then    
    leave fetchSeqLoop;    
else        
    select \_seqname, \_value;    
end if;    
end Loop;    
close fetchSeqCursor;    
  
这就是一个完整的过程叻，那么会思考的人一般在这里都会思考，如果是这样的话，怎样做嵌套的游 标循环叻，这里可以根据statement block 的 scope 实现叻， Mysql 里通过 begin end 来划分一个 statem ent block，在 block 里定义的变量范围也在这个 block 里，所以关于嵌套的游标循环我们可以多加一 个begin end 来区分他们所对应的error handler( 注意在 Mysql 里同一个 error 的 handler 只能定义一次， 多定义的话，在compile 的过程中会提示里duplicate handler defination ，所以 NOT FOUND 的 hand ler就只能定义一次 ) ，在一个 begin end 里定义这个里面游标的 NOT FOUND handler ，    
  
declare fetchSeqOk boolean; \#\# define the flag for loop judgement    
declare \_seqname varchar(50); \#\# define the varient for store the data    
declare \_value bigint(20);    
declare fetchSeqCursor cursor for select seqname, value from sys\_sequence;\#\# define the cursor    
declare continue handler for NOT FOUND set fetchSeqOk = true; \#define the continue handler for not found flag    
set fetchSeqOk = false;    
  
open fetchSeqCursor;    
fetchSeqLoop:Loop

fetch fetchSeqCursor into \_seqname, \_value;     
if fetchSeqOk then    
  leave fetchSeqLoop;    
else   
  begin    
   declare fetchSeqOk boolean default 'inner';    
   declare cursor2 cursor for select .... from ...;\#\# define the cursor    
   declare continue handler for NOT FOUND set fetchSeqOk = true; \#define the continue handler for n ot    
   set fetchSeqOk = false;    
   open cursor2;    
   fetchloop2 loop    
    if fetchSeqOk then    
    else    
    end if;    
   end loop;    
   close cursor2;    
  end;    
end if;    
end Loop;    
close fetchSeqCursor;    
  
这样就可以轻松实现更多层次的循环了，不过相对oracle 的 PL/SQL 来说， Mysql 现在还不支持动态游    
标的定义，所以很强大的动态拼出SQL 的在游标里还不能做到，不过这完全不影响我对 Mysql 的喜爱程    
度，她就想那羞涩的荷花一样，虽然没有灿烂的色彩，但那简约的色调，清新而不染一丝铅尘的高雅    
，一样吸引着无数的mysql 迷么，正如接天莲叶无穷碧，映日荷花别样红。

附 ：Mysql 也有类似 Oracle 里的 execute immediate 的动态 SQL 的功能，通过这个功能可有多少弥补一    
些动态游标的缺憾叻    
set @sqlStr='select \* from table where condition1 = ?';    
prepare s1 for @sqlStr；    
execute s1 using @condition1; 如果有多个参数用逗号分隔    
deallocate prepare s1; 手工释放，或者是 connection 关闭时， server 自动回收

Sql代码  ![收藏代码][icon_star.png]

1.  **DROP** **PROCEDURE** IF EXISTS \`INITBNSTRENDS\`;  
2.    
3.  **CREATE** DEFINER = \`huisou\`@\`%\` **PROCEDURE** \`INITBNSTRENDS\`()  
4.  **BEGIN**  
5.          **DECLARE** fetchSeqOk BOOLEAN;  
6.          **DECLARE** \_COM\_ID **INT**;  
7.    
8.          **DECLARE** \_COM\_ID\_CURSOR **CURSOR** **FOR** **SELECT** id **FROM** e\_enterprise **WHERE** user\_id **IS** NOT NULL AND status<>'-3';\#企业游标  
9.    
10.         **DECLARE** **CONTINUE** HANDLER **FOR** NOT FOUND **SET** fetchSeqOk = **true**;\#结束标识  
11.         **SET** fetchSeqOk = **FALSE**;  
12.   
13.     **OPEN** \_COM\_ID\_CURSOR;  
14.     \_COM\_ID\_CURSOR:LOOP  
15.                 IF fetchSeqOk **THEN**  
16.                         LEAVE \_COM\_ID\_CURSOR;  
17.                 **ELSE**  
18.                         **FETCH** \_COM\_ID\_CURSOR **INTO** \_COM\_ID;\#把企业ID值覆给\_COM\_ID  
19.                         \#处理资讯  
20.                         **BEGIN**  
21.                                 **DECLARE** \_INFO\_ID **INT**;  
22.                                 **DECLARE** \_INFO\_SHORTSUBJECT **VARCHAR**(255);  
23.                                 **DECLARE** \_INFO\_IMGPATH **VARCHAR**(128);  
24.                                 **DECLARE** \_INFO\_ISSUEDATE DATETIME;  
25.                                 **DECLARE** fetchSeqOk BOOLEAN **DEFAULT** 'inner';   
26.                                 **DECLARE** \_INFO\_CURSOR **CURSOR** **FOR** **SELECT** id,shortsubject,micropic,issuedate **FROM** hc\_info **WHERE** issue\_id=\_COM\_ID AND issuedate>'2012-01-01' AND status<>'-3';  
27.                                 **DECLARE** **CONTINUE** HANDLER **FOR** NOT FOUND **SET** fetchSeqOk = **true**;\#结束标识  
28.                                 **SET** fetchSeqOk = **FALSE**;  
29.                                 **OPEN** \_INFO\_CURSOR;  
30.                                 \_INFO\_CURSOR:LOOP  
31.                                         **FETCH** \_INFO\_CURSOR **INTO** \_INFO\_ID,\_INFO\_SHORTSUBJECT,\_INFO\_IMGPATH,\_INFO\_ISSUEDATE;  
32.                                         IF fetchSeqOk **THEN**  
33.                                                 LEAVE \_INFO\_CURSOR;  
34.                                         **ELSE**  
35.                                                 **INSERT** **INTO** bns\_trends(OBJECT\_ID,OBJECT\_TYPE,SUMMARY,IMG\_PATH,COM\_ID,VOTE\_NUM,COMMEND\_NUM,COMMENT\_NUM,CREATE\_TIME,STATUS)  
36.                                                 **VALUES**(\_INFO\_ID,'3',\_INFO\_SHORTSUBJECT,\_INFO\_IMGPATH,\_COM\_ID,0,0,0,\_INFO\_ISSUEDATE,'1');  
37.                                         **END** IF;  
38.                                 **END** LOOP;  
39.                         **END**;  
40.   
41.                         \#处理供应  
42.                         **BEGIN**  
43.                                 **DECLARE** \_SALE\_ID **INT**;  
44.                                 **DECLARE** \_SALE\_TITLE **VARCHAR**(255);  
45.                                 **DECLARE** \_SALE\_IMGPATH **VARCHAR**(128);  
46.                                 **DECLARE** \_SALE\_ISSUEDATE DATETIME;  
47.                                 **DECLARE** fetchSeqOk BOOLEAN **DEFAULT** 'inner';   
48.                                 **DECLARE** \_SALE\_CURSOR **CURSOR** **FOR** **SELECT** id,title,picurl,issuedate **FROM** e\_product\_sale **WHERE** e\_id=\_COM\_ID AND issuedate>'2012-01-01' AND status<>'-3';  
49.                                 **DECLARE** **CONTINUE** HANDLER **FOR** NOT FOUND **SET** fetchSeqOk = **true**;\#结束标识  
50.                                 **SET** fetchSeqOk = **FALSE**;  
51.                                 **OPEN** \_SALE\_CURSOR;  
52.                                 \_SALE\_CURSOR:LOOP  
53.                                         **FETCH** \_SALE\_CURSOR **INTO** \_SALE\_ID,\_SALE\_TITLE,\_SALE\_IMGPATH,\_SALE\_ISSUEDATE;  
54.                                         IF fetchSeqOk **THEN**  
55.                                                 LEAVE \_SALE\_CURSOR;  
56.                                         **ELSE**                                                  
57.                                                 **INSERT** **INTO** bns\_trends(OBJECT\_ID,OBJECT\_TYPE,SUMMARY,IMG\_PATH,COM\_ID,VOTE\_NUM,COMMEND\_NUM,COMMENT\_NUM,CREATE\_TIME,STATUS)  
58.                                                 **VALUES**(\_SALE\_ID,'1',\_SALE\_TITLE,\_SALE\_IMGPATH,\_COM\_ID,0,0,0,\_SALE\_ISSUEDATE,'1');  
59.                                         **END** IF;  
60.                                 **END** LOOP;  
61.                         **END**;  
62.   
63.                         \#处理求购  
64.                         **BEGIN**  
65.                                 **DECLARE** \_BUY\_ID **INT**;  
66.                                 **DECLARE** \_BUY\_TITLE **VARCHAR**(255);  
67.                                 **DECLARE** \_BUY\_IMGPATH **VARCHAR**(128);  
68.                                 **DECLARE** \_BUY\_ISSUEDATE DATETIME;  
69.                                 **DECLARE** fetchSeqOk BOOLEAN **DEFAULT** 'inner';   
70.                                 **DECLARE** \_BUY\_CURSOR **CURSOR** **FOR** **SELECT** id,title,picurl,issuedate **FROM** e\_product\_buy **WHERE** e\_id=\_COM\_ID AND status<>'-3';  
71.                                 **DECLARE** **CONTINUE** HANDLER **FOR** NOT FOUND **SET** fetchSeqOk = **true**;\#结束标识  
72.                                 **SET** fetchSeqOk = **FALSE**;  
73.                                 **OPEN** \_BUY\_CURSOR;  
74.                                 \_BUY\_CURSOR:LOOP  
75.                                         **FETCH** \_BUY\_CURSOR **INTO** \_BUY\_ID,\_BUY\_TITLE,\_BUY\_IMGPATH,\_BUY\_ISSUEDATE;  
76.                                         IF fetchSeqOk **THEN**  
77.                                                 LEAVE \_BUY\_CURSOR;  
78.                                         **ELSE**                                                  
79.                                                 **INSERT** **INTO** bns\_trends(OBJECT\_ID,OBJECT\_TYPE,SUMMARY,IMG\_PATH,COM\_ID,VOTE\_NUM,COMMEND\_NUM,COMMENT\_NUM,CREATE\_TIME,STATUS)  
80.                                                 **VALUES**(\_BUY\_ID,'1',\_BUY\_TITLE,\_BUY\_IMGPATH,\_COM\_ID,0,0,0,\_BUY\_ISSUEDATE,'1');  
81.                                         **END** IF;  
82.                                 **END** LOOP;  
83.                         **END**;  
84.                 **END** IF;  
85.         **END** LOOP;  
86. **END**;

[icon_star.png]: /images/20220828/9de6513051aa4d0dafada34f9c82f1ff.png