Oracle PL/SQL 游标:隐式、显式、For 循环示例

PL/SQL 中的 CURSOR 是什么?

游标 (Cursor) 是指向该上下文区域的指针。 Oracle 创建用于处理 SQL 语句的上下文区域,其中包含有关该语句的所有信息。

PL/SQL 允许程序员通过游标控制上下文区域。游标保存 SQL 语句返回的行。游标保存的行集称为活动集。这些游标也可以命名,以便可以从代码的其他地方引用它们。

光标有两种类型。

  • 隐式光标
  • 显式光标

隐式光标

每当数据库中发生任何 DML 操作时,都会创建一个隐式游标,用于保存该特定操作中受影响的行。这些游标无法命名,因此无法从代码的其他地方控制或引用它们。我们只能通过游标属性引用最近的游标。

显式光标

程序员可以创建命名上下文区域来执行他们的 DML 操作,以便更好地控制它。显式游标应在声明部分中定义 PL/SQL 块,它是为代码中需要使用的‘SELECT’语句创建的。

以下是使用显式游标所涉及的步骤。

  • 声明游标 声明游标只是意味着为声明部分定义的 SELECT 语句创建一个命名的上下文区域。此上下文区域的名称与游标名称相同。
  • 打开光标打开光标将指示 PL / SQL 为该游标分配内存。它将使游标准备好获取记录。
  • 从游标获取数据在此过程中,将执行“SELECT”语句,并将获取的行存储在分配的内存中。这些现在称为活动集。从游标中获取数据是一种记录级活动,这意味着我们可以逐条记录地访问数据。每个获取语句将获取一个活动集并保存该特定记录的信息。此语句与获取记录并分配给“INTO”子句中的变量的“SELECT”语句相同,但不会引发任何异常。
  • 关闭游标一旦获取了所有记录,我们就需要关闭游标,以便释放分配给该上下文区域的内存。

句法

DECLARE
CURSOR <cursor_name> IS <SELECT statement^>
<cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;
  • 在上面的语法中,声明部分包含游标的声明和将分配所获取数据的游标变量。
  • 该游标是为游标声明中给出的“SELECT”语句创建的。
  • 在执行部分,声明的游标被打开,获取和关闭。

游标属性

隐式游标和显式游标都具有某些可访问的属性。这些属性提供有关游标操作的更多信息。以下是不同的游标属性及其用法。

游标属性 描述
%成立 如果最近的获取操作成功获取了记录,则返回布尔结果“TRUE”,否则返回 FALSE。
%未找到 这与 %FOUND 相反,如果最近的获取操作无法获取任何记录,它将返回“TRUE”。
%开了 如果给定的游标已打开,则返回布尔结果“TRUE”,否则返回“FALSE”
%行数 它返回数值。它给出受 DML 活动影响的记录的实际数量。

显式游标示例:

在这个例子中,我们将看到如何声明、打开、获取和关闭显式游标。

我们将使用游标从 emp 表中投影所有员工的姓名。我们还将使用游标属性来设置循环以从游标中获取所有记录。

Oracle PL/SQL 游标

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
lv_emp_name emp.emp_name%type;

BEGIN
OPEN guru99_det;

LOOP
FETCH guru99_det INTO lv_emp_name;
IF guru99_det%NOTFOUND
THEN
EXIT;
END IF;
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name);
END LOOP;
Dbms_output.put_line(‘Total rows fetched is‘||guru99_det%R0WCOUNT);
CLOSE guru99_det;
END:
/

输出

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY 
Total rows fetched is 3

代码说明

  • 代码行 2:为语句‘SELECT emp_name FROM emp’声明游标guru99_det。
  • 代码行 3:声明变量 lv_emp_name。
  • 代码行 5:打开游标guru99_det。
  • 代码第 6 行: 设置基本循环语句以获取“emp”表中的所有记录。
  • 代码第 7 行: 获取 guru99_det 数据并将值分配给 lv_emp_name。
  • 代码第 9 行: 使用游标属性“%NOTFOUND”来查找是否已提取游标中的所有记录。如果已提取,则它将返回“TRUE”,并且控件将退出循环,否则控件将继续从游标中提取数据并打印数据。
  • 代码第 11 行: 循环语句的 EXIT 条件。
  • 代码第 12 行: 打印获取的员工姓名。
  • 代码第 14 行: 使用游标属性“%ROWCOUNT”查找游标中受影响/获取的记录总数。
  • 代码第 15 行: 退出循环后,游标关闭,分配的内存被释放。

FOR 循环游标语句

“FOR LOOP”语句可用于处理游标。我们可以在 FOR 循环语句中给出游标名称而不是范围限制,这样循环将从游标的第一个记录到游标的最后一个记录进行。游标变量、游标的打开、游标的获取和关闭将由 FOR 循环隐式完成。

句法

DECLARE
CURSOR <cursor_name> IS <SELECT statement>;
BEGIN
  FOR I IN <cursor_name>
  LOOP
  .
  .
  END LOOP;
END;
  • 在上面的语法中,声明部分包含游标的声明。
  • 该游标是为游标声明中给出的“SELECT”语句创建的。
  • 在执行部分,在 FOR 循环中设置声明的游标,并且循环变量“I”在这种情况下将充当游标变量。

Oracle 游标循环示例:
在此示例中,我们将使用游标 FOR 循环从 emp 表中投影所有员工姓名。

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp; 
BEGIN
FOR lv_emp_name IN guru99_det
LOOP
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name.emp_name);
END LOOP;
END;
/

输出

Employee Fetched:BBB 
Employee Fetched:XXX
Employee Fetched:YYY

代码说明

  • 代码行 2:为语句‘SELECT emp_name FROM emp’声明游标guru99_det。
  • 代码行 4:使用循环变量 lv_emp_name 为游标构建“FOR”循环。
  • 代码第 5 行: 在循环的每次迭代中打印员工姓名。
  • 代码第 8 行: 退出循环

请注意: 在 Cursor-FOR 循环中,不能使用游标属性,因为游标的打开、获取和关闭是通过以下方式隐式完成的: FOR循环.