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

前言

大家好，我是小郭，今天我们来学习存储过程，在几年前的系统中存储过程随处可见，他的优势就是提高数据库的安全性和数据的完整性，我们可以利用存储过程完成一系列的流程而无需在代码中反复跳转，但是他的缺点也暴露了出来，开发与维护的难度时间越长难度越大，我们来看看他的神奇之处。

一张图了解存储过程的内容

1. 什么是存储过程？

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

2. 存储过程的优势与劣势

优势

1. 封装性

2. 可增强 SQL 语句的功能和灵活性

3. 可减少网络流量

4. 高性能

5. 提高数据库的安全性和数据的完整性

6. 使数据独立

   劣势

7. 难以调试存储过程

8. 开发与维护不容易

9. 可移植性差

3. 含义

声明存储过程，是我们书写内部实现的入口，它就是一个我们封装起来的方法，写完调用就可以了。

// 声明存储过程
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]

实战

实战的代码也很简单，主要就是嵌套的循环，这其中也猜了许多的坑。

为了方便看内容是否正确，可以通过select xx来输出变量结果。

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;

4. 实战踩坑处理

1. [24000][1326] Cursor is not open 错误原因：游标未打开

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

解决：要注意游标的顺序，先开启游标在进行循环

open specialList;
specialLoop:LOOP
        fetch specialList into rpupMcsRsltAsgnId
        

5. 总结

写到这里想必大家已经对存储过程有了了解，我在这里主要介绍了存储过程的概念和实战，在某些场景下，存储过程为我们提供了许多的遍历，但我们也看到他缺点也是很大的问题，是我们在实战的时候需要特别处理的。