PL/SQL遊標

Oracle會創建一個存儲區域，被稱為上下文區域，用於處理SQL語句，其中包含需要處理的語句，例如所有的信息，行數處理，等等。

遊標是指向這一上下文的區域。 PL/SQL通過控製光標在上下文區域。遊標持有的行(一個或多個)由SQL語句返回。行集合光標保持的被稱為活動集合。

可以命名一個光標，它可以在程序中獲取和處理SQL語句，一次返回的行引用。有兩種類型的遊標：

隱式遊標
顯式遊標

隱式遊標

Oracle在一個SQL語句的執行，當冇有顯式遊標的語句隱式遊標時自動創建。程序員無法控製隱式遊標其中的信息。

每當發出一個DML語句（INSERT，UPDATE和DELETE），隱式遊標與此語句關聯。對於INSERT操作時，光標保持一個需要插入的數據。對於UPDATE和DELETE操作，光標標識會受到影響的行。

在PL/SQL，可以參考最近的隱式遊標的SQL遊標，它有類似％FOUND，％ISOPEN，％NOTFOUND，和％ROWCOUNT屬性。在SQL遊標有額外的屬性，％BULK_ROWCOUNT和％BULK_EXCEPTIONS，設計用於所有語句中使用。下表提供了最常用的屬性的描述：

屬性	描述
%FOUND	返回TRUE如果一個INSERT，UPDATE或DELETE語句影響了一行或多行或SELECT INTO語句返回一行或多行。否則，它將返回FALSE。
%NOTFOUND	邏輯相反%FOUND。返回TRUE如果一個INSERT，UPDATE或DELETE語句影響冇有行或SELECT INTO語句返回任何行。否則，它將返回FALSE。
%ISOPEN	隱式遊標總是返回FALSE，因為Oracle執行其相關的SQL語句之後自動關閉SQL遊標。
%ROWCOUNT	返回受INSERT，UPDATE影響的行數，或DELETE語句，或者通過一個SELECT INTO語句返回。

任何SQL遊標屬性將被訪問，SQL％attribute_name如示例圖所示。

例子：

這裡使用我們已經創建，並在前麵的章節中使用的CUSTOMERS表。

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

下麵的程序將更新表，並通過每個客戶的薪水增加500和使用SQL％ROWCOUNT屬性來確定受影響的行數：

DECLARE 
   total_rows number(2);
BEGIN
   UPDATE customers
   SET salary = salary + 500;
   IF sql%notfound THEN
      dbms_output.put_line('no customers selected');
   ELSIF sql%found THEN
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected ');
   END IF; 
END;
/

當上述代碼在SQL提示符執行時，它產生了以下結果：

6 customers selected

PL/SQL procedure successfully completed.

如果查看customers表中的記錄，會發現各行已更新：

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 |
|  2 | Khilan   |  25 | Delhi     |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2500.00 |
|  4 | Chaitali |  25 | Mumbai    |  7000.00 |
|  5 | Hardik   |  27 | Bhopal    |  9000.00 |
|  6 | Komal    |  22 | MP        |  5000.00 |
+----+----------+-----+-----------+----------+

顯式遊標

顯式遊標是程序員定義遊標獲得更多的控製權的上下文的區域。顯式遊標應在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;

例子：

下麵是一個完整的例子來說明明確遊標的概念：

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;
/

當上述代碼在SQL提示符執行時，它產生了以下結果：

1 Ramesh Ahmedabad 
2 Khilan Delhi 
3 kaushik Kota    
4 Chaitali Mumbai 
5 Hardik Bhopal  
6 Komal MP  

PL/SQL procedure successfully completed.