大学数据库实验C#图书管理系统

CREATE PROCEDURE 按类别查阅图书 @Btype char(20) AS SELECT * FROM 图书基本信息 WHERE Btype=@Btype GO CREATE PROCEDURE 按书名查阅图书 @Bname varchar(20) AS SELECT * FROM 图书基本信息 WHERE Bname=@Bname GO CREATE PROCEDURE 按类别与日期查阅图书 @Btype char(20),@Pdate smalldatetime AS SELECT * FROM 图书基本信息 WHERE Btype=@Btype AND Pdate=@Pdate GO CREATE PROCEDURE 统计月借书情况 @MONTH int AS IF @MONTH < 1 or @MONTH > 12 BEGIN PRINT'输入月份不正确' END ELSE SELECT Bname,COUNT(*) FROM 图书基本信息 JOIN 历史借阅 ON 图书基本信息.Bno=借阅单据.Bno WHERE MONTH(Borrowdate)=@MONTH GROUP BY Bname GO CREATE PROCEDURE 计算罚款额 @Rno char(10),@Bno char(15), @Finekind char(6),@Fine numeric(6,2) output AS IF @Finekind ='过期' SELECT @Fine = DATEDIFF(day,SReturndate,getdate()) * 0.5 FROM 借阅单据 WHERE Rno = @Rno and Bno = @Bno ELSE SET @Fine = (SELECT Bprice FROM 图书基本信息 a join 借阅单据 b on a.Bno=b.Bno WHERE Rno = @Rno and b.Bno = @Bno ) GO Create PROCEDURE 保存罚款记录 @Rno char(10),@Bno char(15), @Finekind char(6) AS DECLARE @fine numeric(6,2) EXEC 计算罚款额 @Rno,@Bno,@Finekind,@fine output Insert 罚款清单(Bno,Rno,Finekind,Fine) Values (@Bno,@Rno,@Finekind,@fine) DELETE FROM 借阅单据 where Bno=@Bno and Rno=@Rno IF @Finekind='丢失' Update 图书基本信息 Set Tquantity=Tquantity-1 Where Bno=@Bno ELSE Update 图书基本信息 Set Nowquantity=Nowquantity+1 Where Bno=@Bno Go Create PROCEDURE 还书操作 @Bno char(15),@Rno char(10) AS DELETE FROM 借阅单据 WHERE Bno = @Bno AND Rno = @Rno and Borrowno in (select top 1 Borrowno From 借阅单据 order by Borrowno ASC) UPDATE 图书基本信息 SET Nowquantity = Nowquantity+1 WHERE Bno = @Bno insert 历史借阅(Retime) values (getdate()) GO Create PROCEDURE 查询过期 @Bno char(15),@Rno char(10) AS SELECT * FROM 借阅单据 WHERE exists(select SReturndate from 借阅单据 where SReturndate<Getdate()) and Rno = @Rno and Bno = @Bno Go