Plsql 简明教程

PL/SQL - Cursors

在本章中,我们将讨论 PL/SQL 中的游标。Oracle 创建一个名为上下文的内存区域以处理 SQL 语句,该上下文中包含处理该语句所需的所有信息;例如,已处理的行数等。

cursor 是此上下文区域的一个指针。PL/SQL 通过游标控制上下文区域。游标保存 SQL 语句返回的行(一个或多个)。游标保存的行集称为 active set

您可以为游标命名,以便在程序中引用它来获取和处理 SQL 语句返回的行,每次一行。有两种类型的游标:

  1. Implicit cursors

  2. Explicit cursors

Implicit Cursors

当为语句没有显式游标时,在执行 SQL 语句时,Oracle 始终自动创建隐式游标。程序员无法控制隐式游标及其信息。

每当发出 DML 语句(INSERT、UPDATE 和 DELETE)时,都会有一个隐式游标与该语句相关联。对于 INSERT 操作,游标保存需要插入的数据。对于 UPDATE 和 DELETE 操作,游标标识将受到影响的行。

在 PL/SQL 中,您可以将最近的隐式游标称为 SQL cursor ,它始终具有 %FOUND, %ISOPEN, %NOTFOUND%ROWCOUNT 等属性。SQL 游标有 %BULK_ROWCOUNT%BULK_EXCEPTIONS 等附加属性,设计用于与 FORALL 语句一起使用。下表提供了最常用属性的说明−

S.No

Attribute & Description

1

如果 INSERT、UPDATE 或 DELETE 语句影响了一行或多行,或 SELECT INTO 语句返回了一行或多行,则返回 TRUE。否则,返回 FALSE。

2

%FOUND 的逻辑相反。如果 INSERT、UPDATE 或 DELETE 语句未影响任何行,或 SELECT INTO 语句未返回任何行,则返回 TRUE。否则,返回 FALSE。

3

对于隐式游标,始终返回 FALSE,因为 Oracle 在执行其关联的 SQL 语句后会自动关闭 SQL 游标。

4

返回受 INSERT、UPDATE 或 DELETE 语句影响的行数,或 SELECT INTO 语句返回的行数。

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

Example

我们将使用我们在前几章创建和使用的 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 |
+----+----------+-----+-----------+----------+

Explicit Cursors

显式游标是程序员定义的游标,用于对 context area 获得更多控制。应在 PL/SQL Block 的声明部分中定义显式游标。它创建在返回多行的 SELECT 语句上。

创建显式游标的语法如下−

CURSOR cursor_name IS select_statement;

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

  1. 声明游标以初始化内存

  2. 打开游标以分配内存

  3. 获取游标以检索数据

  4. 关闭游标以释放分配的内存

Declaring the Cursor

声明游标使用名称和关联的 SELECT 语句定义游标。例如−

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

Opening the Cursor

打开游标为游标分配内存,并使其准备好从 SQL 语句中检索返回的行。例如,我们将打开上面定义的游标,如下所示−

OPEN c_customers;

Fetching the Cursor

获取游标包括一次访问一行。例如,我们将从已打开的游标获取行,如下所示 -

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

关闭游标意味着释放分配的内存。例如,我们将关闭已打开的游标,如下所示 -

CLOSE c_customers;

Example

以下是说明显式游标概念的完整示例;

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.