Plsql 简明教程

PL/SQL - Cursors

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

In this chapter, we will discuss the cursors in PL/SQL. Oracle creates a memory area, known as the context area, for processing an SQL statement, which contains all the information needed for processing the statement; for example, the number of rows processed, etc.

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

A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.

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

You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors −

Implicit cursors
Explicit cursors

Implicit Cursors

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

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.

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

Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

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

In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes −

S.No	Attribute & Description
1	%FOUND Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
2	%NOTFOUND The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
3	%ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
4	%ROWCOUNT Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

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

Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example.

Example

我们将使用我们在前几章创建和使用的 CUSTOMERS 表。

We will be using the CUSTOMERS table we had created and used in the previous chapters.

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 属性来确定受影响的行数−

The following program will update the table and increase the salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected −

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 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

6 customers selected

PL/SQL procedure successfully completed.

如果您查看 customers 表中的记录，您会发现这些行已更新−

If you check the records in customers table, you will find that the rows have been updated −

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 语句上。

Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

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

The syntax for creating an explicit cursor is −

CURSOR cursor_name IS select_statement;

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

Working with an explicit cursor includes the following steps −

Declaring the cursor for initializing the memory
Opening the cursor for allocating the memory
Fetching the cursor for retrieving the data
Closing the cursor to release the allocated memory

Declaring the Cursor

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

Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example −

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

Opening the Cursor

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

Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open the above defined cursor as follows −

OPEN c_customers;

Fetching the Cursor

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

Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from the above-opened cursor as follows −

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

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

Closing the cursor means releasing the allocated memory. For example, we will close the above-opened cursor as follows −

CLOSE c_customers;

Example

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

Following is a complete example to illustrate the concepts of explicit cursors &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 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP

PL/SQL procedure successfully completed.