mysql 游标使用实例_mysql游标使用经典实例2

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 ;