news 2026/8/28 16:32:00

MySQL-存储过程的创建和使用

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
MySQL-存储过程的创建和使用
文章目录* *一、存储过程 * * 1.1 存储过程介绍 * 1.2 存储过程的创建与删除 * * 1.2.1 创建存储过程 * 1.2.2 删除存储过程 * 1.3存储过程的调用 * 1.4 存储过程中的变量使用 * * 1.4.1 局部变量 * 1.4.2 用户变量 * 1.4.3 将查询结果赋值给变量 * 1.5存储过程的参数 * * 1.5.1 输入参数 IN * 1.5.2 输出参数 OUT * 1.5.3 输入输出参数 INOUT * 1.6 存储过程中的流程控制 * * 1.6.1 分支语句 * 1.6.2 循环语句 * 1.7 存储过程管理 * * 1.7.1 查询存储过程 * 1.7.2 修改存储过程 * 1.7.3 删除存储过程 * 二、存储过程案例 * * 2.1 准备数据 * 2.2 创建存储过程 * * 2.2.1 创建存储过程 * 2.2.2 测试### 一、存储过程#### 1.1 存储过程介绍存储过程：> 将能够完成特定功能的SQL指令进行封装（SQL指令集），编译之后存储在数据库服务器上，并为其命名，客户端可以通过直接调用SQL指令集，获取执行结果> 存储过程解决的问题：> > * 如果有需要多次执行的SQL，每次执行都需要通过连接传递到MySQL服务器，并且需要经过编译和执行后，再返回执行结果。重复且浪费资源> * 如果需要连续执行多个SQL指令，并且第二个SQL指令需要使用第一个SQL执行的结果集作为参数。存储过程优点：> * SQL指令无需客户端编写、通过网络传输，可以节省网络开销，同时避免SQL指令在传输过程中被恶意篡改，保证安全性；> * 存储过程经过编译创建并保存在数据库服务器中，执行过程无需编译，对SQL指令的执行过程提升了性能；> * 存储过程中多个SQL指令之间存在逻辑关系，支持流程控制语句（分支、循环），可以实现更为复杂的业务逻辑处理；存储过程的缺点：> * 存储过程是根据不同数据库引擎进行编译、创建并存储在数据库中。如果需要不同类型数据库迁移，需要对数据库存储过程进行重新编写。> * 存储过程受限与数据库产品，如果需要高性能的优化会成为一个问题；> * 在互联网项目中，如果需要数据库高并发（连接）访问，存储过程会增加数据库的连接执行时间。因为我们将复杂的业务交给了数据库进行处理。#### 1.2 存储过程的创建与删除##### 1.2.1 创建存储过程> 将能够完成特定功能的SQL指令进行封装语法CREATE PROCEDURE <proc_name>([IN/OUT args]) BEGIN – SQL END;示例– 创建存储过程，实现加法运算 – 存储过程是有出入参数和输出参数的 CREATE PROCEDURE proc_test1(IN a INT,IN b INT,OUT c INT) BEGIN SET c = a+b; END; ##### 1.2.2 删除存储过程 – 删除存储过程 DROP PROCEDURE proc_test1; #### 1.3存储过程的调用 – 定义变量 SET @m = 0; – 调用存储过程 CALL proc_test1(3,2,@m); – 显示变量值 SELECT @m from DUAL; #### 1.4 存储过程中的变量使用> 存储过程中的变量分为两种：局部变量和用户变量##### 1.4.1 局部变量> 局部变量：定义在存储过程中的变量，只能在存储过程内部使用 – 局部变量需要定义在存储过程中，而且必须定义在存储过程开始 declare <attr_name> [default value]; – 创建存储过程:计算输入参数的平方与输入参数/2 之和 CREATE PROCEDURE proc_test2(IN a INT,OUT r INT) BEGIN DECLARE x INT DEFAULT 0; – 局部变量定义 DECLARE y INT DEFAULT 0; – 局部变量定义 set x = aa; set y = a/2; SET r = x + y ; END ##### 1.4.2 用户变量> 用户变量：相当于全局变量，定义的用户变量，可以通过select @attrName from dual进行查询; – 用户变量会存储在mysql数据库的数据字典中（dual） – 用户变量定义使用set关键字直接定义，变量名要以@开头 SET @a = 0; – 定义用户变量 SELECT @a from DUAL;-- 查询用户变量 ##### 1.4.3 将查询结果赋值给变量> 在存储过程中，使用select ... into给变量赋值 – 创建存储过程，查询学生表中的学生数量,赋值给参数s CREATE PROCEDURE proc_test3(OUT s INT) BEGIN SELECT COUNT(stu_num) INTO s from students;-- 将查询到的学生数量，赋值给参数s END; – 调用存储过程test3 SET @s=0; CALL proc_test3(@s); select @s from DUAL;注意> 用户变量相当于全局变量，可以在SQL质量以及多个存储过程中共享，因此在开发中建议尽量减少使用用户变量，防止用户变量过多导致程序不易理解、难以维护；#### 1.5存储过程的参数> MySQL存储过程的参数一共有三种：IN 、OUT、INOUT##### 1.5.1 输入参数 IN> 输入参数–在调用存储过程中传递给存储过程的参数（在调用的过程必须为具有实际变量的 或者 字面值） – 创建存储过程：添加学生信息 CREATE PROCEDURE proc_test4 (IN snum CHAR(8),IN sname VARCHAR(20),IN denger CHAR(2),IN age INT,IN tel VARCHAR(20),IN cid INT) BEGIN INSERT INTO students(stu_num,stu_name,stu_denger,stu_age,stu_tel,cid) VALUES(snum,sname,denger,age,tel,cid); END; – 调用存储过程 CALL proc_test4(‘8’,‘张飞’,‘男’,88,‘13667565656’,2); ##### 1.5.2 输出参数 OUT> 将存储过程中产生的数据，返回给过程调用者，相当于Java的返回值，但不同的是，存储过程可以有多个输出参数。 – 创建存储过程：根据学号，查询学生姓名 CREATE PROCEDURE proc_test5(IN snum INT,OUT sname VARCHAR(20)) BEGIN SELECT stu_name INTO sname from students where stu_num = snum; END; – 设置用户参数 SET @name = ‘’; – 调用存储过程 CALL proc_test5(8,@name); select @name from DUAL; ##### 1.5.3 输入输出参数 INOUT – 存储过程：根据学号，查询学生姓名，使用INOUT CREATE PROCEDURE proc_test6(INOUT str VARCHAR(20)) BEGIN SELECT stu_name INTO str from students where stu_num = str; END; – 设置参数,默认赋值为查询参数值 set @str = ‘8’; – 调用存储过程 CALL proc_test6(@str); SELECT @str from dual; #### 1.6 存储过程中的流程控制> 在存储过程中，支持流程控制语句用于实现逻辑的控制##### 1.6.1 分支语句if-then-else * 单分支： – 单分支:如果条件成立，则执行SQL CREATE PROCEDURE test7(IN a INT) BEGIN IF CONDITION THEN – SQL END IF; END; * 双分支 – 双分支：如果条件成立，则执行SQL1;否则，执行SQL2 CREATE PROCEDURE test7(IN a INT) BEGIN IF CONDITION THEN – SQL1 ELSE – SQL2 END IF; END; * case – CASE CREATE PROCEDURE proc_test8(IN a INT) BEGIN CASE a WHEN 1 THEN – 参数a = 1 时，执行SQL1 – SQL1 WHEN 2 THEN – 参数a = 2 时，执行SQL2 – SQL2 ELSE – SQL3 – 如果变量值和所有的when值都不匹配，则执行SQL3 END CASE; END; ##### 1.6.2 循环语句* while – 创建存储过程：添加参数，按照参数值，创建班级信息，即参与为3，就创建3条班级信息 CREATE PROCEDURE proc_test7(IN num INT) BEGIN DECLARE i INT; – 局部变量 SET i = 0; WHILE i<num DO – i < 参数变量时，循环执行SQL语句 – SQL SET i = i+1; – 每循环一次，i增加1 END WHILE; END; * repeat – repeat 创建存储过程：添加参数，按照参数值，创建班级信息，即参与为3，就创建3条班级信息 CREATE PROCEDURE proc_test7(IN num INT) BEGIN DECLARE i INT; – 局部变量 SET i = 0; REPEAT – 循环执行SQL – SQL SET i = i+1; – 每执行一次，i+1 UNTIL i > num; – UNTIL 判断后面结果，符合即跳出循环 END WHILE; END; * loop – LOOP 创建存储过程：添加参数，按照参数值，创建班级信息，即参与为3，就创建3条班级信息 CREATE PROCEDURE proc_test7(IN num INT) BEGIN DECLARE i INT; – 局部变量 SET i = 0; myloop:LOOP – 设置myloop，在myloop中循环执行SQL – SQL set i = i+1; – 每循环一次，i+1 IF i = num THEN – 判断当i = 参数值时 LEAVE myloop; – 跳出myloop循环 END IF; END LOOP; END; #### 1.7 存储过程管理##### 1.7.1 查询存储过程> 存储过程隶属于某个数据库的，也就是说，当我们将存储过程创建在某个数据库中，只能在当前数据库中调用，不能跨库调用 – 根据数据库名，查询当前数据库中的存储过程 show PROCEDURE STATUS where db = ‘db_test’; – 查询存储过程的创建细节 SHOW CREATE PROCEDURE db_test.proc_test1; ##### 1.7.2 修改存储过程> 修改存储过程，主要是指修改存储过程的特征/特性 alter procedure <proc_name> 特征1 {特征2…}存储过程的特征参数*CONTAINS SQL表示子程序包含SQL语句，但不包含读或写的数据操作*NO SQL表示子程序不包含SQL语句*READS SQL DATA表示子程序包含读数据的语句*MODIFIES SQL DATA表示子程序中包含写数据的语句*SQL SECURITY {DEFINER| INVOKER}指明谁有权限来执行 *DEFINER定义者才有执行权限 *INVOKER调用者可以执行*COMMENT string表示注释信息 – 修改存储过程 ALTER PROCEDURE proc_test1 NO SQL; ##### 1.7.3 删除存储过程> 删除存储过程 – 删除存储过程 DROP DROP PROCEDURE proc_test1; ### 二、存储过程案例> 使用存储过程，完成借书操作#### 2.1 准备数据> 数据库准备：新建数据库 ## 创建数据库 create database da_test3; ## 使用数据库 use db_test3; > 数据表及数据准备 – 创建图书信息表 CREATE TABLE books( book_id INT PRIMARY KEY auto_increment, book_name VARCHAR(50) NOT NULL, book_author VARCHAR(20) NOT NULL, book_price DECIMAL(10,2) NOT NULL, book_stock INT NOT NULL, book_desc VARCHAR(200) ); – 添加图书信息 INSERT INTO books(book_name,book_author,book_price,book_stock,book_desc) VALUES (‘Java从入门到放弃’,‘斯蒂芬’,28.80,100,‘一本带你从入门到放弃的java顶级教材’); INSERT INTO books(book_name,book_author,book_price,book_stock,book_desc) VALUES (‘MySQL从入门到放弃’,‘库里’,68.20,20,‘一本带你从入门到放弃的mysql顶级教材’); – 创建学生信息表 CREATE TABLE students( stu_num CHAR(8) PRIMARY KEY, stu_name VARCHAR(20) NOT NULL, stu_denger CHAR(2) NOT NULL, stu_age INT NOT NULL ); – 添加学生信息 INSERT INTO students (stu_num,stu_name,stu_denger,stu_age) VALUES(‘1001’,‘不知火舞’,‘女’,‘20’); INSERT INTO students (stu_num,stu_name,stu_denger,stu_age) VALUES(‘1002’,‘安其拉’,‘女’,‘25’); INSERT INTO students (stu_num,stu_name,stu_denger,stu_age) VALUES(‘1003’,‘奕星’,‘男’,‘30’); #### 2.2 创建存储过程> 创建一个存储过程，实现借书的操作：哪个学生接了哪本数，借书数量> > 操作：> > * 保存借书记录> * 修改图书库存> > 条件：> > * 判断学生是否存在> * 判断图书是否存在，库存是否充足创建借书记录表– 借书记录表 CREATE TABLE records( rid INT PRIMARY KEY auto_increment, snum CHAR(4) NOT NULL, bid INT NOT NULL, borrow_num INT NOT NULL, is_return INT NOT NULL, – 0-未归还；1-已归还 borrow_date date NOT NULL, CONSTRAINT FK_RECORDS_STUDENTS FOREIGN KEY(snum) REFERENCES students(stu_num), CONSTRAINT FK_RECORDS_BOOKS FOREIGN KEY(bid) REFERENCES books(book_id) ); ##### 2.2.1 创建存储过程 – 实现借书业务 – 参数1： 输入参数 学号 a – 参数2： 输入参数 图书标号 b – 参数3： 输入参数 借书数量 m – 参数4： 输出参数 借书状态（1-借书成功；2-学号不存在；3-图书不存在；4-库存不足） CREATE PROCEDURE proc_borrow_book(IN a CHAR(4),IN b INT,IN m INT,OUT state INT) BEGIN DECLARE stu_count INT DEFAULT 0; DECLARE b_count INT DEFAULT 0; DECLARE b_stock INT DEFAULT 0; – 一、判断学号是否存在 根据参数a去学生表查询是否存在学生 SELECT COUNT(stu_num) INTO stu_count from students where stu_num = a; IF stu_count > 0 THEN – 学号存在 – 二、查看图书编号是否存在 SELECT COUNT(book_id) INTO b_count from books where book_id = b; IF b_count > 0 THEN – 图书存在 – 三、查询图书库存是否充足 SELECT book_stock INTO b_stock from books where book_id = b; IF b_stock >= m THEN – 库存满足 – 1、插入借书记录表 INSERT INTO records(snum,bid,borrow_num,is_return,borrow_date) VALUES(a,b,m,0,SYSDATE()); – 2、更新books表库存数据book_stock UPDATE books SET book_stock = (b_stock - m) where book_id = b; – 3、借书成功，返回成功状态 0 SET state = 1; ELSE – 库存不足 SET state = 4; END IF; ELSE – 图书不存在 SET state = 3; END IF; ELSE – 学号不存在 SET state = 2; END IF; END; ##### 2.2.2 测试 SELECT * FROM students;-- 学生表 select * from books;-- 图书表 select * from records;-- 借书记录表 – 测试借书存储过程 – 1、正常借书成功业务测试：学生学号a = 1001;借书编号b = 1;借书数量m = 10; SET @state = 0; CALL proc_borrow_book(‘1001’,1,10,@state); SELECT @state from DUAL; – 2、测试学号不存在：学生学号a = 1008;借书编号b = 1;借书数量m = 10; SET @state = 0; CALL proc_borrow_book(‘1008’,1,10,@state); SELECT @state from DUAL; – 3、测试图书编号不存在：学生学号a = 1002;借书编号b = 8;借书数量m = 10; SET @state = 0; CALL proc_borrow_book(‘1002’,8,10,@state); SELECT @state from DUAL; – 4、测试图书库存不足：学生学号a = 1002;借书编号b = 2;借书数量m = 100; SET @state = 0; CALL proc_borrow_book(‘1002’,2,100,@state); SELECT @state from DUAL;
版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/8/28 16:30:06

如何快速用 Firecrawl 把网页变成 LLM 可读数据

如何快速用 Firecrawl 把网页变成 LLM 可读数据 【免费下载链接】firecrawl The context API to search, scrape, and interact with the web at scale. &#x1f525; 项目地址: https://gitcode.com/GitHub_Trending/fi/firecrawl 给 Agent 喂网页内容&#xff0c;绕不…

作者头像 李华
网站建设 2026/8/28 16:30:01

响应式界面的线上观察

响应式界面的线上观察复杂表格连续筛选、排序后&#xff0c;页面可能出现 CPU 占用升高或无响应&#xff0c;但并不一定伴随 JavaScript 异常。原因可能是响应式更新循环&#xff0c;也可能是大量计算、布局或第三方逻辑&#xff0c;需要结合现场数据确认。 Vue 3 的 Proxy 响应…

作者头像 李华
网站建设 2026/8/28 16:25:37

利用MCP协议构建AI代理搜索器：从“人找工具”到“协议找工具”

过去一个月&#xff0c;我每次在 MCP 客户端里配置新工具时&#xff0c;都会卡在同一个环节&#xff1a;我得先知道某个工具的地址、用途和调用约定&#xff0c;然后手动写进配置文件。等你真正配完&#xff0c;你会意识到一个更根本的问题已经摆到眼前——AI 代理的数量已经多…

作者头像 李华
网站建设 2026/8/28 16:23:50

AI协作开发中如何保持人的意义感与工程质量

在实际的 AI 辅助开发中&#xff0c;比模型选型和提示词技巧更先出问题的&#xff0c;往往是工作方式和心理感受。 When people think AI did the creative work, task meaning and effort decline 这句话描述了一个值得每个 AI 应用开发者、AI 编程工具使用者和技术管理者关…

作者头像 李华