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. 
廣告