数据库存储过程

-- 存储过程的创建语法
create procedure procedureName()
begin
	sql语句
end$

-- 查看已有的procedure
-- show procedure status

-- 调用存储过程
-- call procedure()$

-- 删除存储过程
-- drop procedure procedureName$

-- 创建存储过程p1
create procedure p1()
begin	
	select 'hello' from dual;
end$

-- 引入变量p2
-- 在存储过程中，用declare声明变量
-- 格式：declare 变量名 变量类型 [default 默认值]
create procedure p2()
begin
	declare age int default 90;
	declare height int default 175;
	select concat('年龄',age,'升高',height) from dual;
end$

-- 使用变量运算p3
-- 存储过程中变量可以使用sql语句中合法的运算，如+-*、
-- 注意的是，运算的结果，如何赋值给变量
-- set 变量名：=expression
create procedure p3()
begin
	declare age tinyint unsigned default 90;
	declare height tinyint unsigned default 175;
	select age,height from dual;
	set age:=age+20;
	select concat('20年后年龄',age,岁) from dual;
end$

-- 使用表达式p4
-- if/else控制结构
-- if condition then
-- statement
-- else
-- end
create procedure p4()
begin
	declare age tinyint unsigned default 90;
	declare height tinyint unsigned default 175;
	if age>70 then
		select '古稀之年' from dual;
	else
		select '风华正茂' from dual;
	end if;
end$

-- 流程控制p6
-- 顺序，选择，循环
create procedure p5()
begin
	declare age int default 90;
	declare height int default 175;
	declare gender char(1) default '男';
	if gender='男' then
		select '你是男的' from dual;
	end if;
	if height>175 then
		select '身高中等' from dual;
	end if;
	if age<20 then
		select '小鲜肉' from dual;
	elseif age<=50 then
		select '年轻有为' from dual;
	elseif age<=70 then
		select '安享天伦' from dual;
	else
		select '佩服佩服' from dual;
	end if;
end$

-- 流程控制case
create procedure p6()
begin
	declare pos int default 0;
	set pos:=floor(5*rand());
	case pos
	when 1 then select 'cat';
	when 2 then select 'dog';
	when 3 then select 'pig';
	else select 'human being';
	end case
end$

-- 小练习求1-100之和
-- 过程名称不能定义为sum
create procedure p6()
begin
	declare total int default 0;
	declare num int default 0;
	while num<100 do
		set total:=total+num;
		set num:=num+1;
	end while;
	select total;
end$

-- 存储过程传参p5
-- 存储过程的括号里，可以声明参数
-- 语法是[in/out/inout] 参数名 参数类型
-- 求1-N之和(传参输入in)
create procedure p7(in n int)
begin
	declare total int default 0;
	declare num int default 0;
	while num<n do
		set num:=num+1;
		set total:=total+num;
	end while;
	select total;
end$

-- 将结果输出out
-- 调用call p8(100 @sumary)
-- 查值select @sumary
create procedure p8(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$

-- repeat 循环
create procedure p9()
begin
	declare i int default 0;
	repeat
		set i:=i+1;
		select i;
	until i>10 end repeat;
	select i;
end$
-- 循环求和
create procedure p10(in n int)
begin
	declare i int default 0;
	declare total int default 0;	
	repeat
		set i:=i+1;
		set total:=total+i;
	until i=n end repeat;
	select total;
end$

-- cursor 游标 游动的标志
-- 1条sql，对应N条结果集的资源，取出资源的接口/句柄，就是游标
-- 沿着游标，可以一次取出一行，实现步骤
-- declare声明：declare 游标名称 cursor for select_statement
-- open打开:open 游标名
-- fetch取值：fetch 游标名 into val1,val2[,...]
-- close关闭：close 游标名称
create procedure p11()
begin
	declare row_gid int;
	declare row_num int;
	declare row_name varchar(20);
	declare getgoods cursor for select gid,num,name from goods;
	open getgoods;
	fetch getgoods into row_gid,row_num,row_name;
	select row_num,row_name;
	close getgoods;
end$

-- 游标取值越界时，有没有标识？怎么利用标识来结束
-- 在mysql cursor中可以declare continue handler来操作一个越界标识
-- declare continue handler for NOT FOUND statement;
create procedure p12()
begin
	declare row_gid int;
	declare row_num int;
	declare row_name varchar(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;
	repeat
		fetch getgoods into row_gid,row_num,row_name;
		select row_num,row_name;
	until you>=0 end repeat;
	close getgoods;
end$

-- declare exit handler for NOT FOUND statement;
-- exit与continue的区别是，exit触发后，后面的语句不在执行
create procedure p13()
begin
	declare row_gid int;
	declare row_num int;
	declare row_name varchar(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 row_gid,row_num,row_name;
		select row_num,row_name;
	until you=0 end repeat;
	close getgoods;
end$