7.存储过程

说明

可以封装SQL语句集，可以完成一些复杂的业务逻辑，并且可以入参出参。

优缺点

优点：

1. 在生产环境下，可以直接修改存储过程来修改业务逻辑，而不需要重新部署。
2. 执行速度快，要比一条一条执行效率更高。
3. 减少网络传输流量。

缺点：

1. 过程化编程，复杂业务处理的维护成本高。
2. 调试不便。
3. 可移植性差。

开始

创建存储过程

-- 存储过程
-- IN 标记入参，OUT 标记出餐
CREATE PROCEDURE number_add ( IN a INT, IN b INT, OUT c INT ) BEGIN
	
SET c = a + b;
END;

调用存储过程

-- 调用存储过程

-- 定义变量@m
SET @m = 0;
CALL number_add ( 2, 3, @m );

-- 显示变量值
SELECT
	@m 
FROM
	DUAL;

变量

存储过程中的变量有两种：局部变量和用户变量

局部变量

DECLARE a INT <DEFAULT 0>;

定义局部变量

CREATE PROCEDURE number_square_divide2 ( IN a INT, OUT b INT ) BEGIN
	DECLARE x INT DEFAULT 0;
	SET x = a * a;
	SET b = x / 2;
END;

用户变量

相当于全局变量，可以通过 SELECT @m FROM DUAL; 进行查询

SET @n = 1;

SET @n = 1;
CALL number_square_divide2 ( 2, @n );
SELECT
	@n 
FROM
	DUAL;

将查询结果赋值给变量

在存储过程中使用SELECT…INTO…给变量赋值

-- 创建存储过程，查询学生的数量并返回
CREATE PROCEDURE student_num ( OUT c INT ) BEGIN
	SELECT COUNT(stu_num) INTO c FROM students;
END;

-- 调用存储过程
CALL student_num ( @n );

-- 查看结果
SELECT @n FROM DUAL;

由于用户变量可以共享，在开发中尽量少使用用户变量。用户变量过多会导致程序不易理解，难以维护。

参数

入参

-- 创建存储过程，添加学生信息
CREATE PROCEDURE add_student (
	IN stu_number CHAR ( 8 ),
	IN stu_name VARCHAR ( 20 ),
	IN stu_gender CHAR ( 2 ),
	IN stu_age INT,
	IN cid INT,
	IN remark VARCHAR ( 64 ) 
	) BEGIN
	INSERT INTO students ( stu_number, stu_name, stu_gender, stu_age, cid, remark )
	VALUES
	( stu_number, stu_name, stu_gender, stu_age, cid, remark );
	END;

-- 调用存储过程
CALL add_student('20210108', '小丽', '女', 20, 1, '我是转校生');

出参

一个存储过程可以有多个返回值

-- 创建存储过程,根据学生学号查询学生姓名
CREATE PROCEDURE select_name_for_number ( IN snum CHAR ( 8 ), OUT s_name VARCHAR ( 20 ) ) BEGIN
	SELECT stu_name INTO s_name FROM students WHERE stu_num = snum;
END;

-- 创建用户变量
SET @name = '';
-- 调用存储过程
CALL select_name_for_number('20210108', @name);

SELECT @name FROM DUAL;

INOUT（输入输出参数）

-- 创建存储过程,根据学生学号查询学生姓名
CREATE PROCEDURE select_name_for_number2 ( INOUT str VARCHAR(20) ) BEGIN
	SELECT stu_name INTO str FROM students WHERE stu_num = str;
END;

SET @name = '20210108';
CALL select_name_for_number2(@name);

SELECT @name FROM DUAL;

分支语句

IF…ELSE

IF search_condition THEN
	statement_list
ELSE
	statement_list
END IF;

-- 创建一个存储过程,如果参数输入的是1,则添加一条班级信息,如果参数输入的是2,则添加一条学学生信息
CREATE PROCEDURE add_student_for_num(IN num INT) BEGIN
	IF num = 1 THEN
		INSERT INTO classes(class_name) VALUES('C++');
	ELSE
		INSERT INTO students(stu_num, stu_name, stu_gender, stu_age, cid, remark) VALUES('20210109', '李明', '男', 24, 1, '我不是药神');
	END IF;
END;

CALL add_student_for_num(2);

CASE

-- CASE分支在存储过程中的使用
CREATE PROCEDURE add_student2(IN a INT) BEGIN
	CASE a
	WHEN a = 1 THEN
		INSERT INTO students(stu_num, stu_name, stu_gender, stu_age, cid) 
		VALUES('20210110', '刘华强', '男', 45, 1);
	ELSE
		INSERT INTO classes(class_name) VALUES('Golang');
	END CASE;
END;

CALL add_student2(2);

循环语句

while

-- 创建存储过程,循环添加班级
CREATE PROCEDURE loop_add_classes(IN num INT) BEGIN
	DECLARE i INT DEFAULT 0;
	WHILE i < num DO
		INSERT INTO classes(class_name) VALUES (CONCAT('Java',i));
		SET i = i + 1;
	END WHILE;
END;

-- 调用存储过程
CALL loop_add_classes(4);

repeat

-- 创建存储过程,循环添加班级
CREATE PROCEDURE loop_add_classes2(IN num INT) BEGIN
	DECLARE i INT DEFAULT 0;
	REPEAT
		INSERT INTO classes(class_name) VALUES(CONCAT('Python', i));
		SET i = i + 1;
  UNTIL i > num END REPEAT;
END;

CALL loop_add_classes2(4);

loop

-- LOOP循环在存储过程中的使用
CREATE PROCEDURE loop_classes3(IN a INT) BEGIN
	DECLARE i INT DEFAULT 0;
	myloop: LOOP
		INSERT INTO classes(class_name) VALUES(CONCAT('HTML', i));
		SET i = i + 1;
		IF i = a THEN
			LEAVE myloop; 
		END IF; 
	END LOOP ;
END;

CALL loop_classes3(5);

存储过程管理

查询

-- 查询当前数据库的存储过程
SHOW PROCEDURE STATUS 
WHERE
	db = 'test';
	
-- 查询存储过程的创建细节
SHOW CREATE PROCEDURE test.add_student;

修改

修改存储过程的特征

• CONTAINS SQL 表示子程序包含SQL语句，但不包含读或写数据的语句。
• NO SQL 表示子程序中不包含SQL语句。
• READS SQL DATA 表示子程序中包含读数据的语句。
• MODIFIES SQL DATA 表示子程序中包含写数据的语句。
• SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行。
• DEFINER 表示只有定义者自己才能够执行。
• INVOKER 表示调用者可以执行。
• COMMENT ‘string’表示注释信息

-- 修改存储过程的特征
ALTER PROCEDURE add_student NO SQL;

删除

DROP PROCEDURE add_student;

实战

准备数据，链接地址，提取码shkt

业务分析：
创建一个存储过程实现借书的操作，哪个学生借了哪本书，借了多少本？
操作：

• 保存借书记录
• 修改图书库存

条件:

• 判断学生是否存在
• 判断图书是否存在、库存是否充足

-- 实现借书操作
-- 借书成功1，学生不存在2，图书不存在3，库存不充足4
CREATE PROCEDURE borrow_book(IN a CHAR(4), IN b INT, IN c INT, OUT state INT) BEGIN
	DECLARE stu_count INT DEFAULT 0;
	DECLARE book_exist INT DEFAULT 0;
	DECLARE book_count INT DEFAULT 0;
	
	SELECT COUNT(stu_num) INTO stu_count FROM students WHERE stu_num = a;
-- 	判断学生是否存在
	IF stu_count > 0 THEN
		SELECT COUNT(book_id) INTO book_exist FROM books WHERE book_id = b;
		
-- 		判断图书是否存在
		IF book_exist > 0 THEN
			SELECT book_stock INTO book_count FROM books WHERE book_id = b;
			
-- 			判断图书是否充足
			IF book_count >= c THEN
				INSERT INTO records(snum, bid, borrow_num, borrow_date) VALUES(a, b, c, NOW());
				UPDATE books SET book_stock = book_stock - c WHERE book_id = b;
				SET state = 1;
			ELSE
				SET state = 4;
			END IF;
			
		ELSE
			SET state = 3;
		END IF;
		
	ELSE
		SET state = 2;
	END IF;
END;

测试

-- 测试存储过程
SET @borrow_state = 0;
CALL borrow_book('1001', 1, 10, @borrow_state);


SELECT @borrow_state FROM DUAL;

结果