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.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;