设为首页收藏本站
开启辅助访问

MySQL 存储过程实现简单的借书还书业务

 您所在的位置：网站首页 › 存储过程的测试 › MySQL 存储过程实现简单的借书还书业务

MySQL 存储过程实现简单的借书还书业务

2023-11-05 03:28| 来源: 网络整理| 查看: 265

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

注：未经本作者允许，严禁转载

一、准备数据

建库建表加数据

## 借书案例 -- 创建数据库 CREATE DATABASE db_book_case; -- 使用数据库 USE db_book_case; -- 创建图书信息表 CREATE TABLE books( book_id INT 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), PRIMARY KEY (book_id) ); -- 添加图书信息 INSERT INTO books(book_name,book_author,book_price,book_stock,book_desc) VALUES('Java程序设计','老威',67.9,20,'老威教你Java入门到入土'); INSERT INTO books(book_name,book_author,book_price,book_stock,book_desc) VALUES('Java王者之路','海明',97.9,10,'Java王者领路人·海明'); -- 创建学生信息表 CREATE TABLE students( stu_num CHAR(8), stu_name VARCHAR(20) NOT NULL, stu_gender CHAR(2) NOT NULL DEFAULT '男', stu_age INT NOT NULL, PRIMARY KEY (stu_num) ); -- 添加学生信息 INSERT INTO students(stu_num,stu_name,stu_gender,stu_age)VALUES('1001','张三','男','18'); INSERT INTO students(stu_num,stu_name,stu_gender,stu_age)VALUES('1002','李四','女','18'); INSERT INTO students(stu_num,stu_name,stu_gender,stu_age)VALUES('1003','王五','男','18'); 二、创建关系表

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

业务分析

操作：

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

条件：

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

创建借书记录表

-- 借书记录表 CREATE TABLE records( rid INT AUTO_INCREMENT COMMENT '借书记录id', snum CHAR(8) NOT NULL COMMENT '学生id', bid INT NOT NULL COMMENT '图书id', borrwo_num INT NOT NULL COMMENT '借书的数量', is_return INT NOT NULL COMMENT '0表示未归还，1表示已经归还', borrow_date DATE NOT NULL COMMENT '借书时间', CONSTRAINT FK_RECORDS_STUDENTS FOREIGN KEY(snum) REFERENCES students(stu_num), CONSTRAINT FK_RECORDS_BOOKS FOREIGN KEY(bid) REFERENCES books(book_id), PRIMARY KEY (rid) );

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Eb9OJfUl-1640432982246)(MySQL%E5%80%9F%E4%B9%A6%E6%A1%88%E4%BE%8B.assets/image-20211224215208922.png)]

三、创建存储过程 -- 创建存储过程：实现借书业务 -- 参数1：a 输入参数 学号 -- 参数2：b 输入参数 图书编号 -- 参数3：m 输入参数 借书的数量 -- 参数4：state 输出参数 借书状态（1：借书成功，2：学号不存在，3：图书不存在，4：库存不足） DELIMITER $$ CREATE PROCEDURE proc_borrow_book(IN a CHAR(8),IN b INT,IN m INT,OUT state INT) BEGIN DECLARE stu_count INT DEFAULT 0; DECLARE book_count INT DEFAULT 0; DECLARE stock INT DEFAULT 0; -- 判断学号是否存在：根据参数 a 到学生信息表查询是否有 stu_num=a 的记录 SELECT COUNT(stu_num) INTO stu_count FROM students WHERE stu_num=a; IF stu_count>0 THEN -- 学号存在 -- 判断图书 id 是否存在：根据参数 b 查询图书记录总数 SELECT COUNT(book_id) INTO book_count FROM books WHERE book_id=b; IF book_count>0 THEN -- 图书存在 -- 判断图书的库存是否充足：查询当前图书库存，然后和 m 进行比较 SELECT book_stock INTO stock FROM books WHERE book_id=b; IF stock>=m THEN -- 执行借书操作 -- 操作1：在借书记录表中添加记录,使用系统时间：SYSDATE() INSERT INTO records(snum,bid,borrwo_num,is_return,borrow_date) VALUES(a,b,m,0,SYSDATE()); -- 操作2：修改图书库存 UPDATE books SET book_stock=stock-m 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 $$

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Gf8AHwzR-1640432982247)(MySQL%E5%80%9F%E4%B9%A6%E6%A1%88%E4%BE%8B.assets/image-20211225120621541.png)]

创建没问题，调用测试一下存储过程

## 调用存储过程借书 -- 测试学号不存在 SET @state=0; CALL proc_borrow_book('1005',1,2,@state); SELECT @state FROM DUAL; -- 测试图书不存在 SET @state=0; CALL proc_borrow_book('1001',3,2,@state); SELECT @state FROM DUAL; -- 测试图书库存不足 SET @state=0; CALL proc_borrow_book('1001',2,50,@state); SELECT @state FROM DUAL; -- 测试借书成功 SET @state=0; CALL proc_borrow_book('1001',1,2,@state); SELECT @state FROM DUAL;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gn6Hgydc-1640432982249)(MySQL%E5%80%9F%E4%B9%A6%E6%A1%88%E4%BE%8B.assets/image-20211225115243318.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qCe5p7F4-1640432982250)(MySQL%E5%80%9F%E4%B9%A6%E6%A1%88%E4%BE%8B.assets/image-20211225120643504.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CcLRbRq9-1640432982251)(MySQL%E5%80%9F%E4%B9%A6%E6%A1%88%E4%BE%8B.assets/image-20211225120655663.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0DBOXNIY-1640432982252)(MySQL%E5%80%9F%E4%B9%A6%E6%A1%88%E4%BE%8B.assets/image-20211225120739270.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Yfy6pK3G-1640432982252)(MySQL%E5%80%9F%E4%B9%A6%E6%A1%88%E4%BE%8B.assets/image-20211225120438417.png)]

来看一下结果，是否有借书记录

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4382AGuM-1640432982254)(MySQL%E5%80%9F%E4%B9%A6%E6%A1%88%E4%BE%8B.assets/image-20211225120454956.png)]

books 表原来库存是 20 本的 现在是18本

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-67nCfOVp-1640432982255)(MySQL%E5%80%9F%E4%B9%A6%E6%A1%88%E4%BE%8B.assets/image-20211225120814738.png)]

还书的简单业务逻辑 -- 创建存储过程：实现还书业务 -- 参数1：a 输入参数 学号 -- 参数2：b 输入参数 图书编号 -- 参数3：m 输入参数 还书的数量 -- 参数4：state 输出参数 借书状态（1：还书成功，2：学号不存在，3：图书不存在，4：库存不足） DELIMITER $$ CREATE PROCEDURE proc_also_book(IN a CHAR(8),IN b INT(11),IN m INT(11),OUT state INT) BEGIN DECLARE stu_count INT DEFAULT 0; DECLARE book_count INT DEFAULT 0; DECLARE stock INT DEFAULT 0; DECLARE borr_num INT DEFAULT 0; -- 判断是否是这个学号借的 SELECT COUNT(snum) INTO stu_count FROM records WHERE snum=a; IF stu_count>0 THEN -- 有这个学号 -- 判断这个学号借了哪本书 SELECT COUNT(bid) INTO book_count FROM records WHERE bid=b; IF book_count>0 THEN -- 借的图书存在 -- 判断还书，还几本 SELECT borrwo_num INTO stock FROM records WHERE bid=b; IF m>=stock THEN -- 执行还书操作 -- 操作1：修改归还的书本库存 UPDATE books SET book_stock=book_stock+m WHERE book_id=b; -- 操作2：在借书记录表中修改数据 UPDATE records SET borrwo_num=stock-m WHERE snum=a; -- 查询记录库存 SELECT borrwo_num INTO borr_num FROM records WHERE snum=a; -- 如果还书的库存为 0 则删除记录 IF borr_num=0 THEN DELETE FROM records WHERE snum=a; END IF; -- 还书成功 SET state=1; ELSE -- 超过手上需要还的书 SET state=4; END IF; ELSE -- 借的图书不存在 SET state=3; END IF; ELSE -- 学号不存在 SET state=2; END IF; END $$ -- 测试还书的存储过程 SET @state=0; CALL proc_also_book('1001',1,2,@state); SELECT @state FROM DUAL;

我这里写的是如果还书的库存为 0 则删除该记录，也是没问题的

在这里插入图片描述



【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3