当前位置：首页 > article >正文

五 MySQL 存储过程

article 2024/12/23 18:12:36

五、企业级开发技术

5.1 存储过程

关于存储过程我只能说请看下图，这是阿里巴巴发布的《阿里巴巴Java开发手册（终极版）v1.3版本》在 MySQL 第七条中强制指出禁止使用存储过程

所以对于存储过程不必深究，做到会写能看懂即可

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GdHDI4jj-1680502271512)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230329084802.png)]

5.1.1 什么是存储过程

Stored Procedure

是一组为了完成特定功能的 SQL 语句集合
经编译后保存在数据库中
通过指定存储过程的名字并给出参数的值
MySQL5.0 版本开始支持存储过程，使数据库引擎更加灵活和强大

5.1.2 存储过程可以包含

可带参数，也可返回结果
可包含数据操纵语句、变量、逻辑控制语句等

5.1.3 存储过程的优缺点

优点

减少网络流量
提升执行速度
减少数据库连接次数
安全性高
复用性高

缺点

可移植性差

SQL 最大的缺点还是 SQL 语言本身的局限性 SQL 本身是一种结构化查询语言，我们不应该用存储过程处理复杂的业务逻辑让 SQL 回归它结构化查询语言的功用。复杂的业务逻辑，还是交给代码去处理吧

5.1.4 创建存储过程

CREATE
    [DEFINER = { user | CURRENT_USER }]  
    # 定义DEFINER默认为当前用户
PROCEDURE 存储过程名
    [SQL SECURITY { DEFINER | INVOKER } | …]
    # 指定DEFINER或INVOKER权限
BEGIN
    …
END

特性	说明
LANGUAGE SQL	表示存储过程语言，默认SQL
{CONTAINS SQL \| NO SQL \| READS SQL DATA \| MODIFIES SQL DATA}	表示存储过程要做的工作类别默认值为CONTAINS SQL
SQL SECURITY { DEFINER \| INVOKER }	指定存储过程的执行权限默认值是DEFINERDEFINDER：使用创建者的权限INVOKER：用执行者的权限
COMMENT ‘string’	存储过程的注释信息

如果省略 SQL SECURITY 特性，则使用 DEFINER 属性指定调用者，且调用者必须具有 EXECUTE 权限，必须在 mysql.user 表中如果将 SQL SECURITY 特性指定为 INVOKER，则 DEFINER 属性无效

5.1.5 定义存储过程的参数

IN：指输入参数

该参数的值必须在调用存储过程时指定
存储过程中可以使用该参数，但它不能被返回

OUT：指输出参数

该参数可以在存储过程中发生改变，并可以返回

INOUT：指输入输出参数

该参数的值在调用存储过程时指定
在存储过程中可以被改变和返回

如果需要定义多个参数，需要使用,进行分隔

5.1.6 调用存储过程

CALL 存储过程名([参数1,参数2, …]);
# 根据存储过程的定义包含相应的参数

存储过程调用类似于Java中的方法调用

5.1.7 查看存储过程状态

SHOW PROCEDURE STATUS

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WlNvBJq2-1680502271515)(./assets/image-20230403133422488.png)]

5.1.8 查看存储创建代码

SHOW CREATE PROCEDURE 存储过程名

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HaxrdHeW-1680502271515)(./assets/image-20230403133612810.png)]

5.1.9 修改存储过程

ALTER PROCEDURE 存储过程名[特性………]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IZHB4851-1680502271515)(./assets/image-20230403133809483.png)]

5.1.10 删除存储过程

DROP PROCEDURE 存储过程名

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yLgE2dkr-1680502271516)(./assets/image-20230403133956314.png)]

5.1.11 存储过程中的变量

与Java语言类似，定义存储过程时可以使用变量

DECLARE 变量名[,变量名...] 数据类型 [DEFAULT 值];

给变量进行赋值

SET 变量名 = 表达式值[,变量名=表达式...] ;

定义存储过程时，所有局部变量的声明一定要放在存储过程体的开始；否则，会提示语法错误

系统变量

指 MySQL 全局变量，以@@开头，形式为@@变量名

用户自定义变量

局部变量
- 一般用于SQL的语句块中，如：存储过程中的BEGIN和END语句块
- 作用域仅限于定义该变量的语句块内
- 生命周期也仅限于该存储过程的调用期间
- 在存储过程执行到END时，局部变量就会被释放
会话变量
- 是服务器为每个客户端连接维护的变量，与MySQL客户端是绑定的
- 也称作用户变量
- 可以暂存值，并传递给同一连接中其他SQL语句进行使用
- 当MySQL客户端连接退出时，用户变量就会被释放
- 用户变量创建时，一般以@开头，形式为@变量名

演示案例

根据病人名称和检查项目ID输出最后一次检查时间

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_exam_GetLastExamDateByPatientNameAndDepID`(IN patient_name VARCHAR(50), IN dep_id INT,OUT last_exam_date DATETIME)
BEGIN
	#Routine body goes here...
  DECLARE patient_id INT;  #声明局部变量
  SELECT patientID INTO patient_id FROM patient WHERE patientName= patient_name;
  SELECT patient_id; #输出病人的ID
  SELECT MAX(examDate) INTO last_exam_date FROM prescription WHERE patientID = patient_id AND depID = dep_id;
END

调用存储过程

SET  @patient_name='夏颖';
SET  @dep_id =1;
CALL proc_exam_GetLastExamDateByPatientNameAndDepID(@patient_name, @dep_id, @last);

SELECT @last;

5.1.12 存储过程控制语句

与Java语言的流程控制语句类似，MySQL提供的控制语句

条件语句
- IF-ELSE IF-ELSE 条件语句
- CASE 条件语句
循环语句
- WHILE 循环
- LOOP 循环
- REPEAT循环
迭代语句

5.1.13 IF-ELSE 条件语句

IF 条件 THEN 语句列表
   [ELSEIF 条件 THEN 语句列表]
   [ELSE 语句列表]
END IF;

根据病人的家庭收入，返还补贴不同比例的医疗费用

家庭年收入在5000元以下的返还当年总医疗费用的20%
家庭年收入在10000以下的返还当年总医疗费用的15%
家庭年收入在30000以下的返还总医疗费用的5%
30000元以上或未登记的不享受医疗费用返还
输入病人编号和年份，计算该患者当年的应返还的医疗费用

CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_CH05_4`(IN patient_ID INT ,IN in_year VARCHAR(50),OUT ou_subsidy FLOAT  )
BEGIN
 DECLARE tital_Cost FLOAT;
 DECLARE totial_income FLOAT;
 
SELECT incomeMoney INTO totial_income FROM income WHERE patientID =patient_ID;

SELECT sum(checkItemCost) INTO tital_Cost FROM prescription  
INNER JOIN checkitem ON prescription.checkItemID=checkitem.checkItemID 
WHERE patientID=patient_ID AND examDate >= CONCAT(in_year,'-01-01') 
AND examDate <= CONCAT(in_year,'-12-31');


IF totial_income>=0 AND totial_income<5000 THEN
	SET ou_subsidy =tital_Cost*0.2;
ELSEIF totial_income>=5000 AND totial_income<10000 THEN
	SET ou_subsidy =tital_Cost*0.15;
ELSEIF totial_income>=10000 AND totial_income<30000 THEN
	SET ou_subsidy =tital_Cost*0.05;
ELSE
	SET ou_subsidy =0;
END IF;
END

5.1.14 CASE 条件语句

CASE
   WHEN 条件 THEN 语句列表
   [WHEN 条件 THEN 语句列表]
   [ELSE 语句列表]
END CASE;

CASE 列名
   WHEN 条件值 THEN 语句列表
   [WHEN 条件值 THEN 语句列表]
   [ELSE 语句列表]
END CASE;

使用CASE语句实现返还补贴不同比例的医疗费用

CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_CH05_5`(IN patient_ID INT ,IN in_year VARCHAR(50),OUT ou_subsidy FLOAT  )
BEGIN
 DECLARE tital_Cost FLOAT;
 DECLARE totial_income FLOAT;
 
SELECT incomeMoney INTO totial_income FROM income WHERE patientID =patient_ID;


SELECT sum(checkItemCost) INTO tital_Cost FROM prescription  
INNER JOIN checkitem ON prescription.checkItemID=checkitem.checkItemID 
WHERE patientID=patient_ID AND examDate >= CONCAT(in_year,'-01-01') 
AND examDate <= CONCAT(in_year,'-12-31');


CASE 
	WHEN totial_income>=0 AND totial_income<5000 THEN
		SET ou_subsidy =tital_Cost*0.2;
	WHEN totial_income>=5000 AND totial_income<10000 THEN
		SET ou_subsidy =tital_Cost*0.15;
	WHEN totial_income>=10000 AND totial_income<30000 THEN
		SET ou_subsidy =tital_Cost*0.05;
	WHEN totial_income>=30000 AND totial_income<0 THEN
		SET ou_subsidy =0;
END CASE;

END

在某种情况下（例如，做等值判断），使用第二种写法更加简洁但是，因为CASE后面有列名，功能上会有一些限制

5.1.15 WHILE 循环语句

[label:] WHILE 条件 DO
   语句列表
END WHILE [label]

首先判断条件是否成立。如果成立，则执行循环体
label为标号，用于区分不同的循环，可省略
用在begin、repeat、while 或者loop 语句前

假设有测试表test，有Id字段、Val字段

根据输入的行数要求，批量插入测试数据

DECLARE rand_val FLOAT;
WHILE rows > 0 DO
  SELECT RAND() INTO rand_val;
  INSERT INTO test VALUES(NULL, rand_val);
  SET rows = rows - 1;
END WHILE;

5.1.16 LOOP 循环语句

[label:] LOOP
   语句列表
END LOOP [label] ;

不需判断初始条件，直接执行循环体

LEAVE label ;

遇到 LEAVE 语句，退出循环

批量插3个新的检查项目，检查项目名称为胃镜、肠镜和支气管纤维镜，各项检查的价格均为70元

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_checkitem_insert`( IN checkitems VARCHAR(100))
BEGIN
 	DECLARE comma_pos INT;
  DECLARE current_checkitem VARCHAR(20);
	loop_label: LOOP
		SET comma_pos = LOCATE(',', checkitems);
		SET current_checkitem = SUBSTR(checkitems, 1, comma_pos-1);
		IF current_checkitem <> '' THEN
			SET checkitems = SUBSTR(checkitems, comma_pos+1);
    ELSE
      SET current_checkitem = checkitems;
    END IF;
    INSERT INTO checkitem(checkItemName,checkItemCost) VALUES(current_checkitem,70);
		IF comma_pos=0 OR current_checkitem='' THEN
      LEAVE loop_label;
      # 退出loop_label标识的程序块
    END IF;
	END LOOP loop_label;
	# LOOP循环结束
END

5.1.17 REPEAT 循环语句

[label:] REPEAT
   语句列表
UNTIL 条件
END REPEAT [label]

先执行循环操作再判断循环条件
与 LOOP 循环语句相比较相同点
不需要初始条件直接进入循环体
不同点：REPEAT 语句可以设置退出条件

使用REPEAT循环语句编码实现，根据输入的行数要求，向测试表test中批量插入测试数据

CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_CH05_7`(IN rows INT )
BEGIN
  
	DECLARE rand FLOAT;
	
	REPEAT
	SELECT RAND() INTO rand;
		INSERT INTO test (val)VALUES(rand);
		SET rows = rows -1 ;
  UNTIL rows <= 0 END REPEAT;

END

5.1.18 迭代语句

ITERATE label;

从当前代码处返回到程序块开始位置，重新执行
ITERATE关键字可以嵌入到LOOP、WHILE和REPEAT程序块中

输入需增加数据行数，随机产生的测试数据必须大于0.5

CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_CH05_8`(IN rows INT)
BEGIN 
	DECLARE rand FLOAT; 
	random_lbl : REPEAT
		SELECT RAND() INTO rand; 
			IF rand< 0.5 THEN 
				ITERATE random_lbl; 
			END IF;
		INSERT INTO test (val) VALUES (rand);
		SET rows=rows-1; 
	UNTIL rows<=0 END REPEAT; 
END

查看全文

http://www.kler.cn/a/6514.html