MySQL—存储过程详解

基本介绍

存储过程和函数是数据库中预先编译并存储的一组SQL语句集合。它们的主要目的是提高代码的复用性、减少数据传输、简化业务逻辑处理，并且一旦编译成功，可以永久有效。

存储过程和函数的好处

  • 提高代码的复用性：存储过程和函数可以在多个地方重复使用，减少了代码的冗余。
  • 减少数据传输：通过在数据库服务器上执行逻辑操作，减少了数据在数据库和应用服务器之间的传输，提高了传输效率。
  • 减少代码层面的业务处理：将复杂的业务逻辑封装在存储过程或函数中，简化了应用层的代码。
  • 一次编译永久有效：存储过程和函数一旦编译成功，可以在数据库中永久使用，无需每次执行时重新编译。

存储过程和函数的区别

  • 存储函数：必须有返回值。存储函数通常用于执行一些计算或转换操作，并返回一个结果。
  • 存储过程：可以没有返回值。存储过程通常用于执行一系列操作，如插入、更新、删除等，不一定需要返回结果。

基本操作

DELIMITER

  • DELIMITER 关键字：用于声明 SQL 语句的分隔符，告诉 MySQL 该段命令已经结束。
  • 默认分隔符：MySQL 语句的默认分隔符是分号 ;。
  • 修改分隔符：当需要在一条功能 SQL 语句中包含分号，但不作为结束标识时，可以使用 DELIMITER 来指定新的分隔符。
DELIMITER 分隔符

存储过程的创建、调用、查看和删除

创建存储过程：

-- 修改分隔符为$
DELIMITER $-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)
BEGINsql语句;
END$-- 修改分隔符为分号
DELIMITER ;

调用存储过程：

CALL 存储过程名称(实际参数);

查看存储过程（MySQL 8.0 及以上版本）：

SHOW PROCEDURE STATUS WHERE db='数据库名称';

删除存储过程：

DROP PROCEDURE [IF EXISTS] 存储过程名称;

数据练习

-- 数据准备
CREATE TABLE student (id INT PRIMARY KEY,NAME VARCHAR(50),age INT,gender CHAR(1),score INT
);INSERT INTO student (id, NAME, age, gender, score) VALUES
(1, '张三', 23, '男', 95),
(2, '李四', 24, '男', 98),
(3, '王五', 25, '女', 100),
(4, '赵六', 26, '女', 90);-- 创建存储过程
DELIMITER $CREATE PROCEDURE stu_group()
BEGINSELECT gender, SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$DELIMITER ;-- 调用存储过程
CALL stu_group();-- 查看存储过程
SHOW PROCEDURE STATUS WHERE Db = 'test';-- 删除存储过程
DROP PROCEDURE IF EXISTS stu_group;

存储过程语法

变量使用

在存储过程中，可以使用变量来存储和操作数据。变量可以是局部的，也可以是全局的。局部变量只能在 BEGIN ... END 块中使用。

定义变量：

DECLARE 变量名 数据类型 [DEFAULT 默认值];

变量赋值：

SET 变量名 = 变量值;
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];

示例：

DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN-- 定义两个变量DECLARE men, women INT;-- 查询男同学的总分数，为men赋值SELECT SUM(score) INTO men FROM student WHERE gender='男';-- 查询女同学的总分数，为women赋值SELECT SUM(score) INTO women FROM student WHERE gender='女';-- 使用变量SELECT men, women;
END$
DELIMITER ;
-- 调用存储过程
CALL pro_test3();

IF 语句

IF 语句用于条件判断。

语法：

IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;

示例： 

DELIMITER $
CREATE PROCEDURE pro_test4()
BEGINDECLARE total INT;							-- 定义总分数变量DECLARE description VARCHAR(10);			-- 定义分数描述变量SELECT SUM(score) INTO total FROM student; 	-- 为总分数变量赋值-- 判断总分数IF total >= 380 THENSET description = '学习优秀';ELSEIF total >= 320 AND total < 380 THENSET description = '学习良好';ELSESET description = '学习一般';END IF;-- 查询分数描述信息SELECT description;
END$
DELIMITER ;
-- 调用pro_test4存储过程
CALL pro_test4();

参数传递

存储过程可以接受参数，参数可以是输入参数、输出参数或输入输出参数。

语法：

DELIMITER $
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN执行的sql语句;
END$
DELIMITER ;

示例：

DELIMITER $
CREATE PROCEDURE pro_test6(IN total INT, OUT description VARCHAR(10))
BEGIN-- 判断总分数IF total >= 380 THEN SET description = '学习优秀';ELSEIF total >= 320 AND total < 380 THEN SET description = '学习不错';ELSE SET description = '学习一般';END IF;
END$
DELIMITER ;
-- 调用pro_test6存储过程
CALL pro_test6(310, @description);
CALL pro_test6((SELECT SUM(score) FROM student), @description);
-- 查询总成绩描述
SELECT @description;

查看参数方法

  • @变量名 : 用户会话变量，代表整个会话过程他都是有作用的，类似于全局变量
  • @@变量名 : 系统变量

CASE 语句

CASE 语句用于多条件判断。

语法：

CASE 表达式WHEN 值1 THEN 执行sql语句1;[WHEN 值2 THEN 执行sql语句2;]...[ELSE 执行sql语句n;]
END CASE;

示例：

DELIMITER $
CREATE PROCEDURE pro_test7(IN total INT)
BEGIN-- 定义变量DECLARE description VARCHAR(10);-- 使用case判断CASEWHEN total >= 380 THENSET description = '学习优秀';WHEN total >= 320 AND total < 380 THENSET description = '学习不错';ELSE SET description = '学习一般';END CASE;-- 查询分数描述信息SELECT description;
END$
DELIMITER ;
-- 调用pro_test7存储过程
CALL pro_test7(390);
CALL pro_test7((SELECT SUM(score) FROM student));

WHILE 循环

WHILE 循环用于在条件为真时重复执行代码块。

语法：

WHILE 条件判断语句 DO循环体语句;条件控制语句;
END WHILE;

示例：

计算 1~100 之间的偶数和

DELIMITER $
CREATE PROCEDURE pro_test8()
BEGIN-- 定义求和变量DECLARE result INT DEFAULT 0;-- 定义初始化变量DECLARE num INT DEFAULT 1;-- while循环WHILE num <= 100 DOIF num % 2 = 0 THENSET result = result + num;END IF;SET num = num + 1;END WHILE;-- 查询求和结果SELECT result;
END$
DELIMITER ;
-- 调用pro_test8存储过程
CALL pro_test8();

REPEAT 循环

REPEAT 循环用于在条件为真之前重复执行代码块。

语法：

初始化语句;
REPEAT循环体语句;条件控制语句;UNTIL 条件判断语句
END REPEAT;

示例：

DELIMITER $
CREATE PROCEDURE pro_test9()
BEGIN-- 定义求和变量DECLARE result INT DEFAULT 0;-- 定义初始化变量DECLARE num INT DEFAULT 1;-- repeat循环REPEAT-- 累加SET result = result + num;-- 让num+1SET num = num + 1;-- 停止循环UNTIL num > 10END REPEAT;-- 查询求和结果SELECT result;
END$
DELIMITER ;
-- 调用pro_test9存储过程
CALL pro_test9();

LOOP 循环

LOOP 循环用于无条件循环，直到使用 LEAVE 语句退出循环。

语法：

[循环名称:] LOOP条件判断语句[LEAVE 循环名称;]循环体语句;条件控制语句;
END LOOP 循环名称;

示例：

计算 1~10 之间的和

DELIMITER $
CREATE PROCEDURE pro_test10()
BEGIN-- 定义求和变量DECLARE result INT DEFAULT 0;-- 定义初始化变量DECLARE num INT DEFAULT 1;-- loop循环l:LOOP-- 条件成立，停止循环IF num > 10 THENLEAVE l;END IF;-- 累加SET result = result + num;-- 让num+1SET num = num + 1;END LOOP l;-- 查询求和结果SELECT result;
END$
DELIMITER ;
-- 调用pro_test10存储过程
CALL pro_test10();

游标

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理

  • 游标可以遍历返回的多行结果，每次拿到一整行数据

  • 简单来说游标就类似于集合的迭代器遍历

  • MySQL 中的游标只能用在存储过程和函数中

语法：

-- 声明一个游标，用于处理特定的查询结果集
DECLARE 游标名称 CURSOR FOR 查询sql语句;-- 打开游标，执行对应的SQL查询
OPEN 游标名称;-- 从游标中提取一行数据到变量中，这些变量用于存储该行的数据
FETCH 游标名称 INTO 变量名1,变量名2,...;-- 关闭游标，释放其占用的资源
CLOSE 游标名称;

Mysql 通过一个 Error handler 声明来判断指针是否到尾部，并且必须和创建游标的 SQL 语句声明在一起：

DECLARE EXIT HANDLER FOR NOT FOUND (do some action，一般是设置标志变量)

示例：

-- 创建一个名为stu_score的表，用于存储学生成绩
-- 该表包含两个字段：id（自增主键）和score（成绩）
CREATE TABLE stu_score (id INT PRIMARY KEY AUTO_INCREMENT,score INT
);-- 改变结束符为$，以便在存储过程中有更多的灵活性
DELIMITER $
-- 创建一个名为pro_test12的存储过程
CREATE PROCEDURE pro_test12()
BEGIN-- 定义一个变量s_score，用于存储学生的成绩DECLARE s_score INT;-- 定义一个变量flag，用于标记游标数据是否结束，默认为0DECLARE flag INT DEFAULT 0;-- 创建一个游标stu_result，用于获取所有学生表中的成绩数据DECLARE stu_result CURSOR FOR SELECT score FROM student;-- 当游标数据结束后，将flag设置为1，用于控制重复循环的结束DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;-- 打开游标stu_result，准备获取数据OPEN stu_result;-- 重复执行以下语句，直到游标数据结束REPEAT-- 使用FETCH从游标中获取数据，并存储到s_score变量中FETCH stu_result INTO s_score;-- 将获取的成绩数据插入到stu_score表中INSERT INTO stu_score VALUES (NULL, s_score);-- 当flag等于1时结束循环，即游标数据已全部处理完毕UNTIL flag = 1END REPEAT;-- 关闭游标stu_result，释放资源CLOSE stu_result;
END$
-- 恢复结束符为默认的分号
DELIMITER ;-- 调用pro_test12存储过程，执行存储过程中的逻辑
CALL pro_test12();
-- 查询stu_score表中的所有数据，以验证存储过程的结果
SELECT * FROM stu_score;

存储函数

存储函数和存储过程非常相似，但存储函数有返回值，而存储过程没有返回值（尽管存储过程可以通过 OUT 参数返回数据）。

创建存储函数

语法：

DELIMITER $
-- 标准语法
CREATE FUNCTION 函数名称(参数 数据类型)
RETURNS 返回值类型
BEGIN执行的sql语句;RETURN 结果;
END$
DELIMITER ;

当在 MySQL 中创建存储函数时，如果遇到 [HY000][1418] This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled 错误，这是因为 MySQL 的二进制日志（binary logging）默认要求存储函数必须是确定性的（DETERMINISTIC）、不执行 SQL 语句（NO SQL）或只读取 SQL 数据（READS SQL DATA）。

解决方案

  • 可以在存储函数的声明中添加 DETERMINISTIC、NO SQL 或 READS SQL DATA 关键字。 

示例：

定义一个存储函数 fun_test，获取学生表中成绩大于 95 分的学生数量。

DELIMITER $
CREATE FUNCTION fun_test()
RETURNS INT
DETERMINISTIC
BEGIN-- 定义统计变量DECLARE result INT;-- 查询成绩大于95分的学生数量，给统计变量赋值SELECT COUNT(score) INTO result FROM student WHERE score > 95;-- 返回统计结果RETURN result;
END$
DELIMITER ;

调用存储函数

由于存储函数有返回值，所以使用 SELECT 调用：

SELECT 函数名称(实际参数);

删除存储函数

DROP FUNCTION 函数名称;

完整代码 

-- 数据准备
CREATE TABLE student (id INT PRIMARY KEY,NAME VARCHAR(50),age INT,gender CHAR(1),score INT
);INSERT INTO student (id, NAME, age, gender, score) VALUES
(1, '张三', 23, '男', 95),
(2, '李四', 24, '男', 98),
(3, '王五', 25, '女', 100),
(4, '赵六', 26, '女', 90);-- 创建存储函数
DELIMITER $
CREATE FUNCTION fun_test()
RETURNS INT
DETERMINISTIC
BEGIN-- 定义统计变量DECLARE result INT;-- 查询成绩大于95分的学生数量，给统计变量赋值SELECT COUNT(score) INTO result FROM student WHERE score > 95;-- 返回统计结果RETURN result;
END$
DELIMITER ;-- 调用fun_test存储函数
SELECT fun_test();-- 删除存储函数
DROP FUNCTION fun_test;

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.xdnf.cn/news/150428.html

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章

记某地级市护网的攻防演练行动

记某地级市护网的攻防演练行动

0x1 前言 哈喽&#xff0c;师傅们&#xff01; 这次给师傅们分享的是上上个星期的地级市护网的攻防演练的两个案例&#xff0c;涉及到的知识点可能比较偏&#xff0c;下面我也会提前给师傅们拓展下改漏洞相关的知识点内容。护网攻防演练中&#xff0c;涉及到的很多敏感内容这…
阅读更多...
【Linux】驱动的基本架构和编译

【Linux】驱动的基本架构和编译

驱动源码 /** Silicon Integrated Co., Ltd haptic sih688x haptic driver file** Copyright (c) 2021 kugua <daokuan.zhusi-in.com>** This program is free software; you can redistribute it and/or modify it* under the terms of the GNU General Public Licen…
阅读更多...
css实现自定义静态进度条-vue2

css实现自定义静态进度条-vue2

实现如图所示 html&#xff1a; <div class"progress-container"><div class"progress-box left" :style"leftStyle"><div class"progress-value-top left">总中标电量</div><div class"progress-val…
阅读更多...
前端请求音频返回pcm流进行播放

前端请求音频返回pcm流进行播放

业务场景是chat回答&#xff0c;点击播放则会将回答内容进行请求&#xff0c;返回音频数据流进行播放 实现方案&#xff0c;因为后端返回的是流式接口&#xff0c;但是流式接口我去截取后用自己完成的流式播放器方法进行播放会存在杂音&#xff0c;但是短句接口返回速度尚可&a…
阅读更多...
composer环境变量（phpstudy集成环境）无法使用问题

composer环境变量（phpstudy集成环境）无法使用问题

composer 不是内部或外部命令,也不是可运行的程序 或批处理文件。 按下WinR组合键打开“运行”&#xff0c;输入sysdm.cpl 回车&#xff0c;打开“系统属性”并切换至“高级”选项卡&#xff0c;点击“环境变量”进行配置 配置完后点击确定&#xff0c;重新打开命令行&#x…
阅读更多...
Bootstrap框架-container类，container-fluid类，栅格系统

Bootstrap框架-container类，container-fluid类，栅格系统

1.Bootstrap Bootstrap为页面内容和栅格系统包裹了一个.container容器&#xff0c;框架预先定义类 1.1container类 响应式布局容器的宽度 手机-小于768px 宽度设置100%&#xff1b; 平板-大于等于768px 设置宽度为750px 桌面显示器-大于等于992px 设置宽度 970px 大屏幕显…
阅读更多...
康养为松，智能为鹤：华为全屋智能画出的松鹤长春图

康养为松，智能为鹤：华为全屋智能画出的松鹤长春图

在道家文化中&#xff0c;喜欢将松与鹤并举&#xff0c;以其长寿与仙逸表达对老年人的美好祝愿。松鹤延年、松龄鹤寿等成语皆出于此。松鹤长春图&#xff0c;也成为国画当中的经久不衰的题材。 当我们迎来老龄化时代&#xff0c;“松鹤长春”则成为了整个社会的共同期待。 根据…
阅读更多...
【初阶数据结构】排序——插入排序

【初阶数据结构】排序——插入排序

目录 前言直接插入排序希尔排序 前言 排序&#xff1a;所谓排序就是使一串记录&#xff0c;按照其中的某个或某些关键字的大小&#xff0c;递增或递减的排列起来的操作。排序算法&#xff0c;就是如何使得记录按照要求排列的方法。   例如&#xff1a;买东西时会根据销量或价…
阅读更多...
java并发编程笔记 之 线程和进程

java并发编程笔记 之 线程和进程

文章目录 前言线程线程优先级和时间片创建多线程及运行线程的状态 进程查看进程的命令进程的通信方式 线程和进程的区别从关系上疑问集锦 前言 并发 1、并发是指在同一时间段内&#xff0c;计算机系统能够处理多个任务的能力。 2、在并发编程中&#xff0c;我们可以理解为多个…
阅读更多...
代码随想录算法训练营第三十九天 | 198.打家劫舍 ，213.打家劫舍II，337.打家劫舍III

代码随想录算法训练营第三十九天 | 198.打家劫舍 ，213.打家劫舍II，337.打家劫舍III

第三十九天打卡&#xff0c;今天解决打家劫舍系列问题&#xff0c;树形dp比较难。 198.打家劫舍 题目链接 解题过程 dp[i]&#xff1a;考虑下标i&#xff08;包括i&#xff09;以内的房屋&#xff0c;最多可以偷窃的金额为dp[i]。 要么不偷这一间&#xff0c;那就是前面那间…
阅读更多...
毕业设计选题：基于ssm+vue+uniapp的校园失物招领小程序

毕业设计选题：基于ssm+vue+uniapp的校园失物招领小程序

开发语言&#xff1a;Java框架&#xff1a;ssmuniappJDK版本&#xff1a;JDK1.8服务器&#xff1a;tomcat7数据库&#xff1a;mysql 5.7&#xff08;一定要5.7版本&#xff09;数据库工具&#xff1a;Navicat11开发软件&#xff1a;eclipse/myeclipse/ideaMaven包&#xff1a;M…
阅读更多...
大瓜-CSP-J/S2024第一轮认证题目涉嫌泄露。竞赛公平能否维护？

大瓜-CSP-J/S2024第一轮认证题目涉嫌泄露。竞赛公平能否维护？

2024年全国信息学奥赛&#xff08;CSP-J/S&#xff09;泄题事件在竞赛界掀起了巨大的波澜。这场赛事本应是全国最具公信力的编程竞赛之一&#xff0c;但部分题目在考试前已被某些培训机构押中&#xff0c;这一泄题行为不仅让考生与家长感到愤怒&#xff0c;也让公众对奥赛的公平…
阅读更多...
scp 命令：在两台主机间远程传输文件

scp 命令：在两台主机间远程传输文件

一、命令简介 ​scp​ 命令使用 SSH ​加密的方式在本地主机和远程主机之间复制文件。 ‍ 二、命令参数 格式 scp [选项] 发送方主机和目录 接收方主机和目录注意&#xff1a;左边是发送方&#xff0c;右边是接收方。固定格式。 示例 #示例1 scp ~/test.txt soulio172.1…
阅读更多...
豆包MarsCode体验

豆包MarsCode体验

这个AI助手贴合做题者的思路&#xff0c;可以实时对代码进行分析&#xff0c;提出纠错、优化、规范性意见&#xff0c;非常好用。
阅读更多...
基于数据挖掘的航空客户满意度分析预测系统

基于数据挖掘的航空客户满意度分析预测系统

温馨提示&#xff1a;文末有 CSDN 平台官方提供的学长 QQ 名片 :) 1. 项目简介 航空公司致力于提供多样化的服务以满足乘客需求&#xff0c;包括但不限于提供免费无线网络、免费食物饮品、提供网上预约服务、飞机出口位置、座椅舒适度、卫生状况等&#xff0c;并希望以此提升乘…
阅读更多...
构造者模式多种实现方式

构造者模式多种实现方式

构造者模式 ​ 构造者模式建议将对象构造代码从产品类中抽取出来&#xff0c; 并将其放在一个名为构造者的独立对象中 ​ 构建者模式也是用来创建对象&#xff0c;但是相对于工厂模式来说&#xff0c;建造者模式适用于构建复杂对象&#xff0c;而工厂模式适用于创建对象的封装…
阅读更多...
asp.net core日志与异常处理小结

asp.net core日志与异常处理小结

asp.net core的webApplicationBuilder中自带了一个日志组件,无需手动注册服务就能直接在控制器中构造注入&#xff0c;本文主要介绍了net core日志与异常处理小结&#xff0c;需要的朋友可以参考下 ILogger简单使用 asp.net core的webApplicationBuilder中自带了一个日志组件…
阅读更多...
网络安全-长亭雷池waf的sql绕过，安全狗绕过（5种绕过3+2）

网络安全-长亭雷池waf的sql绕过，安全狗绕过（5种绕过3+2）

目录 一、环境 二、讲解 三、绕过前思路整理 3.1 思路 3.1.1 入门思路 0x00截断filename 3.1.2 双写上传描述行(差异绕过&#xff09;【成功】 3.1.3双写整个 part 开头部分 3.1.4 构造假的 part 部分 1【成功】 3.1.5 构造假的 part 部分2【成功】 3.1.6 两个 bounda…
阅读更多...
闲盒支持的组网方式和注意事项

闲盒支持的组网方式和注意事项

1. 直连光猫拨号​ 通过光猫拨号&#xff0c;设备直连光猫的设备&#xff0c;需要对光猫开启UPNP并关闭DMZ 如果只接一个盒子&#xff0c;建议直接针对盒子IP开dmz。 2. 直连路由器​ 通过路由器拨号&#xff0c;设备直连路由器的设备&#xff0c;需要对路由器开启UPNP并关闭…
阅读更多...
Sql Developer日期显示格式设置

Sql Developer日期显示格式设置

默认时间格式显示 设置时间格式&#xff1a;工具->首选项->数据库->NLS->日期格式: DD-MON-RR 修改为: YYYY-MM-DD HH24:MI:SS 设置完格式显示&#xff1a;
阅读更多...
最新文章

Copyright @ 2022~2023