PL/SQL游標 - tw511教學網

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

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

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

隱式游標
顯式游標

隱式游標

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

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

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

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

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