PL/SQL游標


在本章中,我們將討論和學習PL/SQL中的游標。 Oracle建立一個稱為上下文區域的記憶體區域,用於處理SQL語句,它包含處理該語句所需的所有資訊; 例如,處理的行數等。

游標是指向此上下文區域的指標。PL/SQL通過游標控制上下文區域,游標儲存SQL語句返回的行(一個或多個)。 游標所在的行集稱為活動集。

可以命名一個游標,以便在程式中參照它來獲取和處理SQL語句返回的行,一次處理一個(行)。PL/SQL中有兩種型別的游標 -

  • 隱式游標
  • 顯式游標

隱式游標

當執行SQL語句時,如果語句沒有顯式游標,則Oracle會自動建立隱式游標。程式員無法控制隱式游標及其資訊。

每當發出DML語句(INSERTUPDATEDELETE)時,隱式游標與此語句相關聯。 對於INSERT操作,游標儲存需要插入的資料。對於UPDATEDELETE操作,游標標識將受到影響的行。

在PL/SQL中,可以將最近的隱式游標參照為SQL游標,它始終具有%FOUND%ISOPEN%NOTFOUND%ROWCOUNT等屬性。 SQL游標具有額外的屬性%BULK_ROWCOUNT%BULK_EXCEPTIONS,旨在與FORALL語句一起使用。下表提供了游標中最常用屬性的描述 -

編號 屬性 描述
1 %FOUND 如果INSERTUPDATEDELETE語句影響一行或多行,或老兄SELECT INTO語句返回一行或多行,則返回TRUE,否則返回FALSE
2 %NOTFOUND %FOUND的邏輯相反。 如果INSERT,UPDATE或DELETE語句沒有影響任何行,或SELECT INTO語句未返回任何行,則返回TRUE。 否則返回FALSE。
3 %ISOPEN 由於Oracle在執行關聯的SQL語句後會自動關閉SQL游標,因此總是為隱式游標返回FALSE
4 %ROWCOUNT 返回受INSERTUPDATEDELETE語句,或者受SELECT INTO語句影響的行數。

任何SQL游標屬性將被存取為sql%attribute_name,如下例所示。

範例

這裡將使用在前幾章中建立和使用的CUSTOMERS表,表結構和資料參考: /18/144/4212.htmlplsql_variable_types.html

CREATE TABLE CUSTOMERS( 
   ID   INT NOT NULL, 
   NAME VARCHAR (20) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR (25), 
   SALARY   DECIMAL (18, 2),        
   PRIMARY KEY (ID) 
);

-- 插入資料
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

以下程式將表中每個客戶的工資增加500,並使用SQL%ROWCOUNT屬性來確定受影響的行數 -

SET SERVEROUTPUT ON SIZE 99999;
DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE customers 
   SET salary = salary + 500; 
   IF sql%notfound THEN 
      dbms_output.put_line('沒有找到客戶資訊~'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line('一共有:' || total_rows || ' 個客戶的工資被更新! '); 
   END IF;  
END; 
/

執行上面範例程式碼,得到以下結果 -

如果查詢了客戶表中的記錄,會發現工資已更新(增加了500) -

SQL> select id,name,salary from customers;

        ID NAME                     SALARY
---------- -------------------- ----------
         1 Ramesh                     2500
         2 Khilan                     2000
         3 kaushik                    2500
         4 Chaitali                   7000
         5 Hardik                     9000
         6 Komal                      5000

顯式游標

顯式游標是用於獲得對上下文區域的更多控制的程式員定義的游標。應在PL/SQL塊的宣告部分中定義一個顯式游標。它是在一個返回多行的SELECT語句中建立的。

建立顯式游標的語法是 -

CURSOR cursor_name IS select_statement;

使用顯式游標包括以下步驟 -

  • 宣告游標初始化記憶體
  • 開啟游標分配記憶體
  • 從游標獲取資料
  • 關閉游標以釋放分配的記憶體

宣告游標

宣告游標使用名稱和相關的SELECT語句來定義游標。 例如 -

CURSOR c_customers IS 
   SELECT id, name, address FROM customers;

開啟游標

開啟游標將為游標分配記憶體,並使其準備好將SQL語句返回的行記錄資料提取到其中。例如,開啟上面定義的游標,如下所示:

OPEN c_customers;

獲取游標
獲取游標一次僅存取一行。 例如,從上面開啟的游標中獲取行,如下所示程式碼:

FETCH c_customers INTO c_id, c_name, c_addr;

關閉游標

關閉游標意味著釋放分配的記憶體。例如,關閉上面開啟的游標,如下所示:

CLOSE c_customers;

範例

以下是一個完整的例子來說明顯式游標的概念。

SET SERVEROUTPUT ON SIZE 99999;
DECLARE 
   c_id customers.id%type; 
   c_name customers.name%type; 
   c_addr customers.address%type; 
   CURSOR c_customers is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN c_customers; 
   LOOP 
   FETCH c_customers into c_id, c_name, c_addr; 
      EXIT WHEN c_customers%notfound; 
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
   END LOOP; 
   CLOSE c_customers; 
END; 
/

執行上面範例程式碼,得到以下結果 -