上和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;