mysql 存储过程实例_mysql存储过程几个案例

案例一

CREATE DEFINER=`csc86`@`%` FUNCTION `getsubmain`() RETURNS varchar(100) CHARSET utf8

begin

declare submain2 int;

set submain2=(select submain from submain_store limit 0,1);

delete from submain_store where submain=submain2;

return concat('shop',submain2);

end

案例二

CREATE DEFINER=`root`@`%` PROCEDURE `immgrateProduct`()

BEGIN

DECLARE done INT DEFAULT 0;

declare increDefault INT DEFAULT 999;

DECLARE oldid VARCHAR(50);

DECLARE rs_cursor CURSOR FOR select id from csc_shop.csc_products;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

open rs_cursor;

cursor_loop:loop

FETCH rs_cursor into oldid;

if done=1 THEN

leave cursor_loop;

end if;

set @increValue = (SELECT newid from csc_shop.csc_products_relation ORDER BY newid desc LIMIT 0,1);

if @increValue is null THEN

insert into csc_shop.csc_products_relation (oldid,newid) values (oldid,increDefault+1);

end if;

if @increValue THEN

insert into csc_shop.csc_products_relation (oldid,newid) values (oldid,@increValue+1);

end if;

end loop cursor_loop;

close rs_cursor;

COMMIT;

END