Mysql 存储过程 mysql workbench

10. January 2017 DataBase 0

show procedure status; 查看存储过程

DELIMITER // 定义存储过程
create procedure film4(id2 int)
begin
select *
from user
where id=id2;
end //

call film4(2); //调用存储过程

drop film4 删除存储过程

show create procedure film4  查看存储过程的创建信息

delimiter // 存储过程使用变量
create procedure actor_insert()
begin
set @x=1;
insert into user(id,name,age) value (11,’lijian’,99);
set @x=2;
insert into user(id,name,age) value (12,’lisa’,99);
set @x=3;
end //

delimiter //  简单光标使用
create procedure age_state()
begin
declare age int;
declare id int;
declare cur_age cursor for select id,age from user;
declare exit handler for not found close cur_age;
set @a=10;
set @b=11;
open cur_age;
repeat
fetch cur_age into id,age;
if id<5 then
set @a=100;
else
set @b=110;
end if;
until 10 end repeat;
close cur_age;
end //

delimiter //循环插入DML
create procedure act_in()
begin
set @x=0;
ins:loop
set @[email protected]+1;
if @x=10 then
leave ins;
end if;
insert into user(name,age) values(‘test’,100);
end loop ins;
end //


Leave a Reply

Your email address will not be published. Required fields are marked *