存储过程

create procedure p1()
BEGIN
select * from test;
end;

--调用
call p1

--查看
show create procedure p1;

-- 删除
DROP procedure p1 ;


delimiter $$

create procedure p1()
BEGIN
select * from test;
end$$

 

create procedure p2()
begin
declare str_count int default 0;
select count(*) into stu_dent from studetn;
end;

 


create procedure p3()
begin
declare str_count int default 0;
declare result varchar(10);
if score >=85 then
set result := '优秀';
else score >=60 THEN
set result := '鸡哥'
end if;
end;

 


create procedure p9(in in int)
BEGIN
declare total int default 0;

sum:loop
if n <=0 then
leave sum;
end if ;

end total :=total +n;
set n := n-1;
end loop sum;

select total;

end ;

call p9( n: 100 )

 

 

-- 声明游标
-- 创建游标
-- 开启游标
-- 获取游标记录
-- 插入数据到新表
-- 关闭游标


create procedure p11(in uage init )
begin
declare u_cursor cursor for
select name,profession from studetn where age <= uage ;

drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);

open u_cursor;
while true DO
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null ,uname,upro);

end while;
close u_cursor;

end;