MySQL存储过程

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

create procedure procedureName()
begin
  --sql
end$
######################
show procedure status;
调用call procedure()

声明变量：
格式 declare 变量名 变量类型 【default  默认值】
###############################
delimiter //
create procedure p3()
begin
declare age int default 18;
declare height int default 180;
select concat('age is', age, 'height is',height);
end;
//
call p3()//
##################################
set age := age + 20;
#################################
if condition then
statement
else
end if;
################
声明参数，传参
create procedure p5(width int ,height int)
begin
    select concat('area is ' width*height ) as area;
    if width > height then
    select 'fat'
    elseif width < height then
    select 'not fat'
    else 
    select 'fang'
    end if;
end;
//
#########################
顺序 选择  循环
求1-100的和
create procedure p6()
begin
  declare total int default 0;
  declare num int default 0;

  while num<=100 do
   -- add num to total
   set total := total+num;
   set num := num+1;
   end while;
   
   select total
end;
#############################
p7 I want get the sumary of 1 to N;

in型
create procedure p7(in n int)

while num <=n do

call p7(10);
###############################
in和out型
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;
call p8(100,@sumary)
select @sumary
##################################
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 "1";
  when 2 then select "2";
  when 3 then select "3";
  else select 'I dont know'
  end case;
end;

call p10()
#####################################
repeate

create procedure p11()
begin
  declare total int default 0;
  declare i int default 0;
  repeat
    select i;
    set i := i+1;
  until i >100 end repeat;
  select total;
end;
######################################

游标：
create procedure p12()
begin
  declare row_gid int;
  declare row_num int;
  declare row_name varchar(20);
  declare getgoods cousor 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//

call p12()//
############################################
create procedure p13()
begin
  declare row_gid int;
  declare row_num int;
  declare row_name varchar(20);
  declare getgoods cousor for select gid,num,name from goods;
  
  open getgoods;
  fetch getgoods into row_gid,row_num,row_name;
  select row_num,row_name;

  fetch getgoods into row_gid,row_num,row_name;
  select row_num,row_name;

  fetch getgoods into row_gid,row_num,row_name;
  select row_num,row_name;

  fetch getgoods into row_gid,row_num,row_name;
  select row_num,row_name;

  close getgoods;
end//
call p13()  最后报错。
################################################
循环取出一张表的所有数据
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 gooods;

  open getgoods;

  repeat
    set i := i+1;
    fetch getgoods into into row_gid,row_num,row_name;
    select row_num,row_name;
    until i >=cnt end repeat
   
   cloes getgoods;
  

end//
###################################
通过越界标志来结束。
mysql 游标，可以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 you int defalut 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 into row_gid,row_num,row_name;
    select row_num,row_name;
    until you=0 end repeat;
   
   cloes getgoods;
  

end//
###################################
多取出1行，如何解决？
思考：如果not found后，后面的select 不再执行，最后就不会多取一行。
申明不再是continue, exist就能达到目的,后面的语句不再执行。
declare exit handler for not found set you :0;
#####################################
除了continue,exit外，还有一种undo handler
continue是触发后，后面语句继续执行
exit触发后，后面不再执行
undo触发后，前面的语句撤销
###############################
一行都没有怎么办？
create procedure p17
begin
  declare row_gid int;
  declare row_num int;
  declare row_name varchar(20);


  declare you int defalut 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 into row_gid,row_num,row_name;


  repeat

    select row_num,row_name;    
    fetch getgoods into into row_gid,row_num,row_name;
    until you=0 end repeat;
   
   cloes getgoods;
  

end//
create procedure p18
begin
  declare row_gid int;
  declare row_num int;
  declare row_name varchar(20);


  declare you int defalut 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 into row_gid,row_num,row_name;


  while you=1 do
    select row_num,row_name;
    fetch getgoods into into row_gid,row_num,row_name;
   end while;
   
   cloes getgoods;
  

end//
#####################################