存储过程(procedure)
修改mysql结束符 delimiter name
procedure创建语法：
create procedure procedureName();
begin
declare 声明变量
sql语句
end$
查看：procedure
show procedure status;
调取：procedure
call procedureName()；
删除：drop procedure procedureName();
例1：带入变量
create procedure p2()
begin
declare age int default 18;
declare height int default 180;
select concat('年龄',age,'身高',height);
end$
例2：变量计算
create procedure p3()
begin
declare age int default 18;
set age:= age+20;
select concat('20年后年龄',age);
end$
例3：变量判断if
create procedure p4()
begin
declare age int default 18;
if age >=18 then
select concat('成年');
else
select concat('未成');
end if ;
end$
例4：存储过程传参if
/**
存储过程的括号里，可以声明参数
语法是[in/out/inout]参数名 参数类型
in 传入
out 输出
**/
create procedure p5(width int,height int)
begin
select concat('你的面积',width* height) as area;
if width > height then
select '你挺胖';
elseif width < height then
select '你挺瘦';
else
select '你是方的';
end if;
end$
例5：存储过程 while
create procedure p6()
begin
declare total int default 0;
declare num int default 0;
while num < 100 do
set num :=num+1;
set total := total+num;
end while;
select total;
end $
例6：存储过程中的传参类型 in out
create procedure p7(in n int ,out total int)
begin
declare num int default 0;
set total :=0;
while num < n do
set num = num+1;
set total = total + num;
end while;
end$
调用 call p7(100,@q)$
select @q$
例7：存储过程 inout
create procedure p8(inout age int)
begin
set age :=age+20;
end$
首先声明变量 set @age=20;
调用存储过程 call p8(@age);
查询结果 select @age;
例8：when case
create procedure p9()
begin
declare pos int default 0;
set pos := floor(5*rand());
case pos
when 1 then select '飞';
when 2 then select '劫持';
when 3 then select '打击';
end case;
end$
例9： repeat
create procedure p10()
begin
declare total int default 0;
declare i int default 0;
repeat
set i := i+1;
set total := total + i;
until i>= 100 end repeat;
select total;
end$
例10：游标cursor for
create procedure p11()
begin
declare r_gid int;
declare r_num int;
declare r_name char(20);
declare getgoods cursor for select gid,num,name from goods;
open getgoods;
fetch getgoods into r_gid,r_num,r_name;
select r_gid ,r_num,r_name;
fetch getgoods into r_gid,r_num,r_name;
select r_gid ,r_num,r_name;
close getgoods;
end$
例11：游标cursor for
create procedure p12()
begin
declare r_gid int; --声明变量
declare r_num int; --声明变量
declare r_name char(20); --声明变量
declare cnt int default 0;
declare i int default 0;
declare getgoods cursor for select gid,num,name from goods; --声明游标
select count(*) into cnt from goods; --查询总条数赋值给变量cnt
open getgoods; --打开游标
repeat
set i := i+1;
fetch getgoods into r_gid,r_num,r_name;
select r_gid ,r_num,r_name;
until i>=cnt end repeat;
close getgoods; --关闭游标
end$
例12：游标cursor for
/*
declare [exit|continue|undo] handler for NOT FOUND set you := 0;
声明是使用continue 将会出现最后一条数据重复 因为conticue 触发不回立即停止
反之exit立即停止
undo 触发撤销最后一条 mysql 还不支持
*/
create procedure p13()
begin
declare r_gid int;
declare r_num int;
declare r_name char(20);
declare you int default 1;
declare getgoods cursor for select gid,num,name from goods;
declare exit handler for NOT FOUND set you := 0;
open getgoods;
repeat
fetch getgoods into r_gid,r_num,r_name;
select r_gid ,r_num,r_name;
until you=0 end repeat;
close getgoods;
end$
例13：游标cursor for
/*
declare [exit|continue|undo] handler for NOT FOUND set you := 0;
声明是使用continue 将会出现最后一条数据重复 因为conticue 触发不回立即停止
反之exit立即停止
undo 触发撤销最后一条 mysql 还不支持
改变逻辑修复continue的最后一条数据重复问题
首先查询赋值在循环
*/
create procedure p14()
begin
declare r_gid int;
declare r_num int;
declare r_name char(20);
declare you int default 1;
declare getgoods cursor for select gid,num,name from goods;
declare continue handler for NOT FOUND set you := 0;
open getgoods;
fetch getgoods into r_gid,r_num,r_name;
repeat
select r_gid ,r_num,r_name;
fetch getgoods into r_gid,r_num,r_name;
until you=0 end repeat;
close getgoods;
end$
例14：
create procedure p15()
begin
declare r_gid int;
declare r_num int;
declare r_name char(20);
declare you int default 1;
declare getgoods cursor for select gid,num,name from goods;
declare continue handler for NOT FOUND set you := 0;
open getgoods;
fetch getgoods into r_gid,r_num,r_name;
while you = 1 do
select r_gid ,r_num,r_name;
fetch getgoods into r_gid,r_num,r_name;
end while;
close getgoods;
end$
