sql 遊標使用

1. 定義遊標定義 遊標語句的核心是定義了一個遊標標識名，並把遊標標識名和一個查詢語句關聯起來。DECLARE語句用於聲明遊標，它通過SELECT查詢定義遊標儲存的資料集合。語句格式為： DECLARE 遊標名稱 [INSENSITIVE] [SCROLL] CURSOR FOR select語句 [FOR{READ ONLY|UPDATE[OF 列名字表]}] 參數說明： INSENSITIVE選項：說明所定義的遊標使用SELECT語句查詢結果的拷貝，對遊標的操作都基於該拷貝進行。因此，這期間對遊標基本表的資料修改不能反映到遊標中。這種遊標也不允許通過它修改基本表的資料。 SCROLL選項：指定該遊標可用所有的遊標資料定位方法提取資料，遊標定位方法包括PRIOR、FIRST、LAST、ABSOLUTE n 和RELATIVE n 選項。 Select語句：為標準的SELECT查詢語句，其查詢結果為遊標的資料集合，構成遊標資料集合的一個或多個表稱作遊標的基表。 在遊標聲明語句中，有下列條件之一時，系統自動把遊標定義為INSENSITIVE遊標： SELECT語句中使用了DISTINCT、UNION、 GROUP BY或HAVING等關鍵字； 任一個遊標基表中不存在唯一索引。 其他 READ ONLY選項：說明定義唯讀遊標。 UPDATE [OF 列名字表]選項：定義遊標可修改的列。如果使用OF 列名字表選項，說明只允許修改所指定的列，否則，所有列均可修改。 例如，查詢教師名字和所教的課程名，定義遊標TCURSOR的語句如下 ： DECLARE TCURSOR CURSOR FOR SELECT tname, cname FROM teacher ,couse WHERE teacher.tno = couse.tno 2. 開啟遊標 開啟遊標語句執行遊標定義中的查詢語句，查詢結果存放在遊標緩衝區中。並使遊標指標指向遊標區中的第一個元組，作為遊標的預設訪問位置。查詢結果的內容取決與查詢語句的設定和查詢條件。 開啟遊標的語句格式： EXEC SQL OPEN 〈遊標名〉 如果開啟的遊標為INSENSITIVE遊標，在開啟時將產生一個暫存資料表，將定義的遊標資料集合從其基表中拷貝過來。 SQL Server中,遊標開啟後，可以從全域變數@@CURSOR_ROWS中讀取遊標結果集合中的行數。 例1：開啟前面所建立的查詢教師姓名和所教課名稱的遊標。 OPEN tcursor 例2：顯示遊標結果集合中資料行數 SELECT 資料行數 = @@CURSOR_ROWS 3. 讀遊標區中的當前元組 讀遊標區資料語句是讀取遊標區中當前元組的值，並將各分量依次賦給指定的共用主變數。FETCH語句用於讀取遊標中的資料，語句格式為： FETCH [[NEXT|PRIOR|FIRST|LAST| ABSOLUTE n| RELATIVE n] FROM ] 遊標名 [INTO @變數1, @變數2, ….] 其中： NEXT：說明讀取遊標中的下一行，第一次對遊標實行讀取操作時，NEXT返回結果集合中的第一行。 PRIOR、FIRST、LAST、ABSOLUTE n 和RELATIVE n 選項只適用於SCROLL遊標。它們分別說明讀取遊標中的上一行、第一行、最後一行、第n 行和相對於當前位置的第n 行。n 為負值時，ABSOLUTE n 和RELATIVE n 說明讀取從遊標結果集合中的最後一行或當前行倒數n行的資料。 INTO子句 說明將讀取的資料存放到指定的局部變數中，每一個變數的資料類型應與遊標所返回的資料類型嚴格匹配，否則將產生錯誤。 如果遊標區的元組已經讀完，那麼系統狀態變數SQLSTATE的值被設為02000，意為"no tuple found"。 例如，讀取tcursor中當前位置後的第二行資料 FETCH RELATIVE 2 FROM tcursor 4. 利用遊標修改資料 SQL Server中的 UPDATE語句 和 DELETE語句也支援遊標操作，它們可以通過遊標修改或刪除遊標基表中的當前資料行。 UPDATE語句的格式為： UPDATE table_name SET 列名=運算式}[,…n] WHERE CURRENT OF cursor_name DELETE語句的格式為： DELETE FROM table_name WHERE CURRENT OF cursor_name 說明: CURRENT OF cursor_name： 表示當前遊標指標所指的當前行資料。CURRENT OF 只能在UPDATE和DELETE語句中使用。 注意： o使用遊標修改基表資料的前提是聲明的遊標是可更新的。 o 對相應的資料庫物件（遊標的基表）有修改和刪除許可權。 5. 關閉遊標 關閉遊標後，遊標區的資料不可再讀。CLOSE語句關閉已開啟的遊標，之後不能對遊標進行讀取等操作，但可以使用OPEN語句再次開啟該遊標。 CLOSE語句的格式為： CLOSE 遊標名 例如：關閉tcursor遊標如下描述： CLOSE tcursor 6 刪除遊標語句 DEALLOCATE語句刪除定義遊標的資料結構，刪除後不可再用。語句格式： DEALLOCATE 遊標名 例如，刪除tcursor遊標 DEALLOCATE tcursor 例1,讀JS2001班的學生的學號和姓名： #define MAX 30 EXEC SQL BEGIN DECLARE SECTION; char TN[12], FU[20]； //定義主變數// EXEC SQL END DECLARE SECTION; char tarn1[30][12],tarn2[30][20]； //定義 C 變數// . . . . . . EXEC SQL //執行SQL語句，定義遊標// DCLARE Scursor CURSOR FOR //聲明遊標Scursor// SELECT sno,sname //查詢sno,sname// FROM student //對student表chax 查詢// WHERE sclass = 'JS2001'； //sclass＝'JS2001'的班級// EXEC SQL OPEN Scursor ； //開啟遊標// for (i=0; i<MAX; i++) { EXEC SQL FETCH FROM Scursor INTO @TN, @FU； //取到宿主變數// tarn1= TN; //賦值到C陣列變數// tarn2= FU; } .................... EXEC SQL CLOSE Scursor ； //關閉遊標// EXEC SQL DEALLOCATE Scursor ； //刪除遊標// 例2，定義遊標，使結果集包括 pubs 資料庫的 authors 表中的所有行和列。因為沒指定 SCROLL 選項，FETCH NEXT 是唯一可用的提取選項。 DECLARE authors_cursor CURSOR FOR //聲明遊標authors_cursor // SELECT * FROM authors OPEN authors_cursor //開啟遊標authors_cursor // FETCH NEXT FROM authors_cursor //讀遊標authors_cursor中的一行 // .................... 例3, 定義和使用滾動遊標 DECLARE tcursor SCROLL CURSOR FOR SELECT tname, cname FROM teacher ,couse WHERE teacher.tno = couse.tno; OPEN tcursor; 取tcursor中當前位置向下的第二行資料 FETCH RELATIVE 2 FROM tcursor 取tcursor中最後一行資料 FETCH LAST FROM tcursor 取tcursor中當前位置向前的第4行資料 FETCH RELATIVE -4 FROM tcursor 例4 利用 @@FETCH_STATUS 控制一個 WHILE 迴圈中的遊標活動。 DECLARE S_Cursor CURSOR FOR SELECT sname, sno FROM student OPEN S_Cursor FETCH NEXT FROM S_Cursor WHILE @@FETCH_STATUS = '000000' BEGIN FETCH NEXT FROM S_Cursor END CLOSE S_Cursor DEALLOCATE S_Cursor 例5，職工普調工資，從最低工資調起，每人工資長10%，但工資總額不能超過50萬元。當調完某個職工工資後，如果工資總額達到或超過50萬元，就不再調了，另外，如果職工全部調了一遍，工資總額還沒到50萬元，也到此為止。現有職工表emp，定義了一個遊標cl,遊標的查詢語句取出職工號和工資值，並按增序排列。下面是程式碼： void addsalary() { EXEC SQL BEGIN DECLARE SECTION //聲明SQL變數// char empno[8], e_sno，SQLSTATE[6]； float s_sal, e_sal； EXEC SQL END DECLARE SECTION; //聲明SQL變數完// EXEC SQL DECLARE cl CURSOR FOR //定義遊標 cl ,對emp表的eno,sal列可以作任何操作 SELECT eno, sal FROM emp ORDER BY sal ASC; EXEC SQL OPEN cl; //開啟遊標/ EXEC SQL SELECT SUM(sal) INTO @s_sal FROM emp; //取工資總和到s_sal while(s_sal < 500000.00) { EXEC SQL FETCH FROM cl INTO @e_sno , @e_sal; //從遊標中讀出的資料放入e_sno,e_sal變數 If(SQLSTATE='02000') BREAK; //讀完記錄，退出// EXEC SQL UPDATE emp SET sal=sal*1.1 //遊標內容更新，將empno=e_sno相應的sal*1.1 WHERE empno=@e_eno; s_sal = s_sal+ e_sal *0.1; //計算工資總和到s_sal }; EXEC SQL CLOSE cl; /關閉遊標/} SQL Server提供兩種遊標應用介面方法：第一種是符合ANSI標準的SQL遊標語句，它們可以實現聲明、開啟、讀取或關閉遊標操作，這些語句可用在Transact_SQL語句或預存程序內；第二種是庫函數形式，用戶端的DB_Library 或ODBC應用程式可以調用這些函數。 遊標語句增強了Transact_SQL對集合資料的處理能力，在SQL Server中，通過遊標還可以修改或刪除基表中的資料。 7，使用遊標時應注意的問題： (1) 儘管使用遊標比較靈活，可以實現對資料集中單行資料的直接操作，但遊標會在下面幾個方面影響系統的效能： -使用遊標會導致頁鎖與表鎖的增加 -導致網路通訊量的增加 -增加了伺服器處理相應指令的額外開銷 (2) 使用遊標時的最佳化問題： -明確指出遊標的用途：for read only或for update -在for update後指定被修改的列