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

一、存储过程

1.1 存储过程介绍

存储过程：
   将能够完成特定功能的SQL指令进行封装（SQL指令集），编译之后存储在数据库服务器上，并为其定名，客户端可以通过直接调用SQL指令集，获取执行结果
    存储过程办理的问题：
  

• 假如有需要多次执行的SQL，每次执行都需要通过毗连传递到MySQL服务器，并且需要经过编译和执行后，再返回执行结果。重复且浪费资源
  
• 假如需要一连执行多个SQL指令，并且第二个SQL指令需要使用第一个SQL执行的结果集作为参数。
  

  存储过程优点：
   

• SQL指令无需客户端编写、通过网络传输，可以节省网络开销，同时克制SQL指令在传输过程中被恶意篡改，保证安全性；
  
• 存储过程经过编译创建并生存在数据库服务器中，执行过程无需编译，对SQL指令的执行过程提升了性能；
  
• 存储过程中多个SQL指令之间存在逻辑关系，支持流程控制语句（分支、循环），可以实现更为复杂的业务逻辑处理；
  

  存储过程的缺点：
   

• 存储过程是根据差别数据库引擎进行编译、创建并存储在数据库中。假如需要差别类型数据库迁移，需要对数据库存储过程进行重新编写。
  
• 存储过程受限与数据库产品，假如需要高性能的优化会成为一个问题；
  
• 在互联网项目中，假如需要数据库高并发（毗连）访问，存储过程会增长数据库的毗连执行时间。由于我们将复杂的业务交给了数据库进行处理。
  

  1.2 存储过程的创建与删除

1.2.1 创建存储过程

   将能够完成特定功能的SQL指令进行封装
  语法

1. CREATE PROCEDURE <proc_name>([IN/OUT args])
2. BEGIN
3.         -- SQL
4. END;

复制代码

示例

1. -- 创建存储过程，实现加法运算
2. -- 存储过程是有出入参数和输出参数的
3. CREATE PROCEDURE proc_test1(IN a INT,IN b INT,OUT c INT)
4. BEGIN
5.         SET c = a+b;
6. END;

复制代码

1.2.2 删除存储过程

1. -- 删除存储过程
2. DROP PROCEDURE proc_test1;

复制代码

1.3存储过程的调用

1. -- 定义变量
2. SET @m = 0;
4. -- 调用存储过程
5. CALL proc_test1(3,2,@m);
7. -- 显示变量值
8. SELECT @m from DUAL;

复制代码

1.4 存储过程中的变量使用

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

   局部变量：定义在存储过程中的变量，只能在存储过程内部使用

1. -- 局部变量需要定义在存储过程中，而且必须定义在存储过程开始
2. declare <attr_name> <type> [default value];

复制代码

1. -- 创建存储过程:计算输入参数的平方与输入参数/2 之和
2. CREATE PROCEDURE proc_test2(IN a INT,OUT r INT)
3. BEGIN
4.         DECLARE x INT DEFAULT 0; -- 局部变量定义
5.         DECLARE y INT DEFAULT 0; -- 局部变量定义
6.         set x = a*a;
7.         set y = a/2;
8.         SET r = x + y ;
9. END

复制代码

1.4.2 用户变量

   用户变量：相当于全局变量，定义的用户变量，可以通过 select @attrName from dual进行查询;

1. -- 用户变量会存储在mysql数据库的数据字典中（dual）
2. -- 用户变量定义使用set关键字直接定义，变量名要以@开头
4. SET @a = 0; -- 定义用户变量
5. SELECT @a from DUAL;-- 查询用户变量

复制代码

1.4.3 将查询结果赋值给变量

   在存储过程中，使用 select ... into 给变量赋值

1. -- 创建存储过程，查询学生表中的学生数量,赋值给参数s
2. CREATE PROCEDURE proc_test3(OUT s INT)
3. BEGIN
4.         SELECT COUNT(stu_num) INTO s from students;-- 将查询到的学生数量，赋值给参数s
5. END;
7. -- 调用存储过程test3
8. SET @s=0;
9. CALL proc_test3(@s);
10. select @s from DUAL;

复制代码

注意
   用户变量相当于全局变量，可以在SQL质量以及多个存储过程中共享，因此在开辟中发起只管减少使用用户变量，防止用户变量过多导致程序不易理解、难以维护；
  1.5存储过程的参数

   MySQL存储过程的参数一共有三种：IN 、OUT、INOUT
  1.5.1 输入参数 IN

   输入参数–在调用存储过程中传递给存储过程的参数（在调用的过程必须为具有实际变量的 大概 字面值）

1. -- 创建存储过程：添加学生信息
2. 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)
3. BEGIN
4.         INSERT INTO students(stu_num,stu_name,stu_denger,stu_age,stu_tel,cid)                                  VALUES(snum,sname,denger,age,tel,cid);
5. END;
7. -- 调用存储过程
8. CALL proc_test4('8','张飞','男',88,'13667565656',2);

复制代码

1.5.2 输出参数 OUT

   将存储过程中产生的数据，返回给过程调用者，相当于Java的返回值，但差别的是，存储过程可以有多个输出参数。

1. -- 创建存储过程：根据学号，查询学生姓名
2. CREATE PROCEDURE proc_test5(IN snum INT,OUT sname VARCHAR(20))
3. BEGIN
4.         SELECT stu_name INTO sname from students where stu_num = snum;
5. END;
7. -- 设置用户参数
8. SET @name = '';
10. -- 调用存储过程
11. CALL proc_test5(8,@name);
12. select @name from DUAL;

复制代码

1.5.3 输入输出参数 INOUT

1. -- 存储过程：根据学号，查询学生姓名，使用INOUT
2. CREATE PROCEDURE proc_test6(INOUT str VARCHAR(20))
3. BEGIN
4.         SELECT stu_name INTO str from students where stu_num = str;
5. END;
7. -- 设置参数,默认赋值为查询参数值
8. set @str = '8';
10. -- 调用存储过程
11. CALL proc_test6(@str);
12. SELECT @str from dual;

复制代码

1.6 存储过程中的流程控制

   在存储过程中，支持流程控制语句用于实现逻辑的控制
  1.6.1 分支语句

• if-then-else
  
  
  
  • 单分支：
    
  1. -- 单分支:如果条件成立，则执行SQL
  2. CREATE PROCEDURE test7(IN a INT)
  3. BEGIN
  4.         IF CONDITION THEN
  5.                 -- SQL
  6.         END IF;
  7. END;

  复制代码
    
  
  
  
  • 双分支
    
  1. -- 双分支：如果条件成立，则执行SQL1;否则，执行SQL2
  2. CREATE PROCEDURE test7(IN a INT)
  3. BEGIN
  4.         IF CONDITION THEN
  5.                 -- SQL1
  6.         ELSE
  7.                 -- SQL2
  8.         END IF;
  9. END;

  复制代码
  
  
• case
  

1. -- CASE
2. CREATE PROCEDURE proc_test8(IN a INT)
3. BEGIN
4.         CASE a
5.         WHEN 1 THEN        -- 参数a = 1 时，执行SQL1
6.                 -- SQL1
7.         WHEN 2 THEN        -- 参数a = 2 时，执行SQL2
8.                 -- SQL2
9.         ELSE
10.                 -- SQL3                -- 如果变量值和所有的when值都不匹配，则执行SQL3
11.         END CASE;
12. END;

复制代码

1.6.2 循环语句

• while
  

1. -- 创建存储过程：添加参数，按照参数值，创建班级信息，即参与为3，就创建3条班级信息
2. CREATE PROCEDURE proc_test7(IN num INT)
3. BEGIN
4.         DECLARE i INT;        -- 局部变量
5.         SET i = 0;
6.         WHILE i<num DO        -- i < 参数变量时，循环执行SQL语句
7.                 -- SQL
8.                 SET i = i+1;        -- 每循环一次，i增加1
9.         END WHILE;
10. END;

复制代码

• repeat
  

1. -- repeat 创建存储过程：添加参数，按照参数值，创建班级信息，即参与为3，就创建3条班级信息
2. CREATE PROCEDURE proc_test7(IN num INT)
3. BEGIN
4.         DECLARE i INT;                -- 局部变量
5.         SET i = 0;
6.         REPEAT                                -- 循环执行SQL
7.                 -- SQL
8.                 SET i = i+1;        -- 每执行一次，i+1
9.         UNTIL i > num;                -- UNTIL 判断后面结果，符合即跳出循环
10.         END WHILE;
11. END;

复制代码

• loop
  

1. -- LOOP 创建存储过程：添加参数，按照参数值，创建班级信息，即参与为3，就创建3条班级信息
2. CREATE PROCEDURE proc_test7(IN num INT)
3. BEGIN
4.         DECLARE i INT;        -- 局部变量
5.         SET i = 0;
6.         myloop:LOOP        -- 设置myloop，在myloop中循环执行SQL
7.                 -- SQL
8.                 set i = i+1;        -- 每循环一次，i+1
9.                 IF i = num THEN                -- 判断当i = 参数值时
10.                         LEAVE myloop;                -- 跳出myloop循环
11.                 END IF;
12.         END LOOP;
13. END;

复制代码

1.7 存储过程管理

1.7.1 查询存储过程

   存储过程隶属于某个数据库的，也就是说，当我们将存储过程创建在某个数据库中，只能在当前数据库中调用，不能跨库调用

1. -- 根据数据库名，查询当前数据库中的存储过程
2. show PROCEDURE STATUS where db = 'db_test';
4. -- 查询存储过程的创建细节
5. SHOW CREATE PROCEDURE db_test.proc_test1;

复制代码

1.7.2 修改存储过程

   修改存储过程，紧张是指修改存储过程的特征/特性

1. 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  表现注释信息
  

1. -- 修改存储过程
2. ALTER PROCEDURE proc_test1 NO SQL;

复制代码

1.7.3 删除存储过程

   删除存储过程

1. -- 删除存储过程 DROP
2. DROP PROCEDURE proc_test1;

复制代码

二、存储过程案例

   使用存储过程，完成借书操纵
  2.1 准备数据

   数据库准备：新建数据库

1. ## 创建数据库
2. create database da_test3;
3. ## 使用数据库
4. use db_test3;

复制代码

  数据表及数据准备

1. -- 创建图书信息表
2. CREATE TABLE books(
3.         book_id INT PRIMARY KEY auto_increment,
4.         book_name VARCHAR(50) NOT NULL,
5.         book_author VARCHAR(20) NOT NULL,
6.         book_price DECIMAL(10,2) NOT NULL,
7.         book_stock INT NOT NULL,
8.         book_desc VARCHAR(200)
9. );
11. -- 添加图书信息
12. INSERT INTO books(book_name,book_author,book_price,book_stock,book_desc)
13. VALUES ('Java从入门到放弃','斯蒂芬',28.80,100,'一本带你从入门到放弃的java顶级教材');
14. INSERT INTO books(book_name,book_author,book_price,book_stock,book_desc)
15. VALUES ('MySQL从入门到放弃','库里',68.20,20,'一本带你从入门到放弃的mysql顶级教材');
17. -- 创建学生信息表
18. CREATE TABLE students(
19.         stu_num CHAR(8) PRIMARY KEY,
20.         stu_name VARCHAR(20) NOT NULL,
21.         stu_denger CHAR(2) NOT NULL,
22.         stu_age INT NOT NULL
23. );
25. -- 添加学生信息
26. INSERT INTO students (stu_num,stu_name,stu_denger,stu_age) VALUES('1001','不知火舞','女','20');
27. INSERT INTO students (stu_num,stu_name,stu_denger,stu_age) VALUES('1002','安其拉','女','25');
28. INSERT INTO students (stu_num,stu_name,stu_denger,stu_age) VALUES('1003','奕星','男','30');

复制代码

2.2 创建存储过程

   创建一个存储过程，实现借书的操纵：哪个门生接了哪本数，借书数量
  操纵：
  

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

  条件：
  

• 判断门生是否存在
  
• 判断图书是否存在，库存是否充足
  

  创建借书记录表

1. -- 借书记录表
2. CREATE TABLE records(
3.         rid INT PRIMARY KEY auto_increment,
4.         snum CHAR(4) NOT NULL,
5.         bid INT NOT NULL,
6.         borrow_num INT NOT NULL,
7.         is_return INT NOT NULL,        -- 0-未归还；1-已归还
8.         borrow_date date NOT NULL,
9.         CONSTRAINT FK_RECORDS_STUDENTS FOREIGN KEY(snum) REFERENCES students(stu_num),
10.         CONSTRAINT FK_RECORDS_BOOKS FOREIGN KEY(bid) REFERENCES books(book_id)
11. );

复制代码

2.2.1 创建存储过程

1. -- 实现借书业务
2. -- 参数1：        输入参数        学号        a
3. -- 参数2：        输入参数        图书标号        b
4. -- 参数3：        输入参数        借书数量        m
5. -- 参数4：        输出参数        借书状态（1-借书成功；2-学号不存在；3-图书不存在；4-库存不足）
6. CREATE PROCEDURE proc_borrow_book(IN a CHAR(4),IN b INT,IN m INT,OUT state INT)
7. BEGIN
8.         DECLARE stu_count INT DEFAULT 0;       
9.         DECLARE b_count INT DEFAULT 0;
10.         DECLARE b_stock INT DEFAULT 0;
11.         -- 一、判断学号是否存在        根据参数a去学生表查询是否存在学生
12.         SELECT COUNT(stu_num) INTO stu_count from  students where stu_num = a;
13.         IF stu_count > 0 THEN
14.                 -- 学号存在
15.                 -- 二、查看图书编号是否存在
16.                 SELECT COUNT(book_id) INTO b_count from books where book_id = b;
17.                 IF b_count > 0 THEN
18.                         -- 图书存在
19.                         -- 三、查询图书库存是否充足
20.                         SELECT book_stock INTO b_stock from books where book_id = b;
21.                         IF b_stock >= m THEN
22.                                 -- 库存满足
23.                                 -- 1、插入借书记录表
24.                                 INSERT INTO records(snum,bid,borrow_num,is_return,borrow_date) VALUES(a,b,m,0,SYSDATE());
25.                                 -- 2、更新books表库存数据book_stock
26.                                 UPDATE books SET book_stock = (b_stock - m) where book_id = b;
27.                                 -- 3、借书成功，返回成功状态 0
28.                                 SET state = 1;
29.                         ELSE
30.                                 -- 库存不足
31.                         SET state = 4;
32.                         END IF;
33.                 ELSE
34.                         -- 图书不存在
35.                 SET state = 3;
36.                 END IF;
37.         ELSE
38.                 -- 学号不存在
39.         SET state = 2;
40.         END IF;
41. END;

复制代码

2.2.2 测试

1. SELECT * FROM students;-- 学生表
2. select * from books;-- 图书表
3. select * from records;-- 借书记录表
5. -- 测试借书存储过程
7. -- 1、正常借书成功业务测试：学生学号a = 1001;借书编号b = 1;借书数量m = 10;
8. SET @state = 0;
9. CALL proc_borrow_book('1001',1,10,@state);
10. SELECT @state from DUAL;
12. -- 2、测试学号不存在：学生学号a = 1008;借书编号b = 1;借书数量m = 10;
13. SET @state = 0;
14. CALL proc_borrow_book('1008',1,10,@state);
15. SELECT @state from DUAL;
17. -- 3、测试图书编号不存在：学生学号a = 1002;借书编号b = 8;借书数量m = 10;
18. SET @state = 0;
19. CALL proc_borrow_book('1002',8,10,@state);
20. SELECT @state from DUAL;
22. -- 4、测试图书库存不足：学生学号a = 1002;借书编号b = 2;借书数量m = 100;
23. SET @state = 0;
24. CALL proc_borrow_book('1002',2,100,@state);
25. SELECT @state from DUAL;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。