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;