圖書借閱系統數據庫設計實驗報告-閱讀頁

　　

【正文】 n_ISBN AND BookID = in_BookID IF ERROR 0 BEGIN ROLLBACK TRAN SET out_str = 39。 RETURN 0 END /* 更新讀者借閱數量 */ UPDATE Reader SET Num = Num 1 WHERE ReaderID = in_ReaderID IF ERROR 0 BEGIN ROLLBACK TRAN SET out_str = 39。 RETURN 0 END /* 更新圖書庫存量數量 */ UPDATE Book SET Invnum = Invnum + 1 WHERE ISBN = in_ISBN IF ERROR = 0 /*如果所有語句都不出錯，則提交事務并返回*/ BEGIN COMMIT TRAN SET out_str = 39。 RETURN 1 END ELSE /*如果執行出錯，則回滾事務并返回*/ BEGIN ROLLBACK TRAN SET out_str = 39。 RETURN 0 ENDEND /*執行向借閱表 Lend 表插入借書記錄的存儲過程（借書）*/USE MBook203GODECLARE out_str char(30)EXEC Book_Borrow 39。,39。,39。,out_str OUTPUTSELECT out_str/*執行刪除借閱表 Lend 一條借書記錄的存儲過程（還書）*/USE MBook203GODECLARE out_str char(30)EXEC Book_Return 39。,39。,39。,out_str OUTPUTSELECT out_str/* 、姓名及書名。　　　 ③計算應還書日期可用DATEADD函數，其語法格式為：　　　　　DATEADD (datepart , number, date )　　　　　其中：datepart是指與number相加的date部分，取值可以是YY、M、D、W等。　　　　　 Date是參加運算的指定日期。201381039。201382039。 ⑤列出歷史借閱表中有超期未歸還圖書的還書記錄（Book_History_Overdue） */USE MBook203GOIF EXISTS(SELECT name FROM WHERE name = 39。)DROP PROC Book_Current_OverdueGOCREATE PROCEDURE Book_Current_Overdue Book_Current_Overdue_Cursor CURSOR VARYING OUTPUT, out_str char(50) OUTPUT WITH ENCRYPTION ASBEGIN IF EXISTS (SELECT FROM (SELECT ReaderID,Name FROM Reader )R,(SELECT ReaderID,BookID,ISBN,bbt FROM Lend )L, (SELECT ISBN,Bname FROM Book)B WHERE = AND = AND DATEDIFF(DAY,GETDATE()) 90) BEGIN SET out_str = 39。 SET Book_Current_Overdue_Cursor = CURSOR DYNAMIC /*設置輸出游標類型為 動態游標*/ FOR SELECT ,GETDATE() 當前日期 FROM (SELECT ReaderID,Name FROM Reader )R,(SELECT ReaderID,BookID,ISBN,bbt FROM Lend )L, (SELECT ISBN,Bname FROM Book)B WHERE = AND = AND DATEDIFF(DAY,GETDATE()) 90 OPEN Book_Current_Overdue_Cursor /*打開游標*/ RETURN 1 END ELSE BEGIN SET out_str = 39。 RETURN 0 END END⑤列出歷史借閱表中有超期未歸還圖書的還書記錄USE MBook203GOIF EXISTS(SELECT name FROM WHERE name = 39。)DROP PROC Book_History_OverdueGOCREATE PROCEDURE Book_History_Overdue Book_History_Overdue_Cursor CURSOR VARYING OUTPUT, out_str char(50) OUTPUT WITH ENCRYPTION ASBEGIN IF EXISTS( SELECT FROM (SELECT ReaderID,Name FROM Reader )R,(SELECT ReaderID,BookID,ISBN,bbt,rbbt FROM HistoryLend )H,(SELECT ISBN,Bname FROM Book)B WHERE = AND = AND DATEDIFF(DAY,) 90 ) BEGIN SET out_str = 39。 SET Book_History_Overdue_Cursor = CURSOR DYNAMIC FOR SELECT , FROM (SELECT ReaderID,Name FROM Reader )R,(SELECT ReaderID,BookID,ISBN,bbt,rbbt FROM HistoryLend )H,(SELECT ISBN,Bname FROM Book)B WHERE = AND = AND DATEDIFF(DAY,) 9 OPEN Book_History_Overdue_Cursor RETURN 1 END ELSE BEGIN SET out_str = 39。 RETURN 0 ENDEND/* 當前借閱表 中超期未歸還圖書的 借書記錄 存儲過程*/USE MBook203GODECLARE out_str char(50),MyCursor CURSOREXEC Book_Current_Overdue Book_Current_Overdue_Cursor = MyCursor OUTPUT,out_str = out_str OUTPUTSELECT out_strFETCH NEXT FROM MyCursorWHILE(FETCH_STATUS = 0) BEGIN FETCH NEXT FROM MyCursor ENDCLOSE MyCursorDEALLOCATE MyCursor/* 歷史借閱表 中超期未歸還圖書的 還書記錄 存儲過程*/USE MBook203GODECLARE out_str char(50),MyCursor CURSOREXEC Book_History_Overdue Book_History_Overdue_Cursor = MyCursor OUTPUT,out_str = out_str OUTPUTSELECT out_strFETCH NEXT FROM MyCursorWHILE(FETCH_STATUS = 0) BEGIN FETCH NEXT FROM MyCursor ENDCLOSE MyCursorDEALLOCATE MyCursor/*8．用戶自定義書籍檢索存儲過程，用于實現根據 圖書名稱 或 作者姓名 的模糊查詢 返回圖書有關信息。Book_Retrieval39。%39。%39。以下為檢索到的相關圖書信息：39。%39。%39。沒有為您檢索到相關圖書信息，請重新輸入相關篩選條件！39。JAVA39。*/USE MBook203GOIF EXISTS(SELECT name FROM WHERE name = 39。)DROP PROCEDURE Reader_Current_LendGOCREATE PROCEDURE Reader_Current_Lend Reader_Current_Lend_Cursor CURSOR VARYING OUTPUT, in_ReaderID char(12)=null,in_Name varchar(8)=null,out_str char(30) OUTPUT WITH ENCRYPTIONASBEGIN IF EXISTS ( SELECT ,dept,name,Num,Bname FROM (SELECT ReaderID,Dept,Name,Num FROM Reader WHERE ReaderID = in_ReaderID) R,Lend L,Book B WHERE = AND = ) BEGIN SET out_str = 39。 SET Reader_Current_Lend_Cursor = CURSOR DYNAMIC FOR SELECT ,Dept,Spec,Name,Num,Bname FROM (SELECT ReaderID,Dept,Spec,Name,Num FROM Reader WHERE ReaderID = in_ReaderID) R,Lend L,Book B WHERE = AND = OPEN Reader_Current_Lend_Cursor RETURN 1 END ELSE BEGIN SET out_str = 39。 RETURN 0 ENDEND/* ，執行（Reader_Current_Lend）存儲過程*/USE MBook203GODECLARE out_str char(30),MyCursor CURSOR,ReaderID char(12)SET ReaderID = 39。EXEC Reader_Current_Lend Reader_Current_Lend_Cursor = MyCursor OUTPUT,out_str = out_str OUTPUT,in_ReaderID = ReaderID SELECT out_strFETCH NEXT FROM MyCursorWHILE(FETCH_STATUS 1) BEGIN FETCH NEXT FROM MyCursor ENDCLOSE MyCursorDEALLOCATE MyCursor/* 執行向借閱表 Lend 表插入借書記錄的存儲過程（借書）*/①測試，當借閱表中存在 圖書 BookID 時讀者繼續借閱該書時：USE MBook203GODECLARE out_str char(30)EXEC Book_Borrow 39。,39。,39。,out_str OUTPUTSELECT out_strGO②測試，當圖書表中 圖書庫存量為0 時，ISBN 號為：9787121083，Java 編程思想讀者 200807014202 繼續借閱該書時：DECLARE out_str char(30)EXEC Book_Borrow 39。,39。,39。,out_str OUTPUTSELECT out_strGODECLARE out_str char(30)EXEC Book_Borrow 39。,39。,39。,out_str OUTPUTSELECT out_strGODECLARE out_str char(30)EXEC Book_Borrow 39。,39。,39。,out_str OUTPUTSELECT out_strGODECLARE out_str char(30)EXEC Book_Borrow 39。,39。,39。,out_str OUTPUTSELECT out_strGODECLARE out_str char(30)EXEC Book_Borrow 39。,39。,39。,out_str OUTPUTSELECT out_strGO/* 執行查詢借閱表 Lend 中超期未歸還借書記錄的存儲過程 */①測試USE MBook203GODECLARE out_str char(50),MyCursor CURSOREXEC Book_Current_Overdue Book_Current_Overdue_Cursor = MyCursor OUTPUT,out_str = out_str OUTPUTSELECT out_strFETCH NEXT FROM MyCursorWHILE(FETCH_STATUS = 0) BEGIN FETCH NEXT FROM MyCursor ENDCLOSE MyCursorDEALLOCAT