mysql 存储过程

存储过程基本语法

create procedure procedureName()
begin 
#SQL语句
end

调用存储过程

call procedureName();

变量

create procedure procedureName()
begin
    #定义一个变量
    declare a int default 10;
    #运算
    set a := a+1;
    select a;
end

if控制

create procedure procedureName()
begin
    #定义一个变量
    declare a int default 10;
    #运算
    set a := a+1;
    if a == 2 then
        select 'a=2'
    else 
        select 'a!=2'
    end if
end

存储过程参数传递

create procedure area(width int,height int)
begin
    select width*height;
end

循环结构

create procedure area(width int,height int)
begin
    while num < 100 do
    #SQL语句
    end while;
end

输入参数

create procedure area(in width int,in height int)
begin
    #SQL语句
end

输出参数？？

create procedure sum(in n int,out allTo)
begin
    if n == 1 then
        set allTo := 1
    else 
        set allTo := 2;
    end; 
end

call sum(8,ss);
select ss;

inout参数？

create procedure demo(inout age int)
begin
    set age := age+10;
end

set @s = 12;
call demo(@s);
select @s;

case结构

create procedure demo()
begin
    declare age int default 0;
    case age
    when 1 then SQL;
    when 2 then SQL;
    ...
    else SQL;
    end case;
end

repeat

create procedure demo()
begin
    repeat 
    SQL
    until condition end repeat;
end

游标

create procedure demo()
begin
    declare id int default 0; 
    declare getgoods cursor for select gid from goods;
    declare continue handle for NOT FOUND set you := 0;
    #declare exit handle for NOT FOUND set you := 0;
    open getgoods;
    repeat 
    fetch getgoods into id;
    select id;
    until you=0 end repeat
    close getgoods;
end