【正文】 r = CURSOR DYNAMIC /*設(shè)置輸出游標(biāo)類型為 動(dòng)態(tài)游標(biāo)*/ FOR SELECT ,,GETDATE() 當(dāng)前日期 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 /*打開游標(biāo)*/ RETURN 1 END ELSE BEGIN SET @out_str = 39。當(dāng)前借閱表中不存在超期未歸還圖書的借書記錄！39。 RETURN 0 END END⑤列出歷史借閱表中有超期未歸還圖書的還書記錄USE MBook203GOIF EXISTS(SELECT name FROM WHERE name = 39。Book_History_Overdue39。)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。歷史借閱表中超期未歸還圖書的還書記錄！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。歷史借閱表中不存在超期未歸還圖書的還書記錄！39。 RETURN 0 ENDEND/* 當(dāng)前借閱表 中超期未歸還圖書的 借書記錄 存儲(chǔ)過程*/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/* 歷史借閱表 中超期未歸還圖書的 還書記錄 存儲(chǔ)過程*/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．用戶自定義書籍檢索存儲(chǔ)過程，用于實(shí)現(xiàn)根據(jù) 圖書名稱 或 作者姓名 的模糊查詢 返回圖書有關(guān)信息。*/USE MBook203GOIF EXISTS(SELECT name FROM WHERE name = 39。Book_Retrieval39。)DROP PROC Book_RetrievalGOCREATE PROCEDURE Book_Retrieval @in_ISBN varchar(16) = null,@in_Bname varchar(40) = null,@in_Author varchar(16)= null, @out_str char(52) OUTPUT,@Book_Retrieval_Cursor CURSOR VARYING OUTPUT WITH ENCRYPTIONASBEGIN IF EXISTS (SELECT * FROM Book WHERE ISBN LIKE @in_ISBN OR Bname LIKE @in_Bname+39。%39。OR Author LIKE @in_Author+39。%39。) BEGIN SET @out_str = 39。以下為檢索到的相關(guān)圖書信息：39。 SET @Book_Retrieval_Cursor = CURSOR DYNAMIC FOR SELECT * FROM Book WHERE ISBN LIKE @in_ISBN OR Bname LIKE @in_Bname+39。%39。OR Author LIKE @in_Author+39。%39。 OPEN @Book_Retrieval_Cursor RETURN 1 END ELSE BEGIN SET @out_str = 39。沒有為您檢索到相關(guān)圖書信息，請重新輸入相關(guān)篩選條件！39。 RETURN 0 ENDEND/* ，采用模糊匹配*/USE MBook203GODECLARE @out_str char(52),@MyCursor CURSOR,@Bname varchar(40)SET @Bname = 39。JAVA39。EXEC Book_Retrieval @Book_Retrieval_Cursor = @MyCursor OUTPUT,@out_str = @out_str OUTPUT,@in_Bname = @Bname SELECT @out_strFETCH NEXT FROM @MyCursorWHILE(@@FETCH_STATUS 1) BEGIN FETCH NEXT FROM @MyCursor ENDCLOSE @MyCursorDEALLOCATE @MyCursor/*（Reader_Current_Lend），根據(jù)MBook203數(shù)據(jù)庫的三個(gè)表(Reader,Lend,Book)　查詢指定讀者（指定借書證號(hào)或指定姓名等）當(dāng)前的借書情況。*/USE MBook203GOIF EXISTS(SELECT name FROM WHERE name = 39。Reader_Current_Lend39。)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。該讀者借閱情況為：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。沒有該讀者借閱記錄！39。 RETURN 0 ENDEND/* ，執(zhí)行（Reader_Current_Lend）存儲(chǔ)過程*/USE MBook203GODECLARE @out_str char(30),@MyCursor CURSOR,@ReaderID char(12)SET @ReaderID = 39。20080703411239。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/* 執(zhí)行向借閱表 Lend 表插入借書記錄的存儲(chǔ)過程（借書）*/①測試，當(dāng)借閱表中存在 圖書 BookID 時(shí)讀者繼續(xù)借閱該書時(shí)：USE MBook203GODECLARE @out_str char(30)EXEC Book_Borrow 39。20080703411239。,39。460121067139。,39。4601200139。,@out_str OUTPUTSELECT @out_strGO②測試，當(dāng)圖書表中 圖書庫存量為0 時(shí)，ISBN 號(hào)為：9787121083，Java 編程思想讀者 200807014202 繼續(xù)借閱該書時(shí)：DECLARE @out_str char(30)EXEC Book_Borrow 39。20080702412239。,39。978712108339。,39。9787100139。,@out_str OUTPUTSELECT @out_strGODECLARE @out_str char(30)EXEC Book_Borrow 39。20080701410839。,39。978712108339。,39。9787100239。,@out_str OUTPUTSELECT @out_strGODECLARE @out_str char(30)EXEC Book_Borrow 39。20080702420139。,39。978712108339。,39。9787100339。,@out_str OUTPUTSELECT @out_strGODECLARE @out_str char(30)EXEC Book_Borrow 39。20080522320539。,39。978712108339。,39。9787100439。,@out_str OUTPUTSELECT @out_strGODECLARE @out_str char(30)EXEC Book_Borrow 39。20080701420239。,39。978712108339。,39。9787100539。,@out_str OUTPUTSELECT @out_strGO/* 執(zhí)行查詢借閱表 Lend 中超期未歸還借書記錄的存儲(chǔ)過程 */①測試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