Database Info.: 12. PL/SQL Cursor

Oracle會建置一個存儲區域，被稱為上下文區域，用於處理SQL語法，其中Package含需要處理的SQL語法。有兩種類型的Cursor：

顯式Cursor

顯式Cursor是由程式開發人員定義Cursor可取得更多的控製權的SQL語法區域。顯式Cursor應在PL/SQL區塊的宣告部分中定義。這是建置一個SELECT語法回傳多筆資料。

建置顯式Cursor語法是：

CURSOR cursor_name IS select_statement;

使用顯式Cursor的工作Package括四個步驟：

宣告(Declare)Cursor

宣告Cursor定義Cursor的名稱和相關的SELECT語法。例如：

CURSOR c_customers IS

   SELECT id, name, address FROM customers;

打開(Open)Cursor

打開CursorCursor分配內存，使得它準備取的SQL語法轉換成它回傳的行。例如，我們將打開上述定義的Cursor如下：

OPEN c_customers;

獲取(Fitch)Cursor

獲取Cursor一次存取一筆資料。例如，將取得的資料從上面打開的Cursor，如下所示：

FETCH c_customers INTO c_id, c_name, c_addr;

關閉(Close)Cursor

關閉Cursor來釋放分配的記憶體。例如，將關閉前面打開的Cursor，如下所示：

CLOSE c_customers;

範例：

下面是一個完整的例子來說明明確Cursor的概念：

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;

產生了以下結果：

1 Ramesh Ahmedabad

2 Khilan Delhi

3 kaushik Kota

4 Chaitali Mumbai

5 Hardik Bhopal

6 Komal MP

PL/SQL procedure successfully completed.

隱式Cursor

Oracle在一個SQL語法的執行，隱式Cursor時自動建置。程式開發人員無法控製隱式Cursor其中的資訊。下面表格提供了最常用的屬性的描述：

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

任何SQL Cursor屬性將被訪問，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;

結果：

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 |

+----+----------+-----+-----------+----------+

Database Info.