1、理解：
        把若干条 SQL 语句封装起来，起个名字，就叫过程
        把这个过程存储在数据库中，就叫存储过程

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

3、查看已有的存储过程：
        示例：show procedure status;
        
4、调用存储过程：
        示例： call procedureName ();
        
5、存储过程是可以编程的，意味着可以使用 变量 表达式 控制结构来完成复杂的功能
    1、声明变量关键词：declare
         格式： declare 变量名 变量类型  [default 默认值]
         实例：
                    create procedure p2()
                    begin
                        declare age int default 18;
                        declare height int default 180;
                        
                        select concat(‘年龄‘，age，‘身高‘，height) from dual;
                    end$
    2、变量运算和赋值：
        1、赋值： set 变量名 := 1
              示例：create procedure p2()
                        begin
                            declare age int default 18;
                            declare height int default 180;
                                set age := age + 20;
                            select concat(‘20年后的年龄‘, age ,‘身高‘ , height) from goods;
                        end$
        2、如何给存储过程传参数
            解释：存储过程的括号里面，可以声明参数
            语法：[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$
        3、控制结构：
            1、顺序
            2、分支/选择
                1、case：
                        create procedure p6(in n int)
                        begin
                            declare total int default 0;
                                set toral := floor(5 * rand());  #取整
                                    case total
                                        when 1
                                            then select ‘nihao‘;
                                        when 2
                                            then select  ‘hello‘;
                                        else
                                            select ‘hi‘;
                                    end case;
                        end$
                            
            3、循环：
                1、while:
                        create procedure p6(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$
                
                2、repeat: 循环
                        create procedure w()
                        begin
                            declare w int default 0;
                            declare e int default 0;
                                repeat
                                    set w := w + 1;
                                    set e := w + e;
                                until w  >= 100  end repeat;
                                select e;                                                   
                        end;

    3、cursor 游标：
        1、解释：1条 SQL ，对应N条结果集资源，取出资源的接口/句柄，就是游标
                        取值：取出一次，向后进1格，当没数据时，报错 02000
        2、构成：
            1、声明：declare 游标名 cursor select语句
            2、打开：open 游标名
            3、取值：fetch 游标名 into var ...;
            4、关闭：close 游标名
        
        3、游标取值越界时，有没有标识？利用标识来结束（触发）
            1、：在 Mysql cursor 中，可以用 declare continue handler / declare exit handler 来操作一个标识符
                        语法：declare exit handler for NOT FOUND set  var :=  0;
            
            2、：continue 和 exit 和 undo 的区别：
                    1、exit          ：触发后，后面的语句不再执行
                    2、continue  ：触发后，后面的语句会继续执行
                    3、undo          ：触发后，前面的语句撤销(但是呢，Mysql 目前还不支持 undo)                  
            
        4、示例：
            1、逻辑上不够严明，如果第一行就为空，会怎么样
                    create procedure q()
                        begin
                            declare w int ;
                            declare q int ;
                            declare e varchar(20) ;             
                            declare var int default 1;
                                declare getgoods cursor for select gid, num, name from goods;
                                    declare exit handler for NOT FOUND set var := 0;
                                    #如果这里不是 exit 而是 continue ,那么最后一行会被多执行一次 select
                                open getgoods;
                                    repeat
                                        fetch getgoods into w, q, e;
                                        select w, q, e;
                                    until var = 0 end repeat;
                                close getgoods;
                        end$
            2、第2种，在逻辑上要正确一些：
                    create procedure q()
                        begin
                            declare w int ;
                            declare q int ;
                            declare e varchar(20) ;             
                            declare var int default 1;
                                declare getgoods cursor for select gid, num, name from goods ;
                                    declare continue handler for NOT FOUND set var := 0;
                                open getgoods;
                                    fetch getgoods into w, q, e;
                                    while var = 1  
                                        do
                                            select w, q, e;
                                            fetch getgoods into w, q, e;
                                    end while;
                                close getgoods;
                        end$
                        

Mysql 存储过程,布布扣,bubuko.com

Mysql 存储过程