SQL第章游標(biāo)及事務(wù)

1、.第第13章章 游標(biāo)及事務(wù)游標(biāo)及事務(wù)13.1 游標(biāo)的定義及優(yōu)點游標(biāo)的定義及優(yōu)點13.2 游標(biāo)的使用游標(biāo)的使用13.3 使用游標(biāo)修改數(shù)據(jù)使用游標(biāo)修改數(shù)據(jù)13.4 事務(wù)的使用事務(wù)的使用返回目錄返回目錄.213.1 游標(biāo)的定義及其優(yōu)點 前面介紹的數(shù)據(jù)檢索方法可以得到數(shù)據(jù)庫中有關(guān)表的數(shù)據(jù)，但這些數(shù)據(jù)是作為一個結(jié)果集得到的，用戶可以把這個結(jié)果集保存到一個文件里，或生成一個新表以便于以后使用。這種查詢是非常重要的。但這種查詢形式有一個很大的缺點，它不能對結(jié)果集中每一行的數(shù)據(jù)進行處理。使用游標(biāo)可以實現(xiàn)對查詢結(jié)果集中的數(shù)據(jù)逐行處理。13.1.1 游標(biāo)的概念游標(biāo)的概念 游標(biāo)(Cursor)是一種處理數(shù)據(jù)的方法，

2、為了查看或者處理結(jié)果集中的數(shù)據(jù)，游標(biāo)提供了在結(jié)果集中向前或者向后瀏覽數(shù)據(jù)的能力。 可以把游標(biāo)看成一種指針，它既可以指向當(dāng)前位置，也可以指向結(jié)果集中的任意位置，它允許用戶對指定位置的數(shù)據(jù)進行處理，可以把結(jié)果集中的數(shù)據(jù)放在數(shù)組、應(yīng)用程序中或其它地方。.313.1.2 使用游標(biāo)的優(yōu)點使用游標(biāo)的優(yōu)點 從游標(biāo)定義可以得知游標(biāo)具有如下的優(yōu)點: 允許程序?qū)τ刹樵冋Z句SELECT返回的行集合中的每一行數(shù)據(jù)執(zhí)行相同或不同的操作，而不是對整個行集合執(zhí)行同一個操作 提供對基于游標(biāo)位置的表中的行進行刪除和更新的能力 游標(biāo)實際上作為面向集合的數(shù)據(jù)庫管理系統(tǒng)（RDBMS）和面向行的程序設(shè)計之間的橋梁，使這兩種處理方式通過

3、游標(biāo)溝通起來.413.2 游標(biāo)的使用13.2.1 使用游標(biāo)的步驟使用游標(biāo)的步驟 使用游標(biāo)的工作流程如下圖13-1所示。具體地說，有如下幾個步驟： 創(chuàng)建游標(biāo)。使用T-SQL語句生成一個結(jié)果集，并且定義游標(biāo)的特征，如游標(biāo)中的記錄是否可以修改。 打開游標(biāo)。 從游標(biāo)的結(jié)果集中讀取數(shù)據(jù)。從游標(biāo)中檢索一行或多行數(shù)據(jù)稱為取數(shù)據(jù)。 對游標(biāo)中的數(shù)據(jù)逐行操作。 關(guān)閉和釋放游標(biāo)。.513.2.2 游標(biāo)的定義及使用過程游標(biāo)的定義及使用過程 1. 聲明游標(biāo)聲明游標(biāo) 聲明游標(biāo)是指用DECLARE語句聲明或創(chuàng)建一個游標(biāo)。 聲明游標(biāo)的語法如下：DECLARE cursor_name SCROLL CURSORFOR sele

4、ct_statementFOR READ ONLY|UPDATEOF column_name_list 其中： cursor_name:是游標(biāo)的名字，為一個合法的SQL Server標(biāo)識符，游標(biāo)的名字必須遵循SQL Server命名規(guī)范。 SCROLL：表示取游標(biāo)時可以使用關(guān)鍵字NEXT、PRIOR、FIRST、LAST、ABSOLUTE、RELATIVE。每個關(guān)鍵字的含義將在介紹FETCH子句時講解。 select_statement：是定義游標(biāo)結(jié)果集的標(biāo)準(zhǔn) SELECT語句，它可以是一個完整語法和語義的Transact-SQL的SELECT語句。.6 但是這個SELECT語句必須有FROM

5、子句，不允許使用關(guān)鍵字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。 FOR READ ONLY：指出該游標(biāo)結(jié)果集只能讀，不能修改。 FOR UPDATE：指出該游標(biāo)結(jié)果集可以被修改。 OF column_name_list：列出可以被修改的列的名單。 應(yīng)該注意應(yīng)該注意: 游標(biāo)有且只有兩種方式：FOR READ ONLY或FOR UPDATE。 當(dāng)游標(biāo)方式指定為FOR READ ONLY時，游標(biāo)涉及的表不能被修改。 當(dāng)游標(biāo)方式指定為FOR UPDATE時，可以刪除或更新游標(biāo)涉及的表中的行。通常，這也是缺省方式。 聲明游標(biāo)的DECLARE CURSOR 語句必須是在

6、該游標(biāo)的任何OPEN語句之前。.7 2. 打開游標(biāo)打開游標(biāo) 打開游標(biāo)是指打開已被聲明但尚未被打開的游標(biāo)，打開游標(biāo)使用OPEN語句。 打開游標(biāo)的語法如下： OPEN cursor_name 其中： cursor_name是一個已聲明的尚未打開的游標(biāo)名。 注意：注意： 當(dāng)游標(biāo)打開成功時，游標(biāo)位置指向結(jié)果集的第一行之前。 只能打開已經(jīng)聲明但尚未打開的游標(biāo)。.8 3. 從打開的游標(biāo)中提取行從打開的游標(biāo)中提取行 游標(biāo)被打開后，游標(biāo)位置位于結(jié)果集的第一行前，此時可以從結(jié)果集中提?。‵ETCH）行。SQL Server將沿著游標(biāo)結(jié)果集一行或多行向下移動游標(biāo)位置，不斷提取結(jié)果集中的數(shù)據(jù)，并修改和保存游標(biāo)當(dāng)前的

7、位置，直到結(jié)果集中的行全部被提取。 從打開的游標(biāo)中提取行的語法如下：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE FROM cursor_name INTO fetch_target_list 其中： cursor_name：為一已聲明并已打開的游標(biāo)名字。 NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE：游標(biāo)移動方向，缺省情況下是NEXT，即向下移動。 NEXT：取下一行數(shù)據(jù)。 PRIOR：取前一行數(shù)據(jù)。 FIRST：取第一行數(shù)據(jù)。.9 LAST：取最后一行數(shù)據(jù)。 ABSOLUTE：按絕對位置取數(shù)據(jù)。 RELATIV

8、E：按相對位置取數(shù)據(jù)。 游標(biāo)位置確定了結(jié)果集中哪一行可以被提取，如果游標(biāo)方式為FOR UPDATE的話，也就確定該位置一行數(shù)據(jù)可以被更新或刪除。 INTO fetch_target_list：指定存放被提取的列數(shù)據(jù)的目的變量清單。這個清單中變量的個數(shù)、數(shù)據(jù)類型、順序必須與定義該游標(biāo)的select_statement的SELECT_list中列出的列清單相匹配。 為了更靈活地操縱數(shù)據(jù)，可以把從已聲明并已打開的游標(biāo)結(jié)果集中提取的列數(shù)據(jù)，分別存放在目的變量中。.10 有兩個全局變量提供關(guān)于游標(biāo)活動的信息： FETCH_STATUS 保存著最后FETCH語句執(zhí)行后的狀態(tài)信息，其值和含義如下： 0 ：表

9、示成功完成FETCH 語句。 -1：表示FETCH語句執(zhí)行有錯誤，或者當(dāng)前游標(biāo)位置已在結(jié)果集中的最后一行，結(jié)果集中不再有數(shù)據(jù)。 -2：提取的行不存在。 rowcount保存著自游標(biāo)打開后的第一個保存著自游標(biāo)打開后的第一個FETCH語句，直到最近一次的語句，直到最近一次的FETCH語句為止，已從游標(biāo)結(jié)語句為止，已從游標(biāo)結(jié)果集中提取的行數(shù)。果集中提取的行數(shù)。 每個打開的游標(biāo)都與一特定的rowcount有關(guān)，關(guān)閉游標(biāo)時，該rowcount變量也被刪除。在FETCH語句執(zhí)行后查看這個變量，可得知從游標(biāo)結(jié)果集中已提取的行數(shù)。.11 4. 關(guān)閉游標(biāo)關(guān)閉游標(biāo) 關(guān)閉(Close)游標(biāo)是停止處理定義游標(biāo)的那個查

10、詢。關(guān)閉游標(biāo)并不改變它的定義，可以再次用open語句打開它，SQL Server會用該游標(biāo)的定義重新創(chuàng)建這個游標(biāo)的一個結(jié)果集。 關(guān)閉游標(biāo)的語法如下：CLOSE cursor_name 其中： cursor_name：是已被打開并將要被關(guān)閉的游標(biāo)名字。 在如下情況下，SQL Server會自動地關(guān)閉已打開的游標(biāo)： 當(dāng)你退出這個SQL Server會話時 從聲明游標(biāo)的存儲過程中返回時.12 5. 釋放游標(biāo)釋放游標(biāo) 釋放( Deallocate)游標(biāo)是指釋放所有分配給此游標(biāo)的資源，包括該游標(biāo)的名字。 釋放游標(biāo)的語法是：DEALLOCATE CURSOR cursor_name 其中： cursor_

11、name：將要被DEALLOCATE釋放的游標(biāo)名字。如果釋放一個已打開但未被關(guān)閉的游標(biāo)，SQL Server會自動先關(guān)閉這個游標(biāo)，然后再釋放它。 注意一下關(guān)閉游標(biāo)與釋放游標(biāo)的區(qū)別注意一下關(guān)閉游標(biāo)與釋放游標(biāo)的區(qū)別：關(guān)閉游標(biāo)并不改變游標(biāo)的定義，一個游標(biāo)關(guān)閉后，不需要再次聲明，就可以重新打開并使用它。但一個游標(biāo)釋放后，就釋放了與該游標(biāo)有關(guān)的一切資源，也包括游標(biāo)的聲明，游標(biāo)釋放后就不能再使用該游標(biāo)了，如需再次使用游標(biāo)，就必須重新定義。.13【例例13-1】定義一個游標(biāo)，將教師表teacher中所有教師的教師姓名、教師職稱顯示出來。USE jwglGODECLARE teach_name VARCHAR

12、(8),teach_profession VARCHAR(16)DECLARE teacher_coursor SCROLL CURSOR FOR SELECT teacher_name,profession FROM teacher FOR READ ONLYOPEN teacher_coursorFETCH FROM teacher_coursor INTO teach_name,teach_profession.14WHILE FETCH_STATUS=0 BEGIN PRINT 教師姓名:+teach_name+ +職 稱: + teach_profession FETCH FROM

13、teacher_coursor INTO teach_name , teach_profession ENDCLOSE teacher_coursorDEALLOCATE teacher_coursor.1513.3 使用游標(biāo)修改數(shù)據(jù) 用戶可以在UPDATE或DELETE語句中使用游標(biāo)來更新、刪除表或視圖中的行，但不能用來插入新行。13.3.1 更新數(shù)據(jù)更新數(shù)據(jù) 通過在UPDATE語句中使用游標(biāo)可以更新表或視圖中的行。被更新的行依賴于游標(biāo)位置的當(dāng)前值。 更新數(shù)據(jù)語法形式如下： UPDATE table_name|view_name SET table_name.|view_name. colu

14、mn_name = new_value .n WHERE CURRENT OF cursor_name 其中： 緊跟UPDATE之后的table_name| view_name：要更新的表名或視圖名，可以加或不加限定。但它必須是聲明該游標(biāo)的SELECT語句中的表名或視圖名。.16 column_name：是要更新的列的列名，可以加或不加限定。但它們必須是聲明游標(biāo)的SELECT語句中UPDATE OF column_name_list的子集 new_value：為被更新列的新值，它可以是一個表達式、空值或子查詢。 WHERE CURRENT OF：使SQL Server只更新由指定游標(biāo)的游標(biāo)位置

15、當(dāng)前值確定的行。cursor_name：是已聲明為FOR UPDATE方式并已打開的游標(biāo)名。.17 注意： 使用UPDATE.CURRENT OF語句一次只能更新當(dāng)前游標(biāo)位置確定的那一行，OPEN語句將游標(biāo)位置定位在結(jié)果集第一行前，可以使用FETCH語句把游標(biāo)位置定位在要被更新的數(shù)據(jù)行處。 用UPDATE.WHERE CURRENT OF語句更新表中的行時，不會移動游標(biāo)位置，被更新的行可以再次被修改，直到下一個FETCH語句的執(zhí)行。 UPDATE.WHERE CURRENT OF語句可以更新多表視圖或被連接的多表，但只能更新其中一個表的行，即所有被更新的列都來自同一個表。.18【例例13-2】

16、通過游標(biāo)將教師表teacher中記錄號為3的章紅老師的職稱由“講師”改為“教授”。USE jwglGODECLARE teacher_coursor SCROLL CURSOR FOR SELECT teacher_name,profession FROM teacher FOR UPDATE OF professionOPEN teacher_coursorFETCH ABSOLUTE 3 FROM teacher_coursorUPDATE teacherSET profession = 教授 WHERE CURRENT OF teacher_coursorFETCH ABSOLUTE 3

17、 FROM teacher_coursorCLOSE teacher_coursorDEALLOCATE teacher_coursor.1913.3.2 刪除數(shù)據(jù)刪除數(shù)據(jù) 通過在DELETE語句中使用游標(biāo)來刪除表或視圖中的行。被刪除的行依賴于游標(biāo)位置的當(dāng)前值。 刪除數(shù)據(jù)語法形式如下：DELETE FROMdatabase.owner.table_name|view_nameWHERE CURRENT OF cursor_name 其中： table_name|view_name：為要從其中刪除行的表名或視圖名，可以加或不加限定。但它必須是定義該游標(biāo)的SELECT語句中的表名或視圖名。 cur

18、sor_name：為已聲明并已打開的游標(biāo)名。 WHERE CURRENT OF：它使SQL Server只刪除由指定游標(biāo)的游標(biāo)位置當(dāng)前值確定的行。.20 注意：注意： 使用游標(biāo)的DELETE語句，一次只能刪除當(dāng)前游標(biāo)位置確定的那一行。OPEN語句將游標(biāo)位置定位在結(jié)果集第一行之前，可以用FETCH語句把游標(biāo)位置定位在要被刪除的行處。 在DELETE語句中使用的游標(biāo)必須聲明為FOR UPDATE方式。而且聲明游標(biāo)的SELECT語句中不能含有連接操作或涉及多表視圖，否則即使聲明中指明了FOR UPDATE方式，也不能刪除其中的行。 對使用游標(biāo)刪除行的表，要求有一個唯一索引。 使用游標(biāo)的DELETE語

19、句，刪除一行后將游標(biāo)位置向前移動一行。.2113.4 事務(wù)的使用13.4.1 什么是事務(wù)什么是事務(wù) 多用戶并發(fā)存取同一數(shù)據(jù)可能會導(dǎo)致產(chǎn)生數(shù)據(jù)的不一致性問題。正確地使用事務(wù)可以有效控制這類問題發(fā)生的頻度甚至能避免這類問題的發(fā)生。 所謂事務(wù)(Transaction ),是指一個操作序列，這些操作序列要么都被執(zhí)行，要么都不被執(zhí)行，它是一個不可分割的工作單元。 事務(wù)中任何一個語句執(zhí)行時出錯，系統(tǒng)都會返回到事務(wù)開始前的狀態(tài)。 事務(wù)是并發(fā)控制的基本單元，是數(shù)據(jù)庫維護數(shù)據(jù)一致性的單位。在每個事務(wù)結(jié)束時，都能保持數(shù)據(jù)一致性。 例如，銀行轉(zhuǎn)帳工作：從一個帳號扣款并使另一個帳號增款，這兩個操作要么都被執(zhí)行，要么都

20、不被執(zhí)行。所以，應(yīng)該把他們看成一個事務(wù)。.2213.4.2 事務(wù)的特點及事務(wù)的管理事務(wù)的特點及事務(wù)的管理 1.事務(wù)的特性 事務(wù)具有4個屬性：原子性(Atomicity)、一致性(Consistency)、隔離性(1solation)和永久性(Durability)。這4個屬性簡稱為ACID特性。 原子性 原子性是指事務(wù)內(nèi)的操作要么都被執(zhí)行，要么都不被執(zhí)行。 一致性 一致性是指在事務(wù)開始前和結(jié)束后，所有的數(shù)據(jù)都必須處于一致性的狀態(tài)。 隔離性 隔離性是指一個事務(wù)所作的修改必須能夠與其他事務(wù)所作的修改隔離起來。 永久性 永久性指如果事務(wù)正常結(jié)束，則它對數(shù)據(jù)庫所做的修改應(yīng)是永久地保存。 .232. 事

21、務(wù)的管理事務(wù)控制語句：控制事務(wù)執(zhí)行的語句。包括將一系列操作定義為一個工作單元來處理。鎖機制：封鎖正被一個事務(wù)修改的數(shù)據(jù)，防止其他用戶訪問到“不一致”的數(shù)據(jù)。事務(wù)日志：使事務(wù)具有可恢復(fù)性。為了盡可能避免死鎖的出現(xiàn)，應(yīng)注意：在所有的事務(wù)中都按同一順序來訪問各個表。盡可能利用存儲過程來完成一個事務(wù)，以保證對各表的訪問次序都是一致的。 事務(wù)應(yīng)該盡量小且應(yīng)盡快提交。 避免人工輸入操作出現(xiàn)在事務(wù)中。 避免并發(fā)地執(zhí)行許多像INSERT、UPDATE、DELETE這類數(shù)據(jù)修改語句。.2413.4.3 事務(wù)控制語句事務(wù)控制語句 在SQL Server中，對事務(wù)的管理是通過事務(wù)控制語句和幾個全局變量結(jié)合起來實現(xiàn)的

22、。 1. 事務(wù)控制語句事務(wù)控制語句 SAVE TRAN：后跟保存點名，表示設(shè)置保存點 BEGIN TRAN tran_name：標(biāo)識一個用戶定義的事務(wù)的開始。tran_name為事務(wù)的名字，標(biāo)識一個事務(wù)開始。 COMMIT TRAN tran_name：表示提交事務(wù)中的一切操作，結(jié)束一個用戶定義的事務(wù)。使得對數(shù)據(jù)庫的改變生效。 ROLLBACK TRAN tran_name|save_name：回退一個事務(wù)到事務(wù)的開頭或一個保存點。表示要撤消該事務(wù)已做的操作，回滾到事務(wù)開始前或保存點前的狀態(tài)。 SAVE TRAN save_name：在事務(wù)中設(shè)置一個保存點。它可以使一個事務(wù)內(nèi)的部分操作回退。.25 2. 兩個可用于事務(wù)管理的全局變量兩個可用于事務(wù)管理的全局變量 兩個可用于事務(wù)管理的全局變量是error及rowcount。 error：給出最近一次執(zhí)行的出錯語句引發(fā)的錯誤號，error為0表示未出錯。 rowcount：給出受事務(wù)中已執(zhí)行語句所影響的數(shù)據(jù)行數(shù)。 3. 事務(wù)控制語句的使用事務(wù)控制語句的使用 事務(wù)控制語句的使用方法如下： BEGIN TRAN / *A組語句序列