17 存储过程

过程:

封装了若干条语句.调用时,这些封装体执行.

函数:

是一个有返回值的过程.

过程是没有返回值的函数

存储过程:

把此过程存储在数据库中,因此称为存储过程

存储过程语法

# 创建语法:
    create procedure procedureName()
    begin
        -- sql语句
    end$

# 删除存储过程
    drop procedure procedureName;

# 查看已有的procedure
    show procedure status;

# 调用存储过程:
    call procedureName();

存储过程1--创建简单的存储过程

# eg:
    create procedure p1()
    begin
        select * from tableName;
    end$

存储过程2--引入变量

# 存储过称是可以编程的,意味着可以使用变量,表达式,控制结构    
# eg:
    create procedure p2()
    begin
        declare age int default 18;
        declare height int default 180;
        select concat('年龄是',age,'身高是',height);
    end$

存储过程3--变量运算

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

存储过称4--控制结构(if/else)

# 语法:
    if 
        conditon 
    then
        statement
    else
        statement
    end;

# eg:
    create procedure p4()
    begin
        declare age int default 18;
        if age >= 18 then
            select '已成年';
        else
            select '未成年';
        end if;
    end$

存储过称5--控制结构2(while)

# 三种控制结构:  
    顺序,选择,循环

# eg: 求1-100的和
    create procedure p5()
    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$ 

存储过称6--控制结构3(case)

# 用if/else也能实现同样的效果
# eg: 
    create procedure p6()
    begin
        declare pos int default 0;
        set pos := floor(5*rand());

        case pos
            when 1 then select 'fly';
            when 2 then select 'sea';
            else select 'run';
        end case;
    end$  

存储过称7--控制结构4(repeat)

# 用while也能实现同样的效果
# eg: 
    create procedure p7()
    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$ 

存储过称8--传参

# 存储过程的括号里,可以声明参数,
# 语法:  
    [in/out/inout] 参数名 参数类型,不写时,默认为in

# 调用: 
    call p8(3,4)

# eg:
    create procedure p8(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$

存储过称9--in型参数

# 存储过程中可以接收到这个参数
# eg: 求1-n的和
    create procedure p9(in n int)
    begin
        declare total int default 0;
        declare num int default 0;

        while num <= n do
            set total := total + num;
            set num := num + 1;
        end while;

        select total;
    end$

存储过称10--out型参数

# 将存储过程中值输出
# 调用: 
    call p10(100,@argsName)

# eg: 求1-n的和
    create procedure p10(in n int,out total int)
    begin
        declare num int default 0;
        set total := 0;

        while num <= n do
            set total := total + num;
            set num := num + 1;
        end while;

        select total;
    end$
# 此处执行完成后,输入select @argsName即可得到对应的结果

存储过称11--inout型参数

# 调用: 
    set @age = 18
    call p11(@age)

# eg:   
    create procedure p11(inout age int)
    begin
        set age := 1;
        set age := age + 20;
    end$

存储过称12--cursor 游标

# 一条sql,对应N条资源,取出资源的接口/句柄,就说游标
# 沿着游标,可以一次取出一行

# eg: 
    create procedure p12()
    begin
        declare row_gid int;
        declare row_num int;
        declare row_name varchar(20);

        -- 这种方法不推荐使用
        -- 定义一个游标
        declare cursorName cursor for select gid,num,name from goods;

        open cursorName;
            -- 将显示的第一行值赋予对应的变量
            fetch cursorName into row_gid,row_num,row_name;
            select row_gid,row_num,row_name;

            -- 将显示的第二行值赋予对应的变量
            fetch cursorName into row_gid,row_num,row_name;
            select row_gid,row_num,row_name;

            -- 将显示的第三行值赋予对应的变量(如果只有两条数据,赋值三次会报错)
            fetch cursorName into row_gid,row_num,row_name;
            select row_gid,row_num,row_name;

        close cursorName;
    end$

存储过称13--cursor 游标(推荐使用)

# eg:
    create procedure p13()
    begin
        declare row_gid int;
        declare row_num int;
        declare row_name varchar(20);

        declare i int default 0;
        -- 定义一个变量,用来计算显示了几条数据
        declare cnt int default 0;

        -- 定义一个游标
        declare cursorName cursor for select gid,num,name from goods;

        select count(*) into cnt from goods;

        open cursorName;

            repeat
                set i := i +1;
                fetch cursorName into row_gid,row_num,row_name;
                select row_gid,row_num,row_name;
            until i >= cnt
            end repeat;
        
        close cursorName;
    end$

存储过称14--越界标志(continue handler for not found)

# 在MySQL cursor中,可以declare continue handler来操作一个越界标志
# 语法: declare continue handler for not  found 语句;
# 表中只有三条数据,但是下面的代码执行后 ,会取出四行,最后一行显示两次
# 代码中有逻辑错误,分析:
    当第四次执行 repeat 时,fetch-->没有数据,触发越界标志中的not  found-->set you := 0-->continue-->执行fetch后面的sql语句(select row_gid,row_num,row_name;)-->所以,最后一行被取出两次

# eg:
    create procedure p14()
    begin
        declare row_gid int;
        declare row_num int;
        declare row_name varchar(20);

        declare you int default 1;
        
        -- 定义一个游标
        declare cursorName cursor for select gid,num,name from goods;
        declare continue handler for not found set you := 0;

        open cursorName;

        repeat
            fetch cursorName into row_gid,row_num,row_name;
            select row_gid,row_num,row_name;
        until you = 0
        end repeat;
        
        close cursorName;
    end$

存储过称15--越界标志(exit handler for not found)

# 越界标志解决逻辑错误(不完善,没考虑到表为空的情况)
# 引入另一种越界标志:declare exit handler for not found 语句;

# eg:
    create procedure p15()
    begin
        declare row_gid int;
        declare row_num int;
        declare row_name varchar(20);

        declare you int default 1;
        
        -- 定义一个游标
        declare cursorName cursor for select gid,num,name from goods;
        declare exit handler for not found set you := 0;

        open cursorName;

        repeat
            fetch cursorName into row_gid,row_num,row_name;
            select row_gid,row_num,row_name;
        until you = 0
        end repeat;
        
        close cursorName;
    end$

存储过称16--越界标志(undo handler)

# 除以上两种越界标志(continue,exit)外,还有undo handler
# continue是触发后,后面的语句继续执行
# exit是触发后,后面的语句不再执行
# undo是触发后,前面的语句撤销(MySQL暂不支持)


# 不改变越界标志,解决逻辑错误(完善)
# eg: 
    create procedure p16()
    begin
        declare row_gid int;
        declare row_num int;
        declare row_name varchar(20);

        declare you int default 1;
        
        -- 定义一个游标
        declare cursorName cursor for select gid,num,name from goods;
        declare exit handler for not found set you := 0;

        open cursorName;
            -- 1: 先取出一行数据,如果取出,则 not found未触发,
            fetch cursorName into row_gid,row_num,row_name;

            repeat
                -- 2: 执行显示语句
                select row_gid,row_num,row_name;
                -- 3: 在进行fetch如果取不到,触发continue越界标志,继续向下执行,
                -- 下面没有sql语句,所以不会再显示
                fetch cursorName into row_gid,row_num,row_name;
            until you = 0
            end repeat;
        
        close cursorName;
    end$