mysql 存储过程 预处理语句 mysql存储过程写法案例

mysql 编写存储过程实例

CREATE PROCEDURE `test`(out return_val int)
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
begin
      DECLARE a,b,c,d char(100);
      DECLARE cur_1 CURSOR for select des from t ;
      DECLARE continue handler for sqlstate '02000' set b=1;
      select count(*) into d from t;
      open cur_1;
       set c=0;
       repeat
         fetch cur_1 into a;
        set c=c+1;
until c=d
       end repeat;
       close cur_1;
       set return_val=c;
    end;




CREATE PROCEDURE `test2`()
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
begin
      declare b int default 0;
      DECLARE a,c char(10);
      DECLARE cur_1 CURSOR for select des from t ;
      DECLARE continue handler for not found set b=1;
       open cur_1;
       set c=0;
       repeat
         fetch cur_1 into a;
         if b<>1 then
         set c=c+1;
         insert into gg(dess,flooo) values(c,a);
         end if;
until b=1
       end repeat;
       close cur_1;
      select c;
    end;