第6章 存儲過程、觸發(fā)器及游標

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

2、儲過程概述,存儲過程與函數(shù)的區(qū)別存儲過程是預編譯的，執(zhí)行效率比函數(shù)高。存儲過程可以不返回任何值，也可以返回多個輸出變量，但函數(shù)有且必須有一個返回值。存儲過程必須單獨執(zhí)行，而函數(shù)可以嵌入到表達式中，使用更靈活。存儲過程主要是對邏輯處理的應用或解決，函數(shù)主要是一種功能應用。,6.1.2 創(chuàng)建存儲過程,1．在SQL Server Management Studio中創(chuàng)建存儲過程,,圖6-1 創(chuàng)建存儲過程,1.打開SQL Server

3、Management Studio，在“對象資源管理器”中，展開“數(shù)據(jù)庫”目錄，選擇“TSG”數(shù)據(jù)庫，在選擇“可編程性|存儲過程”節(jié)點（如圖6-1）。右擊該節(jié)點，在彈出快捷菜單中選擇“新建存儲過程”命令，系統(tǒng)將打開代碼編輯器，并按照存儲過程的格式顯示編碼模板。2.在代碼編輯器中，用戶根據(jù)需要更改存儲過程名稱，添加修改參數(shù)及存儲過程的代碼段，完成存儲過程的編寫之后，單擊“執(zhí)行”按鈕，如果代碼有錯誤，會在下面消息欄中顯示出錯信息及所在行等

4、信息，提示用戶進行修改，在出現(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_s

5、tatement:包含在過程中的一個或多個 T-SQL 語句,CREATE PROC[EDURE] procedure_name[{@parameter data_type}[=default] [OUT|OUTPUT][READONLY][,…n ]][WITH[ENCRYPTION[,…n ]]AS {[;][…n ]}[;],6.1.2 創(chuàng)建存儲過程,【例6-1】 以TSG數(shù)據(jù)庫為當前數(shù)據(jù)庫,創(chuàng)建存儲過程，查詢目前已

6、經(jīng)外借的圖書的讀者證號，書名和借出時間。CREATE PROCEDURE usp_Lend_InfoASSELECT L.PatronID,B.Title,L.LendTimeFROM Lend L JOIN Book BON B.CallNo=L.CallNo AND L.ReturnTime IS NULL,顯示存儲過程代碼,通過檢索數(shù)據(jù)庫的系統(tǒng)表sysobjects以及syscomments，查看存儲過程的代碼SEL

7、ECT 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 表查

8、詢法IF NOT EXISTS (SELECT name FROM sysobjects WHERE name ='procname' AND type='P')CREATE PROCEDURE procname…使用OBJECT_ID函數(shù)IF OBJECT_ID('storename','P') IS NULL CREATE PROCEDURE storena

9、me…,6.1.3 執(zhí)行存儲過程,使用T-SQL的 EXECUTE 語句執(zhí)行存儲過程。[EXEC[UTE]][@return_status=] procedure_name [[@parameter=]{value|@variable[OUT[PUT]] [,…n ]][WITH RECOMPILE][;]@return_status：保存存儲過程的返回狀態(tài)。procedure_name：是要調用的存儲過程名稱。value：

10、傳遞給存儲過程的參數(shù)值?？梢园疵Q調用，也可以按在模塊中定義的順序提供。@variable：是用來存儲輸入?yún)?shù)或輸出參數(shù)的變量。OUTPUT：指定存儲過程將值送入輸出參數(shù)。WITH RECOMPILE：執(zhí)行該存儲過程時強制重新編譯。,6.1.4 修改存儲過程,在 SQL Server Management Studio中修改存儲過程使用ALTER PROCEDURE語句修改存儲過程ALTER {PROC|PROCEDURE}

11、procedure_name [{@parameter data_type} [=default][[OUT[PUT]][,…n]][WITH[ENCRYPTION][RECOMPILE]]AS {[…n]}其參數(shù)及保留字含義與CREATE PROCEDURE相同。,6.1.4 修改存儲過程,【例6-2】 修改存儲過程usp_Lend_Info，將查詢修改為目前已經(jīng)外借的圖書的讀者證號，讀者姓名、書名和借出時間四個字段。AL

12、TER PROCEDURE usp_Lend_InfoASSELECT L.PatronID,P.Name,B.Title,L.LendTimeFROM Lend AS L JOIN Book AS B ON B.CallNo = L.CallNo AND L.Returntime IS NULL JOIN Patron AS P ON L.PatronID = P.Patr

13、onID,6.1.4 修改存儲過程,【例6-3】 修改存儲過程usp_Lend_Info，查詢目前已經(jīng)外借的圖書的讀者證號，讀者姓名、書名和借出時間，以加密方式存儲。ALTER PROCEDURE usp_Lend_InfoWITH ENCRYPTIONASSELECT L.PatronID,P.Name,B.Title,L.LendTimeFROM Lend AS L JOIN Book AS B ON B.Cal

14、lNo = L.CallNo AND L.Returntime IS NULL JOIN Patron AS P ON L.PatronID = P.PatronID,加密方式存儲存儲過程要注意的問題,（1）如果存儲過程以加密方式存儲，無法通過系統(tǒng)表查詢，也不能用系統(tǒng)存儲過程sp_helptext來查看，如在此情況下執(zhí)行sp_helptext usp_Lend_Info，會輸出

15、“對象sp_Lend_Info的文本已加密”信息提示。（2）如果存儲過程已加密方式存儲，在單擊修改存儲過程菜單后，會顯示“數(shù)據(jù)不可訪問，無法編寫其腳本”提示，不允許修改，所以在使用加密方式前請保存好存儲源代碼。,6.1.5 刪除存儲過程,1.在SQL Server Management Studio中刪除存儲過程2.使用DROP PROCEDURE 語句刪除存儲過程DROP PROC[EDURE] procedure_name

16、常用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ù)，調用程序可以將數(shù)據(jù)傳送到存儲過程中供存儲過程使用，輸入?yún)?shù)需要定義變量名及變量類型也可以根據(jù)需要設定其默認值，輸入?yún)?/p>

17、數(shù)既可以將它們的值設置為常量，也可以使用變量的值。輸出參數(shù)：允許存儲過程將數(shù)據(jù)或者游標變量傳回給調用程序，輸出參數(shù)使用OUTPUT關鍵字聲明。參數(shù)傳遞（1）按參數(shù)位置傳遞（2）按參數(shù)名字傳遞* 參數(shù)執(zhí)行可以由位置標識，也可以由名字標識，如果以位置標識，執(zhí)行時按照參數(shù)的順序依次填入；如果以名字傳遞參數(shù)，則參數(shù)的順序是任意的。,6.1.6 存儲過程的參數(shù)及返回值,【例6-4】創(chuàng)建帶參數(shù)的存儲過程，查詢某個讀者的借書歷史信息。CR

18、EATE PROCEDURE usp_Query_LendHistByPatronID @PatronID VARCHAR(20) ASBEGINSET NOCOUNT ON;SELECT * FROM Lend WHERE PatronID = @PatronIDEND存儲過程usp_Query_LendHistByPatronID 以@PatronID作為參數(shù)，假如要查詢讀者證號為“T0101”讀者

19、的借書歷史，可以通過以下兩種方式調用。,6.1.6 存儲過程的參數(shù)及返回值,（1）使用常量調用EXEC usp_Query_LendHistByPatronID 'T0101'或EXEC usp_Query_LendHistByPatronID @PatronID ='T0101'（2）使用變量調用--聲明變量類型DECLARE @InputPatronID VARCHAR（20）--給變

20、量賦值SELECT @InputPatronID ='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）='%'

21、, @Title VARCHAR（50）='%',@Author VARCHAR（10）='%'ASBEGINSET NOCOUNT ON; IF @CallNo '%' SELECT @CallNo = @CallNo +'%' IF @Title '%' SELECT @Title = @Title +'%' I

22、F @Author '%' SELECT @Author = @Author +'%' SELECT * FROM Book WHERE CallNo LIKE @CallNo AND Title LIKE @Title AND Author LIKE @Author END,6.1.6 存儲過程的參數(shù)及返回值,在本例中，如查詢作者為姓周的圖書信息，可以通過下列方法調用，未賦值的

23、參數(shù)會啟用默認值。（1）按參數(shù)位置傳遞EXEC usp_Query_BookInfo '','','周' （2）按參數(shù)名字傳遞EXEC usp_Query_BookInfo @Author='周'按名字傳遞參數(shù)比按位置具有更大的靈活性，但是按位置傳遞參數(shù)速度更快。,6.1.6 存儲過程的參數(shù)及返回值,【例6-6】創(chuàng)建存儲過程用于向Lend表插入借書記錄信息。CR

24、EATE PROCEDURE usp_CheckIn@CallNo VARCHAR(20),@PatronID VARCHAR (20),@LendTime SMALLDATETIMEASBEGIN SET NOCOUNT ON IF NOT EXISTS( SELECT * FROM Lend WHERE CallNo=@CallNo AND PatronID=@PatronID AND Lend

25、Time=@LendTime) INSERT 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 @Call

26、No VARCHAR（20）=NULL, @Title VARCHAR（50） OUTPUT ASBEGIN SET NOCOUNT ON; SELECT @Title=Title FROM Book WHERE CallNo=@CallNo END執(zhí)行本存儲過程的代碼DECLARE @Title VARCHAR(50)EXEC usp_GetBookNameByCallNo 'F121/L612&

27、#39;,@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,

28、@Type VARCHAR(20) OUTPUTAS SELECT @Name=Name,@Department=department,@Type =TypeFROM Patron WHERE PatronID=@PatronID,6.1.6 存儲過程的參數(shù)及返回值,調用該存儲過程，查詢讀者證號為“T0101”讀者的相關信息。DECLARE @Name VARCHAR(30)DECLARE @Department VARCHA

29、R(40)DECLARE @Type VARCHAR(20)EXECUTE usp_Get_Patron_Info 'T0101',@Name OUTPUT, @Department OUTPUT,@Type OUTPUTSELECT @Name,@Department,@Type --顯示執(zhí)行結果,6.1.6 存儲過程的參數(shù)及返回值,存儲過程的返回值使用 RETURN 語句指

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

31、的含義如下： 0 成功執(zhí)行。 1 未指定所需參數(shù)值。 2 指定參數(shù)值無效。 3 獲取借閱歷史數(shù)據(jù)時出錯。 代碼如下CREATE PROCEDURE usp_Get_ReturnedItemCount @PatronID VARCHAR(20) = NULL, @COUNT INT OUTPUTAS,,BEGIN SET NOCOUNT ON; IF @PatronID IS NULL R

32、ETURN (1) ELSE BEGIN -- 確認有該讀者證號 IF (SELECT COUNT (*) FROM Patron WHERE PatronID=@PatronID)= 0RETURN (2) END SELECT @COUNT= COUNT(*) FROM Lend WHERE PatronID = @PatronID IF @@ERROR

33、 0RETURN (3) ELSERETURN (0)END,,執(zhí)行代碼DECLARE @PatronID VARCHAR(20),@nCount INT,@nRtn INT --聲明變量SELECT @PatronID ='T0101' --給變量賦值EXECUTE @nRtn=usp_Get_ ReturnedItemCount @PatronID,

34、 @nCount OUTPUT;IF @nRtn = 0--檢查返回值BEGIN PRINT '執(zhí)行成功！' PRINT '您已經(jīng)歸還' + CONVERT（VARCHAR（10）,@nCount）+'冊圖書！'ENDELSE IF @nRtn = 1 PRINT '必須輸入讀者證號.'ELSE IF @

35、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

36、.2.1觸發(fā)器概述,1．觸發(fā)器分類1）按照觸發(fā)事件分類（1）DML觸發(fā)器（2）DDL 觸發(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

37、）可以調用存儲過程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 TRIGGER trigger_nameON {table_name|view_name}[WITH ENCRYPTION]{FOR|AFTER|INSTEAD OF}{[INSERT][,

38、][UPDATE][,][DELETE]} AS{sql_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í)行

39、DML 觸發(fā)器操作而不是執(zhí)行原 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 A

40、FTER INSERT,DELETE,UPDATEAS BEGIN UPDATE Book SET availableNumber = number- (SELECT COUNT(*) FROM Lend WHERE Book.CallNo = Lend.CallNo AND returntime IS NULL) WHERE (Book.CallNo IN(SELECT Ca

41、llNo 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 DELETEASBEGIN

42、 SET NOCOUNT ON; IF EXISTS (SELECT * FROM deleted WHERE ReturnTime IS Null) BEGIN PRINT '有圖書有被借出，不能刪除!' ROLLBACK TRANSACTION --回滾事務，撤銷該刪除操作 ENDEND,6.2.2 創(chuàng)建觸發(fā)器,【例6-12】在Book表

43、上創(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,Upddate DATETIME NULL) 然后在Book表上創(chuàng)建觸發(fā)器：CR

44、EATE TRIGGER tri_Book_U_Fld_CallNOON Book AFTER UPDATEAS IF UPDATE(Title) INSERT INTO UpdateLog (Content,Upddate) SELECT Title, Getdate() FROM deleted,GetDate()是一個返回當前日期時間的函數(shù)，UPDTE()函數(shù)用來判斷觸發(fā)器中某個列內(nèi)容是否被更改，參數(shù)為列名，

45、如果該列內(nèi)容有更改則該函數(shù)返回值為真，在觸發(fā)器中，合理使用該函數(shù)可以僅對內(nèi)容變化的列進行處理從而提高數(shù)據(jù)處理效率。,6.2.2 創(chuàng)建觸發(fā)器,視圖的定義中，如果SELECT語句有導出列，則不能夠對視圖更新操作，如果想通過視圖更新基表，可以用INSTEAD OF觸發(fā)器來實現(xiàn)。 【例6-13】在視圖上定義INSTEAD OF觸發(fā)器。假設有一個反映讀者的年齡的視圖 CREATE VIEW v_Patron_Age(PatronID,

46、Name,Gender,Age) AS SELECT PatronID,Name,Gender, DATEDIFF(YEAR,BirthDate,GETDATE()) FROM Patron在該視圖上建立一個更新的INSTEAD OF 觸發(fā)器,,CREATE TRIGGER tr_v_Patron_Age_U ON v_Patron_AgeINSTEAD OF UPDATEAS

47、BEGINDECLARE @PatronId VARCHAR(20)DECLARE @Name VARCHAR(30)DECLARE @Gender CHAR(2)DECLARE @Age INTUPDATE Patron set Name =I.Name,Gender=I.Gender, BirthDate=CONVERT(DATETIME,CAST(YEAR(getdate()-

48、 I.Age AS CHAR(4)) + RIGHT(CONVERT(CHAR(10),BirthDate,102),6),102)FROM inserted I WHERE I.PatronID = Patron.PatronIDEND,INSTEAD OF 觸發(fā)器用于替代觸發(fā)器引起的SQL 語句，當向v_Patron_Age視圖執(zhí)行修改語句UPDATE時，視圖的觸發(fā)器被觸發(fā)，此時Inserted

49、表已經(jīng)有了要修改的數(shù)據(jù)，在觸發(fā)器中，根據(jù)修改后的年齡計算讀者的出生年份，再將原有出生日期的月、日部分組裝成新的出生日期，然后執(zhí)行修改基表Patron的語句，而激發(fā)該觸發(fā)器的原始語句UPDATE不會被繼續(xù)執(zhí)行。,6.2.3 修改觸發(fā)器,1. 使用SQL Server Management Studio 修改2. 使用ALTER TRIGGER語句修改觸發(fā)器ALTER TRIGGER的語法基本格式如下： ALTER TRIGGER t

50、rigger_name ON{table_name|view_name}[WITH ENCRYPTION] {FOR|AFTER|INSTEAD OF} {[INSERT][,][UPDATE][,][DELETE]} AS sql_statement[;][,…n]}其選項和創(chuàng)建觸發(fā)器基本一致 可以使用sp_rename 系統(tǒng)存儲過程修改觸發(fā)器名稱，其語法格式為：sp_rename oldname,newname

51、,6.2.3 修改觸發(fā)器,【例6-14】修改例6-11創(chuàng)建的觸發(fā)器，實現(xiàn)如下功能，如果有圖書正在借出或者讀者類別為教師的不允許刪除。ALTER TRIGGER tri_Lend_D ON Lend AFTER DELETEASBEGIN SET NOCOUNT ON; IF EXISTS (SELECT * FROM deleted WHERE ReturnTime IS NULL

52、) OR EXISTS (SELECT * FROM Patron JOIN deleted ON Patron.PatronID=deleted.PatronID AND Patron.Type='教師') BEGIN PRINT '有圖書有被借出或讀者類別為老師的數(shù)據(jù)，不能刪除!' ROLL

53、BACK TRANSACTION ENDEND,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ù)庫中

54、但不執(zhí)行。禁用觸發(fā)器語法為： DISABLE TRIGGER {trigger_name| ALL} ON {object_name}激活觸發(fā)器對于禁止狀態(tài)的觸發(fā)器，可使用ENABLE TRIGGER 激活，其語法為： ENABLE TRIGGER {trigger_name[,…n]|ALL} ON {object_name}也可以在SQL Server Management Studio激活觸發(fā)器。,6.2.5 禁止/

55、激活/觸發(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ù)庫中的操作會對整個行集起作用。由于SELECT語句返回的行集包括滿足該語句WHERE子句中條件的所有行，這種由語

56、句返回的完整行集稱為結果集。 應用程序，特別是交互式聯(lián)機應用程序，并不總能將整個結果集作為一個單元來有效地處理。這些應用程序需要一種機制以便每次處理一行或一部分行。游標（CURSOR）就是提供這種機制的對結果集的一種擴展。,6.3.1 游標概述,游標擴展結果處理的方式在結果集對特定行進行定位。從結果集的當前位置檢索數(shù)據(jù)行。支持對結果集中當前位置進行數(shù)據(jù)修改操作。為由其他用戶對顯示在結果集中的數(shù)據(jù)庫數(shù)據(jù)所

57、做的更改提供不同級別的可見性支持。支持在腳本、存儲過程以及觸發(fā)器中訪問結果集中的數(shù)據(jù)。,6.3.1 游標概述,使用游標的步驟聲明游標，并且定義該游標的特性，例如是否能夠更新游標中的行。執(zhí)行 T-SQL 語句以填充游標。從游標中檢索想要查看的行。從游標中檢索一行或一部分行的操作稱為提取。執(zhí)行一系列提取操作以便向前或向后檢索行的操作稱為滾動。根據(jù)需要，對游標中當前位置的行執(zhí)行修改操作（更新或刪除）。關閉游標。,6.3.2 創(chuàng)建游

58、標,符合SQL92標準的語法聲明DECLARE Cursor_name[INSENSITIVE][SCROLL] CURSORFOR SELECT_statement [FOR{READ ONLY|UPDATE[OF column_name[,…n]]}][;]Cursor_name：游標的名稱。INSENSITIVE：不敏感的游標不允許數(shù)據(jù)更改。SCROLL：關鍵字指明游標可以在任意方向上滾動。SELECT_state

59、ment：是定義游標結果集的 SELECT 語句。READ ONLY：只讀屬性，禁止通過該游標進行更新。UPDATE [OF column_name [,...n]]：定義游標中可更新的列。,6.3.2 創(chuàng)建游標,符合T-SQL標準語法聲明DECLARE Cursor_name CURSOR[LOCAL|GLOBAL] [FORWARD_ONLY|SCROLL] [STATIC|KEYSET|DYNAMIC|FAST_FORW

60、ARD] [READ_ONLY|SCROLL_LOCKS|OPTIMISTIC] [TYPE_WARNING] FOR SELECT_statement [FOR UPDATE [OF column_name [,…n ]]][;]Cursor_name ：是所定義的 T-SQL 服務器游標的名稱。LOCAL：指明游標是局部的，它只能在它所聲明的過程中使用。GLOBAL：關鍵字使得游標對于整個連接全局可見。,,FORWA

61、RD_ONLY：指定游標只能向前滾動。STATIC：與SQL92標準的INSENSITIVE的游標是相同的。KEYSET：指明選取的行的順序。DYNAMIC：指明游標將反映所有對結果集的修改。FAST_FORWARD：指定快速前向游標。READ_ONLY：只讀 。SCROLL_LOCKS：為了保證游標操作的成功，當將行讀入游標時 SQL Server 將鎖定這些行，以確保隨后可對它們進行修改。OPTIMISTIC：樂觀方式

62、，不鎖定基表數(shù)據(jù)行，如果行自讀入游標以來已得到更新，則通過游標進行的定位更新或定位刪除不一定成功。,,TYPE_WARNING：指定將游標從所請求的類型隱式轉換為另一種類型時向客戶端發(fā)送警告消息。SELECT_statement：是定義游標結果集的標準 SELECT 語句。FOR UPDATE [OF column_name [,...n]]：定義游標中可更新的列。,6.3.3 打開游標,打開游標的語法為：OPEN {{[GLOB

63、AL] Cursor_name}|Cursor_variable_name}其中：GLOBAL ：指定 Cursor_name 是指全局游標。Cursor_name：已聲明的游標的名稱。如果全局游標和局部游標都使用Cursor_name 作為其名稱，那么如果指定了 GLOBAL，則 Cursor_name 指的是全局游標；否則 Cursor_name 指的是局部游標。Cursor_variable_name：游標變量的名稱，該變

64、量引用一個游標。,6.3.4 讀取游標,可以使用FETCH語句檢索特定的行，實現(xiàn)游標的讀取。FETCH的語法基本結構如下：FETCH [[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]Cursor_name}|@Cursor_variable_name}[INTO @variable_name [,…n]]NEXT：緊跟當前行

65、返回結果行，并且當前行遞增為返回行。如果FETCH NEXT為對游標的第一次提取操作，則返回結果集中的第一行。NEXT為默認的游標提取選項。PRIOR：返回緊鄰當前行前面的結果行。,,FIRST：返回游標中的第一行并將其作為當前行。LAST：返回游標中的最后一行并將其作為當前行。ABSOLUTE {n|@nvar}：絕對行定位RELATIVE {n|@nvar}：相對行定位GLOBAL：指定 Cursor_name 是指全局游

66、標。Cursor_name ：要從中進行提取的打開的游標的名稱INTO @variable_name[ ,...n]：允許將提取操作的列數(shù)據(jù)放到局部變量中。,可利用全局變量@@fetch_status檢查最后一條FETCH語句狀態(tài)，@@fetch_status變量有三種值，其中0表示命令執(zhí)行成功，-1表示命令失敗或者行數(shù)據(jù)超出了結果集，-2表示所讀取的數(shù)據(jù)已經(jīng)不存在。每執(zhí)行一條FETCH語句之后，都應該檢查該變量，以確定上次執(zhí)行的F

67、ETCH語句操作是否成功。,6.3.5 關閉和刪除游標,CLOSE語句負責關閉游標,CLOSE語法結構如下：CLOSE {{[GLOBAL] Cursor_name}|Cursor_variable_name}用DEALLOCATE命令釋放游標，相當于C語言的Free函數(shù)用來釋放內(nèi)存變量。刪除游標的命令語法格式如下：DEALLOCATE {{[GLOBAL] Cursor_name} |@Cursor_va

68、riable_name},在游標被關閉之后，仍然可以再用OPEN再次打開。,6.3.6 用游標處理數(shù)據(jù)的一般過程,使用游標的典型過程包括聲明、打開游標、通過FETCH逐行讀取數(shù)據(jù)并進行處理，使用完之后，用CLOSE語句關閉游標，再通過DEALLOCATE語句釋放游標的存儲空間。 【例6-17】使用游標，遍歷Patron表，并輸出序號，PatronID和Name 首先聲明變量，包含游標返回的數(shù)據(jù)，為每個結果集列聲明一個變量。

69、DECLARE @iNo INTDECLARE @sPatronID VARCHAR（20）DECLARE @sName VARCHAR（30）DECLARE cMyCURSOR CURSOR FORWARD_ONLY FOR SELECT PatronID, Name FROM Patron,,OPEN cMyCURSOR--使用OPEN語句執(zhí)行SELECT語句并填充游標 --使用F

70、ETCH INTO語句提取單個行，并將列數(shù)據(jù)賦值到變量 --在此進行其他邏輯處理,一般用While循環(huán)進行遍歷。SELECT @iNo = 0FETCH NEXT FROM cMyCURSOR INTO @sPatronID, @sNameWHILE @@FETCH_STATUS=0BEGIN SELECT @iNo = @iNo + 1 PRINT CAST(@iNo AS CHAR(10))+ @sPatron