/**2.存储过程实现分页 @page为第几页 @row为每页多少行*/IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_page') DROP PROCEDURE proc_pageGOCREATE PROCEDURE proc_page@page int,@row int =10 As DECLARE @total AS int SELECT @total=count(*) FROM BorrowIF @total<=(@page-1)*@row print('总条数为'+convert(varchar(5),@total)+'请确认参数')ELSESELECT TOP (@row) * FROM Borrow WHERE rid NOT IN(SELECT TOP ((@page-1)*@row) rid FROM Borrow)GO--执行语句exec proc_page 1 /**3.存储过程统计已'北京'冠名的出版社的图书信息*/IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_book')DROP PROCEDURE proc_bookGOCREATE PROCEDURE proc_book@bname varchar(50) ASSELECT * FROM Book WHERE PubComp like @bnameGO--执行存储过程exec proc_book '北京%'/**5.存储过程实现插入借阅记录，输入参数：借书人ID、姓名、借书的名称，要求：（1）图书信息表Book对应的图书数量减1；（2）读者信息表Reader对应的读者已借书数量加1。如没有该借阅者的信息，则新加一条读者信息条记录；（3）向图书借阅表Borrow中添加一条借阅记录，借阅日期、应归还日期、实际归还日期都采用默认值。表结构参考第2章作业的简答题。*/IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_addreader')DROP PROCEDURE proc_addreaderGOCREATE PROCEDURE proc_addreader@RID varchar(50), --reader ID@Rname varchar(50), --reader name@Bname varchar(50) --borrow book nameASBEGIN TRANSACTIONDECLARE @errorSum intSET @errorSum=0UPDATE Book SET BCount=BCount-1 WHERE(BName=@Bname)SET @errorSum=@errorSum+@@errorIF EXISTS(SELECT * FROM Reader WHERE RName=@Rname)UPDATE Reader SET LendNum=LendNum+1 WHERE RName=@RnameELSEINSERT INTO Reader VALUES(@RID,@RName,1) SET @errorSum=@errorSum+@@errorDECLARE @BID varchar(50) SELECT @BID=BID FROM Book WHERE BName=@BnameINSERT INTO Borrow(RID,BID) VALUES (@RID,@BID)SET @errorSum=@errorSum+@@errorIF (@errorSum<>0)BEGIN print('失败了')ROLLBACK TRANSACTION END ELSE BEGIN print('成功了')COMMIT TRANSACTIONEND GO
/**4.存储过程统计某一时间段的借阅信息*/IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_borrow')DROP PROCEDURE proc_borrowGOCREATE PROCEDURE proc_borrow@beginDate datetime, --注意参数不能直接用函数赋值@endDate datetimeAS IF @beginDate IS NULL SET @beginDate=DateAdd(MM,-1,getDate()) IF @endDate IS NULL SET @endDate=getDate() SELECT * FROM Borrow WHERE LendDate BETWEEN @beginDate AND @endDateGO
/**5.存储过程实现插入借阅记录，输入参数：借书人ID、姓名、借书的名称，要求：（1）图书信息表Book对应的图书数量减1；（2）读者信息表Reader对应的读者已借书数量加1。如没有该借阅者的信息，则新加一条读者信息条记录；（3）向图书借阅表Borrow中添加一条借阅记录，借阅日期、应归还日期、实际归还日期都采用默认值。表结构参考第2章作业的简答题。*/IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_addreader')DROP PROCEDURE proc_addreaderGOCREATE PROCEDURE proc_addreader@RID varchar(50), --reader ID@Rname varchar(50), --reader name@Bname varchar(50) --borrow book nameASBEGIN TRANSACTIONDECLARE @errorSum intSET @errorSum=0UPDATE Book SET BCount=BCount-1 WHERE(BName=@Bname)SET @errorSum=@errorSum+@@errorIF EXISTS(SELECT * FROM Reader WHERE RName=@Rname)UPDATE Reader SET LendNum=LendNum+1 WHERE RName=@RnameELSEINSERT INTO Reader VALUES(@RID,@RName,1) SET @errorSum=@errorSum+@@errorDECLARE @BID varchar(50) SELECT @BID=BID FROM Book WHERE BName=@BnameINSERT INTO Borrow(RID,BID) VALUES (@RID,@BID)SET @errorSum=@errorSum+@@errorIF (@errorSum<>0)BEGIN print('失败了')ROLLBACK TRANSACTION END ELSE BEGIN print('成功了')COMMIT TRANSACTIONEND GO
0 0
