7.存储过程

存储过程"/>

7.存储过程

存储过程

• 说明
• 优缺点
• 开始
• • 创建存储过程
  • 调用存储过程
  • 变量
  • • 局部变量
    • 用户变量
  • 参数
  • • 入参
    • 出参
    • INOUT（输入输出参数）
  • 分支语句
  • • IF...ELSE
    • CASE
  • 循环语句
  • • while
    • repeat
    • loop
  • 存储过程管理
  • • 查询
    • 修改
    • 删除
• 实战

说明

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

优缺点

优点：

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

缺点：

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

开始

创建存储过程

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

调用存储过程

-- 调用存储过程-- 定义变量@m
SET @m = 0;
CALL number_add ( 2, 3, @m );-- 显示变量值
SELECT@m 
FROMDUAL;

变量

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

局部变量

DECLARE a INT <DEFAULT 0>;

定义局部变量

CREATE PROCEDURE number_square_divide2 ( IN a INT, OUT b INT ) BEGINDECLARE 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 
FROMDUAL;

将查询结果赋值给变量

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

-- 创建存储过程，查询学生的数量并返回
CREATE PROCEDURE student_num ( OUT c INT ) BEGINSELECT 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 ) ) BEGININSERT 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 ) ) BEGINSELECT 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) ) BEGINSELECT 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 THENstatement_list
ELSEstatement_list
END IF;

-- 创建一个存储过程,如果参数输入的是1,则添加一条班级信息,如果参数输入的是2,则添加一条学学生信息
CREATE PROCEDURE add_student_for_num(IN num INT) BEGINIF num = 1 THENINSERT INTO classes(class_name) VALUES('C++');ELSEINSERT 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) BEGINCASE aWHEN a = 1 THENINSERT INTO students(stu_num, stu_name, stu_gender, stu_age, cid) VALUES('20210110', '刘华强', '男', 45, 1);ELSEINSERT INTO classes(class_name) VALUES('Golang');END CASE;
END;CALL add_student2(2);

循环语句

while

-- 创建存储过程,循环添加班级
CREATE PROCEDURE loop_add_classes(IN num INT) BEGINDECLARE i INT DEFAULT 0;WHILE i < num DOINSERT 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) BEGINDECLARE i INT DEFAULT 0;REPEATINSERT 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) BEGINDECLARE i INT DEFAULT 0;myloop: LOOPINSERT INTO classes(class_name) VALUES(CONCAT('HTML', i));SET i = i + 1;IF i = a THENLEAVE myloop; END IF; END LOOP ;
END;CALL loop_classes3(5);

存储过程管理

查询

-- 查询当前数据库的存储过程
SHOW PROCEDURE STATUS 
WHEREdb = '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) BEGINDECLARE 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 THENSELECT COUNT(book_id) INTO book_exist FROM books WHERE book_id = b;-- 		判断图书是否存在IF book_exist > 0 THENSELECT book_stock INTO book_count FROM books WHERE book_id = b;-- 			判断图书是否充足IF book_count >= c THENINSERT 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;ELSESET state = 4;END IF;ELSESET state = 3;END IF;ELSESET state = 2;END IF;
END;

测试

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

结果