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> <type> [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 =编程客栈 a*a;
	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.5EqtLfuY存储过程的参数

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_tes编程客栈t编程7(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;

到此这篇关于MySQL存储过程的创建和使用的文章就介绍到这了,更多相关MySQL存储过程使用内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！