【MySQL数据库】存储过程实战—

【MySQL数据库】存储过程实战——图书借阅系统

图书借阅归还借阅不用count判断，归还不用具体字段值判断每次借阅或者归还只能操作1本

数据准备

-- 创建数据库
create database db_test3 CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 使用数据库
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('高级运维工程师','运老师',38.80,12,'运老师带你运维起飞');
insert into
books(book_name,book_author,book_price,book_stock,book_desc)
values('简易mysql','维老师',44.40,9,'维老师带你学mysql');
-- 创建学⽣信息表
create table students(
stu_num char(4) primary key,
stu_name varchar(20) not null,
stu_gender char(2) not null,
stu_age int not null
);
-- 添加学⽣信息
insert into students(stu_num,stu_name,stu_gender,stu_age)
values('1001','张三','男',20);
insert into students(stu_num,stu_name,stu_gender,stu_age)
values('1002','李四','⼥',20);
insert into students(stu_num,stu_name,stu_gender,stu_age)
values('1003','王五','男',20);

创建借书记录表

-- 借书记录表：
create table records(
rid int primary key auto_increment, -- 借阅信息id
snum char(4) not null,-- 学号
bid int not null, -- 书籍id
borrow_num int not null, -- 借阅数量
is_return int not null, -- 归还状态：0 表示未归还 1 表示已经归还
borrow_date date not null,-- 借阅日期
return_date date,-- 归还日期
constraint FK_RECORDS_STUDENTS foreign key(snum) references
students(stu_num),
constraint FK_RECORDS_BOOKS foreign key(bid) REFERENCES
books(book_id)
);

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

-- 实现借书业务：
-- 参数1： a 输入参数 学号
-- 参数2： b 输入参数 图书编号
-- 参数3： state 输出参数 借书的状态（1 借书成功，2 学号不存在，3 图书不存在， 4 库存不⾜）
create procedure proc_borrow_book(IN a char(4),IN b int,OUT state int)
BEGIN
	DECLARE
		stu_exist INT DEFAULT 0;
	DECLARE
		book_exist INT DEFAULT 0;
	DECLARE
		stock INT DEFAULT 0;-- 判断学号是否存在：根据参数 a 到学⽣信息表查询是否有stu_num=a的记录
	SELECT
		stu_num INTO stu_exist 
	FROM
		students 
	WHERE
		stu_num = a;
	IF
		stu_exist > 0 THEN
-- 学号存在
-- 判断图书ID是否存在：根据参数b 查询图书记录总数
		SELECT
			book_id INTO book_exist 
		FROM
			books 
		WHERE
			book_id = b;
		IF book_exist > 0 THEN
-- 图书存在
-- 判断图书库存是否充足：查询当前图书库存，然后和参数m进行比较
	select book_stock INTO stock from books where book_id=b;
	if stock >= 1 then
-- 执行借书
-- 操作1：在借书记录表中添加记录
	insert into records(snum,bid,borrow_num,is_return,borrow_date) values(a,b,1,0,sysdate());
-- 操作2：修改图书库存
	update books set book_stock=stock-1 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;-- 调用存储过程借书

SET @state = 0;
CALL proc_borrow_book ( '1001', 1, @state );
SELECT
					@state;

在这里插入图片描述

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

-- 实现借书业务：
-- 参数1： a 输入参数 学号
-- 参数2： b 输入参数 图书编号
-- 参数3： dt 输入参数 借阅日期
-- 参数4： state 输出参数 归还的状态（1 归还成功 2 归还失败）
CREATE PROCEDURE proc_return_book ( IN a CHAR ( 4 ), IN b INT, IN dt DATE, OUT state INT ) BEGIN
	DECLARE
		record_count INT DEFAULT 0;-- 记录借阅记录id
	DECLARE
		record_id INT DEFAULT 0;-- 记录借阅记录id
-- 查询借阅记录id，如果借阅记录存在record_id的值会变为对应id，否则为0
	SELECT
		rid INTO record_id 
	FROM
		records 
	WHERE
		snum = a 
		AND bid = b 
		AND is_return = 0 
		AND borrow_date = dt;-- 查询借阅记录id，如果借阅记录存在record_id的值会变为对应id，否则为0
	SELECT
		count( rid ) INTO record_count 
	FROM
		records 
	WHERE
		snum = a 
		AND bid = b 
		AND is_return = 0 
		AND borrow_date = dt;
	IF
		record_count <> 0 THEN-- 修改书籍数量为添加借阅数量后的数量
			UPDATE books 
			SET book_stock = book_stock + 1 
		WHERE
			book_id = b;-- 修改借阅记录中的借阅状态为归还
		UPDATE records 
		SET is_return = 1 
		WHERE
			rid = record_id;-- 设置返回值为1，归还成功
		
		SET state = 1;
		ELSE -- 设置返回值为2，归还失败
		
		SET state = 2;
		
	END IF;
	
END;

SET @state = 0;
CALL proc_return_book ( '1001', 1, '2024-05-28', @state );
SELECT
	@state;