
- PL/SQL 教程
- PL/SQL - 首頁
- PL/SQL - 概述
- PL/SQL - 環境
- PL/SQL - 基本語法
- PL/SQL - 資料型別
- PL/SQL - 變數
- PL/SQL - 常量和文字
- PL/SQL - 運算子
- PL/SQL - 條件
- PL/SQL - 迴圈
- PL/SQL - 字串
- PL/SQL - 陣列
- PL/SQL - 過程
- PL/SQL - 函式
- PL/SQL - 遊標
- PL/SQL - 記錄
- PL/SQL - 異常
- PL/SQL - 觸發器
- PL/SQL - 包
- PL/SQL - 集合
- PL/SQL - 事務
- PL/SQL - 日期和時間
- PL/SQL - DBMS 輸出
- PL/SQL - 面向物件
- PL/SQL 有用的資源
- PL/SQL - 問題與解答
- PL/SQL - 快速指南
- PL/SQL - 有用的資源
- PL/SQL - 討論
PL/SQL - 遊標
在本章中,我們將討論 PL/SQL 中的遊標。Oracle 為處理 SQL 語句建立一個稱為上下文區域的記憶體區域,其中包含處理該語句所需的所有資訊,例如處理的行數等。
遊標是指向此上下文區域的指標。PL/SQL 透過遊標來控制上下文區域。遊標儲存由 SQL 語句返回的行(一個或多個)。遊標所儲存的這組行稱為活動集。
你可以命名遊標,以便在程式中引用它來一次獲取 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 對於隱式遊標始終返回 FALSE,因為 Oracle 會在執行其關聯的 SQL 語句後自動關閉 SQL 遊標。 |
4 | %ROWCOUNT 返回 INSERT、UPDATE 或 DELETE 語句影響的行數,或者 SELECT INTO 語句返回的行數。 |
可以像下面示例中所示,透過sql%attribute_name來訪問任何 SQL 遊標屬性。
示例
我們將使用我們在前幾章中建立和使用的 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;
示例
以下是一個完整的示例,用以說明顯式遊標 &minua 的概念;
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.