存储过程介绍

SQL 指令执行过程

存储过程 - 图1

从 SQL 执行的流程中分析存在的问题:

  1. 如果需要重复多次执行相同的 SQL, SQL 执行都需要通过连接传递到 MySQL, 并且需要经过编译和执行的步骤
  2. 如果需要连续执行多个 SQL 指令, 并且第二个 SQL 指令需要使用第一个 SQL 指令执行的结果作为参数

存储过程的介绍

存储过程：

将能够完成特定功能的 SQL 指令进行封装 (SQL 指令集), 编译之后存储在数据库服务器上, 并且为之取一个名字, 客户端可以通过名字直接调用这个 SQL 指令集, 获取执行结果

存储过程优缺点分析

存储过程优点

  1. SQL 指令无需客户端编写, 通过网络传送, 可以节省网络开销, 同时避免 SQL 指令在网络传输过程中被恶意篡改保证安全性
  2. 存储过程经过编译创建并保存在数据库中的, 执行过程无需重复的进行编译操作, 对 SQL 指令的执行过程进行了性能提升
  3. 存储过程中多个 SQL 指令之间存在逻辑关系, 支持流程控制语句 (分支、循环), 可以实现更为复杂的业务

存储过程的缺点

  1. 存储过程是根据不同的数据库进行编译、创建并存储在数据库中；当需要切换到其他的数据库产品时, 需要重新编写针对于新数据库的存储过程
  2. 存储过程受限于数据库产品, 如果需要高性能的优化会成为一个问题
  3. 在互联网项目中, 如果需要数据库的高 (连接) 并发访问, 使用存储过程会增加数据库的连接执行时间 (因为将复杂的业务交给了数据库进行处理)

创建存储过程

语法

  1. create procedure <proc_name>([IN/OUT args])
  2. begin
  3.     -- SQL
  4. end;

示例

  1. # 创建一个存储过程实现加法运算: Java语法中, 方法是有参数和返回值的
  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. # 定义变量 @m
  2. set @m = 0;
  4. # 调用存储过程, 将3传递给a, 将2传递给b, 将 @m 传递给c
  5. call proc_test1(3, 2, @m);
  7. # 显示变量值
  8. select @m from dual;

存储过程中变量的使用

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

定义局部变量

局部变量: 定义在存储过程中的变量, 只能再存储过程内部使用

语法

  1. # 局部变量要定义在存储过程中, ⽽且必须定义在存储过程开始
  3. declare <attr_name> <type> [default value];

示例

  1. create procedure proc_test2(IN a int,OUT r int)
  2. begin
  3.     declare x int default 0; -- 定义 x int 类型, 默认值为 0
  4.     declare y int default 1; -- 定义 y
  5.     set x = a * a;
  6.     set y = a / 2;
  7.     set r = x + y;
  8. end;

定义用户变量

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

  1. # 用户变量会存储在 MySQL 数据库的数据字典中 (dual)
  2. # 用户变量定义使用 set 关键字直接定义, 变量名要以 @ 开头
  4. set @n = 1;

给变量设置值

  • 无论是局部变量还是用户变量, 都是使用 set 关键字修改值
  1. set @n = 1;
  2. call proc_test2(6, @n);
  3. select @n from dual;

将查询结果赋值给变量

  • 在存储过程中使用 select... into... 给变量赋值
  1. # 查询学生数量
  2. create procedure proc_test3(OUT c int)
  3. begin
  4.     select count(stu_num) INTO c from students; --将查询到学生数量赋值给c
  5. end;
  7. # 调用存储过程
  8. call proc_test3(@n);
  9. select @n from dual;

用户变量使用注意事项

因为用户变量相当于全局变量, 可以在 SQL 指令以及多个存储过程中共享, 在开发中建议尽量少使用户变量, 用户变量过多会导致程序不易理解、难以维护

存储过程的参数

MySQL 存储过程的参数一共有三种: IN \ OUT \ INOUT

IN 输入参数

输入参数 —— 在调用存储过程中传递数据给存储过程的参数 (在调用的过程必须为具有实际值的变量或者字面值)

  1. # 创建存储过程: 添加学生信息
  2. create procedure proc_test4(IN snum char(8), IN sname varchar(20),
  3.                             IN gender char(2), IN age int, IN cid int,
  4.                             IN remark varchar(255))
  5. begin
  6.     insert into students(stu_num, stu_name, stu_gender, stu_age, cid, remark)
  7.     values(snum, sname, gender, age, cid, remark);
  8. end;
  10. call proc_test4('20210108', '⼩丽', '⼥', 20, 1, 'aaa');

OUT 输出参数

输出参数 —— 将存储过程中产生的数据返回给过程调用者, 相当于 Java 方法的返回值, 但不同的是一个存储过程可以有多个输出参数

  1. # 创建存储过程, 根据学⽣学号, 查询学⽣姓名
  2. create procedure proc_test5(IN snum char(8), OUT sname varchar(20))
  3. begin
  4.     select stu_name INTO sname from students where stu_num = snum;
  5. end;
  7. set @name = '';
  8. call proc_test5('20210108', @name);
  9. select @name from dual;

INOUT 输⼊输出参数

  1. create procedure proc_test6(INOUT str varchar(20))
  2. begin
  3.     select stu_name INTO str from students where stu_num = str;
  4. end;
  6. set @name = '20210108';
  7. call proc_test6(@name);
  8. select @name from dual;

存储过程中流程控制

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

分支语句

  • **if - then - else**
  1. # 单分支: 如果条件成⽴, 则执行 SQL
  2. if conditions then
  3.     -- SQL
  4. end if;
  6. # 如果参数 a 的值为 1, 则添加⼀条班级信息
  7. create procedure proc_test7(IN a int)
  8. begin
  9.     if a = 1 then
  10.         insert into classes(class_name, remark) 
  11.         values('Java2109', 'test');
  12.     end if;
  13. end;
  1. # 双分支: 如果条件成⽴则执行 SQL1, 否则执行 SQL2
  2. if conditions then
  3.     -- SQL1
  4. else
  5.     -- SQL2
  6. end if;
  8. # 如果参数 a 的值为 1, 则添加⼀条班级信息; 否则添加⼀条学⽣信息
  9. create procedure proc_test7(IN a int)
  10. begin
  11.     if a = 1 then
  12.         insert into classes(class_name, remark) 
  13.         values('Java2109', 'test');
  14.     else
  15.         insert into students(stu_num, stu_name, stu_gender, stu_age, cid, remark)
  16.         values('20210110', '⼩花', '⼥', 19, 1, '...');
  17.     end if;
  18. end;
  • **case**
  1. create procedure proc_test8(IN a int)
  2. begin
  3.     case a
  4.     when 1 then 
  5.         -- SQL1 如果 a 的值为 1, 则执行 SQL1
  6.         insert into classes(class_name, remark)
  7.         values('Java2110', '...');
  8.     when 2 then
  9.         -- SQL2 如果 a 的值为 2, 则执行 SQL2
  10.         insert into students(stu_num, stu_name, stu_gender, stu_age, cid, remark)
  11.         values('20210111', '⼩刚', '男', 21, 2, '...');
  12.     else
  13.         -- SQL (如果变量的值和所有 when 的值都不匹配, 则执行 else 中的 SQL)
  14.         update students set stu_age = 18 where stu_num = '20210110';
  15.     end case;
  16. end;

循环语句

  • **while**
  1. create procedure proc_test9(IN num int)
  2. begin
  3.     declare i int;
  4.     set i = 0;
  5.     while i < num do
  6.         -- SQL
  7.         insert into classes(class_name, remark)
  8.         values(CONCAT('Java', i), '...');
  9.         set i = i + 1;
  10.     end while;
  11. end;
  13. call proc_test9(4);
  • **repeat**
  1. create procedure proc_test10(IN num int)
  2. begin
  3.     declare i int;
  4.     set i = 1;
  5.     repeat
  6.         -- SQL
  7.         insert into classes(class_name, remark)
  8.         values(CONCAT('Python', i),'...');
  9.         set i = i + 1;
  10.     until i > num end repeat;
  11. end;
  13. call proc_test10(4);
  • **loop**
  1. create procedure proc_test11(IN num int)
  2. begin
  3.     declare i int ;
  4.     set i = 0;
  5.     myLoop: loop
  6.         -- SQL
  7.         insert into classes(class_name, remark)
  8.         values(CONCAT('HTML', i), '...');
  9.         set i = i + 1;
  10.         if i = num then
  11.             leave myLoop;
  12.         end if;
  13.     end loop;
  14. end;
  16. call proc_test11(5);

存储过程管理

查询存储过程

存储过程是属于某个数据库的, 也就是说当存储过程创建在某个数据库之后, 只能在当前数据库中调用此存储过程

查询存储过程：查询某个数据库中有哪些存储过程

  1. # 根据数据库名, 查询当前数据库中的存储过程
  2. show procedure status where db = 'db_test2';
  4. # 查询存储过程的创建细节
  5. show create procedure db_test2.proc_test1;

修改存储过程

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

  1. alter procedure <proc_name> 特征1 [特征2 特征3 ...]

存储过程的特征参数:

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

删除存储过程

  1. # drop 删除数据库中的对象 数据库、数据表、列、存储过程、视图、触发器、索引...
  2. # delete 删除数据表中的数据
  3. drop procedure proc_test1;

存储过程练习案例

使用存储过程解决企业项目开发过程中的问题

  • 案例: 使用存储过程完成借书操作

数据准备

  1. # 创建数据库
  2. create database db_test3;
  4. # 使用数据库
  5. use db_test3;
  7. # 创建图书信息表
  8. create table books(
  9.     book_id int primary key auto_increment,
  10.     book_name varchar(50) not null,
  11.     book_author varchar(20) not null,
  12.     book_price decimal(10, 2) not null,
  13.     book_stock int not null,
  14.     book_desc varchar(200)
  15. );
  17. # 添加图书信息
  18. insert into books(book_name, book_author, book_price, book_stock, book_desc)
  19. values('Java程序设计', '亮亮', 38.80, 12, '亮亮老师带你学Java');
  20. insert into books(book_name, book_author, book_price, book_stock, book_desc)
  21. values('Java程序设计', '威哥', 44.40, 9, '千峰伟哥, Java王者领路');
  23. # 创建学生信息表
  24. create table students(
  25.     stu_num char(4) primary key,
  26.     stu_name varchar(20) not null,
  27.     stu_gender char(2) not null,
  28.     stu_age int not null
  29. );
  31. # 添加学生信息
  32. insert into students(stu_num, stu_name, stu_gender, stu_age)
  33. values('1001', '张三', '男', 20);
  34. insert into students(stu_num, stu_name, stu_gender, stu_age)
  35. values('1002', '李四', '女', 20);
  36. insert into students(stu_num, stu_name, stu_gender, stu_age)
  37. values('1003', '王五', '男', 20);
  39. # 创建借书记录表
  40. create table records(
  41.     rid int primary key auto_increment,
  42.     snum char(4) not null,
  43.     bid int not null,
  44.     borrow_num int not null,
  45.     is_return int not null, -- 0 表示未归还 1 表示已归还
  46.     borrow_date date not null,
  47.     constraint FK_RECORDS_STUDENTS foreign key(snum) references students(stu_num),
  48.     constraint FK_RECORDS_BOOKS foreign key(bid) references book(book_id)
  49. );

创建存储过程实现借书业务

业务分析

哪个学生借哪本书, 借了多少本?

操作:

  • 保存结束纪录
  • 修改图书库存

条件:

  • 判断学生是否存在
  • 判段图书是否存在、库存是否充足
  1. # a 输⼊参数 学号
  2. # b 输⼊参数 图书编号
  3. # m 输⼊参数 借书的数量
  4. # state 输出参数 借书的状态 (1 借书成功, 2 学号不存在, 3 图书不存在,  4 库存不足)
  5. create procedure proc_borrow_book(IN a char(4), IN b int, IN m int, OUT state int)
  6. begin
  7.     declare stu_count int default 0;
  8.     declare book_count int default 0;
  9.     declare stock int default 0;
  11.     select count(stu_num) INTO stu_count from students where stu_num = a;
  12.     if stu_count > 0 then
  13.         select count(book_id) INTO book_count from books where book_id = b;
  14.         if book_count > 0 then
  15.             select book_stock INTO stock from books where book_id = b;
  16.             if stock >= m then
  17.                 insert into records(snum, bid, borrow_num, is_return, borrow_date)
  18.                 values(a, b, m, 0, sysdate());
  19.                 update books set book_stock = stock - m where book_id = b;
  20.                 set state = 1;
  21.             else
  22.                 set state = 4;
  23.             end if;
  24.         else
  25.             set state = 3;
  26.         end if;
  27.     else
  28.         set state = 2;
  29.     end if;
  30. end;
  32. # 调用存储过程借书
  33. set @state = 0;
  34. call proc_borrow_book('1001', 1, 2, @state);
  35. select @state from dual;

游标

如果要创建一个存储过程, 需要返回查询语句查询到的多条数据, 该如何实现?

游标的概念

游标可以用来依次取出查询结果集中的每一条数据——逐条读取查询结果集中的纪录

游标的使用步骤

  1. # 1. 声明游标
  2. declare mycursor cursor for select book_name, book_author, book_price from books;
  4. # 2. 打开游标
  5. open mycursor;
  7. # 3. 使用游标: 提取游标当前指向的纪录 (提取之后, 游标自动下移)
  8. fetch mycursor into bname, bauthro, bprice;
  10. # 4. 关闭游标
  11. close mycursor;

游标使用案例

  1. create procedure proc_test2(OUT result varchar(200))
  2. begin
  3.     declare bname varchar(20);
  4.     declare bauthor varchar(20);
  5.     declare bprice decimal(10, 2);
  6.     declare num int;
  7.     declare i int;
  8.     declare str varchar(50);
  9.     declare mycursor cursor for select book_name, book_author, book_price from books;
  11.     -- 此查询语句执行之后返回的是一个结果集 (多条记录), 使用游标可以来遍历查询结果集
  12.     select count(1) INTO num from books;  
  13.     open mycursor;
  14.     set i = 0;
  15.     -- 使用游标要结合循环语句
  16.     while i < num do
  17.         -- 使用游标: 提取游标当前指向的纪录 (提取之后, 游标自动下移)
  18.         FETCH mycursor INTO bname, bauthor, bprice;
  19.         set i = i + 1;
  20.         -- set str = concat_ws('~', bname, bauthor, brpice);
  21.         select concat_ws('~', bname, bauthor, bprice) INTO str;
  22.         set result = concat_ws(',', result, str);
  23.     end while;
  24.     close mycursor;
  25. end;
  27. # 案例测试
  28. set @r = '';
  29. call proc_test2(@r);
  30. select @r from dual;