1、南昌工程學院計算機系第第8章章 游標和事務游標和事務報告報告 人：譚德坤人：譚德坤Email:8.1 游標( Cursor)8.2 事務(Transaction) 游標的概念 聲明游標 打開游標 數據處理 關閉游標 釋放游標 事務的概念 事務的模式 事務控制本章知識點 前面介紹的數據檢索方法可以得到數據庫中有關表的數據，但這些數據是作為一個結果集得到的，用戶可以把這個結果集保存到一個文件里，或生成一個新表以便于以后使用。這種查詢是非常重要的。但這種查詢形式有一個很大的缺點，它不能對結果集中每一行的數據進行處理。使用游標可以實現對查詢結果集中的數據逐行處理。為何引入游標？為何引入游標？8.1.1

2、 游標的概念游標的概念 游標是SQL Server提供的一種機制，它能夠對一個結果集進行逐行處理，其工作方式類似于指針，可以指向結果集中的任意位置以此對指定位置的數據進行處理。可以把結果集中的數據放在數組、應用程序中或其它地方。Transact-SQL游標遵循ANSI-92標準。使用游標的優點使用游標的優點 從游標定義可以得知游標具有如下的優點，正是這些優點使得游標在實際編程應用中具有重要作用。 允許程序對由查詢語句SELECT返回的行集合中的每一行數據執行相同或不同的操作，而不是對整個行集合執行同一個操作 提供對基于游標位置的表中的行進行刪除和更新的能力 游標實際上作為面向集合的數據庫管理系

3、統（RDBMS）和面向行的程序設計之間的橋梁，使這兩種處理方式通過游標溝通起來使用游標的步驟 使用游標要遵循聲明游標打開游標讀取游標關閉游標刪除游標的順序。 5步！8.1.2 聲明游標declare 游標名游標名 cursor local | global forward_only | scroll static | keyset | dynamic | fast_forward read_only | scroll_locks | optimisticfor select語句語句 for update of 列名列名 ,.n參數說明： 游標名：游標命名必須符合標識符規則，不能超過30 個字符

4、。 select語句：定義結果集的標準select語句，且不許用compute、compute by、for browse和into子句。 local：游標的作用域是局部的創建它的批處理、存儲過程或觸發器。 global：游標的作用域是全局的。在由連接執行的任何存儲過程或批處理中，都可以引用該游標名稱。該游標僅在連接斷開時自動釋放。參數說明：forward_only 只進。僅支持next。Scroll 滾動 支持：next、prior、first、last、absolute、relativestatic 靜態 open時在tempdb創建臨時表復本保存結果集。只提取，不許修改dynamic 動

5、態 記錄值、順序等在每次提取時都可能因其他用戶的更改而變動。不支持 absolute 提取選項。keyset 鍵集 open時在tempdb創建keyset表，記錄結果集中每條記錄的關鍵字段值和順序。參數說明：fast_forward 快速向前 優化的 forward_only、read_only.與scroll、for_update、 forward_only互斥read_only 只讀 在 update 或 delete 語句的 where current of 子句中不能引用游標scroll_locks 滾動鎖定 當滾動記錄指針提取當前記錄時，系統將會鎖定該行，確保游標更新或刪除的成功O

6、ptimistic 樂觀 行自從被讀入游標以來，如果已修改該行，嘗試進行的更新或刪除將失敗for update of 列,.更新 指定可更新的列。默認所有列例例8-1 定義一個游標，其數據為表定義一個游標，其數據為表salers中中的全部數據的全部數據DECLARE myCursor CURSORFor select * from Salers語法格式：OPEN GLOBAL 游標名游標名 當游標被打開時，行指針會指在第一行之前 打開游標后,如果 error=0表示打開操作成功 打開游標后,可用 cursor_rows返回游標記錄數-m 游標被異步填充.-m是鍵集中當前的行數-1 游標為動態。

7、符合條件記錄的行數不斷變化0 沒有符合的記錄、游標沒打開、關閉或釋放n 游標已完全填充。n是在游標中的總行數8.1.3 打開游標8.1.4從打開的游標中提取數據 FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n FROM cursor_name INTO variable_name ,.n NEXT：提取上次提取行之后的行，即向下移動。如：提取上次提取行之后的行，即向下移動。如果果 FETCH NEXT 為對游標的第一次提取操作，則返回結為對游標的第一次提取操作，則返回結果集中的第一行。果集中的第一行。NEXT 為默認的游標

8、提取選項。為默認的游標提取選項。 PRIOR：提取上次提取行之前的行。如果：提取上次提取行之前的行。如果 FETCH PRIOR 為對游標的第一次提取操作，則沒有行返回并且為對游標的第一次提取操作，則沒有行返回并且游標置于第一行之前。游標置于第一行之前。 LAST：提取游標中的最后一行并將其作為當：提取游標中的最后一行并將其作為當前行。前行。ABSOLUTE n ：如果：如果 n 為正數，則提取游標中從為正數，則提取游標中從第第 1 行開始的第行開始的第 n 行。如果行。如果 n 為負整數，則提為負整數，則提取游標中的倒數第取游標中的倒數第 n 行。行。 RELATIVE n ：如果：如果 n

9、 為正數，則提取所上次為正數，則提取所上次提取行之后的第提取行之后的第 n 行。如果行。如果 n 為負數，則提取為負數，則提取上次所提取行之前的第上次所提取行之前的第 n 行。如果行。如果 n 為為 0，則，則同一行被再次提取。同一行被再次提取。 cursor_name ：要從中進行提取的游標的名稱：要從中進行提取的游標的名稱。 INTO variable_name,.n：允許將提取操：允許將提取操作的列數據放到局部變量中。作的列數據放到局部變量中。注意：注意： FETCH語句每次只能提取一行數據。因為Transact-SQL游標不支持塊（多行）提取操作。 用fetch_status返回執行F

10、ETCH操作之后，當前游標指針的狀態。狀態值如下： 0 表示行已成功地讀取。 -1 表示讀取操作已超出了結果集。 -2 表示行在表中不存在。注意：注意： 查詢結果用into子句寫入局部變量，須先聲明局部變量的類型和寬度，且與select 語句中相應列的順序、類型和寬度一致。 第一次執行fetch next，則將獲取游標中的第一行數據。 打開游標后第一次執行fetch prior，則得不到任何數據。數據處理(2) 修改數據 語法格式：語法格式：update from 表或視圖表或視圖 set 列名列名=表達式表達式, where current of 游標名游標名功能：修改當前行指定字段的值功能

11、：修改當前行指定字段的值數據處理(3) 刪除數據 語句格式：delete from 表或視圖表或視圖 where current of 游標名游標名 功能： 刪除游標名指定的當前行數據。8.1.5關閉游標 當提取完游標結果集中的數據時，應及時調當提取完游標結果集中的數據時，應及時調用用CLOSE語句關閉游標，以便釋放游標所占用語句關閉游標，以便釋放游標所占用的系統資源。的系統資源。 CLOSE cursor_name cursor_name 是要被關閉的游標名。是要被關閉的游標名。8.1.6 釋放（刪除）游標由于關閉游標時并沒有刪除游標，因此，游標仍由于關閉游標時并沒有刪除游標，因此，游標仍然

12、占用著系統資源。則將其關閉后，還需要使然占用著系統資源。則將其關閉后，還需要使用用DEALLOCATE語句來釋放游標所占用的系語句來釋放游標所占用的系統資源。統資源。 DEALLOCATE cursor_name 使用游標修改數據 UPDATE語句和語句和DELETE語句也支持游標操語句也支持游標操作，它們可以通過游標修改或刪除游標基表中作，它們可以通過游標修改或刪除游標基表中的當前數據行。這樣，就可以通過游標更新和的當前數據行。這樣，就可以通過游標更新和刪除數據表中的數據。刪除數據表中的數據。 UPDATE table_nameSET 子句WHERE CURRENT OF cursor_na

13、me table_name table_name：為需要修改的數據表名。：為需要修改的數據表名。 cursor_namecursor_name：為游標名。：為游標名。例如：例如： Update SalersSet sex=女Where CURRENT OF myCursor將游標將游標“myCursor”中當前行中的中當前行中的“sex”列列的值修改為的值修改為“女女”。 DELETE FROM table_nameWHERE CURRENT OF cursor_name table_name：為需要修改的數據表名。：為需要修改的數據表名。 cursor_name：為游標名。：為游標名。例如：

14、例如：DELETE FROM SalersWhere CURRENT OF myCursor將游標將游標“myCursor”中當前行中刪除。中當前行中刪除。 USE Student GO DECLARE student_cur3 CURSOR FOR SELECT student_Name FROM student_Info OPEN student_cur3 GO一個完整的實例 FETCH NEXT FROM student_cur3 GO UPDATE student_Info SET student_Name = liu WHERE CURRENT OF student_cur3 GO

15、CLOSE student_cur3 DEALLOCATE student_cur3 GOdeclare 游標名游標名 cursor for select語句語句(2) open 游標名游標名(3) 使用使用移動讀取：移動讀取： fetch 游標名游標名 into 變量名變量名,刪除數據：刪除數據： delete from 表或視圖名表或視圖名 where current of 游標名游標名修改數據：修改數據： update from 表或視圖名表或視圖名 set 列名列名=表達式表達式, where current of 游標名游標名(4) close 游標名游標名(5) deallocat

16、e 游標名游標名是從查詢記錄集中逐條逐行地移動讀取、修改是從查詢記錄集中逐條逐行地移動讀取、修改或刪除記錄的處理機制。或刪除記錄的處理機制。靜態靜態(Static) / 動態動態(Dynamic)只進只進(Forward Only)/ 滾動滾動(Scroll)游標小結8.2 事事 務務 事務是SQL Server中的一個邏輯單元，其中包括一系列的操作，這些語句將被作為一個整體進行處理。通過事務，SQL Server能將邏輯相關的一組操作綁定在一起，以便服務器保持數據的完整性。8.2.1 事務的基本概念事務的基本概念 事務是一種機制，是一個操作序列，它包含了一組數據庫操作命令，所有的命令作為一個

17、整體一起向系統提交或撤消操作請求，即要么都執行，要么都不執行。因此，事務是一個不可分割的工作邏輯單元，類似于操作系統中的原語。在數據庫系統上執行并發操作時，事務是作為最小的控制單元來使用的。 事務是作為單個邏輯工作單元執行的一系列操作。一個邏輯工作單元必須有四個屬性，稱為ACID（原子性、一致性、隔離性和持久性）屬性，只有這樣才能成為一個事務。原子性：事務必須是原子工作單元。對于其數據修改，要么全都執行，要么全都不執行。一致性：事務在完成時，必須使所有的數據都保持一致狀態。在相關數據庫中，所有規則都必須應用于事務的修改，以保持所有數據的完整性。事務結束時，所有的內部數據結構都必須是正確的。隔離

18、性：由并發事務所作的修改必須與任何其他并發事務所作的修改隔離。事務查看數據時數據所處的狀態，要么是另一并發事務修改它之前的狀態，要么是另一事務修改它之后的狀態，事務不會查看中間狀態的數據。這稱為可串行性，因為它能夠重新裝載起始數據，并且重播一系列事務，以使數據結束時的狀態與原始事務執行的狀態 相同。持久性：事務完成之后，它對于系統的影響是永久性的。該修改即使出現系統故障也將一直保持。通常在程序中用BEGIN TRANSACTION命令來標識一個事務的開始，用COMMIT TRANSACTION命令標識事務結束。這兩個命令之間的所有語句被視為一體，只有執行到COMMIT TRANSACTION命

19、令時，事務中對數據庫的更新操作才算確認。和BEGINEND命令類似，這兩個命令也可以進行嵌套，即事務是可以嵌套執行這兩個命令的。1語法語法 BEGIN TRAN SACTION transaction_name | tran_name_variable WITH MARK description 2參數參數 transaction_name：給事務分配的名稱。 tran_name_variable：用戶定義的、含有有效事務名稱的變量的名稱。必須用 char、varchar、nchar或nvarchar數據類型聲明該變量。 WITH MARK description：指定在日志中標記事務。des

20、cription 是描述該標記的字符串。如果使用了 WITH MARK，則必須指定事務名。WITH MARK 允許將事務日志還原到命名標記。 SQL Server以下面幾種事務模式運行：顯式事務隱性事務自動提交事務分布式事務8.2.2 顯式事務顯式事務 顯示事務是指由用戶通過Transact-SQL事務語句而定義的事務。 Transact-SQL事務語句包括以下語句： BEGIN TRANSACTION語句：標記一個本地事務的開始。 COMMIT TRANSACTION語句：標記一個顯示事務或隱式事務的結束，說明事務已經成功執行，并將事務內全部被修改的數據保存到數據庫中。 COMMIT WOR

21、K語句：標記一個事務的結束。 ROLLBACK TRANSACTION語句：回滾顯示事務或隱式事務到事務的起始位置，或事務內部的存儲點。 ROLLBACK WORK語句：回滾顯示事務到事務的起始位置。8.2.3 隱式事務隱式事務 隱式事務是指在當前事務提交或回滾后，SQL Server自動開始的事務。 在回滾之后，SQL Server又自動開始一個新的事務。 在隱式模式下，當執行下面任何一個語句時，可以使SQL Server重新啟動一個事務。所有CREATE語句ALTER TABLE語句所有DROP語句TRUNCATE TABLE語句GRANT語句REVOKE語句INSERT語句UPDATE語

22、句DELETE語句SELECT語句OPEN語句FETCH 要關閉隱式事務模式前，執行SET IMPLICIT_TRANSACTIONS OFF語句即可。8.2.4 自動事務模式自動事務模式 自動事務是一種能夠自動執行并能自動回滾的事務。在自動事務模式下，當一個語句成功執行后，它被自動提交，而當它執行過程中產生錯誤則被自動回滾。 8.2.5 分布式事務分布式事務 分布式事務跨越兩個或多個稱為資源管理器的服務器。 8.3 鎖鎖 定定 如果在數據庫不一致性狀態時讀取數據，就有可能產生不一致性問題。解決這類問題的常用方法就是對此數據源進行了鎖定，在SQL Server中可以對以下的對象進行鎖定。數據行

23、（Row）：數據頁中的單行數據。索引行（Key）：索引頁中的單行數據即索引的鍵值。頁（Page）：頁是SQL Server 存取數據的基本單位，其大小為8KB。盤區（Extent）：一個盤區由8 個連續的頁組成。表（Table）。數據庫（Database）。8.3.1 鎖定粒度鎖定粒度 SQL Server 2000具有多粒度鎖定，可以根據事務所執行的任務靈活選擇所鎖定的資源粒度。為了最小化鎖的成本，SQL Server 2000自動地將適當級別的鎖分配給任務。 8.3.2 鎖模式鎖模式 SQL Server 使用以下資源鎖模式。 1共享鎖 共享（S）鎖允許并發事務讀取（SELECT）一個資源

24、。資源上存在共享（S）鎖時，任何其他事務都不能修改數據。 一旦已經讀取數據，便立即釋放資源上的共享（S）鎖，除非將事務隔離級別設置為可重復讀或更高級別，或者在事務生存周期內用鎖定提示保留共享（S）鎖。2更新鎖更新鎖 更新（U）鎖可以防止通常形式的死鎖。一般更新模式由一個事務組成，此事務讀取記錄，獲取資源（頁或行）的共享（S）鎖，然后修改行，此操作要求鎖轉換為排它（X）鎖。如果兩個事務獲得了資源上的共享模式鎖，然后試圖同時更新數據，則一個事務嘗試將鎖轉換為排它（X）鎖。 若要避免這種潛在的死鎖問題，請使用更新（U）鎖。 3排它鎖排它鎖 排它（X）鎖可以防止并發事務對資源進行訪問。其他事務不能讀取

25、或修改排它（X）鎖鎖定的數據。4意向鎖意向鎖 意向鎖表示SQL Server需要在層次結構中的某些底層資源上獲取共享（S）鎖或排它（X）鎖。 5架構鎖架構鎖 執行表的數據定義語言（DDL）操作（例如添加列或除去表）時，使用架構修改（Sch-M）鎖。6大容量更新鎖大容量更新鎖 當將數據大容量復制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 設置了 table lock on bulk 表選項時，將使用大容量更新（BU）鎖。大容量更新（BU）鎖允許進程將數據并發地大容量復制到同一表，同時防止其他不進行大容量復制數據的進程訪問該表。8.3.3 顯示鎖定信息顯示鎖定信息

26、可以通過企業管理器或存儲過程來查看鎖。 1用企業管理器查看鎖 在企業管理器中選擇目錄樹窗口中管理文件夾下當前活動中的鎖/對象節點，如圖8-1所示，則可以查看當前鎖定的進程，選擇同級的鎖/對象節點下的相應子節點，則可以查看當前鎖定的對象。 圖圖8-1 8-1 查看鎖查看鎖圖圖8-2 8-2 鎖屬性對話框鎖屬性對話框2用系統存儲過程用系統存儲過程Sp_lock 查看鎖查看鎖 sp_lock spid SQL Server 的進程編號spid 可以在master.dbo.sysprocesses 系統表中查到，spid 是INT類型的數據，如果不指定spid，則顯示所有的鎖。8.3.4 死鎖及其處理

27、死鎖及其處理 當某組資源的兩個或多個線程之間有循環相關性時，將發生死鎖。死鎖是在多用戶或多進程狀況下，為使用同一資源而產生的無法解決的爭用狀態，通俗地講就是兩個用戶各占用一個資源，兩人都想使用對方的資源，但同時又不愿放棄自己的資源，就一直等待對方放棄資源。如果不進行外部干涉，就將一直耗下去。 死鎖會造成資源的大量浪費，甚至會使系統崩潰。 盡量避免并發地執行涉及到修改數據的語句。 要求每個事務一次就將所有要使用的數據全部加鎖，否則就不予執行。 預先規定一個封鎖順序，所有的事務都必須按這個順序對數據執行封鎖。例如，不同的過程在事務內部對對象的更新執行順序應盡量保持一致。 每個事務的執行時間不可太長

28、，對程序段長的事務可考慮將其分割為幾個事務。8.4 監監 測測 SQL Server 2000 提供多種工具，監測SQL Server 實例的性能以及在數據庫中發生的用戶活動。通過監測可以使用戶確定數據庫應用程序是否按預期有效地運行，即使應用程序、數據庫和環境發生了變化。 8.4.1 系統性能評估及監測系統性能評估及監測 系統性能評估是指通過有效的網絡流量、磁盤I/O和CPU時間，可獲得最短的響應時間和最大的吞吐量，從而獲得最佳性能。 響應時間是這樣度量的：以可視的形式確認正在處理查詢并將結果集中的首行返回給用戶所需的時間長度；吞吐量是服務器在給定時間內處理的查詢總數的度量值。隨著用戶數的增加

29、，對服務器資源的競爭也隨之增加，轉而導致響應時間增加和總體吞吐量減少。 影響系統性能有以下幾個方面的因素： 系統資源（硬件）Microsoft Windows NT 4.0 和 Windows 2000 操作系統數據庫應用程序客戶端應用程序網絡 監測以下幾個方面，便可以基本解決問題： SQL Server 存儲過程或用戶應用程序提交的批處理 SQL 語句用戶活動（如阻塞鎖或死鎖）硬件活動（如磁盤使用） 可以使用SQL 事件探查器監視和解決 Transact-SQL問題以及與應用程序有關的問題。可以使用系統監視器監視硬件問題和其他與系統有關的問題。8.4.2 性能監測工具性能監測工具 監測應用程

30、序、SQL Server 實例或操作系統環境一般按照下面的流程操作。 確定監視目標。 為要執行的監視類型選擇合適的工具。 使用工具監視 SQL Server 或系統環境并分析捕獲的數據。 識別要監視的事件。 事件確定所要監視和捕獲的活動。選擇要監視的事件取決于要監視的目標和原因。例如，當監視磁盤活動時，沒必要監視 SQL Server 鎖。 確定要捕獲的事件數據。 事件數據描述事件發生時的每個實例。 具體選擇什么工具取決于監測類型和要監測的事件類型。SQL 事件探查器 SQL事件探查器能夠監測服務器和數據庫活動，如死鎖數、嚴重錯誤、跟蹤存儲過程和Transact-SQL語句或登錄活動等。系統監

31、視器 通過使用預定義對象和計數器或用戶定義的計數器監測事件，監測服務器的性能和活動。系統監測器（Microsoft Windows NT4.0 中的性能監測器）收集事件數而不是關于事件的數據（例如內存使用、活動事務數、阻塞的鎖數或 CPU 活動）。 當前活動窗口（SQL Server 企業管理器） 以圖形顯示有關當前正在 SQL Server 實例上運行的進程、被阻塞的進程、鎖和用戶活動的信息。 錯誤日志 除了在別處可用的信息外，還包含有關 SQL Server 中的事件的額外信息。可以利用錯誤日志中的信息解決與 SQL Server 有關的問題。 sp_who 報告有關當前 SQL Serv

32、er 用戶和進程的快照信息，包括當前正在執行的語句以及該語句是否被阻塞。 sp_lock 有關鎖的報表快照信息，包括對象 ID、索引 ID、鎖的類型和鎖應用的類型或資源。這是另一種在SQL Server 企業管理器的當前活動窗口中查看鎖活動的 Transact-SQL 方法。sp_spaceused 顯示對表（或整個數據庫）所使用的當前硬盤空間量的估計。這是另一種在 SQL Server 企業管理器內查看數據庫使用情況的 Transact-SQL 方法。sp_monitor 顯示統計，包括 CPU 使用、I/O 使用和自上次執行 sp_monitor 后的空閑時間。DBCC 語句 能夠檢查性能

33、統計和數據庫的邏輯及物理一致性。 內置函數 顯示自服務器啟動后有關 SQL Server 活動的快照統計，這些統計存儲在預定義的 SQL Server 計數器內。 SQL 事件探查器存儲過程和函數 使用 Transact-SQL 存儲過程收集 SQL 事件探查器統計信息。跟蹤標記 顯示有關服務器內的特定活動的信息，用于診斷問題或性能問題（如死鎖鏈）。簡單網絡管理協議（SNMP） SNMP是一個提供網絡管理服務的應用程序協議。 事件或活動SQL 事件探查器系統監視器當前活動窗口Transact-SQL錯誤日志趨勢分析是是重播捕獲的事件是特殊監測是是是是生成警告是圖形界面是是是是在自定義應用程序內使用是是表表8-18-1要監測