数据库21/10/21

存储过程

存储过程——借书案例

1.数据准备

• 创建图书表并插入数据

use test;
create table books(
book_id int primary key auto_increment,
book_name varchar(50) not null,
book_price decimal(10,2) not null,
book_stock int not null,
book_desc varchar(200)
);

insert into books(book_name,book_price,book_stock,book_desc) values('Java程序设计',45.8,20,'Java基础教学');
insert into books(book_name,book_price,book_stock,book_desc) values('Pythen程序设计',40.8,12,'Pythen基础教学');

• 创建学生表并插入数据

create table students(
  stu_num char(8) 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('2021001','张晋','男',20);
insert into students(stu_num,stu_name,stu_gender,stu_age) values('2021002','李林','男',20);
insert into students(stu_num,stu_name,stu_gender,stu_age) values('2021003','林涵','女',21);

• 创建借书表并连接图书表与学生表

create table records(
  rid int primary key auto_increment,
  snum char(8) not null,
  bid int not null,
  books_num int not null,
  is_return int not null,
  borrow_date date not null,
  constraint FK_STUDENTS foreign key(snum) references students(stu_num),
  constraint FK_BOOKS foreign key(bid) references books(book_id)
);

2.业务分析

实现借书的操作：哪个学生借哪本书，借了多少本？
操作：

• 保留借书记录
• 修改图书库存
  条件：
• 判断学生是否存在
• 判断图书是否存在，库存是否充足
  3.创建存储过程

-- a:学生学号  b:图书编号  c:借书数量  
-- state:借书状态，1借书成功，2学号不存在，3图书编号不存在，4库存不足，
create procedure pro_test(in a char(8),in b int,in c int,out state int)  
begin
  declare stu_count int;
  declare book_count int;
  declare book_num int;
  -- 判断学生学号是否存在
  select count(stu_num) into stu_count from students where stu_num=a;
  if stu_count>0 THEN
    -- 判断图书编号是否存在
    select count(book_id) into book_count from books where book_id=b;
      if book_count>0 THEN
				-- 判断图书库存是否充足
        select book_stock into book_num from books where book_id=b;
				if c<=book_num then 
					-- 添加一条记录
					insert into records(snum,bid,books_num,is_return,borrow_date)
					-- 系统时间
					values(a,b,c,0,sysdate());
					-- 修改图书库存
					update books set book_stock = book_num-c 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;

4.测试存储过程

• 输入不存在的学号，输出2，借书表无任何添加记录

set @s = 0;
call pro_test('2021004',1,4,@s);
select @s from dual;

• 输入不存在的图书编号，输出3，借书表无任何添加记录

set @s = 0;
call pro_test('2021003',3,4,@s);
select @s from dual;

• 输入大于库存数量的借书数量，输出4，借书表无任何添加记录

set @s = 0;
call pro_test('2021003',2,13,@s);
select @s from dual;

• 输出正确的数据，输出1

set @s = 0;
call pro_test('2021003',2,2,@s);
select @s from dual;

• 借书表插入了一条记录

• 图书表图书库存-2