MySQL游标

 

       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 自动回收

 

DROP PROCEDURE IF EXISTS `INITBNSTRENDS`;

CREATE DEFINER = `huisou`@`%` PROCEDURE `INITBNSTRENDS`()
BEGIN
		DECLARE fetchSeqOk BOOLEAN;
		DECLARE _COM_ID INT;

		DECLARE _COM_ID_CURSOR CURSOR FOR SELECT id FROM e_enterprise WHERE user_id IS NOT NULL AND status<>'-3';#企业游标

		DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchSeqOk = true;#结束标识
		SET fetchSeqOk = FALSE;

    OPEN _COM_ID_CURSOR;
    _COM_ID_CURSOR:LOOP
				IF fetchSeqOk THEN
						LEAVE _COM_ID_CURSOR;
				ELSE
						FETCH _COM_ID_CURSOR INTO _COM_ID;#把企业ID值覆给_COM_ID
						#处理资讯
						BEGIN
								DECLARE _INFO_ID INT;
								DECLARE _INFO_SHORTSUBJECT VARCHAR(255);
								DECLARE _INFO_IMGPATH VARCHAR(128);
								DECLARE _INFO_ISSUEDATE DATETIME;
								DECLARE fetchSeqOk BOOLEAN DEFAULT 'inner'; 
								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';
								DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchSeqOk = true;#结束标识
								SET fetchSeqOk = FALSE;
								OPEN _INFO_CURSOR;
								_INFO_CURSOR:LOOP
										FETCH _INFO_CURSOR INTO _INFO_ID,_INFO_SHORTSUBJECT,_INFO_IMGPATH,_INFO_ISSUEDATE;
										IF fetchSeqOk THEN
												LEAVE _INFO_CURSOR;
										ELSE
												INSERT INTO bns_trends(OBJECT_ID,OBJECT_TYPE,SUMMARY,IMG_PATH,COM_ID,VOTE_NUM,COMMEND_NUM,COMMENT_NUM,CREATE_TIME,STATUS)
												VALUES(_INFO_ID,'3',_INFO_SHORTSUBJECT,_INFO_IMGPATH,_COM_ID,0,0,0,_INFO_ISSUEDATE,'1');
										END IF;
								END LOOP;
						END;

						#处理供应
						BEGIN
								DECLARE _SALE_ID INT;
								DECLARE _SALE_TITLE VARCHAR(255);
								DECLARE _SALE_IMGPATH VARCHAR(128);
								DECLARE _SALE_ISSUEDATE DATETIME;
								DECLARE fetchSeqOk BOOLEAN DEFAULT 'inner'; 
								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';
								DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchSeqOk = true;#结束标识
								SET fetchSeqOk = FALSE;
								OPEN _SALE_CURSOR;
								_SALE_CURSOR:LOOP
										FETCH _SALE_CURSOR INTO _SALE_ID,_SALE_TITLE,_SALE_IMGPATH,_SALE_ISSUEDATE;
										IF fetchSeqOk THEN
												LEAVE _SALE_CURSOR;
										ELSE												
												INSERT INTO bns_trends(OBJECT_ID,OBJECT_TYPE,SUMMARY,IMG_PATH,COM_ID,VOTE_NUM,COMMEND_NUM,COMMENT_NUM,CREATE_TIME,STATUS)
												VALUES(_SALE_ID,'1',_SALE_TITLE,_SALE_IMGPATH,_COM_ID,0,0,0,_SALE_ISSUEDATE,'1');
										END IF;
								END LOOP;
						END;

						#处理求购
						BEGIN
								DECLARE _BUY_ID INT;
								DECLARE _BUY_TITLE VARCHAR(255);
								DECLARE _BUY_IMGPATH VARCHAR(128);
								DECLARE _BUY_ISSUEDATE DATETIME;
								DECLARE fetchSeqOk BOOLEAN DEFAULT 'inner'; 
								DECLARE _BUY_CURSOR CURSOR FOR SELECT id,title,picurl,issuedate FROM e_product_buy WHERE e_id=_COM_ID AND status<>'-3';
								DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchSeqOk = true;#结束标识
								SET fetchSeqOk = FALSE;
								OPEN _BUY_CURSOR;
								_BUY_CURSOR:LOOP
										FETCH _BUY_CURSOR INTO _BUY_ID,_BUY_TITLE,_BUY_IMGPATH,_BUY_ISSUEDATE;
										IF fetchSeqOk THEN
												LEAVE _BUY_CURSOR;
										ELSE												
												INSERT INTO bns_trends(OBJECT_ID,OBJECT_TYPE,SUMMARY,IMG_PATH,COM_ID,VOTE_NUM,COMMEND_NUM,COMMENT_NUM,CREATE_TIME,STATUS)
												VALUES(_BUY_ID,'1',_BUY_TITLE,_BUY_IMGPATH,_COM_ID,0,0,0,_BUY_ISSUEDATE,'1');
										END IF;
								END LOOP;
						END;
				END IF;
		END LOOP;
END;

 

评论

1 楼 dy.f 2012-10-27  

写得很好，通俗易懂。
想问下“DECLARE fetchSeqOk BOOLEAN DEFAULT 'inner';”为什么默认值是'inner'？