【正文】 O 批處理結(jié)束標(biāo)志 CREATE VIEW employee_info AS SELECT * FROM employee GO CREATE VIEW語句與其他語句隔離 SELECT * FROM employee_info GO 例 933 創(chuàng)建一個(gè)視圖，使用 GO命令將 CREATE VIEW語句與批處理中的其他語句 (如 USE、SELECT語句等 )隔離。 游標(biāo)管理與應(yīng)用 游標(biāo)概述 聲明游標(biāo) 使用游標(biāo) 游標(biāo)的應(yīng)用 使用系統(tǒng)存儲(chǔ)過程管理游標(biāo) 游標(biāo)概述 游標(biāo)管理與應(yīng)用 1. 游標(biāo)種類 (1)TransactSQL游標(biāo)：是由 DECLARE CURSOR語句定義，主要用在服務(wù)器上，由從客戶端發(fā)送給服務(wù)器的 TransactSQL語句或批處理、存儲(chǔ)過程、觸發(fā)器中的 TransactSQL語句進(jìn)行管理。 TransactSQL游標(biāo)不支持提取數(shù)據(jù)塊或多行數(shù)據(jù)。 (2)API游標(biāo)：支持在 OLE DB、 ODBC以及 DB_library中使用游標(biāo)函數(shù)，主要用在服務(wù)器上。每一次客戶端應(yīng)用程序調(diào)用 API游標(biāo)函數(shù)， SQL Server的 OLE DB提供者、 ODBC驅(qū)動(dòng)器或 DB_library的動(dòng)態(tài)鏈接庫(kù)(DLL)都會(huì)將這些客戶請(qǐng)求送給服務(wù)器以對(duì) API游標(biāo)進(jìn)行處理。 (3)客戶游標(biāo)：當(dāng)客戶機(jī)緩存結(jié)果集時(shí)才使用。在客戶游標(biāo)中，有一個(gè)默認(rèn)的結(jié)果集被用來在客戶機(jī)上緩存整個(gè)結(jié)果集?？蛻粲螛?biāo)僅支持靜態(tài)游標(biāo)。一般情況下，服務(wù)器游標(biāo)能支持絕大多數(shù)的游標(biāo)操作，但不支持所有的 TransactSQL語句或批處理，所以客戶游標(biāo)常常僅被用作服務(wù)器游標(biāo)的輔助。 ? 由于 API游標(biāo)和 TransactSQL游標(biāo)使用在服務(wù)器端，所以被稱為服務(wù)器游標(biāo)或后臺(tái)游標(biāo)，而客戶端游標(biāo)被稱為前臺(tái)游標(biāo)。 2. 服務(wù)器游標(biāo)與默認(rèn)結(jié)果集的比較 游標(biāo)概述 ? SQL Server以兩種方式為用戶返回結(jié)果集：默認(rèn)結(jié)果集和服務(wù)器游標(biāo)。 (1)默認(rèn)結(jié)果集具有的特點(diǎn)：開銷??；取數(shù)據(jù)時(shí)提供最大性能；僅支持默認(rèn)的單進(jìn)、只讀游標(biāo)功能；返回結(jié)果行時(shí)一次 — 行；連接時(shí)一次只支持一個(gè)活動(dòng)語句；支持所有 TransactSQL語句。 (2)服務(wù)器游標(biāo)具有的持點(diǎn)：支持所有游標(biāo)功能；可以為用戶返回?cái)?shù)據(jù)塊；在單個(gè)連接上支持多個(gè)活動(dòng)語句；以性能補(bǔ)償游標(biāo)功能；不支持所有返回多于一行結(jié)果集的 TransactSQL語句。 ? 使用游標(biāo)不如使用默認(rèn)結(jié)果集的效率高。在默認(rèn)結(jié)果集中，客戶端只向服務(wù)器發(fā)送要執(zhí)行的語句。而使用服務(wù)器游標(biāo)時(shí)，每個(gè) FETCH語句都必須從客戶端發(fā)往服務(wù)器，再在服務(wù)器中分析此語句并將它編譯為執(zhí)行汁劃。 3. 服務(wù)器游標(biāo)與客戶端游標(biāo)的比較 游標(biāo)概述 ? 使用服務(wù)器游標(biāo)比使用客戶游標(biāo)有以下幾方面的優(yōu)點(diǎn)： (1)性能：如果要在游標(biāo)中訪問部分?jǐn)?shù)據(jù)，使用服務(wù)器游標(biāo)將提供最佳性能，因?yàn)橹挥斜蝗〉降臄?shù)據(jù)在網(wǎng)絡(luò)上發(fā)送，客戶游標(biāo)在客戶端存取所有結(jié)果集。 (2)更準(zhǔn)確的定位更新：服務(wù)器游標(biāo)直接支持定位操作，如 UPDATE和DELETE語句，客戶游標(biāo)通過產(chǎn)生 TransactSQL搜索 UPDATE語句模擬定位游標(biāo)更新，如果多行與 UPDATE語句的 WHERE子句的條件相匹配將導(dǎo)致無意義更新。 (3)內(nèi)存使用：使用服務(wù)器游標(biāo)時(shí)，客戶端不需要高速存取大量數(shù)據(jù)或者保持有關(guān)游標(biāo)位置的信息，這些都由服務(wù)器來完成。 (4)多活動(dòng)語句：使用服務(wù)器游標(biāo)時(shí)，結(jié)果不會(huì)存留在游標(biāo)操作之間的連接上，這就允許同時(shí)擁有多個(gè)活動(dòng)的基于游標(biāo)的語句。 游標(biāo)概述 ? SQL Server支持 4種類型的服務(wù)器游標(biāo)，它們是單進(jìn)游標(biāo)、靜態(tài)游標(biāo)、動(dòng)態(tài)游標(biāo)和鍵集驅(qū)動(dòng)游標(biāo)。 (1)單進(jìn)游標(biāo)只支持游標(biāo)按從前向后順序提取數(shù)據(jù)，游標(biāo)從數(shù)據(jù)庫(kù)中提取一條記錄并進(jìn)行操作，操作完畢后，再提取下一條記錄。 (2)靜態(tài)游標(biāo)也稱為快照游標(biāo)，它總是按照游標(biāo)打開時(shí)的原樣顯示結(jié)果集，并不反映在數(shù)據(jù)庫(kù)中對(duì)任何結(jié)果集成員所做的修改，因此不能利用靜態(tài)游標(biāo)修改基表中的數(shù)據(jù)。靜態(tài)游標(biāo)打開時(shí)的結(jié)果集存儲(chǔ)在數(shù)據(jù)庫(kù) tempdb中。靜態(tài)游標(biāo)始終是只讀的。 (3)動(dòng)態(tài)游標(biāo)也稱為敏感游標(biāo)，與靜態(tài)游標(biāo)相對(duì)，當(dāng)游標(biāo)在結(jié)果集中滾動(dòng)時(shí)，結(jié)果集中的數(shù)據(jù)記錄的數(shù)據(jù)值、順序和成員的變化均反映到游標(biāo)上，用戶所做的各種操作均可通過游標(biāo)反映。 (4)鍵集驅(qū)動(dòng)游標(biāo)介于靜態(tài)游標(biāo)和動(dòng)態(tài)游標(biāo)之間，兼有兩者的特點(diǎn)。打開鍵集驅(qū)動(dòng)游標(biāo)后，游標(biāo)中的成員和行順序是固定的。鍵集驅(qū)動(dòng)游標(biāo)由一套惟一標(biāo)識(shí)符控制，這些惟一標(biāo)識(shí)符就是鍵集。用戶對(duì)基表中的非關(guān)鍵值列插入數(shù)據(jù)或進(jìn)行修改造成數(shù)據(jù)值的變化，在整個(gè)游標(biāo)中都是可見。鍵集驅(qū)動(dòng)游標(biāo)的鍵集在游標(biāo)打開時(shí)建立在數(shù)據(jù)庫(kù) tempdb中。 4. 服務(wù)器游標(biāo)類型 聲明游標(biāo) 游標(biāo)管理與應(yīng)用 SQL Server游標(biāo)具有下面的處理過程： (1)聲明游標(biāo)，定義其特性，如游標(biāo)中的行是否可以被更新。 (2) 執(zhí)行 TransactSQL語句生成游標(biāo)。 (3)在游標(biāo)中檢索要查看的行。從游標(biāo)中檢索一行或幾行的操作稱為取數(shù)據(jù)。向前或向后執(zhí)行取數(shù)據(jù)操作來檢索行的行為稱為滾動(dòng)。 (4)關(guān)閉游標(biāo)。 聲明游標(biāo) ?語法格式如下： DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] 1. SQL92游標(biāo)定義格式 聲明游標(biāo) 表 913 SCROLL的取值 SCROLL選項(xiàng) 含 義 FIRST 提取游標(biāo)中的第一行數(shù)據(jù) LAST 提取游標(biāo)中的最后一行數(shù)據(jù) PRIOR 提取游標(biāo)當(dāng)前位置的上一行數(shù)據(jù) NEXT 提取游標(biāo)當(dāng)前位置的下一行數(shù)據(jù) RELATIVE n 提取游標(biāo)當(dāng)前位置之前或之后的第 n行數(shù)據(jù) (n為正表示向后，n為負(fù)表示向前 ) ABSULUTE n 提取游標(biāo)中的第 n行數(shù)據(jù) 聲明游標(biāo) 例 934 使用 SQL92標(biāo)準(zhǔn)的游標(biāo)聲明語句聲明一個(gè)游標(biāo)，用于訪問 Sales數(shù)據(jù)庫(kù)中的 goods表的信息。 USE Sales GO DECLARE Goods_cursor CURSOR FOR SELECT * FROM Goods FOR READ ONLY 聲明游標(biāo) ? 語法格式如下： DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] 2. TransactSQL擴(kuò)展游標(biāo)定義格式 聲明游標(biāo) 例 935 為 customer表定義一個(gè)全局滾動(dòng)動(dòng)態(tài)游標(biāo)，用于訪問顧客的編號(hào)、姓名、地址、電話信息。 DECLARE cur_customer CURSOR GLOBAL SCROLL DYNAMIC FOR SELECT customer_id,customer_name,address,telphone FROM customer 游標(biāo)管理與應(yīng)用 使用游標(biāo) 1. 打開游標(biāo) 游標(biāo)聲明之后，必須打開才能使用。打開游標(biāo)的語法格式如下： OPEN {{[GLOBAL] cursor_name} | cursor_variable_name } ?例如，打開例 935所聲明的游標(biāo)操作： OPEN cur_customer 使用游標(biāo) 2. 讀取游標(biāo) ? 一旦游標(biāo)被成功打開，就可以從游標(biāo)中逐行地讀取數(shù)據(jù)，以進(jìn)行相關(guān)處理。從游標(biāo)中讀取數(shù)據(jù)主要使用 FETCH命令。其語法格式為： FETCH [ [NEXT|PRIOR|FIRST|LAST |ABSOLUTE {n|@nvar} |RELATIVE {n|@nvar}] FROM] {{[GLOBAL] cursor_name}| cursor_variable_name } [INTO @variable_name [ ,...n ]] 使用游標(biāo) 表 914 @@fetch_status變量 返回值 描 述 0 FETCH命令已成功執(zhí)行 1 FETCH命令失敗或者行數(shù)據(jù)己超出了結(jié)果集 2 所讀取的數(shù)據(jù)已經(jīng)不存在。 使用游標(biāo) OPEN cur_customer FETCH NEXT FROM cur_customer /*取第一個(gè)數(shù)據(jù)行 */ WHILE @@fetch_status = 0 /* 檢查 @@fetch_status是否還有數(shù)據(jù)可取 */ BEGIN FETCH NEXT FROM cur_customer END 例 936 打開例 935中聲明的游標(biāo)，讀取游標(biāo)中的數(shù)據(jù)。 使用游標(biāo) ? CLOSE的語法格式為： CLOSE {{[ GLOBAL ] cursor_name } | cursor_variable_name } ?例如，關(guān)閉例 935中的游標(biāo) cur_customer的命令： CLOSE cur_customer ?游標(biāo) cur_customer在關(guān)閉后，仍可用 OPEN語句打開繼續(xù)讀取數(shù)據(jù)行。 3. 關(guān)閉游標(biāo) 使用游標(biāo) ? DEALLOCATE命令刪除游標(biāo)與游標(biāo)名或游標(biāo)變量之間的聯(lián)系，并且釋放游標(biāo)占用的所有系統(tǒng)資源。其語法格式為： DEALLOCATE {{ [ GLOBAL ] cursor_name } | cursor_variable_name } ? 一旦某個(gè)游標(biāo)被刪除，在重新打開之前，必需再次對(duì)其進(jìn)行聲明。 ? 例如，釋放例 935所定義的游標(biāo) cur_customer。 DEALLOCATE cur_customer ? DEALLOCATE cursor_variable_name語句只刪除對(duì)游標(biāo)命名變量的引用。直到批處理、存儲(chǔ)過程或觸發(fā)器結(jié)束時(shí)變量離開作用域，才釋放變量。 4. 釋放游標(biāo) 游標(biāo)管理與應(yīng)用 游標(biāo)的應(yīng)用 1. 用游標(biāo)修改和刪除表數(shù)據(jù) ? 定位修改游標(biāo)數(shù)據(jù)的語法格式為： UPDATE table_name SET {column_name={expression | DEFAULT | NULL }[, ... n] WHERE CURRENT OF {{[GLOBAL] cursor_name} | cursor_variable_name} ? 刪除游標(biāo)數(shù)據(jù)的語法格式為： DELETE FROM table_name WHERE CURRENT OF {{[GLOBAL] cursor_name} | cursor_variable_name} 游標(biāo)的應(yīng)用 例 937 定義游標(biāo) cur_customer，通過 cur_customer更新 customer表中的 customer_name和linkman_name列。 DECLARE cur_customer CURSOR FOR SELECT * FROM customer FOR UPDATE OF customer_name,linkman_name /*該兩列可更新 */ OPEN cur_customer /*打開 cur_customer游標(biāo) */ FETCH NEXT FROM cur_customer /*將第一行數(shù)據(jù)放入緩沖區(qū)，以便更新操作 */ UPDATE customer SET customer_name=39。南方體育用品公司 39。,linkman_name=39。李強(qiáng) 39。 WHERE CURRENT OF cur_customer CLOSE cur_customer /*關(guān)閉 cur_customer游標(biāo) */ 游標(biāo)的應(yīng)用 ?若要?jiǎng)h除 customer表的一行數(shù)據(jù)，則使用以下命令替換例 937中的 UPDATE語句，就可以刪除通過游標(biāo)讀入的一行數(shù)據(jù)。 DELETE FROM customer WHERE CURRENT OF cur_customer 游標(biāo)的應(yīng)用 2. 使用游標(biāo)變量 ? CURSOR關(guān)鍵字還可以作為變量類型來使用，此時(shí)，必須要將 CURSOR進(jìn)行變量聲明。其語法格式為： DECLARE {@cursor_variable_name CURSOR } [ ,...n] 游標(biāo)的應(yīng)用 游標(biāo)與一個(gè)游標(biāo)變量相關(guān)聯(lián)的方法 (1) 分別定義游標(biāo)變量與游標(biāo)，再將游標(biāo)賦給游標(biāo)變量。 DECLARE @cur_var CURSOR /*定義游標(biāo)變量 */ DECLARE cur_customer CURSOR FOR SELECT * FROM customer /*定義游標(biāo) */ SET @cur_var=cur_customer