1、第6章 存儲過程、觸發(fā)器及游標,1.存儲過程 2.觸發(fā)器 3.游標,6.1存儲過程,定義 存儲過程是一組為了完成特定功能的SQL語句的集合，它經(jīng)編譯后存儲在數(shù)據(jù)庫中，用戶通過指定的調(diào)用方法執(zhí)行之。存儲過程具有名稱，參數(shù)及返回值，并且可以嵌套調(diào)用。,6.1.1 存儲過程概述,存儲過程分類 系統(tǒng)存儲過程 擴展存儲過程 用戶自定義存儲過程 存儲過程的優(yōu)點 快速執(zhí)行 安全性好 訪問統(tǒng)一 命名代碼，允許延遲綁定 減少網(wǎng)絡通信流量,6.1.1 存儲過程概述,存儲過程與函數(shù)的區(qū)別 存儲過程是預編譯的，執(zhí)行效率比函數(shù)高。 存儲過程可以不返回任何值，也可以返回多個輸出變量，但函數(shù)有且必須有一個返回值。 存儲過程

2、必須單獨執(zhí)行，而函數(shù)可以嵌入到表達式中，使用更靈活。 存儲過程主要是對邏輯處理的應用或解決，函數(shù)主要是一種功能應用。,6.1.2 創(chuàng)建存儲過程,1在SQL Server Management Studio中創(chuàng)建存儲過程,圖6-1 創(chuàng)建存儲過程,1.打開SQL Server Management Studio，在“對象資源管理器”中，展開“數(shù)據(jù)庫”目錄，選擇“TSG”數(shù)據(jù)庫，在選擇“可編程性|存儲過程”節(jié)點（如圖6-1）。右擊該節(jié)點，在彈出快捷菜單中選擇“新建存儲過程”命令，系統(tǒng)將打開代碼編輯器，并按照存儲過程的格式顯示編碼模板。 2.在代碼編輯器中，用戶根據(jù)需要更改存儲過程名稱，添加修改參數(shù)及

3、存儲過程的代碼段，完成存儲過程的編寫之后，單擊“執(zhí)行”按鈕，如果代碼有錯誤，會在下面消息欄中顯示出錯信息及所在行等信息，提示用戶進行修改，在出現(xiàn)“命令已成功完成”提示后，即完成創(chuàng)建。,6.1.2 創(chuàng)建存儲過程,2.使用CREATE PROCEDURE語句創(chuàng)建存儲過程 procedure_name：存儲過程的名稱 parameter：存儲過程中的參數(shù) data_type：參數(shù)的數(shù)據(jù)類型 Default：參數(shù)的默認值 OUTPUT：指示該參數(shù)是輸出參數(shù) READONLY：指示該參數(shù)是只讀的 ENCRYPTION：指示加密存儲 sql_statement:包含在過程中的一個或多個 T-SQL 語句,

4、CREATE PROCEDURE procedure_name parameter data_type=default OUT|OUTPUTREADONLY,n WITHENCRYPTION,n AS ;n ;,6.1.2 創(chuàng)建存儲過程,【例6-1】 以TSG數(shù)據(jù)庫為當前數(shù)據(jù)庫,創(chuàng)建存儲過程，查詢目前已經(jīng)外借的圖書的讀者證號，書名和借出時間。 CREATE PROCEDURE usp_Lend_Info AS SELECT L.PatronID,B.Title,L.LendTime FROM Lend L JOIN Book B ON B.CallNo=L.CallNo AND L.Retur

5、nTime IS NULL,顯示存儲過程代碼,通過檢索數(shù)據(jù)庫的系統(tǒng)表sysobjects以及syscomments，查看存儲過程的代碼 SELECT text FROM syscomments where id IN (SELECT id FROM sysobjects where name =usp_Lend_Info) 使用系統(tǒng)存儲過程sp_helptext 來顯示代碼 sp_helptext usp_Lend_Info * 如果在存儲過程定義中使用了ENCRYPTION指示符則無法顯示代碼。,檢查存儲過程是否重名,使用 sysobjects 表查詢法 IF NOT EXISTS (SEL

6、ECT name FROM sysobjects WHERE name =procname AND type=P) CREATE PROCEDURE procname 使用OBJECT_ID函數(shù) IF OBJECT_ID(storename,P) IS NULL CREATE PROCEDURE storename,6.1.3 執(zhí)行存儲過程,使用T-SQL的 EXECUTE 語句執(zhí)行存儲過程。 EXECUTEreturn_status= procedure_name parameter=value|variableOUTPUT ,n WITH RECOMPILE; return_status：

7、保存存儲過程的返回狀態(tài)。 procedure_name：是要調(diào)用的存儲過程名稱。 value：傳遞給存儲過程的參數(shù)值。可以按名稱調(diào)用，也可以按在模塊中定義的順序提供。 variable：是用來存儲輸入?yún)?shù)或輸出參數(shù)的變量。 OUTPUT：指定存儲過程將值送入輸出參數(shù)。 WITH RECOMPILE：執(zhí)行該存儲過程時強制重新編譯。,6.1.4 修改存儲過程,在 SQL Server Management Studio中修改存儲過程 使用ALTER PROCEDURE語句修改存儲過程 ALTER PROC|PROCEDURE procedure_name parameter data_type =

8、defaultOUTPUT,n WITHENCRYPTIONRECOMPILE AS n 其參數(shù)及保留字含義與CREATE PROCEDURE相同。,6.1.4 修改存儲過程,【例6-2】 修改存儲過程usp_Lend_Info，將查詢修改為目前已經(jīng)外借的圖書的讀者證號，讀者姓名、書名和借出時間四個字段。 ALTER PROCEDURE usp_Lend_Info AS SELECT L.PatronID,P.Name,B.Title,L.LendTime FROM Lend AS L JOIN Book AS B ON B.CallNo = L.CallNo AND L.Returntime

9、 IS NULL JOIN Patron AS P ON L.PatronID = P.PatronID,6.1.4 修改存儲過程,【例6-3】 修改存儲過程usp_Lend_Info，查詢目前已經(jīng)外借的圖書的讀者證號，讀者姓名、書名和借出時間，以加密方式存儲。 ALTER PROCEDURE usp_Lend_Info WITH ENCRYPTION AS SELECT L.PatronID,P.Name,B.Title,L.LendTime FROM Lend AS L JOIN Book AS B ON B.CallNo = L.CallNo AND L.Returntime IS NU

10、LL JOIN Patron AS P ON L.PatronID = P.PatronID,加密方式存儲存儲過程要注意的問題,（1）如果存儲過程以加密方式存儲，無法通過系統(tǒng)表查詢，也不能用系統(tǒng)存儲過程sp_helptext來查看，如在此情況下執(zhí)行sp_helptext usp_Lend_Info，會輸出“對象sp_Lend_Info的文本已加密”信息提示。 （2）如果存儲過程已加密方式存儲，在單擊修改存儲過程菜單后，會顯示“數(shù)據(jù)不可訪問，無法編寫其腳本”提示，不允許修改，所以在使用加密方式前請保存好存儲源代碼。,6.1.5 刪除存儲過程,1.在SQL Server Management St

11、udio中刪除存儲過程 2.使用DROP PROCEDURE 語句刪除存儲過程 DROP PROCEDURE procedure_name 常用OBJECT_ID 函數(shù)檢測存儲過程存在后刪除 IF OBJECT_ID(proceduere_name,P) IS NOT NULL DROP PROCEDURE proceduere_name,6.1.6 存儲過程的參數(shù)及返回值,存儲過程的參數(shù) 輸入?yún)?shù)：通過輸入?yún)?shù)，調(diào)用程序可以將數(shù)據(jù)傳送到存儲過程中供存儲過程使用，輸入?yún)?shù)需要定義變量名及變量類型也可以根據(jù)需要設定其默認值，輸入?yún)?shù)既可以將它們的值設置為常量，也可以使用變量的值。 輸出參數(shù)：允許

12、存儲過程將數(shù)據(jù)或者游標變量傳回給調(diào)用程序，輸出參數(shù)使用OUTPUT關鍵字聲明。 參數(shù)傳遞 （1）按參數(shù)位置傳遞 （2）按參數(shù)名字傳遞 * 參數(shù)執(zhí)行可以由位置標識，也可以由名字標識，如果以位置標識，執(zhí)行時按照參數(shù)的順序依次填入；如果以名字傳遞參數(shù)，則參數(shù)的順序是任意的。,6.1.6 存儲過程的參數(shù)及返回值,【例6-4】創(chuàng)建帶參數(shù)的存儲過程，查詢某個讀者的借書歷史信息。 CREATE PROCEDURE usp_Query_LendHistByPatronID PatronID VARCHAR(20) AS BEGIN SET NOCOUNT ON; SELECT * FROM Lend WHER

13、E PatronID = PatronID END 存儲過程usp_Query_LendHistByPatronID 以PatronID作為參數(shù)，假如要查詢讀者證號為“T0101”讀者的借書歷史，可以通過以下兩種方式調(diào)用。,6.1.6 存儲過程的參數(shù)及返回值,（1）使用常量調(diào)用 EXEC usp_Query_LendHistByPatronID T0101或 EXEC usp_Query_LendHistByPatronID PatronID =T0101 （2）使用變量調(diào)用 -聲明變量類型 DECLARE InputPatronID VARCHAR（20） -給變量賦值 SELECT Inp

14、utPatronID =T0101 -執(zhí)行 EXEC usp_Query_LendHistByPatronID InputPatronID,6.1.6 存儲過程的參數(shù)及返回值,【例6-5】創(chuàng)建多個參數(shù)存儲過程，根據(jù)索書號、書名和作者查詢圖書信息。 CREATE PROCEDURE usp_Query_BookInfo CallNo VARCHAR（20）=%, Title VARCHAR（50）=%, Author VARCHAR（10）=% AS BEGIN SET NOCOUNT ON; IF CallNo % SELECT CallNo = CallNo +% IF Title % SE

15、LECT Title = Title +% IF Author % SELECT Author = Author +% SELECT * FROM Book WHERE CallNo LIKE CallNo AND Title LIKE Title AND Author LIKE Author END,6.1.6 存儲過程的參數(shù)及返回值,在本例中，如查詢作者為姓周的圖書信息，可以通過下列方法調(diào)用，未賦值的參數(shù)會啟用默認值。 （1）按參數(shù)位置傳遞 EXEC usp_Query_BookInfo ,周 （2）按參數(shù)名字傳遞 EXEC usp_Query_BookInfo Author=周 按名字傳

16、遞參數(shù)比按位置具有更大的靈活性，但是按位置傳遞參數(shù)速度更快。,6.1.6 存儲過程的參數(shù)及返回值,【例6-6】創(chuàng)建存儲過程用于向Lend表插入借書記錄信息。 CREATE PROCEDURE usp_CheckIn CallNo VARCHAR(20), PatronID VARCHAR (20), LendTime SMALLDATETIME AS BEGIN SET NOCOUNT ON IF NOT EXISTS( SELECT * FROM Lend WHERE CallNo=CallNo AND PatronID=PatronID AND LendTime=LendTime) INS

17、ERT INTO Lend( CallNo,PatronID,LendTime) VALUES (CallNo ,PatronID ,LendTime) END,6.1.6 存儲過程的參數(shù)及返回值,【例6-7】創(chuàng)建存儲過程，通過輸入索取號參數(shù)在Book表中查找對應的書名并通過參數(shù)輸出。 CREATE PROCEDURE usp_GetBookNameByCallNo CallNo VARCHAR（20）=NULL, Title VARCHAR（50） OUTPUT AS BEGIN SET NOCOUNT ON; SELECT Title=Title FROM Book WHERE CallN

18、o=CallNo END 執(zhí)行本存儲過程的代碼 DECLARE Title VARCHAR(50) EXEC usp_GetBookNameByCallNo F121/L612,Title OUTPUT SELECT Title,6.1.6 存儲過程的參數(shù)及返回值,【例6-8】創(chuàng)建存儲過程，通過輸入讀者證號，輸出該讀者的姓名，讀者部門及讀者類別。 CREATE PROCEDURE usp_Get_Patron_Info PatronID VARCHAR(20), Name VARCHAR(30) OUTPUT, Department VARCHAR(40) OUTPUT, Type VARCH

19、AR(20) OUTPUT AS SELECT Name=Name,Department=department,Type =Type FROM Patron WHERE PatronID=PatronID,6.1.6 存儲過程的參數(shù)及返回值,調(diào)用該存儲過程，查詢讀者證號為“T0101”讀者的相關信息。 DECLARE Name VARCHAR(30) DECLARE Department VARCHAR(40) DECLARE Type VARCHAR(20) EXECUTE usp_Get_Patron_Info T0101,Name OUTPUT, Department OUTPUT,Ty

20、pe OUTPUT SELECT Name,Department,Type -顯示執(zhí)行結(jié)果,6.1.6 存儲過程的參數(shù)及返回值,存儲過程的返回值 使用 RETURN 語句指定存儲過程的返回代碼。 如果返回值在-1到-99之間，表示沒有成功執(zhí)行，可以通過判斷返回值來進行相應的處理。 可以用RETURN語句將大于0或者小于-99的整數(shù)作為自定義返回值，來表示不同的執(zhí)行結(jié)果。 典型代碼 DECLARE result INT EXECUTE result=my_pro,6.1.6 存儲過程的參數(shù)及返回值,【例6-9】創(chuàng)建存儲過程，根據(jù)讀者證號獲取已經(jīng)還回圖書的冊數(shù)，并使用自定義返回值標識執(zhí)行狀態(tài)。自定

21、義返回值的含義如下： 0 成功執(zhí)行。 1 未指定所需參數(shù)值。 2 指定參數(shù)值無效。 3 獲取借閱歷史數(shù)據(jù)時出錯。 代碼如下 CREATE PROCEDURE usp_Get_ReturnedItemCount PatronID VARCHAR(20) = NULL, COUNT INT OUTPUT AS,BEGIN SET NOCOUNT ON; IF PatronID IS NULL RETURN (1) ELSE BEGIN - 確認有該讀者證號 IF (SELECT COUNT (*) FROM Patron WHERE PatronID=PatronID)= 0 RETURN (2)

22、 END SELECT COUNT= COUNT(*) FROM Lend WHERE PatronID = PatronID IF ERROR 0 RETURN (3) ELSE RETURN (0) END,執(zhí)行代碼 DECLARE PatronID VARCHAR(20),nCount INT,nRtn INT -聲明變量 SELECT PatronID =T0101 -給變量賦值 EXECUTE nRtn=usp_Get_ ReturnedItemCount PatronID, nCount OUTPUT; IF nRtn = 0-檢查返回值 BEGIN PRINT 執(zhí)行成功！ PRI

23、NT 您已經(jīng)歸還 + CONVERT（VARCHAR（10）,nCount）+冊圖書！ END ELSE IF nRtn = 1 PRINT 必須輸入讀者證號. ELSE IF nRtn = 2 PRINT 無此讀者. ELSE IF nRtn = 3 PRINT 獲取數(shù)據(jù)出錯. ELSE PRINT 其他錯誤,6.2 觸發(fā)器,觸發(fā)器是一種特殊的存儲過程，當在指定的數(shù)據(jù)表中對數(shù)據(jù)進行插入、修改以及刪除操作時，會自動執(zhí)行對應的觸發(fā)器代碼。觸發(fā)器為數(shù)據(jù)庫提供了有效的監(jiān)控和處理機制，確保數(shù)據(jù)和業(yè)務的完整性。,6.2.1觸發(fā)器概述,1觸發(fā)器分類 1）按照觸發(fā)事件分類 （1）DML觸發(fā)器 （2）DDL

24、觸發(fā)器 （3）登錄觸發(fā)器。 2）按照觸發(fā)執(zhí)行方式分類 （1）AFTER觸發(fā)器 （2）INSTEAD OF 觸發(fā)器 3）DML觸發(fā)器 （1）INSERT觸發(fā)器 （2）DELETE觸發(fā)器 （3）UPDATE觸發(fā)器,6.2.1觸發(fā)器概述,2觸發(fā)器的優(yōu)點及局限性 1）觸發(fā)器的優(yōu)點 （1）強化了約束的功能 （2）可以跟蹤數(shù)據(jù)變化 （3）支持級聯(lián)運行 （4）可以調(diào)用存儲過程 2）觸發(fā)器的局限性 （1）觸發(fā)器性能通常比較低 （2）不恰當?shù)氖褂糜|發(fā)器容易造成數(shù)據(jù)庫維護困難。,6.2.2 創(chuàng)建觸發(fā)器,1.使用對象資源管理器創(chuàng)建觸發(fā)器 2.使用CREATE TRIGGER語句創(chuàng)建觸發(fā)器,CREATE TRIGGE

25、R trigger_name ON table_name|view_name WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT, UPDATE, DELETE ASsql_statement;,n,圖6-2創(chuàng)建觸發(fā)器,6.2.2 創(chuàng)建觸發(fā)器,解釋 trigger_name ：觸發(fā)器的名稱。 table_name | view_name：對其執(zhí)行 DML 觸發(fā)器的表或視圖 FOR | AFTER：FOR 或AFTER 指定 DML 觸發(fā)器僅在觸發(fā) SQL 語句中指定的所有操作都已成功執(zhí)行時才被觸發(fā)。 INSTEAD OF：指定執(zhí)行 DML 觸發(fā)器操作而不是執(zhí)

26、行原 SQL 語句 DELETE , INSERT , UPDATE :指定觸發(fā)條件數(shù)據(jù)修改語句 sql_statement：觸發(fā)條件和操作的SQL語句集合。,6.2.2 創(chuàng)建觸發(fā)器,【例6-10】在Lend表上創(chuàng)建觸發(fā)器，維護Book表的AvailableNumber列的一致性。 CREATE TRIGGER Tri_Lend_IUD ON Lend AFTER INSERT,DELETE,UPDATE AS BEGIN UPDATE Book SET availableNumber = number- (SELECT COUNT(*) FROM Lend WHERE Book.CallNo

27、 = Lend.CallNo AND returntime IS NULL) WHERE (Book.CallNo IN(SELECT CallNo FROM deleted) OR Book.CallNo IN (SELECT CallNo FROM inserted) END,6.2.2 創(chuàng)建觸發(fā)器,【例6-11】在Lend表上創(chuàng)建DELETE觸發(fā)器，實現(xiàn)如下功能，如果有圖書正在借出，則不允許刪除。 CREATE TRIGGER tri_Lend_D ON Lend AFTER DELETE AS BEGIN SET NOCOUNT ON; IF EXISTS (SELECT * FROM

28、 deleted WHERE ReturnTime IS Null) BEGIN PRINT 有圖書有被借出，不能刪除! ROLLBACK TRANSACTION -回滾事務，撤銷該刪除操作 END END,6.2.2 創(chuàng)建觸發(fā)器,【例6-12】在Book表上創(chuàng)建UPDATE觸發(fā)器，判斷如果修改了書名字段內(nèi)容，把書名原來的內(nèi)容及變更時間記錄在更新日志表的內(nèi)容及更新時間字段中。 首先創(chuàng)建更新日志表： CREATE TABLE UpDateLog ( ID INT IDENTITY(1,1) NOT NULL, -從1開始增量為1的自動增長整數(shù) Content NCHAR (100) NULL,

29、Upddate DATETIME NULL) 然后在Book表上創(chuàng)建觸發(fā)器： CREATE TRIGGER tri_Book_U_Fld_CallNO ON Book AFTER UPDATE AS IF UPDATE(Title) INSERT INTO UpdateLog (Content,Upddate) SELECT Title, Getdate() FROM deleted,GetDate()是一個返回當前日期時間的函數(shù)，UPDTE()函數(shù)用來判斷觸發(fā)器中某個列內(nèi)容是否被更改，參數(shù)為列名，如果該列內(nèi)容有更改則該函數(shù)返回值為真，在觸發(fā)器中，合理使用該函數(shù)可以僅對內(nèi)容變化的列進行處理從而

30、提高數(shù)據(jù)處理效率。,6.2.2 創(chuàng)建觸發(fā)器,視圖的定義中，如果SELECT語句有導出列，則不能夠?qū)σ晥D更新操作，如果想通過視圖更新基表，可以用INSTEAD OF觸發(fā)器來實現(xiàn)。 【例6-13】在視圖上定義INSTEAD OF觸發(fā)器。 假設有一個反映讀者的年齡的視圖 CREATE VIEW v_Patron_Age(PatronID,Name,Gender,Age) AS SELECT PatronID,Name,Gender, DATEDIFF(YEAR,BirthDate,GETDATE() FROM Patron 在該視圖上建立一個更新的INSTEAD OF 觸發(fā)器,CREATE TRIG

31、GER tr_v_Patron_Age_U ON v_Patron_Age INSTEAD OF UPDATE AS BEGIN DECLARE PatronId VARCHAR(20) DECLARE Name VARCHAR(30) DECLARE Gender CHAR(2) DECLARE Age INT UPDATE Patron set Name =I.Name,Gender=I.Gender, BirthDate=CONVERT(DATETIME,CAST(YEAR(getdate()- I.Age AS CHAR(4) + RIGHT(CONVERT(CHAR(10),Birt

32、hDate,102),6),102) FROM inserted I WHERE I.PatronID = Patron.PatronID END,INSTEAD OF 觸發(fā)器用于替代觸發(fā)器引起的SQL 語句，當向v_Patron_Age視圖執(zhí)行修改語句UPDATE時，視圖的觸發(fā)器被觸發(fā)，此時Inserted表已經(jīng)有了要修改的數(shù)據(jù)，在觸發(fā)器中，根據(jù)修改后的年齡計算讀者的出生年份，再將原有出生日期的月、日部分組裝成新的出生日期，然后執(zhí)行修改基表Patron的語句，而激發(fā)該觸發(fā)器的原始語句UPDATE不會被繼續(xù)執(zhí)行。,6.2.3 修改觸發(fā)器,1. 使用SQL Server Management S

33、tudio 修改 2. 使用ALTER TRIGGER語句修改觸發(fā)器 ALTER TRIGGER的語法基本格式如下： ALTER TRIGGER trigger_name ONtable_name|view_nameWITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE AS sql_statement;,n 其選項和創(chuàng)建觸發(fā)器基本一致 可以使用sp_rename 系統(tǒng)存儲過程修改觸發(fā)器名稱，其語法格式為： sp_rename oldname,newname,6.2.3 修改觸發(fā)器,【例6-14】修改例6-11創(chuàng)建的觸發(fā)器，實現(xiàn)如下功

34、能，如果有圖書正在借出或者讀者類別為教師的不允許刪除。 ALTER TRIGGER tri_Lend_D ON Lend AFTER DELETE AS BEGIN SET NOCOUNT ON; IF EXISTS (SELECT * FROM deleted WHERE ReturnTime IS NULL) OR EXISTS (SELECT * FROM Patron JOIN deleted ON Patron.PatronID=deleted.PatronID AND Patron.Type=教師) BEGIN PRINT 有圖書有被借出或讀者類別為老師的數(shù)據(jù)，不能刪除! ROLL

35、BACK TRANSACTION END END,6.2.4 刪除觸發(fā)器,1. 使用SQL Server Management Studio 刪除 2. 使用DROP TRIGGER語句刪除 DROP TRIGGER語法基本格式如下： DROP TRIGGER trigger_name 當刪除觸發(fā)器所在的表時，會自動刪除與該表相關的觸發(fā)器。,6.2.5 禁止/激活/觸發(fā)器,禁用觸發(fā)器 禁用觸發(fā)器是該觸發(fā)器仍然作為對象存在當前數(shù)據(jù)庫中但不執(zhí)行。禁用觸發(fā)器語法為： DISABLE TRIGGER trigger_name| ALL ON object_name 激活觸發(fā)器 對于禁止狀態(tài)的觸發(fā)器，可

36、使用ENABLE TRIGGER 激活，其語法為： ENABLE TRIGGER trigger_name,n|ALL ON object_name 也可以在SQL Server Management Studio激活觸發(fā)器。,6.2.5 禁止/激活/觸發(fā)器,【例6-15】 將Lend表上的tri_Lend_IUD觸發(fā)器禁用。 DISABLE TRIGGER tri_Lend_IUD ON Lend 【例6-16】 將Lend表上的tri_Lend_IUD觸發(fā)器啟用。 ENABLE TRIGGER tri_Lend_IUD ON Lend,6.3 游標,關系數(shù)據(jù)庫中的操作會對整個行集起作用。由

37、于SELECT語句返回的行集包括滿足該語句WHERE子句中條件的所有行，這種由語句返回的完整行集稱為結(jié)果集。 應用程序，特別是交互式聯(lián)機應用程序，并不總能將整個結(jié)果集作為一個單元來有效地處理。這些應用程序需要一種機制以便每次處理一行或一部分行。游標（CURSOR）就是提供這種機制的對結(jié)果集的一種擴展。,6.3.1 游標概述,游標擴展結(jié)果處理的方式 在結(jié)果集對特定行進行定位。 從結(jié)果集的當前位置檢索數(shù)據(jù)行。 支持對結(jié)果集中當前位置進行數(shù)據(jù)修改操作。 為由其他用戶對顯示在結(jié)果集中的數(shù)據(jù)庫數(shù)據(jù)所做的更改提供不同級別的可見性支持。 支持在腳本、存儲過程以及觸發(fā)器中訪問結(jié)果集中的數(shù)據(jù)。,6.3.1 游標

38、概述,使用游標的步驟 聲明游標，并且定義該游標的特性，例如是否能夠更新游標中的行。 執(zhí)行 T-SQL 語句以填充游標。 從游標中檢索想要查看的行。從游標中檢索一行或一部分行的操作稱為提取。執(zhí)行一系列提取操作以便向前或向后檢索行的操作稱為滾動。 根據(jù)需要，對游標中當前位置的行執(zhí)行修改操作（更新或刪除）。 關閉游標。,6.3.2 創(chuàng)建游標,符合SQL92標準的語法聲明 DECLARE Cursor_nameINSENSITIVESCROLL CURSOR FOR SELECT_statement FORREAD ONLY|UPDATEOF column_name,n; Cursor_name：游標

39、的名稱。 INSENSITIVE：不敏感的游標不允許數(shù)據(jù)更改。 SCROLL：關鍵字指明游標可以在任意方向上滾動。 SELECT_statement：是定義游標結(jié)果集的 SELECT 語句。 READ ONLY：只讀屬性，禁止通過該游標進行更新。 UPDATE OF column_name ,.n：定義游標中可更新的列。,6.3.2 創(chuàng)建游標,符合T-SQL標準語法聲明 DECLARE Cursor_name CURSORLOCAL|GLOBAL FORWARD_ONLY|SCROLL STATIC|KEYSET|DYNAMIC|FAST_FORWARD READ_ONLY|SCROLL_LO

40、CKS|OPTIMISTIC TYPE_WARNING FOR SELECT_statement FOR UPDATE OF column_name ,n ; Cursor_name ：是所定義的 T-SQL 服務器游標的名稱。 LOCAL：指明游標是局部的，它只能在它所聲明的過程中使用。 GLOBAL：關鍵字使得游標對于整個連接全局可見。,FORWARD_ONLY：指定游標只能向前滾動。 STATIC：與SQL92標準的INSENSITIVE的游標是相同的。 KEYSET：指明選取的行的順序。 DYNAMIC：指明游標將反映所有對結(jié)果集的修改。 FAST_FORWARD：指定快速前向游標。

41、READ_ONLY：只讀 。 SCROLL_LOCKS：為了保證游標操作的成功，當將行讀入游標時 SQL Server 將鎖定這些行，以確保隨后可對它們進行修改。 OPTIMISTIC：樂觀方式，不鎖定基表數(shù)據(jù)行，如果行自讀入游標以來已得到更新，則通過游標進行的定位更新或定位刪除不一定成功。,TYPE_WARNING：指定將游標從所請求的類型隱式轉(zhuǎn)換為另一種類型時向客戶端發(fā)送警告消息。 SELECT_statement：是定義游標結(jié)果集的標準 SELECT 語句。 FOR UPDATE OF column_name ,.n：定義游標中可更新的列。,6.3.3 打開游標,打開游標的語法為： OP

42、EN GLOBAL Cursor_name|Cursor_variable_name 其中： GLOBAL ：指定 Cursor_name 是指全局游標。 Cursor_name：已聲明的游標的名稱。如果全局游標和局部游標都使用Cursor_name 作為其名稱，那么如果指定了 GLOBAL，則 Cursor_name 指的是全局游標；否則 Cursor_name 指的是局部游標。 Cursor_variable_name：游標變量的名稱，該變量引用一個游標。,6.3.4 讀取游標,可以使用FETCH語句檢索特定的行，實現(xiàn)游標的讀取。FETCH的語法基本結(jié)構(gòu)如下： FETCH NEXT|PRI

43、OR|FIRST|LAST|ABSOLUTEn|nvar|RELATIVEn|nvar FROM GLOBALCursor_name|Cursor_variable_name INTO variable_name ,n NEXT：緊跟當前行返回結(jié)果行，并且當前行遞增為返回行。如果FETCH NEXT為對游標的第一次提取操作，則返回結(jié)果集中的第一行。NEXT為默認的游標提取選項。 PRIOR：返回緊鄰當前行前面的結(jié)果行。,FIRST：返回游標中的第一行并將其作為當前行。 LAST：返回游標中的最后一行并將其作為當前行。 ABSOLUTE n|nvar：絕對行定位 RELATIVE n|nvar：

44、相對行定位 GLOBAL：指定 Cursor_name 是指全局游標。 Cursor_name ：要從中進行提取的打開的游標的名稱 INTO variable_name ,.n：允許將提取操作的列數(shù)據(jù)放到局部變量中。,可利用全局變量fetch_status檢查最后一條FETCH語句狀態(tài)，fetch_status變量有三種值，其中0表示命令執(zhí)行成功，-1表示命令失敗或者行數(shù)據(jù)超出了結(jié)果集，-2表示所讀取的數(shù)據(jù)已經(jīng)不存在。每執(zhí)行一條FETCH語句之后，都應該檢查該變量，以確定上次執(zhí)行的FETCH語句操作是否成功。,6.3.5 關閉和刪除游標,CLOSE語句負責關閉游標,CLOSE語法結(jié)構(gòu)如下： C

45、LOSE GLOBAL Cursor_name|Cursor_variable_name 用DEALLOCATE命令釋放游標，相當于C語言的Free函數(shù)用來釋放內(nèi)存變量。刪除游標的命令語法格式如下： DEALLOCATE GLOBAL Cursor_name |Cursor_variable_name,在游標被關閉之后，仍然可以再用OPEN再次打開。,6.3.6 用游標處理數(shù)據(jù)的一般過程,使用游標的典型過程包括聲明、打開游標、通過FETCH逐行讀取數(shù)據(jù)并進行處理，使用完之后，用CLOSE語句關閉游標，再通過DEALLOCATE語句釋放游標的存儲空間。 【例6-17】使用游標，遍歷Patron表

46、，并輸出序號，PatronID和Name 首先聲明變量，包含游標返回的數(shù)據(jù)，為每個結(jié)果集列聲明一個變量。 DECLARE iNo INT DECLARE sPatronID VARCHAR（20） DECLARE sName VARCHAR（30） DECLARE cMyCURSOR CURSOR FORWARD_ONLY FOR SELECT PatronID, Name FROM Patron,OPEN cMyCURSOR-使用OPEN語句執(zhí)行SELECT語句并填充游標 -使用FETCH INTO語句提取單個行，并將列數(shù)據(jù)賦值到變量 -在此進行其他邏輯處理,一般用While循環(huán)進行遍歷。

47、SELECT iNo = 0 FETCH NEXT FROM cMyCURSOR INTO sPatronID, sName WHILE FETCH_STATUS=0 BEGIN SELECT iNo = iNo + 1 PRINT CAST(iNo AS CHAR(10)+ sPatronID+sName FETCH NEXT FROM cMyCURSOR INTO sPatronID,sName END CLOSE cMyCURSOR-關閉游標 DEALLOCATE cMyCURSOR-釋放資源,6.3.7 游標的應用,使用游標修改和刪除表數(shù)據(jù) 游標提供了將游標數(shù)據(jù)的變化反應到基表的定位修

48、改及刪除方法。如果游標在聲明的時候使用的FOR UPDFATE 選項，就可以用UPDATE或DELETE命令以WHERE CURRENT OF 關鍵字直接修改或刪除游標中的數(shù)據(jù)以便達到更新基表的目的。 定位修改游標數(shù)據(jù)的基本語法格式為： UPDATE table_name SET column_name=expression|default|NULL,n WHERE CURRENT OF cursor_name|cursor_varialbe_name 刪除游標數(shù)據(jù)的基本語法格式為： DELETE FROM table_name WHERE CURRENT OF cursor_name|cur

49、sor_varialbe_name,6.3.7 游標的應用,【例6-18】定義游標cur_Patron，通過該游標將讀者證號為S0120090103的讀者將其姓名由原來的王雨文修改為王雨雯，出生日期由原來的1990-04-29修改為1990-10-29。 DECLARE PatronID VARCHAR(20),Name varchar(30), DECLARE BirthDate SMALLDATETIME -使用FOR UPDATE 關鍵字聲明可更新游標，更改字段為Name,BirthDate DECLARE cur_Patron CURSOR FOR SELECT PatronID,Na

50、me,BirthDate FROM Patron FOR UPDATE OF Name, BirthDate OPEN cur_Patron FETCH NEXT FROM cur_Patron INTO PatronID,Name,BirthDate WHILE FETCH_STATUS=0 BEGIN IF PatronID =S0120090103) -找到對應記錄，執(zhí)行更新操作 UPDATE Patron set Name=王雨雯, BirthDate=CONVERT(datetime,1990-10-29) WHERE CURRENT OF cur_Patron FETCH NEXT FROM cur_Patron INTO PatronID,Name,BirthDate END CLOSE cur_Patron -