文章目录* *一、存储过程 * * 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 ab; 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 [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 aa; 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 s0; 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 inum DO – i 参数变量时循环执行SQL语句 – SQL SET i i1; – 每循环一次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 i1; – 每执行一次i1 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 i1; – 每循环一次i1 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; 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 ab; 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 [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 aa; 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 s0; 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 inum DO – i 参数变量时循环执行SQL语句 – SQL SET i i1; – 每循环一次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 i1; – 每执行一次i1 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 i1; – 每循环一次i1 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;