PL/SQL 游标

在本章中，我们将讨论和学习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语句一起使用。下表提供了游标中最常用属性的描述 -

任何SQL游标属性将被访问为sql％attribute_name，如下例所示。

示例

这里将使用在前几章中创建和使用的CUSTOMERS表，表结构和数据参考： 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) 
);

-- 插入数据
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

以下程序将表中每个客户的工资增加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; 
/
SQL

执行上面示例代码，得到以下结果 -

如果查询了客户表中的记录，会发现工资已更新(增加了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

显式游标

显式游标是用于获得对上下文区域的更多控制的程序员定义的游标。应在PL/SQL块的声明部分中定义一个显式游标。它是在一个返回多行的SELECT语句中创建的。

创建显式游标的语法是 -

CURSOR cursor_name IS select_statement;
SQL

使用显式游标包括以下步骤 -

• 声明游标初始化内存
• 打开游标分配内存
• 从游标获取数据
• 关闭游标以释放分配的内存

声明游标

声明游标使用名称和相关的SELECT语句来定义游标。 例如 -

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

打开游标

打开游标将为游标分配内存，并使其准备好将SQL语句返回的行记录数据提取到其中。例如，打开上面定义的游标，如下所示：

OPEN c_customers;
SQL

获取游标获取游标一次仅访问一行。 例如，从上面打开的游标中获取行，如下所示代码：

FETCH c_customers INTO c_id, c_name, c_addr;
SQL

关闭游标

关闭游标意味着释放分配的内存。例如，关闭上面打开的游标，如下所示：

CLOSE c_customers;
SQL

示例

以下是一个完整的例子来说明显式游标的概念。

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

执行上面示例代码，得到以下结果 -