MySQL存储过程

存储过程

我们把若干条sql封装起来，起个名字->过程，把此过程存储在数据库中->存储过程

创建语句：

delimiter $$
create procedure drocedureName()
begin
--sql语句;
end$$ 

查看已有存储过程

show procedure status； 

调用存储过程

drop procedure procedureName; 

删除存储过程

drop procedure procedureName; 

声明变量

在存储过程中，用declare声明变量

--格式
declare 变量名 变量类型 [default 默认值]
delimiter $$
create procedure p2()
begin
    declare age smallint default 18;
    declare height int default 180;
    select concat('年龄是',age,'身高是',height);
end$$ 

变量运算

--存储过程中，变量可以在sql语句中合法的运算，如+-*/
--注意的是，运算的结果，如何赋值给变量
--set 变量名 := expression
create procedure p3()
begin
  declare age int default 18;
  set age := age + 20;
  select concat('20年后的年龄是',age);
end$$ 

if/else控制结构

if condition then
statement
else
...
end if;
end$$
------------------------------------
create procedure p4()
begin
  declare age int default 18;
  if age >=18 then
    select '已成年';
  else
    select '未成年';
  end if;
end$$ 

传参

--存储过程的括号里，可以声明参数，
语法是[in/out/inout]参数名 参数类型
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$$ 

循环

--计算1~100的和
create procedure p6()
begin
  declare sum int default 0;
  declare i int default 0;
  while i <=100 do
    set sum := sum + i;
    set i := i+1;
  end while;
  select concat('1~100的和为:',sum);
end$$
-- the 'in' argue is input type which can get the value we input.
--求1~n的和
create procedure p7(in n int)
begin
  declare sum int default 0;
  declare i int default 0;
  while i <=n do
    set sum := sum + i;
    set i := i+1;
  end while;
  select concat('1~',n,'的和为:',sum);
end$$
--in为输入参数，sum为输出参数
create procedure p8(in n int , out sum int)
begin
  declare i int default 0;
  set sum := 0;    --必须设置初始值，不然结果为NULL
  while i <=n do
    set sum := sum + i;
    set i := i+1;
  end while;
end$$
--调用
call p8(100,@sum);
--输入输出参数inout
create procedure p9(inout age int)
begin
  set age := age +20;
end$$
--调用
set @currage = 18;
call p9(@currage);
select @currage; 
--how to use case
create procedure p10()
begin
  declare pos int default 0;
  set pos := floor(5*rand());
  case pos
  when 1 then select 'still flying';
  when 2 then select 'fall in sea';
  when 3 then select 'in the island';
  else select 'i dont know';
  end case;
end$$
--repeat 循环
/*
repeat
sql statment;
until condition end repeat;
*/
--求1~100的和
create procedure p11()
begin
  declare sum int default 0;
  declare i int default 0;
  repeat
  set i := i + 1;
  set sum := sum+i;
  until i>100 end repeat;
  select sum;
end$$ 

游标 cursor

一条sql取出对应的N条结果集的资源，取出资源的接口/句柄，就是游标，沿着游标，可以一次取出1行。

declare 声明　　declare 游标名 cursor for select_statement;

open 打开　　 open 游标名

fetch 取值　　 fetch 游标名 into var1,var2[,....]

close 关闭　　　close 游标名;

create procedure p12()
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;  --cat
  fetch getgoods into row_gid,row_num,row_name;
  select row_num,row_name;  --dog
  fetch getgoods into row_gid,row_num,row_name;
  select row_num,row_name;  --pig
  fetch getgoods into row_gid,row_num,row_name;
  select row_num,row_name;  --报错，游标到头了。
  close getgoods;
end$$

----------------游标结合循环取出数据-----------
create procedure p14()
begin
  declare row_gid int;
  declare row_num int;
  declare row_name varchar(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;
  open getgoods; #打开游标
  repeat
  set i := i+1;
  fetch getgoods into row_gid,row_num,row_name;  #取值
  select row_num,row_name;
  until i >= cnt end repeat;
  close getgoods;  #关闭游标  
end$$ 

--游标取值越界时，有没有标识？利用标识来结束
--在mysql cursor中，可以declare continue handler来操作1个越界标识
--declare continue handler for NOT FOUND statement;
create procedure p15()
begin
  declare row_gid int;
  declare row_num int;
  declare row_name varchar(20);
  declare done int default 0;
  declare cnt int default 0;
  declare i int default 0;
  declare getgoods cursor for select gid,num,name from goods;  #声明游标
  declare continue handler for NOT FOUND set done :=1;
  open getgoods; #打开游标
  repeat
  fetch getgoods into row_gid,row_num,row_name;  #取值
  IF done = 0 THEN
  select row_num,row_name;
  END IF;
  until done=1 end repeat;
  close getgoods;  #关闭游标  
end$$ 

mysql 游标最后一行重复问题

一、解决方法，最后的重复的原因是fetch getgoods into...的时候，当第一次到达最后一条记录时，取出记录，把值给变量，这时一切正常，没有错误。然后UNTIL done=1循环到REPEAT重新开始，再FETCH一条时，已经没有记录，done会被 SET done=1;，但此刻没有进行任何判断，程序会继续执行SELECT ……语句，而由于FETCH没有取到记录，则没有对变量进行赋值，所以变量仍是原值。当到UNTIL done 时程序退出REPEAT，从而最后的记录会被重复。

二、declare exit handler for NOT FOUND statement;

exit与continue的区别是exit触发后，后面的语句不在执行

create procedure p16()
begin
  declare row_gid int;
  declare row_num int;
  declare row_name varchar(20);
  declare done int default 0;
  declare cnt int default 0;
  declare i int default 0;
  declare getgoods cursor for select gid,num,name from goods;  #声明游标
  declare exit handler for NOT FOUND set done :=1;
  open getgoods; #打开游标
  repeat
  fetch getgoods into row_gid,row_num,row_name;
  select row_num,row_name;
  until done=1 end repeat;
  close getgoods;  #关闭游标  
end$$ 

除continue、exit外，还有一种undo handler。 
continue 是触发后，后面的语句继续执行 
exit 是触发后，后面的语句不再执行 
undo 是触发后，前面的语句撤销（但是，目前mysql还不支持undo）

create procedure p17()
begin
  declare row_gid int;
  declare row_num int;
  declare row_name varchar(20);
  declare done int default 0;
  declare cnt int default 0;
  declare i int default 0;
  declare getgoods cursor for select gid,num,name from goods;  #声明游标
  declare continue handler for NOT FOUND set done :=1;
  open getgoods; #打开游标
  fetch getgoods into row_gid,row_num,row_name;  #先fetch下，看有无数据
  repeat
      select row_num,row_name;
      fetch getgoods into row_gid,row_num,row_name;
  until done=1 end repeat;
  close getgoods;  #关闭游标  
end$$

-------------------------while-----------------

create procedure p18()
begin
  declare row_gid int;
  declare row_num int;
  declare row_name varchar(20);
  declare done int default 0;
  declare cnt int default 0;
  declare i int default 0;
  declare getgoods cursor for select gid,num,name from goods where 0;  #声明游标
  declare continue handler for NOT FOUND set done :=1;
  open getgoods; #打开游标
  fetch getgoods into row_gid,row_num,row_name;  #先fetch下，看有无数据
  while done = 0 do
      select row_num,row_name;
      fetch getgoods into row_gid,row_num,row_name;
  end while;
  close getgoods;  #关闭游标  
end$$