MySQL存储过程的declare声明变量必须放在最前面
复杂异常处理
第一次正常,第二次返回-1
游标.
恩..会写过程了...
这回像DBA了..
复杂异常处理
- drop table if exists user;
- CREATE TABLE `user` (
- `userid` bigint(20) NOT NULL DEFAULT '0',
- `balance` int(11) DEFAULT NULL,
- `checksum` varchar(32) DEFAULT NULL,
- PRIMARY KEY (`userid`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- drop procedure if exists t;
- delimiter $$
- create procedure t(pUserid bigint,pBalance int,pChecksum varchar(32))
- begin
- declare vResult smallint default 0;
- DECLARE EXIT HANDLER FOR SQLEXCEPTION
- begin
- rollback;
- set vResult:=-1;
- select vResult;
- end;
- insert into user values(pUserid,pBalance,pChecksum);
- select * from user;
- end $$
- delimiter ;
- call t(1,1100,'checksum');
- call t(1,1100,'checksum');
第一次正常,第二次返回-1
游标.
- drop procedure if exists t2;
- delimiter $$
- create procedure t2()
- begin
- declare done int default 0;
- declare vResult int default 0;
- declare vUserID bigint;
- declare vBalance int;
- declare pChecksum varchar(32);
- declare cur_test CURSOR for select * from user ;
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
- open cur_test;
- repeat
- fetch cur_test into vUserID, vBalance,pChecksum;
- if done !=1 then
- set vResult:=vResult+vBalance;
- end if;
- until done end repeat;
- close cur_test;
- select vResult;
- end$$
- delimiter ;
- call t2();
恩..会写过程了...
这回像DBA了..
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-2120167/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-2120167/