mysql存儲過程+游標的應用實例5/17

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 ;