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,转载请注明原文链接:https://www.cnblogs.com/Marydon20170307/p/14095964.html