mysql 存储过程 基础例子

DELIMITER //  -- 定义分隔符，防止作为sql语句执行drop procedure if exists pr_add;create procedure pr_add( )begin   declare c int;   set c = 2;   if c = 1 then    insert into ONLINE_USER_INFO values(1,'admin','厉害了','');   else delete from ONLINE_USER_INFO where user_code = 1;   end if;   while c < 10 do     insert into ONLINE_USER_INFO values(c+2,'admin','厉害了','');    set c = c + 1;   end while;   test_loop:loop   delete from ONLINE_USER_INFO where user_code = c-2;   set c = c - 1;   if c < 8 then     leave test_loop;   end if;   end loop;end //   -- 必须，否则创建存储过程失败DELIMITER ;call pr_add();

2.事务回滚

DELIMITER //  -- 定义分隔符，防止作为sql语句执行drop procedure if exists pr_add;create procedure pr_add( )begin   declare c int;   declare t_error INTEGER default 0;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; --出现sql异常则设置t_error为1   set c = 1;START TRANSACTION;   --开启事务   if c = 1 then    insert into ONLINE_USER_INFO values(1,'admin','厉害了','');   else delete from ONLINE_USER_INFO where user_code = 1;   end if;   while c < 10 do     insert into ONLINE_USER_INFO values(c+2,'admin','厉害了','');    set c = c + 1;   end while;   test_loop:loop   delete from ONLINE_USER_INFO where user_code = c-2;   set c = c - 1;   if c < 8 then     insert into ONLINE_USER_INFO values('admin','厉害了','');    --出现异常    leave test_loop;   end if;   end loop;   if t_error = 1 then     ROLLBACK;    --执行回滚操作   else commit;   --提交sql操作   end if;end //   -- 存储的结束DELIMITER ;call pr_add()