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

判断变量是否为空

posted @ 2020-12-09 15:16  Marydon  阅读(765)  评论(0编辑  收藏  举报