MySQL存储过程学习实践

首先引用：http://tech.e800.com.cn/articles/2009/87/1249616096147_1.html

 

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 cursor into _seqname, _value;
关闭游标
close fetchSeqCursor;
不过这都是针对cursor的操作而已，和PL/SQL没有什么区别吧，不过光是了解到这个是根本不足以
写出Mysql的fetch过程的，还要了解其他的更深入的知识，我们才能真正的写出好的游标使用的procedure

首先fetch离不开循环语句，那么先了解一下循环吧。
我一般使用Loop和while觉得比较清楚，而且代码简单。
这里使用Loop为例
fetchSeqLoop:Loop
fetch cursor 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
if fetchSeqOk then
leave fetchSeqLoop;
else
fetch cursor into _seqname, _value;

select _seqname, _value;
end if;

end Loop;
close fetchSeqCursor;

这就是一个完整的过程叻，那么会思考的人一般在这里都会思考，如果是这样的话，怎样做嵌套的游
标循环叻，这里可以根据statement block的scope实现叻，Mysql里通过begin end来划分一个statement block，在block里定义的变量范围也在这个block里，所以关于嵌套的游标循环我们可以多加一个begin end来区分他们所对应的error handler(注意在Mysql里同一个error的handler只能定义一次，多定义的话，在compile的过程中会提示里duplicate handler defination，所以NOT FOUND的handler就只能定义一次)，在一个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
if fetchSeqOk then
leave fetchSeqLoop;
else
fetch cursor into _seqname, _value; 
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自动回收。

下面给出我自己写的一个比较复杂的跨数据库的存储过程，实现的业务不多说了，看代码：

主要学习了：http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html

DROP PROCEDURE IF EXISTS p1;
CREATE PROCEDURE p1()
BEGIN
	DECLARE userIdVarchar1 VARCHAR(255);
	DECLARE userIdInt1 INTEGER;
	DECLARE fetchFlag1 TINYINT DEFAULT 1;
	DECLARE cursor1 CURSOR FOR SELECT fqt.users.userId FROM fqt.users WHERE classId<>'' LIMIT 10;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchFlag1=0;
	OPEN cursor1;
	cursor1Loop:LOOP
		IF fetchFlag1=0 THEN LEAVE cursor1Loop;
		ELSE
			FETCH cursor1 INTO userIdVarchar1;
			SELECT jchome.jchome_member.uid INTO userIdInt1 FROM jchome.jchome_member WHERE jchome.jchome_member.username=userIdVarchar1;
			BEGIN
				DECLARE userIdVarchar2 VARCHAR(255);
				DECLARE userIdInt2 INTEGER;
				DECLARE fetchFlag2 TINYINT DEFAULT 1;
				DECLARE statusFlag VARCHAR(30) DEFAULT 'OK';
				DECLARE cursor2 CURSOR FOR SELECT fqt.users.userId FROM fqt.users WHERE fqt.users.classId =(SELECT u.classId FROM fqt.users AS u WHERE u.userId=userIdVarchar1) AND fqt.users.userId<>userIdVarchar1;
				DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchFlag2=0;
				DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET statusFlag='Duplicate Entry';
				OPEN cursor2;
				cursor2Loop:LOOP
					IF fetchFlag2=0 THEN LEAVE cursor2Loop;
					ELSE
						FETCH cursor2 INTO userIdVarchar2;
						SELECT jchome.jchome_member.uid INTO userIdInt2 FROM jchome.jchome_member WHERE jchome.jchome_member.username=userIdVarchar2;
						IF userIdInt2 IS NULL THEN LEAVE cursor2Loop;
						ELSE
							IF statusFlag<>'Duplicate Entry' THEN
								SET statusFlag='OK';
								INSERT INTO jchome.jchome_friend(uid,fuid,fusername,status,gid,note,num,dateline) VALUES (userIdInt1,userIdInt2,userIdVarchar2,1,6,'test',0,0);
								SELECT userIdInt1,userIdInt2,userIdVarchar2;
							ELSE
								SELECT 'Duplicate Entry';
							END IF;
						END IF;
					END IF;
				END LOOP;
				CLOSE cursor2;
			END;
		END IF;
	END LOOP;
	CLOSE cursor1;
END;

CALL p1();

 对于错误的DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'

参考：http://dev.mysql.com/doc/refman/5.1/zh/error-handling.html

附录B：错误代码和消息

 

中途想用cast函数将varchar转成mediumint，结果报错，后来查看http://dev.mysql.com/doc/refman/5.1/zh/functions.html#cast-functions发现

• CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name)

CAST() 和CONVERT() 函数可用来获取一个类型的值，并产生另一个类型的值。

这个类型 可以是以下值其中的 一个： 

•  
  • BINARY[(N)]
  • CHAR[(N)]
  • DATE
  • DATETIME
  • DECIMAL
  • SIGNED [INTEGER]
  • TIME
  • UNSIGNED [INTEGER]