【MySQL】技多不压身，存储过程你会了吗？

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。

// 声明存储过程
CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE ccc ([proc_parameter[,...]])
    [characteristic ...] routine_body
 //IN入参 OUT出参 param_name参数名称 type参数类型
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
// 执行存储过程特性  
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL //Sql语句组成
  | [NOT] DETERMINISTIC //NOT 结果不确定，相同输入可能得到不同输出
  // CONTAINS SQL 子程序包含SQL语句
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
routine_body:
　　Valid SQL routine statement
//存储过程开始和结束符号 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

create
   procedure update_xxx(IN Id varchar(50))
begin
    DECLARE code varchar(40);
    DECLARE done boolean default false;
    DECLARE edone boolean default false;
    /*查询企业code*/
    DECLARE list  CURSOR FOR select ...;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    open list;
    posLoop:LOOP
        fetch list into medinsCode;
        select medinsCode;
        if (done) then
            leave posLoop;
        else
            set edone = false;
            begin
                DECLARE specialList CURSOR FOR select ...;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone = true;
                open specialList;
                specialLoop:LOOP
                    fetch specialList into xxx;
                    if edone then
                        LEAVE specialLoop;
                    elseif (pubonlnStas = 1 and minPricStas = 0) then
                        update ...
                    elseif (pubonlnStas = 1 and minPricStas = 1) then
                        update ...
                    elseif (pubonlnStas = 0) then
                       update ...
                    end if;
                end LOOP specialLoop;
                close specialList;
            end;
            begin
                DECLARE otherList  CURSOR FOR select ...;
                SET done = false;
                open otherList;
                otherLoop:LOOP
                    fetch otherList into xxx;
                    if done then
                        LEAVE otherLoop;
                    elseif (pubonlnStas = 1) then
                       update ...
                    elseif (pubonlnStas = 0) then
                        update ...
                    end if;
                end LOOP otherLoop;
                close otherList;
            end;
            update ...
        end if;
    end LOOP posLoop;
    close list;
end;

specialLoop:LOOP
        fetch specialList into rpupMcsRsltAsgnId
        open specialList;
   ...

open specialList;
specialLoop:LOOP
        fetch specialList into rpupMcsRsltAsgnId