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

3,390 阅读3分钟

前言

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

板凳.jpeg

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

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. 总结

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

告辞.jpg