如何建立和使用 SQL 遊标

目錄

• 一、遊标
• 二、使用遊标2.1 建立遊标2.2 使用遊标2.3 關閉遊标
• 三、小結

本文介紹什麼是 SQL 遊标，為什麼使用遊标，如何使用遊标。你使用的 DBMS 可能會提供某種形式的遊标，以及這裡沒有提及的功能。更詳細的内容請參閱具體的 DBMS 文檔。

一、遊标

SQL 檢索操作傳回一組稱為結果集的行，這組傳回的行都是與 SQL 語句相比對的行（零行到多行）。

簡單地使用 SELECT 語句，沒有辦法得到第一行、下一行或前 10 行。但這是關系 DBMS 功能的組成部分。

結果集（result set）

SQL 查詢所檢索出的結果。

有時，需要在檢索出來的行中前進或後退一行或多行，這就是遊标的用途所在。

遊标（cursor）是一個存儲在 DBMS 伺服器上的資料庫查詢，它不是一條 SELECT 語句，而是被該語句檢索出來的結果集。

在存儲了遊标之後，應用程式可以根據需要滾動或浏覽其中的資料。

說明：SQLite 支援

SQLite 支援的遊标稱為步驟（step），本文講述的基本概念适用于 SQLite 的步驟，但文法可能完全不同。

不同的 DBMS 支援不同的遊标選項和特性。常見的一些選項和特性如下。

• 能夠标記遊标為隻讀，使資料能讀取，但不能更新和删除。
• 能控制可以執行的定向操作（向前、向後、第一、最後、絕對位置和相對位置等）。
• 能标記某些列為可編輯的，某些列為不可編輯的。
• 規定範圍，使遊标對建立它的特定請求（如存儲過程）或對所有請求可通路。
• 訓示 DBMS 對檢索出的資料（而不是指出表中活動資料）進行複制，使資料在遊标打開和通路期間不變化。

遊标主要用于互動式應用，其中使用者需要滾動螢幕上的資料，并對資料進行浏覽或做出更改。

二、使用遊标

使用遊标涉及幾個明确的步驟。

• 在使用遊标前，必須聲明（定義）它。這個過程實際上沒有檢索資料，它隻是定義要使用的 SELECT 語句和遊标選項。
• 一旦聲明，就必須打開遊标以供使用。這個過程用前面定義的 SELECT 語句把資料實際檢索出來。
• 對于填有資料的遊标，根據需要取出（檢索）各行。
• 在結束遊标使用時，必須關閉遊标，可能的話，釋放遊标（有賴于具體的 DBMS）。

聲明遊标後，可根據需要頻繁地打開和關閉遊标。在遊标打開時，可根據需要頻繁地執行取操作。

2.1 建立遊标

使用 DECLARE 語句建立遊标，這條語句在不同的 DBMS 中有所不同。

DECLARE 命名遊标，并定義相應的 SELECT 語句，根據需要帶 WHERE 和其他子句。

為了說明，我們建立一個遊标來檢索沒有電子郵件位址的所有顧客，作為應用程式的組成部分，幫助操作人員找出空缺的電子郵件位址。

下面是建立此遊标的 DB2、MariaDB、MySQL 和 SQL Server 版本。

DECLARE CustCursor CURSORFORSELECT * FROM CustomersWHERE cust_email IS NULL;           

下面是 Oracle 和 PostgreSQL 版本：

DECLARE CURSOR CustCursorISSELECT * FROM CustomersWHERE cust_email IS NULL;           

在上面兩個版本中，DECLARE 語句用來定義和命名遊标，這裡為 CustCursor。SELECT 語句定義一個包含沒有電子郵件位址（NULL 值）的所有顧客的遊标。

定義遊标之後，就可以打開它了。

2.2 使用遊标

使用 OPEN CURSOR 語句打開遊标，這條語句很簡單，在大多數 DBMS 中的文法相同：

OPEN CURSOR CustCursor           

在處理 OPEN CURSOR 語句時，執行查詢，存儲檢索出的資料以供浏覽和滾動。

現在可以用 FETCH 語句通路遊标資料了。FETCH 指出要檢索哪些行，從何處檢索它們以及将它們放于何處（如變量名）。

第一個例子使用 Oracle 文法從遊标中檢索一行（第一行）：

DECLARE TYPE CustCursor IS REF CURSOR    RETURN Customers%ROWTYPE;DECLARE CustRecord Customers%ROWTYPEBEGIN    OPEN CustCursor;    FETCH CustCursor INTO CustRecord;    CLOSE CustCursor;END;           

在這個例子中，FETCH 用來檢索目前行（自動從第一行開始），放到聲明的變量 CustRecord 中。對于檢索出來的資料不做任何處理。

下一個例子（也使用 Oracle 文法）中，從第一行到最後一行，對檢索出來的資料進行循環：

DECLARE TYPE CustCursor IS REF CURSOR    RETURN Customers%ROWTYPE;DECLARE CustRecord Customers%ROWTYPEBEGIN    OPEN CustCursor;    LOOP    FETCH CustCursor INTO CustRecord;    EXIT WHEN CustCursor%NOTFOUND;       ...    END LOOP;    CLOSE CustCursor;END;           

與前一個例子一樣，這個例子使用 FETCH 檢索目前行，放到一個名為 CustRecord 的變量中。

但不一樣的是，這裡的 FETCH 位于 LOOP 内，是以它反複執行。

代碼 EXIT WHEN CustCursor%NOTFOUND 使在取不出更多的行時終止處理（退出循環）。

這個例子也沒有做實際的處理，實際例子中可用具體的處理代碼替換省略号。

下面是另一個例子，這次使用 Microsoft SQL Server 文法：

DECLARE @cust_id CHAR(10),        @cust_name CHAR(50),        @cust_address CHAR(50),        @cust_city CHAR(50),        @cust_state CHAR(5),        @cust_zip CHAR(10),        @cust_country CHAR(50),        @cust_contact CHAR(50),        @cust_email CHAR(255)OPEN CustCursorFETCH NEXT FROM CustCursor    INTO @cust_id, @cust_name, @cust_address,         @cust_city, @cust_state, @cust_zip,         @cust_country, @cust_contact, @cust_email   ...WHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM CustCursor        INTO @cust_id, @cust_name, @cust_address,             @cust_city, @cust_state, @cust_zip,             @cust_country, @cust_contact, @cust_email...ENDCLOSE CustCursor           

在此例中，為每個檢索出的列聲明一個變量，FETCH 語句檢索一行并儲存值到這些變量中。

使用 WHILE 循環處理每一行，條件 WHILE @@FETCH_STATUS = 0 在取不出更多的行時終止處理（退出循環）。

這個例子也不進行具體的處理，實際代碼中，應該用具體的處理代碼替換其中的“...”。

2.3 關閉遊标

如前面幾個例子所述，遊标在使用完畢時需要關閉。此外，SQL Server 等 DBMS 要求明确釋放遊标所占用的資源。

下面是 DB2、Oracle 和 PostgreSQL 的文法。

CLOSE CustCursor           

下面是 Microsoft SQL Server 的版本。

CLOSE CustCursorDEALLOCATE CURSOR CustCursor           

CLOSE 語句用來關閉遊标。一旦遊标關閉，如果不再次打開，将不能使用。第二次使用它時不需要再聲明，隻需用 OPEN 打開它即可。

三、小結

本文介紹了什麼是遊标，為什麼使用遊标。

你使用的 DBMS 可能會提供某種形式的遊标，以及這裡沒有提及的功能。更詳細的内容請參閱具體的 DBMS 文檔。