mysql 存储过程 示例

1.情景展示

　　现在有个取票号的需求：

　　1.将取出当前票号，将它放入中间表，然后更新主表票号（票号+1）；

　　2.以后取票号先从中间表拿，中间表没有再从主表拿，拿到之后再次放入中间表并更新主表。

　　用存储过程如何实现？

2.分析

　　主表在这里不展示，只展示中间表的表结构。

3.解决方案

　　可视化开发工具：Navicat　 

　　选中你的数据库实例，点击函数，右侧就会出现该数据库实例下所有的存储过程及函数

　　Navicat和plsql不同的是：Navicat函数窗口，没有单独的存储过程管理窗口。

　　在右侧出现的函数窗口，点击“新建函数”，起个名字

　　模式：有三种，IN-->入参（SQL限制条件），OUT-->出参（SQL执行结果），INTOUT-->既是入参也是出参；

　　名：参数名称（入参一般以"in_"开头，返参一般以"out_"开头，方便区分）；

　　类型：指定参数的数据类型

　　点击完成，这样，存储过程的框架就搭好了。

　　点击保存却会报语法错误，具体什么错不知道。

　　后来才发现，mysql与oracle不同，存储过程字符串类型的参数必须限制大小。

　　再次保存就不会报错啦。 

　　如何声明变量？

　　因为我是从oracle转过来的，所以一直以oracle的思维方式来创建mysql的存储过程。

　　在oracle中，存储过程想要使用变量，不需要使用关键词DECLARE，并且变量统一放在BEGIN之前，方便统一管理；

　　而在mysql中，要想使用变量需要满足的条件：

　　第一，即使是存储过程，也要使用：关键词DECLARE；

　　第二，每使用一个变量前面都必须加declare；

2022年2月13日19:17:06

如果不想使用多个declare的话，可以这样搞：

　　第三，变量的声明必须在begin和and之间。

　　第四，变量的数据类型该限制最大值的必须做出限制（主要是字符串类型，数值类型不需要）。

　　如何使用INTO给变量正确赋值？

　　一开始，我以为是酱紫的：

　　原来，正确的写法是这样子的：

　　当将查询结果塞给两个及以上变量时，INTO左边的字段和INTO右边的变量数量必须一一对应上才行，并且只能使用一个INTO。

　　也可以这个样子：

　　当将查询结果塞给一个变量时，可以将这个作为最后一个查询字段，并且可以使用INTO赋值，后面不能再跟其他字段。

　　IF条件判断

说明：if条件判断，最好使用小括号()包起来，便于维护和解读。

查看代码

CREATE PROCEDURE `PRO_TEST` (
  IN `IN_ORG_CODE` VARCHAR ( 50 ),
  IN `IN_INVOICING_CLERK` VARCHAR ( 50 ),
  IN `IN_BILL_TYPE` VARCHAR ( 10 ),
  IN `IN_MEDICAL_TYPE` VARCHAR ( 10 ),
  OUT `OUT_BILL_CODE` VARCHAR ( 50 ),
  OUT `OUT_BILL_NUMBER` VARCHAR ( 50 )) 
/**
  * 内容：获取当前票号(电子票/纸质票)
  * 日期：2020/12/05
  * 作者：MARYDON
  * 版本：1.0
  * 注意：创建存储过程不能用create or replace，出参、入参声明数据类型时必须指定大小
  */
BEGIN
/*定义票据终止号变量*/
  DECLARE
    V_END_NUMBER VARCHAR ( 50 );
/*1. 更新主表当前票号（假更新，控制并发）*/
  UPDATE CZ_EINVOICES 
  SET CURRENTNO = CURRENTNO 
  WHERE
    ORGCODE = IN_ORG_CODE 
    AND IVCTYPE = IN_BILL_TYPE 
    AND MEDICALTYPE = IN_MEDICAL_TYPE;
/*2. 从票据中间表获取票号*/
  SELECT
    BILLCODE,
    BILLNUMBER INTO OUT_BILL_CODE,
    OUT_BILL_NUMBER 
  FROM
    VOUCHER_NUMBER 
  WHERE
    ORGCODE = IN_ORG_CODE 
    AND BILLTYPE = IN_BILL_TYPE 
    AND MEDICALTYPE = IN_MEDICAL_TYPE 
    AND STATUS = 0 -- 只取开票失败的记录
  ORDER BY
    BILLNUMBER 
    LIMIT 1;
/*3. 票号非空判断*/
  IF
    ( OUT_BILL_NUMBER IS NULL ) THEN-- 3.1 如果票号为空，继续执行；
/*3.1.1 查询票据主表*/
    SELECT
      T.ORGCODE,
      T.IVCCODE,
      T.CURRENTNO,
      T.ENDNO INTO IN_ORG_CODE,
      OUT_BILL_CODE,
      OUT_BILL_NUMBER,
      V_END_NUMBER 
    FROM
      CZ_EINVOICES T 
    WHERE
      T.ORGCODE = IN_ORG_CODE 
      AND T.IVCTYPE = IN_BILL_TYPE 
      AND T.MEDICALTYPE = IN_MEDICAL_TYPE;
        -- 当前号与最大号比对：
    IF
      ( OUT_BILL_NUMBER IS NOT NULL AND OUT_BILL_NUMBER <= CAST( V_END_NUMBER AS SIGNED ) ) THEN-- 3.1.2 如果票号非空，继续执行；
/*3.1.2.1 将票据信息插入中间表*/
        INSERT INTO VOUCHER_NUMBER ( ORGCODE, INVOICINGCLERK, MEDICALTYPE, BILLTYPE, BILLCODE, BILLNUMBER, `STATUS` )
      VALUES
        ( IN_ORG_CODE, NULL, IN_MEDICAL_TYPE, IN_BILL_TYPE, OUT_BILL_CODE, OUT_BILL_NUMBER, 1 );
/*3.1.2.2 更新主表当前票号*/
      UPDATE CZ_EINVOICES 
      SET CURRENTNO = OUT_BILL_NUMBER + 1 
      WHERE
        ORGCODE = IN_ORG_CODE 
        AND IVCCODE = OUT_BILL_CODE -- 不同票据类型的票据代码都不相同，所以可以不用票据类型和医疗类型当作联合限定条件
        AND CURRENTNO = OUT_BILL_NUMBER;
    ELSE -- 3.1.3 否则，直接返回空票号（说明该机构没有对应的票据信息或当前号>最大号）。
    END IF;
  ELSE -- 3.2 否则，直接返回该票号。
/*3.2.1 将中间表，当前票号状态改成1*/
    UPDATE VOUCHER_NUMBER 
    SET `STATUS` = 1 
    WHERE
      ORGCODE = IN_ORG_CODE 
      AND BILLCODE = OUT_BILL_CODE 
      AND BILLNUMBER = OUT_BILL_NUMBER;
  END IF;
/*4. 返回结果*/
  SELECT
    OUT_BILL_CODE,
    OUT_BILL_NUMBER 
  FROM
    DUAL;
END;

　　当我再次打开存储过程的时候，也许会出现这种：

　　这个位置出现的是数据库用户。

　　在创建存储过程的时候，红框里的代码是可以删掉的，并不会影响存储过程的创建；

　　另外，在oracle中习惯使用CREATE OR REPLACE PROCEDURE，但是，mysql建存储过程的时候，不能加"OR REPLACE"，

　　但是，应该可以使用：DROP PROCEDURE IF EXISTS 存储过程名称；CREATE PROCEDURE 存储过程名称()；

　　definer这个值并不会限制函数和存储过程被调用的权限，但会限制函数和存储过程访问数据库的权限；

　　函数和存储过程在访问数据库时，会获取definer用户对应的数据库访问权限。

　　临时表　　

　　在mysql中，查询也可以使用临时表 DUAL的。　　

　　既可以不加“from dual”；

　　也可以加“from dual”。 

　　字符串转数值类型

　　CAST( '100' AS SIGNED )

　　另外，数值类型字符串是可以直接进行加减操作。

2021-01-22

　　返回结果

　　在mysql中，使用存储过程，有一个令人恶心的地方就是：

　　输出变量的两种方式

　　方式一：

　　点击运行按钮，输入执行条件后，执行结果如下：

　　 方式二：

　　执行结果就很奇葩了 

　　只有手动查询一次变量才行

　　小结： 

　　当我们通过into的方式进行变量赋值，并且需要将该变量返回，必须重新查询才能正常返回。

　　易错点：关于变量的使用 

　　在上面的存储过程示例中，我将查询结果别名与出参名称保持一致，这样，就能做出结果返回（上面我们已经验证过了）

　　但，恰恰由于这次图方便，却给自己带来的巨大的麻烦。

　　执行结果就是：

　　按说当前号>结束号的情况是不会出现的，但是它却出现了，经过多次测试，发现：该存储过程永远不进if条件，只走else。

　　这就变得十分诡异了，折腾了一个小时，发现问题在于：

　　别名不能当变量来使用。

　　它们的名字虽然一样，但是，通过查询语句并没有把查询结果塞进对应的变量当中，换句话说就是，输出参数并没有值，所以，造成了这种只走else的局面。

　　解决方案：

　　使用INTO，先赋值后使用。

查看代码

CREATE DEFINER=`用户名`@`%` PROCEDURE `PRO_GET_IVCVOUCHER_NUMBER`(
  IN `IN_ORG_CODE` VARCHAR ( 50 ),
  IN `IN_INVOICING_CLERK` VARCHAR ( 50 ),
  IN `IN_BILL_TYPE` VARCHAR ( 10 ),
  IN `IN_MEDICAL_TYPE` VARCHAR ( 10 ),
  OUT `OUT_BILL_CODE` VARCHAR ( 50 ),
  OUT `OUT_BILL_NUMBER` VARCHAR ( 50 ),
  OUT OUT_END_NUMBER VARCHAR ( 50 ))
BEGIN
/*1. 添加排他锁*/
  SELECT
    T.IVCCODE,
    T.CURRENTNO,
    T.ENDNO INTO OUT_BILL_CODE,
    OUT_BILL_NUMBER,
    OUT_END_NUMBER 
  FROM
    CZ_EINVOICES T 
  WHERE
    T.ORGCODE = IN_ORG_CODE 
    AND T.IVCTYPE = IN_BILL_TYPE 
    AND T.MEDICALTYPE = IN_MEDICAL_TYPE 
    AND `STATUS` = 1 FOR UPDATE;
/*2. 当前号是否是最大号*/
  IF
/*2.1 当前号是最大号*/
    ( CAST( OUT_BILL_NUMBER AS SIGNED ) >= CAST( OUT_END_NUMBER AS SIGNED ) ) THEN
/*使用中变使用完*/
      UPDATE CZ_EINVOICES 
      SET `STATUS` = 2 
    WHERE
      ORGCODE = IN_ORG_CODE 
      AND IVCTYPE = IN_BILL_TYPE 
      AND MEDICALTYPE = IN_MEDICAL_TYPE 
      AND `STATUS` = 1;
/*未使用变使用中*/
    UPDATE CZ_EINVOICES 
    SET `STATUS` = 1 
    WHERE
      ORGCODE = IN_ORG_CODE 
      AND IVCTYPE = IN_BILL_TYPE 
      AND MEDICALTYPE = IN_MEDICAL_TYPE 
      AND `STATUS` = 3 
    ORDER BY
      CREATEDATE 
      LIMIT 1;
/*2.2 更新票据员当前票号*/
  ELSE -- Navicat的SQL格式化很恶心
    UPDATE CZ_EINVOICES 
    SET CURRENTNO = CURRENTNO + 1 
    WHERE
      ORGCODE = IN_ORG_CODE 
      AND IVCTYPE = IN_BILL_TYPE 
      AND MEDICALTYPE = IN_MEDICAL_TYPE 
      AND `STATUS` = 1;
  END IF;
/*3. 返回查询结果*/
  SELECT
    OUT_BILL_CODE,
    OUT_BILL_NUMBER,
    OUT_END_NUMBER;
/*4. 立刻提交*/
  COMMIT;
END

　　执行结果：

2021-03-25

　　返回多行记录（结果集） 

　　如果习惯了使用oracle的存储过程，由于oracle想要返回结果集，必须通过系统游标来实现，所以，我就相当然的认为mysql也需要游标

　　其实，在mysql中很简单：

　　select的结果就可以直接返回，并且不需要声明任何输出参数

2021-07-13

　　mysql子查询示例

查看代码

SELECT
/*项目名称*/
  (
  SELECT
    S4.CHARGENAME 
  FROM
    CZ_HOSP_PROJECT S2,
    CZ_CENTER_HOSP S3,
    CZ_CENTER_PROJECT S4 
  WHERE
    T2.ITEMCODE = S2.CHARGECODE 
    AND T2.ORGID = S2.ORGCODE 
    AND S3.HPID = S2.ID 
    AND S4.ID = S3.CPID 
    AND S2.TYPE = 'zy' 
    AND S2.`STATUS` = 1 
  ) ITEMNAME,
/*项目金额*/
  T2.ITEMAMOUNT 
FROM
  CZ_FET_MAIN_ZY T,
  CZ_FET_DETAILS_ZY T2,
  CZ_UNITINFO T3,
  CZ_FET_PJKJ T4 
WHERE
  T2.PATIENTNUMBER = T.PATIENTNUMBER 
  AND T2.INVOICENO = T.INVOICENO 
  AND T3.ORGCODE = T.ORGID 
  AND T.ORGID = T2.ORGID 
  AND T4.EINVOICECODE = T.EINVOICECODE 
  AND T4.EINVOICENUMBER = T.EINVOICENUMBER 
  AND T4.PATIENTNUMBER = T.PATIENTNUMBER 
  AND T.ORGID = '41604714-4' 
  AND T.EINVOICECODE = 41060221 
  AND T.EINVOICENUMBER = 0002972377

2022年2月19日18:11:49

判断变量是否为空

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

作者：​​Marydon​​