Oracle PL/SQL – 游标
简述
在本章中，我们将讨论 PL/SQL 中的游标。Oracle 为处理一条SQL 语句创建了一个内存区，称为上下文区，其中包含了处理该语句所需的所有信息；例如，处理的行数等。
游标是指向该上下文区域的指针。PL/SQL 通过游标控制上下文区域。游标保存 SQL 语句返回的行（一个或多个）。游标保存的行集称为active set.
您可以命名一个游标，以便在程序中引用它来获取和处理 SQL 语句返回的行，一次一个。有两种类型的游标 -
- 隐式游标
- 显式游标
隐式游标
每当执行 SQL 语句时，如果该语句没有显式游标，Oracle 就会自动创建隐式游标。程序员无法控制隐式游标及其中的信息。
每当发出 DML 语句（INSERT、UPDATE 和 DELETE）时，隐式游标都与该语句相关联。对于 INSERT 操作，游标保存需要插入的数据。对于 UPDATE 和 DELETE 操作，游标标识将受到影响的行。
在 PL/SQL 中，您可以将最近的隐式游标称为 SQL cursor，它总是具有诸如 %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 游标属性都将作为 sql%attribute_name 如下例所示。
例子
我们将使用我们在前几章中创建和使用的 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.
如果您检查客户表中的记录，您会发现行已更新 -
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 |
+----+----------+-----+-----------+----------+
显式游标
显式游标是程序员定义的游标，用于更好地控制 context area. 应在 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.