全部博文(471)
分类:
2012-03-22 13:28:42
原文地址:mysql游标使用经典实例2 作者:sss0213
DELIMITER $$
DROP PROCEDURE IF EXISTS `testdb`.`test` $$
CREATE PROCEDURE `test`(in cprdt1 varchar(30))
BEGIN
DECLARE done INT DEFAULT 0;
declare done1 INT DEFAULT 0;
declare tprdt varchar(200);
declare prdt1 varchar(30) ;
declare prdt2 varchar(30);
declare inta int;
declare curbom cursor for select prdt from sto_boms where flag='1' and cprdt =prdt1;
declare curbom2 cursor for select prdt from sto_boms where flag='1' and cprdt =prdt2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1, done1 = 1;
set tprdt="(";
set inta=0;
set prdt1=cprdt1;
open curbom;
fetch curbom into prdt1;
while done=0 do
if prdt1<>"" then
set tprdt=concat(tprdt,"'",prdt1,"',");
set prdt2=prdt1;
end if ;
/*open curbom2;
fetch curbom2 into prdt2;
while done1=0 do
if prdt2<>"" then
set tprdt=concat(tprdt,"'",prdt2,"',");
end if ;
fetch curbom2 into prdt2;
end while;
close curbom2; */
fetch curbom into prdt1;
end while;
close curbom;
set tprdt=concat(tprdt,")");
select tprdt;
END $$
DELIMITER ;