Oracle学习—图书管理系统_存储过程

图书管理系统（存储过程实现sql）

---

• 图书管理系统(每个学生最多能借书本)
• 创建三张表对业务逻辑的分析，然后创建数据库
• 图书信息表tb_book;
• 学生信息表tb_stu;
• 借书记录表tb_record;

---

• 学生入学：在学生表中添加学生信息
• 图书入库：如果书库中存在该书的记录
• 学生借书：根据学号和图书编号进行判断，当学生借书的时候，这本书时候存在，学生能借多少本，书的库存还够不够，借书之后图书的信息和借书记录相应的进行修改
• 学生还书：根据学号，同时编号和书的数量进行还书，还书之后借书记录和图书信息进行相应的修改
• 记录查询：根据提供的学号，查询该同学的记录，如果不存在借书记录则进行提示

---

--创建数据库
    create database library1
    on(
        name='library1',
        filename='C:\Program Files\MicrosoftSQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\library1.mdf',
        size=3,
        maxsize=5,
        filegrowth=2
    )
    log on(
        name='library1_log',
        filename='C:\Program Files\MicrosoftSQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\library1.ldf',
        size=3,
        maxsize=5,
        filegrowth=2
    );
    
    --使用数据库
    use library1;
    
    --创建三张数据表
    --学生表:tb_stu【学号、姓名、性别、可借书的最大数目】
    --图书表:tb_book【图书编号、书名、作者、库存量】
    --借书记录表:tb_record【学号、图书编号、数量、借书日期】
    
    create table tb_stu
    (
        sno int primarykey identity(1,1),
        sname varchar(32) not null,
        ssex char(2) check(ssex in('男','女')) default '女'[W用16] ,--性别默认为女
        maxnum int not null default 7 --最多能借本
    );
    create table tb_book
    (
        bid int primary key identity(1001,1),
        bname varchar(60) not null,
        bauthor varchar(60) not null,
        bnum int not null
    );
    create table tb_record
    (
        r_sno int  not null , --外键
        r_bid int not null , --外键
        r_num int not null ,
        r_date date not null  --借书日期
    );
    
    --三张表关联
    alter table tb_recordadd constraint w_r_sforeignkey(r_sno)referencestb_stu(sno);
    alter table tb_recordadd constraint w_r_bforeignkey(r_bid)referencestb_book(bid);
    
    **--1学生入学(利用存储过程调用插入语句)**
    create procp_addstu
        @namevarchar(32),
        @sexchar(2)
    as
        insert into tb_stu(sname,ssex)values(@name,@sex)
    go
    
    -- 删除存储过程
    drop procp_addstu;
    
    -- 执行存储过程 插入数据 学生入学
    call procp_addstu('张珊','女');
    call procp_addstu('李四','男');
    call procp_addstu('王五','男');
    
    -- 查询学生表
    select* from  tb_stu;

	-- 删除借阅记录
    truncate table tb_record;
    
    **--2图书入库 创建存储过程**
    create procp_addbook
        @namevarchar(32),
        @authorvarchar(60),
        @numint
    as
        declare @mint --根据书名判断书是否已经存储在图书库中
        select @m=count(*) from  tb_book  where   bname=@name
        if(@m<=0) --书库中不存在该书的记录
           begin
               insert into tb_book(bname,bauthor,bnum) values(@name,@author,@num)
           end
        else --书库中存在该书的记录
           begin
               declare
                  @oldnum int,
                  @newnum int
               select @oldnum=bnum  from tb_book  where   bname=@name--获得原来的库存量
               set @newnum=@oldnum+@num--图书入库之后的库存量
               update tb_book set bnum=@newnum  where   bname=@name--更新图书的库存量
               print @name+'入库成功'
           end
    go
    
    -- 图书入库
    call procp_addbook('非诚勿扰','乐嘉',1);
    call procp_addbook('Java编程思想','Thinking',10);
    
    select* from  tb_record;
    select* from  tb_stu;
    select* from  tb_book;
    dropprocp_addbook;
    
    **--3借书**
    create procp_borrow
        @sno int,--借书人的学号
        @name varchar(60),--要借的书的名称
        @num int--要借几本
    as
        declare
           @m int--记录书在图书库中的记录条数
        select @m=count(*) from tb_book where   bname=@name
        if(@m=0)
           begin
               print '未能找到'+@name+'这本书'
           end
        else
           begin
               declare
                  @n int --记录该书在图书库中的库存量
               selec t@n=bnum from tb_book where   bname=@name
               if(@num>@n)--所借书的数量大于库存量
                  begin
                      print'库存不足！'
                  end
               else --库存足能借书
                  begin
                      declare
                         @maxnum int,--最多能借几本
                         @alreadynum int,--已经借了几本
                         @lastnum int --最多还能借几本
                      select @maxnum=maxnum from tb_stu where   sno=@sno
                      select @alreadynum=sum(r_num) from tb_record where   r_sno=@sno[W用17] --如果记录表中没有该学生的借书记录，则返回null
                      set @lastnum=@maxnum-@alreadynum
                      if(@alreadynum is null)
                         begin
                             set @alreadynum=0
                         end
                      else
                         begin
                             set @lastnum=@maxnum-@alreadynum--该学生还能借书的数量
                         end
                      if(@num>@lastnum)
                         begin
                             print '借书数量超过限制，请归还后再借！'
                         end
                      else--表示可以借书
                         begin
                             declare
                                @bid int
                                select @bid=bid from tb_book where   bname=@name
                             declare
                                @r_count int
                                select @r_count=count(*) from tb_record where   r_sno=@sno
                             if(@r_count=0)
                                begin
                                    insert into tb_record values(@sno,@bid,@num,getdate()[W用18] )
                                end
                             else --该学生借过这本书
                                begin
                                    declare
                                      @r_num int,--取出借过该书的本数
                                      @newrnum int --总共借过该书的本数
                                    select @r_num=r_num from tb_record where   r_sno=@snoandr_bid=@bid
                                    set @newrnum=@r_num+@num
                                    update tb_record set r_num=@newrnum where  r_sno=@snoandr_bid=@bid
                                end
                             declare
                                @y1 int
                                select @y1=bnum from tb_book where   bname=@name
                                update tb_book setbnum=@y1-@num where  bname=@name
                             print '借书成功！'
                         end
                  end
           end
    go
    
   call  procp_borrow(1,'电子商务',1);
   call  procp_borrow(1,'非诚勿扰',1);
   call   procp_borrow(2,'2312',23);
    
    **--4还书**
    create procp_back
        @snoint,--学号
        @bnamevarchar(32),--书的名称
        @numint --还书的数量
    as
        declare
           @bid int,--记录还的这本书的编号
           @count int--表示是否存在这个学号和这个图书编号对应的借书记录
           select @bid=bid from tb_book where   bname=@bname
           select @count=count(*) from tb_record where   r_sno=@snoandr_bid=@bid
        if(@count=0)--
           begin
               print'你没有借该书的记录，请确认书名再还书！'
           end
        else
           begin
           declare
               @x int --获取该生借阅该书的数量
               select @x=r_num from tb_record where   r_sno=@snoandr_bid=@bid
               if(@x<@num)
                  begin
                      print'还书数量超过了借书数量，请确认还书的数量，还书失败！'
                  end
               else
                  begin
                      if(@num=@x)
                         begin
                             delete  from  tb_record where  r_sno=@snoandr_bid=@bid
                         end
                      else
                         begin
                             update tb_record set r_num=@x-@num where  r_sno=@snoandr_bid=@bid
                         end
                      --修改图书库的信息
                      declare
                         @bnum int
                          select @bnum=bnum from tb_book where   bname=@bname
                         updatetb_book setbnum=@bnum+@num where  bname=@bname
                         print'还书成功'
                  end
           end
    go
    
   call procp_back(1,'非诚勿扰',1);

    **--5记录查询**
    create procp_find
        @snoint
    as
        declare
           @count int
           select @count=count(*) from tb_record where   r_sno=@sno
           if(@count>0)
               begin
                  select *  from tb_record where  r_sno=@sno
               end
           else
               begin
                  print'您没有借书记录，谢谢查询!'
               end
    go
    
    call procp_find(1);
    call procp_find(2);
    drop procp_find;
    --查询数据
    select* from tb_stu;
    select* from tb_book;
    select* from tb_record;
    --删除表格
    drop table tb_record;
    drop table tb_book;
    drop table tb_stu;