当前位置: 首页 > news >正文

五 MySQL 存储过程

五、企业级开发技术

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; #输出病人的IDSELECT 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  )
BEGINDECLARE 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 THENSET ou_subsidy =tital_Cost*0.2;
ELSEIF totial_income>=5000 AND totial_income<10000 THENSET ou_subsidy =tital_Cost*0.15;
ELSEIF totial_income>=10000 AND totial_income<30000 THENSET ou_subsidy =tital_Cost*0.05;
ELSESET ou_subsidy =0;
END IF;
END

5.1.14 CASE 条件语句

CASEWHEN 条件 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  )
BEGINDECLARE 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 THENSET ou_subsidy =tital_Cost*0.2;WHEN totial_income>=5000 AND totial_income<10000 THENSET ou_subsidy =tital_Cost*0.15;WHEN totial_income>=10000 AND totial_income<30000 THENSET ou_subsidy =tital_Cost*0.05;WHEN totial_income>=30000 AND totial_income<0 THENSET 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 DOSELECT 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))
BEGINDECLARE comma_pos INT;DECLARE current_checkitem VARCHAR(20);loop_label: LOOPSET comma_pos = LOCATE(',', checkitems);SET current_checkitem = SUBSTR(checkitems, 1, comma_pos-1);IF current_checkitem <> '' THENSET checkitems = SUBSTR(checkitems, comma_pos+1);ELSESET current_checkitem = checkitems;END IF;INSERT INTO checkitem(checkItemName,checkItemCost) VALUES(current_checkitem,70);IF comma_pos=0 OR current_checkitem='' THENLEAVE 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 )
BEGINDECLARE rand FLOAT;REPEATSELECT 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 : REPEATSELECT 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

相关文章:

五 MySQL 存储过程

五、企业级开发技术 5.1 存储过程 关于存储过程我只能说请看下图&#xff0c;这是阿里巴巴发布的《阿里巴巴Java开发手册&#xff08;终极版&#xff09;v1.3版本》在 MySQL 第七条中强制指出禁止使用存储过程 所以对于存储过程不必深究&#xff0c;做到会写能看懂即可 [外链…...

【指针函数和函数指针】

指针函数和函数指针1. 概述2. 案例分析指针函数函数指针1. 概述 函数指针和指针函数是两个不同的概念。 函数指针是指一个指针变量&#xff0c;该指针变量存储了一个函数的地址。通过函数指针可以实现动态调用函数&#xff0c;根据需要在程序运行时指定要调用的函数。函数指针的…...

实现卡片高度增加时的缓动动画效果

在开发中&#xff0c;我们可能会遇到需要让卡片高度由内容撑起&#xff08;即不能手动设置height&#xff09;&#xff0c;并且在高度增加时增加缓动动画的需求。本文将介绍几种实现方式。 文章目录方法1&#xff1a;使用CSS的max-height属性和:hover伪类特定例子&#xff1a;鼠…...

什么是HRMS?哪些工作需要使用HRMS?

当今企业的发展离不开技术支持&#xff0c;同样&#xff0c;在管理方面也需要与时俱进&#xff0c;进行数字化转型。人力资源技术的运用是企业管理数字化转型的重要表现之一。在企业选择一款HR软件之前&#xff0c;应该先认识到&#xff0c;什么是人力资源管理软件——即HRMS。…...

【C语言蓝桥杯每日一题】—— 饮料换购

【C语言蓝桥杯每日一题】—— 饮料换购&#x1f60e;前言&#x1f64c;饮料换购&#x1f64c;喝汽水问题&#x1f64c;饮料换购解题源码分享 &#x1f60a;总结撒花&#x1f49e;&#x1f60e;博客昵称&#xff1a;博客小梦 &#x1f60a;最喜欢的座右铭&#xff1a;全神贯注的…...

PMP适合哪些人考?

其实很多小白在最开始了解PMP考试的时候都会有同一个问题&#xff0c;那就是&#xff1a; “我适不适合考PMP&#xff1f;” 如果想做管理&#xff0c;那么一定要考PMP证书。PMP证书是国际认证&#xff0c;在国内的认可度也很高&#xff0c;可以说是管理岗位的入门认证。注意…...

中华好诗词大学季第二季(二)

第四期 1,宋代林升的《题临安邸》是一首著名的墙头诗&#xff0c;请问这里的”邸“指的是什么&#xff1f;旅店 2,宋代林升的《题临安邸》的“临安”是指那个城市&#xff1f;杭州 3,“申黜褒女进&#xff0c;班去赵姬升”具体写到了历史上那四个女人 申皇后&#xff0c;褒…...

【Linux】时间日期指令、查找指令、压缩和解压指令

目录1 时间日期类1.1 date指令-显示当前日期1.2 date指令-设置日期1.3 cal指令2 搜索查找类2.1 find指令2.2 locate指令2.3 grep指令和管道符号 |3 压缩和解压类3.1 gzip/gunzip 指令3.2 zip/unzip 指令1 时间日期类 1.1 date指令-显示当前日期 基本语法 date (功能描述:显示…...

python社区志愿者服务管理系统-vue

本系统主要实现一个基于web的校园志愿者活动系统。此网站是为了给予在校生通过网上报名来参加志愿者活动&#xff0c;省去了各种班群申报的中间环节。利用数据库和python进行web开发。 能实现的基本功能如下&#xff1a; (1)登陆、注册的功能&#xff1a;用户填写用户名和密码进…...

计算机网络 常见网卡信息

文章目录1. PCI 网卡2. PCI Express 网卡3. USB网卡4. 无线网卡万兆网卡光纤网卡1. PCI 网卡 接口类型&#xff1a;PCI 传输速率&#xff1a;10/100Mbps或1000Mbps 支持协议&#xff1a;TCP/IP、UDP、IPX/SPX等 缓存大小&#xff1a;通常为64KB或128KB 2. PCI Express 网卡 …...

Python 自动化指南(繁琐工作自动化)第二版:附录 B:运行程序

原文&#xff1a;https://automateboringuff.com/2e/appendixb/ 如果您在 Mu 中打开了一个程序&#xff0c;运行它只需按 F5 或单击窗口顶部的运行按钮。这是一种在编写程序的同时运行程序的简单方法&#xff0c;但是打开 Mu 来运行你完成的程序可能是一种负担。根据您使用的操…...

自然语言处理实战项目2-文本关键词抽取和关键词分值评估

大家好&#xff0c;我是微学AI&#xff0c;今天给大家带来自然语言处理实战项目2-文本关键词抽取和关键词分值评估。关键词抽取是自然语言处理中的重要任务&#xff0c;也是基础任务。 一、关键词抽取传统方法 1.基于统计的方法&#xff1a; 基于统计的方法是通过对一组文本…...

软件测试面试,项目经验板块如何答?初中高级测试工程师都问什么?

目录&#xff1a;导读前言一、Python编程入门到精通二、接口自动化项目实战三、Web自动化项目实战四、App自动化项目实战五、一线大厂简历六、测试开发DevOps体系七、常用自动化测试工具八、JMeter性能测试九、总结&#xff08;尾部小惊喜&#xff09;前言 项目经验相关的问题…...

软件测试之测试用例的设计

对于测试工作而言&#xff0c;最重要的无疑就是测试用例的设计。好的测试用例可以帮助测试人员更好更快地发现软件中的错误&#xff0c;对于提高产品质量意义重大。本文就是针对测试用例的设计方法。 文章目录测试用例的基本要素测试用例的设计设计测试用例的具体方法等价类划分…...

MySQL安装与配置(保姆级教程)

MySQL安装 我们进入Mysql的官网进行下载MySQL Community Edition(GPL),这里我们以8.0.32.0版本为例&#xff0c;点击下面进行下载&#xff1a; MySQL Community Edition(GPL) 此时我们选择下面一个32位(64位的系统也选他)&#xff0c;上面那个是在线安装等待时间比较长 当然我们…...

MATLAB算法实战应用案例精讲-【元启发式算法】随机蛙跳跃算法(SFLA)(附matlab代码实现)

目录 前言 知识储备 多目标优化问题 多目标元启发式优化方法 算法原理 数...

内网穿透:远程访问内网IP中的电脑

需求&#xff1a;家里电脑在路由器内网中&#xff0c;能连外网。想在外地时能ssh&#xff08;也即vscode&#xff09;访问家里的电脑。 家里电脑系统&#xff1a;win11&#xff08;Ubuntu流程也一模一样&#xff09; 具体流程 家里电脑下载【花生壳】内网穿透软件并登录&#…...

day4 selenium爬取数据总结

day4 selenium爬取数据 一、selenium基本操作 导入相关模块&#xff1a; from selenium.webdriver import Chrome(一)、创建浏览器对象 b Chrome()(二)、打开网页&#xff08;需要爬取哪个页面的数据就打开该页面对应的网页地址&#xff09; 案例&#xff1a;爬取豆瓣电影…...

信息收集之WAF绕过

信息收集之WAF绕过前言一、工具进行目录扫描1. 工具的下载2. 工具的使用二、Python代码进行目录扫描前言 对于web安全无WAF的信息收集&#xff0c;大家可以查看如下链接的文章&#xff1a; web安全之信息收集 对于有WAF信息收集&#xff0c;看如下所示&#xff1a;&#xff08;…...

从数据到智慧,TOOM舆情监测系统让你的决策更加精准!

当今社会信息化程度日益提高&#xff0c;网络平台已成为人们获取最新信息的主要途径&#xff0c;无论是个体还是组织、政府还是企业&#xff0c;都需要通过各种手段及时了解社会舆情&#xff0c;把握市场动态&#xff0c;调整经营策略。而舆情监测系统无疑是这些手段中最为有效…...

Docker 运行 Kafka 带 SASL 认证教程

Docker 运行 Kafka 带 SASL 认证教程 Docker 运行 Kafka 带 SASL 认证教程一、说明二、环境准备三、编写 Docker Compose 和 jaas文件docker-compose.yml代码说明&#xff1a;server_jaas.conf 四、启动服务五、验证服务六、连接kafka服务七、总结 Docker 运行 Kafka 带 SASL 认…...

Java多线程实现之Callable接口深度解析

Java多线程实现之Callable接口深度解析 一、Callable接口概述1.1 接口定义1.2 与Runnable接口的对比1.3 Future接口与FutureTask类 二、Callable接口的基本使用方法2.1 传统方式实现Callable接口2.2 使用Lambda表达式简化Callable实现2.3 使用FutureTask类执行Callable任务 三、…...

数据链路层的主要功能是什么

数据链路层&#xff08;OSI模型第2层&#xff09;的核心功能是在相邻网络节点&#xff08;如交换机、主机&#xff09;间提供可靠的数据帧传输服务&#xff0c;主要职责包括&#xff1a; &#x1f511; 核心功能详解&#xff1a; 帧封装与解封装 封装&#xff1a; 将网络层下发…...

拉力测试cuda pytorch 把 4070显卡拉满

import torch import timedef stress_test_gpu(matrix_size16384, duration300):"""对GPU进行压力测试&#xff0c;通过持续的矩阵乘法来最大化GPU利用率参数:matrix_size: 矩阵维度大小&#xff0c;增大可提高计算复杂度duration: 测试持续时间&#xff08;秒&…...

深入解析C++中的extern关键字:跨文件共享变量与函数的终极指南

&#x1f680; C extern 关键字深度解析&#xff1a;跨文件编程的终极指南 &#x1f4c5; 更新时间&#xff1a;2025年6月5日 &#x1f3f7;️ 标签&#xff1a;C | extern关键字 | 多文件编程 | 链接与声明 | 现代C 文章目录 前言&#x1f525;一、extern 是什么&#xff1f;&…...

(转)什么是DockerCompose?它有什么作用?

一、什么是DockerCompose? DockerCompose可以基于Compose文件帮我们快速的部署分布式应用&#xff0c;而无需手动一个个创建和运行容器。 Compose文件是一个文本文件&#xff0c;通过指令定义集群中的每个容器如何运行。 DockerCompose就是把DockerFile转换成指令去运行。 …...

安卓基础(aar)

重新设置java21的环境&#xff0c;临时设置 $env:JAVA_HOME "D:\Android Studio\jbr" 查看当前环境变量 JAVA_HOME 的值 echo $env:JAVA_HOME 构建ARR文件 ./gradlew :private-lib:assembleRelease 目录是这样的&#xff1a; MyApp/ ├── app/ …...

Linux C语言网络编程详细入门教程:如何一步步实现TCP服务端与客户端通信

文章目录 Linux C语言网络编程详细入门教程&#xff1a;如何一步步实现TCP服务端与客户端通信前言一、网络通信基础概念二、服务端与客户端的完整流程图解三、每一步的详细讲解和代码示例1. 创建Socket&#xff08;服务端和客户端都要&#xff09;2. 绑定本地地址和端口&#x…...

安宝特案例丨Vuzix AR智能眼镜集成专业软件,助力卢森堡医院药房转型,赢得辉瑞创新奖

在Vuzix M400 AR智能眼镜的助力下&#xff0c;卢森堡罗伯特舒曼医院&#xff08;the Robert Schuman Hospitals, HRS&#xff09;凭借在无菌制剂生产流程中引入增强现实技术&#xff08;AR&#xff09;创新项目&#xff0c;荣获了2024年6月7日由卢森堡医院药剂师协会&#xff0…...

MySQL JOIN 表过多的优化思路

当 MySQL 查询涉及大量表 JOIN 时&#xff0c;性能会显著下降。以下是优化思路和简易实现方法&#xff1a; 一、核心优化思路 减少 JOIN 数量 数据冗余&#xff1a;添加必要的冗余字段&#xff08;如订单表直接存储用户名&#xff09;合并表&#xff1a;将频繁关联的小表合并成…...