mysql高级:存储过程

mysql存储过程 procedure
    
在一些编程语言中，如pascal，有一个概念叫"过程" procedure,和"函数" function
(PHP中，没有过程，只有函数)。


过程：没有返回值的"函数"
函数：是一个有返回值的"过程"


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




存储过程的创建语法:
    delimiter $ //定界符


    create procedure procedureName()
    begin
        //封装的sql语句
    end$


查看存储过程：
    show procedure procedureName;
调用存储过程：
    call procedureName();
删除存储过程：
    drop




# 存储过程是可以编程的
# 意味着可以使用变量,表达式，控制结构
变量：
    声明变量:


        declare 关键字


        declare 变量名 变量类型[(长度)] [default 默认值];


    变量赋值:


        set 关键字


        set 变量名 := expression


控制语句：


    if/else:


        if condtion then
            // sql ...
        elseif condtion then
            // sql ...
        else
            // sql ...
        end if;




存储过程传参：
    #存储过程在括号里,可以声明参数
    [IN/OUT/INOUT] 参数名 参数类型
        IN 输入型
        OUT 返回型
        INOUT 可进可出
    例1 in:
        create procedure test(IN width int, IN height int)
        begin
            select concat('面积是:', width * height) as area;


            if width > height then
                select '胖';
            elseif height > width then
                select '高';
            else
                select '方';
            end if;
        end$


    例2 inout:
        #虚岁->周岁
        create procedure test(inout age int)
        begin
            set age := age - 1;
        end$




        set @val = 30$
        call test(@val)$
        selsect @val$


控制结构(顺序、选择、循环)：
    
    while：


        #求1-100的和
        create procedure test(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$
        //(1+100) * 100/2


        call test(100, @sumary);
        select @sumary;


    repeat 循环(PHP中的do while)：
        语法：
            repeat
                // sql ...
            until condition end repeat;


        例：
        create procedure repeatName()
        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$


    case(PHP中的switch case)：


        create procedure caseName(in pos int)
        begin
            case pos
                when 1 then 
                    select '1';
                when 2 then 
                    select '2';
                else select pos;
            end case;
        end$




*cursor 游标 (迭代?)：
    一条sql,对应N条资源。取出资源的接口/句柄，就是游标
    沿着游标，可以一次取出1行。


    监控游标：
        declare continue/exit handler for not found statement;//监控越界符


        continue: 跳出本次
        exit: 结束
    语法：
        declare 声明; declare 游标名 cursor for select_statement;
        declare continue/exit handler for not found statement;//监控越界符
        open 打开; open 游标名
        fetch 取值; fetch 游标名 into var1,var2[,...]
        close 关闭; close 游标名;


    例：
        create procedure cursorName()
        begin
            declare u_uid int;
            declare u_username varchar(60);


            declare is_y int default 1; //声明游标越界标志


            declare users cursor for SELECT uid, username FROM user WHERE uid < 100;


            //当游标指没有找到数据时
            declare exit handler for NOT FOUND set is_y := 0; //监控游标


            open users;


            fetch users into u_uid,u_username;


            while is_y = 1 do
                select u_uid, u_username;
                fetch users into u_uid,u_username;
                /* 测试显示 */
            end while;


            close users;
        end$








    其他监控：


    DELIMITER//
    CREATE PROCEDURE `proc_msg_receiver_friend`()
    COMMENT '消息队列中好友分发存储过程'
    BEGIN
    -- 当SQL失败时回滚
    DECLARE CONTINUE HANDLER FOR sqlexception ROLLBACK;
    -- 开启事务
    START TRANSACTION;
    -- 好友动态分发
    INSERT INTO feed_broadcast
    SELECT feed_id, ef.fri_uid, temp.create_date, app_id, src_type
    FROM 
    (SELECT * FROM eventqueue as eq WHERE eq.`status`=0 AND eq.topic=1 order by create_data DESC LIMIT 100) AS temp,
    friend AS ef
    WHERE temp.uid=ef.uid;
    -- 更改动态消息状态
    UPDATE eventqueue eq SET `status`=1
    WHERE eq.`status`=0 AND eq.topic=1
    ORDER BY create_data DESC 
    LIMIT 100;
    commit;
    END//
    DELIMITER;