PL/SQL 游標(biāo)

在本章中，我們將討論和學(xué)習(xí)PL/SQL中的游標(biāo)。 Oracle創(chuàng)建一個稱為上下文區(qū)域的內(nèi)存區(qū)域，用于處理SQL語句，它包含處理該語句所需的所有信息; 例如，處理的行數(shù)等。

游標(biāo)是指向此上下文區(qū)域的指針。PL/SQL通過游標(biāo)控制上下文區(qū)域，游標(biāo)保存SQL語句返回的行(一個或多個)。 游標(biāo)所在的行集稱為活動集。

可以命名一個游標(biāo)，以便在程序中引用它來獲取和處理SQL語句返回的行，一次處理一個(行)。PL/SQL中有兩種類型的游標(biāo) -

• 隱式游標(biāo)
• 顯式游標(biāo)

隱式游標(biāo)

當(dāng)執(zhí)行SQL語句時，如果語句沒有顯式游標(biāo)，則Oracle會自動創(chuàng)建隱式游標(biāo)。程序員無法控制隱式游標(biāo)及其信息。

每當(dāng)發(fā)出DML語句(INSERT，UPDATE和DELETE)時，隱式游標(biāo)與此語句相關(guān)聯(lián)。 對于INSERT操作，游標(biāo)保存需要插入的數(shù)據(jù)。對于UPDATE和DELETE操作，游標(biāo)標(biāo)識將受到影響的行。

在PL/SQL中，可以將最近的隱式游標(biāo)引用為SQL游標(biāo)，它始終具有%FOUND，%ISOPEN，%NOTFOUND和%ROWCOUNT等屬性。 SQL游標(biāo)具有額外的屬性%BULK_ROWCOUNT和%BULK_EXCEPTIONS，旨在與FORALL語句一起使用。下表提供了游標(biāo)中最常用屬性的描述 -

任何SQL游標(biāo)屬性將被訪問為sql％attribute_name，如下例所示。

示例

這里將使用在前幾章中創(chuàng)建和使用的CUSTOMERS表，表結(jié)構(gòu)和數(shù)據(jù)參考： http://www.yiibai.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) 
);

-- 插入數(shù)據(jù)
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 );
SQL

以下程序?qū)⒈碇忻總€客戶的工資增加500，并使用SQL%ROWCOUNT屬性來確定受影響的行數(shù) -

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

執(zhí)行上面示例代碼，得到以下結(jié)果 -

如果查詢了客戶表中的記錄，會發(fā)現(xiàn)工資已更新(增加了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
SQL

顯式游標(biāo)

顯式游標(biāo)是用于獲得對上下文區(qū)域的更多控制的程序員定義的游標(biāo)。應(yīng)在PL/SQL塊的聲明部分中定義一個顯式游標(biāo)。它是在一個返回多行的SELECT語句中創(chuàng)建的。

創(chuàng)建顯式游標(biāo)的語法是 -

CURSOR cursor_name IS select_statement;
SQL

使用顯式游標(biāo)包括以下步驟 -

• 聲明游標(biāo)初始化內(nèi)存
• 打開游標(biāo)分配內(nèi)存
• 從游標(biāo)獲取數(shù)據(jù)
• 關(guān)閉游標(biāo)以釋放分配的內(nèi)存

聲明游標(biāo)

聲明游標(biāo)使用名稱和相關(guān)的SELECT語句來定義游標(biāo)。 例如 -

CURSOR c_customers IS 
   SELECT id, name, address FROM customers;
SQL

打開游標(biāo)

打開游標(biāo)將為游標(biāo)分配內(nèi)存，并使其準(zhǔn)備好將SQL語句返回的行記錄數(shù)據(jù)提取到其中。例如，打開上面定義的游標(biāo)，如下所示：

OPEN c_customers;
SQL

獲取游標(biāo)獲取游標(biāo)一次僅訪問一行。 例如，從上面打開的游標(biāo)中獲取行，如下所示代碼：

FETCH c_customers INTO c_id, c_name, c_addr;
SQL

關(guān)閉游標(biāo)

關(guān)閉游標(biāo)意味著釋放分配的內(nèi)存。例如，關(guān)閉上面打開的游標(biāo)，如下所示：

CLOSE c_customers;
SQL

示例

以下是一個完整的例子來說明顯式游標(biāo)的概念。

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

執(zhí)行上面示例代碼，得到以下結(jié)果 -