PL/SQL 游标

2025 年 1 月 30 日 | 阅读 11 分钟

当处理 SQL 语句时，Oracle 会创建一个称为上下文区域的内存区域。游标是指向此上下文区域的指针。它包含处理语句所需的所有信息。在 PL/SQL 中，上下文区域由游标控制。游标包含有关 select 语句及其访问的数据行的信息。

游标用于引用程序以提取和处理 SQL 语句返回的行，一次一行。游标有两种类型

隐式游标
显式游标

1) PL/SQL 隐式游标

如果您没有为 SQL 语句使用显式游标，则在执行 SQL 语句时，Oracle 会自动生成隐式游标。

这种类型的游标处理许多与游标相关的功能，例如

将区域保存在内存中
使用相关数据填充此区域
处理内存区域中的数据
在处理完成后释放内存区域

默认情况下，创建这些游标以处理 DML 语句（如 INSERT、UPDATE、DELETE 等）的执行。

Oracle 提供了一些称为隐式游标属性的属性来检查 DML 操作的状态。其中一些是： %FOUND、%NOTFOUND、%ROWCOUNT 和 %ISOPEN。

例如：当您执行 INSERT、UPDATE、DELETE 等 SQL 语句时，游标属性会告诉您是否影响了任何行以及影响了多少行。如果您在 PL/SQL 块中运行 SELECT INTO 语句，则可以使用隐式游标属性来确定 SELECT 语句是否返回了任何行。如果没有选择任何数据，它将返回错误。

下表指定了每个属性的游标状态。

属性	描述
%FOUND	如果 INSERT、DELETE 和 UPDATE 等 DML 语句影响至少一行或更多行，或者 SELECT INTO 语句返回一行或更多行，则其返回值是 TRUE。否则，它将返回 FALSE。
%NOTFOUND	如果 INSERT、DELETE 和 UPDATE 等 DML 语句未影响任何行，或者 SELECT INTO 语句未返回任何行，则其返回值是 TRUE。否则，它将返回 FALSE。它只是 %FOUND 的反面。
%ISOPEN	对于隐式游标，它总是返回 FALSE，因为 SQL 游标在执行其关联的 SQL 语句后会自动关闭。
%ROWCOUNT	它返回受 INSERT、DELETE 和 UPDATE 等 DML 语句影响的行数，或者由 SELECT INTO 语句返回的行数。

PL/SQL 隐式游标示例

创建 customers 表并拥有记录

ID	姓名	年龄	地址	工资
1	Ramesh	23	Allahabad	20000
2	Suresh	22	Kanpur	22000
3	Mahesh	24	Ghaziabad	24000
4	Chandan	25	Noida	26000
5	Alex	21	巴黎	28000
6	Sunita	20	德里	30000

让我们执行以下程序来更新表并将每个客户的工资增加 5000。在这里，SQL%ROWCOUNT 属性用于确定受影响的行数

创建过程

DECLARE 
   total_rows number(2);
BEGIN
   UPDATE  customers
   SET salary = salary + 5000;
   IF sql%notfound THEN
      dbms_output.put_line('no customers updated');
   ELSIF sql%found THEN
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers updated ');
   END IF; 
END;
/

输出

6 customers updated
PL/SQL procedure successfully completed.

现在，如果您检查 customer 表中的记录，您会发现这些行已更新。

ID	姓名	年龄	地址	工资
1	Ramesh	23	Allahabad	25000
2	Suresh	22	Kanpur	27000
3	Mahesh	24	Ghaziabad	29000
4	Chandan	25	Noida	31000
5	Alex	21	巴黎	33000
6	Sunita	20	德里	35000

示例 2

创建一个 PL/SQL 程序来检查给定员工编号是否存在记录。如果是，则检索他的工资。

SET SERVEROUTPUT ON
SET VERIFY OFF
Accept ‘enter the employee no=’
DECLARE
Salary number (5);
BEGIN
Select sal into salary from emp where empno = &empno;
      IF SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE(‘Record is found’);
DBMS_OUTPUT.PUT_LINE(‘Salary = ‘ || Salary);
   END IF;
   EXCEPTION
WHEN NO_DATA_FOUND then
      DBMS_OUTPUT.PUT_LINE(‘The Employee Record is not found’);
END;
/

说明

执行上述代码时，它会要求您输入员工编号。如果找到该记录，则 SQL%FOUND 变为 TRUE，并且执行 IF 子句中的相应语句。如果该语句为 FALSE，则控制权将转移到异常部分。

将上面的 PL/SQL 程序存储在脚本文件中，然后使用 @command 执行它，否则它可能无法正确执行。

示例 3

创建 PL/SQL 程序来更新员工的工资。使用 SQL%NOTFOUND 属性来检查工资是否已更新？

SET SERVEROUTPUT ON
BEGIN
Update emp_data set sal = sal +100 where empno = &empno;
      IF SQL%NOTFOUND THEN
      DBMS_OUTPUT.PUT_LINE(‘Record not found’);
ELSE 
DBMS_OUTPUT.PUT_LINE(‘Record is updated);
   END IF;
      END;
/

隐式游标的局限性

与显式游标相比，它的效率较低。
至少执行两次提取以查看它是否包含多个行。
它更容易出现数据错误。
它为您提供的程序控制较少。

2) PL/SQL 显式游标

显式游标由程序员定义，以便更好地控制上下文区域。这些游标应在 PL/SQL 块的声明部分中定义。它是根据 SELECT 语句创建的，该语句返回多行。

以下是创建显式游标的语法

显式游标的语法

以下是创建显式游标的语法

步骤：

在使用显式游标时，必须遵循以下步骤。

声明游标以在内存中初始化。
打开游标以分配内存。
提取游标以检索数据。
关闭游标以释放已分配的内存。

1) 声明游标

它使用名称和关联的 SELECT 语句定义游标。每个游标都必须在使用前在 PL/SQL 块的声明段中声明。声明游标意味着给它一个名称并指定游标与之关联的 SELECT 语句。此时没有内存分配。 PL/SQL 块的声明段可以一次声明多个游标，但名称不同。

显式游标声明的语法

CURSOR name IS
 SELECT statement; 

2) 打开游标

它用于为游标分配内存，并使其易于将 SQL 语句返回的行提取到其中。声明后，使用 OPEN 语句打开游标以处理游标中的行。

打开游标时，实际的内存分配到游标，这对于处理信息至关重要。解析与游标相关的 SELECT 语句。它还会定位活动数据集，即声明给游标的所有包含的行，并在打开游标时创建。执行 OPEN 语句时，不会从活动数据集中检索行。相反，FETECH 语句会检索行。它还将指针放置在活动集中第一行之前。

游标打开的语法

3) 提取游标

打开游标后，将识别活动数据集并执行与游标相关的 SELECT 语句。 FETCH 语句一次从活动数据集中检索一行。在执行每个 FETCH 语句之后，游标指针移动到活动数据集中的下一行并检索它。

它用于一次访问一行。您可以按如下方式从上面打开的游标中提取行

游标提取的语法

在上述语法中，cursor_name 是从中检索记录的游标的名称，variable_list 是分配列值的变量的名称。对于游标相关的查询返回的每个列名，INTO 测试中必须有一个相应的变量。变量数据类型必须与相应的数据库列匹配。循环结构用于将记录从游标检索到变量中，一次一行。最常用的循环结构是 LOOP-END 循环。您还可以使用 %ROWTYPE 属性将记录提取到记录结构中。

4) 关闭游标

在处理完游标中的行之后，可以使用 CLOSE 语句释放它。 CLOSE 语句关闭游标，并且未定义活动数据集。它用于释放已定义的内存。关闭游标后，您可以重新打开游标以进行进一步处理，可能使用不同的参数值来创建新的活动数据集。

以下语法用于关闭上面打开的游标

游标关闭的语法

PL/SQL 显式游标示例

显式游标由程序员定义，以便更好地控制上下文区域。它在 PL/SQL 块的声明部分中定义。它是根据 SELECT 语句创建的，该语句返回多行。

让我们举一个例子来演示显式游标的使用。在此示例中，我们使用已创建的 CUSTOMERS 表。

创建 customers 表并拥有记录

ID	姓名	年龄	地址	工资
1	Ramesh	23	Allahabad	20000
2	Suresh	22	Kanpur	22000
3	Mahesh	24	Ghaziabad	24000
4	Chandan	25	Noida	26000
5	Alex	21	巴黎	28000
6	Sunita	20	德里	30000

PL/SQL 显式游标属性

PL/SQL 显式游标属性用于检索有关显式游标状态的信息。显式游标具有与隐式游标属性相同的属性集。这些属性可以在 PL/SQL 语句中访问，而不仅仅是 SQL 语句属性。您可以使用给定的语法引用这些属性

在上面的语法中，cursorname 是您输入的游标的名称。

各种显式游标属性为

<游标名称> %FOUND：如果未成功检索到记录，则返回 TRUE，否则返回 FALSE。
<游标名称> %NOTFOUND：如果成功检索到记录，则返回 TRUE，否则返回 FALSE。
<游标名称> %ROWCOUNT：返回当时从指定游标检索到的记录数。
<游标名称> %ISOPEN：如果指定的游标未打开，则返回 FALSE，否则返回 TRUE。

使用显式游标的游标属性时，应注意以下几点

如果您尝试在打开游标之前或关闭游标之后使用 %FOUND、%NOTFOUND，则会导致 INVALID_CURSOR 错误。
如果活动数据集在第一次提取后为空，则属性将返回值，例如 %FOUND = FALSE、%NOTFOUND = TRUE 和 %ROWCOUNT = 0。

示例 1

编写一个 PL/SQL 程序以使用 %ROWTYPE 的变量显示部门名称和位置。

SET SERVEROUTPUT ON;
DECLARE
   CURSOR d_cursor IS
   SELECT * from Dept;
   D_Record Dept % ROWTYPE;
BEGIN
   OPEN d_cursor;
   LOOP
      FETCH d_cursor INTO D_Record;
      EXIT WHEN d_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(D_Record.Dname || '    ' || D_Record.Loc);
   END LOOP;
   CLOSE d_cursor;
END;
/

说明

在执行上述代码时，使用 %ROWTYPE 属性，我们不需要为每一列声明变量。无论何时要处理特定列的值，我们都可以使用点运算符访问它。

例如： D_Record.Dname。因此，它减少了开销。

示例 2

编写一个 PL/SQL 程序，以增加 EMP 表中员工的工资，其中经理的工资增加 10%，职员的工资增加 8%，销售员的工资增加 5%。

SET SERVEROUTPUT ON;
DECLARE
   CURSOR sal_cursor IS
   SELECT eno, job from emp_data;
   Post emp_data.job%TYPE;
  Ecode emp_data.eno%TYPE;
BEGIN
   OPEN sal_cursor;
   LOOP
      FETCH sal_cursor INTO D_Record;
      EXIT WHEN sal_cursor %NOTFOUND;
   END LOOP;
   CLOSE sal_cursor;
END;
/

说明

在执行上述代码时，使用了 %NOTFOUND 属性。无论何时您想处理特定列的值，我们都可以使用它进行访问。

游标 FOR LOOP

作为打开、提取和关闭游标的替代方法，PL/SQL 提供了另一种方法，即游标 FOR LOOP。这是一个与显式游标或直接包含在循环范围内的 SELECT 语句链接的循环。当您需要检索和处理游标中的每个记录时，应首选游标而不是 for 循环。

游标 FOR 循环尤其减少了从游标检索数据时必须编写的代码量。因此，它主要减少了循环错误的几率，因为循环是程序中最容易出错的部分之一。

以下是游标 FOR 循环的语法

 FOR loop_index IN <cursorname>
LOOP
Executable statements;
END LOOP;
</cursorname>

游标 FOR 循环执行以下操作

它隐式地将其循环索引声明为 %ROWTYPE 记录。
然后打开游标。
对于每次循环迭代，它从游标中检索一行。PL/SQL 在每次循环体执行后执行另一次提取。如果游标的 %NOTFOUND 属性评估为 TRUE，则循环终止。循环永远不会执行其游标体，也不会返回任何行。
当所有行都被检索后，关闭游标。
您也可以使用 EXIT 语句结束循环，但不建议这样做。

以下给出了游标 FOR 循环的示例

示例 1：编写一个 PL/SQL 程序，使用游标 FOR 循环打印 DEPT 表的部门编号和名称。

SET SERVEROUTPUT ON;
DECLARE
   CURSOR d_cur IS
      SELECT deptno, dname from dept;
BEGIN
      FOR V in d_cur
      LOOP
        DBMS_OUTPUT.PUT_LINE(V.deptno || ‘  ‘ || V.dname);
   END LOOP;
   CLOSE d_cur;
END;
/

您还可以将 SELECT 语句直接嵌入到游标 FOR 循环中。您应该避免这样做，因为它会导致 SELECT 语句嵌入到代码中意想不到的位置，从而使其更难以维护和增强您的逻辑。

BEGIN
      FOR V in SELECT deptno, dname from dept;
      LOOP
        DBMS_OUTPUT.PUT_LINE(V.deptno ||  ‘   ‘  || V.dname);
   END LOOP;
END;
/

说明

在执行上述代码后，它将显示 DEPT 表中的部门编号和部门名称。

FOR UPDATE 子句

当打开游标时，使用 FOR UPDATE 子句锁定受影响的行。在事务结束时，这些锁会被释放。这是游标中使用的 SELECT 语句的最后一个子句。

以下是 FOR UPDATE 子句的语法

 SELECT
FROM
LOOP
FOR UPDATE [OF COL_NAMES [NOWWAIT];

在上面的语法中，可选关键字 NOWAIT 告诉 PL/SQL，如果所需的行被其他用户锁定，则不要等待。如果未使用它，并且 PL/SQL 服务器无法获取其需要的行的锁，那么它将无限期地等待。

WHERE CURRENT OF 子句允许游标从显式游标更新或删除当前行，而无需显式引用 ROWID。在进行任何修改之前，必须在游标中包含 FOR UPDATE 子句以锁定行。

以下是 FOR UPDATE 子句的示例

示例 1：编写一个 PL/SQL 程序，将所有在“Accounting”部门工作且工资低于 2000 的员工的工资提高 20%。

SET SERVEROUTPUT ON;
DECLARE
   CURSOR Inc_Sal_cur IS
      SELECT E.deptid, Empno, Ename, D.dname, Salary 
      from Emp E, Dept D
      where D.deptno = E.deptno
   and D.dname = ‘Accounting’;
FOR UPDATE of Salary NOWAIT;
BEGIN
      FOR V in Inc_Sal_cur
LOOP
       IF V.Salary < 2000 then
       UPDATE Emp Set salary = V.sal * 1.20;
       WHERE CURRENT OF Inc_Sal_cur;
    END IF;
   END LOOP;
   END;
/

下一个主题PL/SQL 异常

← 上一个下一个 →

我们提供所有技术（如 Java 教程、Android、Java 框架）的教程和面试问题

联系信息

G-13, 2nd Floor, Sec-3, Noida, UP, 201301, India

hr@tpointtech.com

+91-9599086977

关注我们

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

PL/SQL 教程

控制语句

PL/SQL 过程

PL/SQL 函数

PL/SQL 游标

PL/SQL 异常

PL/SQL 触发器

其他

面试题

测验

PL/SQL 游标

1) PL/SQL 隐式游标

PL/SQL 隐式游标示例

示例 2

示例 3

2) PL/SQL 显式游标

显式游标的语法

步骤：

1) 声明游标

2) 打开游标

3) 提取游标

4) 关闭游标

PL/SQL 显式游标示例

PL/SQL 显式游标属性

示例 1

示例 2

游标 FOR LOOP

以下给出了游标 FOR 循环的示例

FOR UPDATE 子句

订阅 Tpoint Tech

联系信息

关注我们

教程

面试题

在线编译器