使用记录和游标

此页上的脚本内容仅用于导航目的，不会以任何方式变更该内容。

可以将数据值存储在记录中，并使用光标作为指向结果集和相关处理信息的指针。

另请参阅： Oracle AI Database PL/SQL Language Reference 以了解有关记录的更多信息

关于记录

记录是 PL/SQL 组合变量，可以存储不同类型的数据值。可以将内部组件（字段）视为标量变量。可以将整个记录作为子程序参数进行传递。记录非常适用于存储表行中的数据或表行中某些列的数据。

记录是一种 PL/SQL 复合变量，它可以存储不同类型的数据值，类似于 C、C++ 或 Java 中的结构类型。记录的内部组件被称为字段。要访问记录字段，可以使用点表示法：record_name.field_name。

您可以将记录字段视为标量变量。还可以将整个记录作为子程序参数传递。

记录非常适用于存储表行中的数据或表行中某些列的数据。每个记录字段对应于一个表列。

有三种方式可以创建记录：

声明一种 RECORD 类型，然后声明一个该类型的变量。

语法如下：

TYPE record_name IS RECORD
  ( field_name data_type [:= initial_value]
 [, field_name data_type [:= initial_value ] ]... );

variable_name record_name;

声明一个 table_name%ROWTYPE 类型的变量。

记录字段具有与表列相同的名称和数据类型。
声明一种 cursor_name%ROWTYPE 类型的变量。

记录字段与游标 SELECT 语句的 FROM 子句中的表列具有相同的名称和数据类型。

另请参见：

Oracle AI Database PL/SQL Language Reference（了解有关定义 RECORD 类型和声明该类型的记录的详细信息）
Oracle AI Database PL/SQL Language Reference（了解有关 RECORD 类型定义的语法）
Oracle AI Database PL/SQL Language Reference（了解有关 %ROWTYPE 属性的详细信息）
Oracle AI Database PL/SQL Language Reference（了解有关 %ROWTYPE 属性的语法）

教程：声明 RECORD 类型

本教程介绍如何使用 SQL Developer 的“编辑”工具声明一个 RECORD 类型 sal_info，其字段可用于保存员工的薪水信息（工作 ID、该工作 ID 的最低薪水和当前薪水以及建议加薪）。

要声明 RECORD 类型 sal_info，请执行以下操作：

在 "Connections"（连接）框架中，展开 hr_conn 。

在 hr_conn 图标下，即会出现方案对象类型列表。
展开程序包。

即会出现程序包列表。
右键单击 EMP_EVAL 。

此时将显示选项的列表。

单击编辑。

此时将打开 EMP_EVAL 窗格，其中显示创建该软件包的 CREATE PACKAGE 语句：

CREATE OR REPLACE PACKAGE EMP_EVAL AS

PROCEDURE eval_department(dept_id IN NUMBER);
FUNCTION calculate_score(evaluation_id IN NUMBER
                        , performance_id IN NUMBER)
                          RETURN NUMBER;

END EMP_EVAL;

在 EMP_EVAL 窗格，紧靠 END EMP_EVAL 之前添加如下代码：

TYPE sal_info IS RECORD
  ( j_id     jobs.job_id%type
  , sal_min  jobs.min_salary%type
  , sal_max  jobs.max_salary%type
  , sal      employees.salary%type
  , sal_raise NUMBER(3,3) );

该窗格的标题字体为斜体，这表示尚未将更改保存在数据库中。

单击图标 Compile 。

随即会编译更改后的程序包规范，并将其保存到数据库中。此时 EMP_EVAL 窗格的标题字体将不再是斜体。

现在您可以声明记录类型 sal_info，如“教程：创建和调用带有记录参数的子程序”中所述。

教程：创建和调用带有记录参数的子程序

此教程演示如何使用 SQL Developer 工具“编辑”来创建和调用具有记录类型为 sal_info 的参数的子程序。

在“教程：声明 RECORD 类型”中创建了记录类型 sal_info。

此教程演示如何使用 SQL Developer 工具“Edit（编辑）”执行以下操作：

创建一个带有 sal_info 类型的参数的过程 SALARY_SCHEDULE。
更改 EVAL_FREQUENCY 函数，以便在该函数中声明一个 sal_info 类型的记录 emp_sal，并填充其字段，然后将其传递给 SALARY_SCHEDULE 过程。

由于 EVAL_FREQUENCY 将调用 SALARY_SCHEDULE，因此 SALARY_SCHEDULE 声明必须位于 EVAL_FREQUENCY 声明之前（否则程序包将无法编译）。但是，您可以在程序包体中的任何位置定义 SALARY_SCHEDULE。

要创建 SALARY_SCHEDULE 并更改 EVAL_FREQUENCY，请执行以下操作：

在 "Connections"（连接）框架中，展开 hr_conn 。
在方案对象类型的列表中，展开程序包。
在软件包列表中，展开 EMP_EVAL 。
在选项列表中，右键单击 EMP_EVAL Body 。
在选项列表中，单击编辑。

此时将显示 EMP_EVAL Bodypane，其中显示用于该程序包体的代码。

在“EMP_EVAL 主体”窗格中的，紧靠 END EMP_EVAL 之前添加以下 SALARY_SCHEDULE 过程定义：

PROCEDURE salary_schedule (emp IN sal_info) AS
  accumulating_sal  NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('If salary ' || emp.sal ||
    ' increases by ' || ROUND((emp.sal_raise * 100),0) ||

    '% each year, it will be:');

  accumulating_sal := emp.sal;

  WHILE accumulating_sal <= emp.sal_max LOOP
    accumulating_sal := accumulating_sal * (1 + emp.sal_raise);
    DBMS_OUTPUT.PUT_LINE(ROUND(accumulating_sal,2) ||', ');

  END LOOP;
END salary_schedule;

该窗格的标题字体为斜体，这表示尚未将更改保存在数据库中。

在“EMP_EVAL Body（EMP_EVAL 主体）”窗格中的下列位置，输入以粗体的字显示的代码：

CREATE OR REPLACE
PACKAGE BODY EMP_EVAL AS

FUNCTION eval_frequency (emp_id EMPLOYEES.EMPLOYEE_ID%TYPE)

  RETURN PLS_INTEGER;

PROCEDURE salary_schedule(emp IN sal_info);

PROCEDURE add_eval(employee_id IN employees.employee_id%type, today IN DATE);

PROCEDURE eval_department (dept_id IN NUMBER) AS

编辑 EVAL_FREQUENCY 函数，按照粗体字所示进行更改：

FUNCTION eval_frequency (emp_id EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date     EMPLOYEES.HIRE_DATE%TYPE;
  today      EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq  PLS_INTEGER;

  emp_sal    SAL_INFO;  -- replaces sal, sal_raise, and sal_max

BEGIN
  SELECT SYSDATE INTO today FROM DUAL;

  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = eval_frequency.emp_id;

  IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
    eval_freq := 1;

    /* populate emp_sal */

    SELECT j.JOB_ID, j.MIN_SALARY, j.MAX_SALARY, e.SALARY

    INTO emp_sal.j_id, emp_sal.sal_min, emp_sal.sal_max, emp_sal.sal

    FROM EMPLOYEES e, JOBS j

    WHERE e.EMPLOYEE_ID = eval_frequency.emp_id

    AND j.JOB_ID = eval_frequency.emp_id;

    emp_sal.sal_raise := 0;  -- default

    CASE emp_sal.j_id
      WHEN 'PU_CLERK' THEN emp_sal.sal_raise := 0.08;
      WHEN 'SH_CLERK' THEN emp_sal.sal_raise := 0.07;
      WHEN 'ST_CLERK' THEN emp_sal.sal_raise := 0.06;
      WHEN 'HR_REP' THEN emp_sal.sal_raise := 0.05;
      WHEN 'PR_REP' THEN emp_sal.sal_raise := 0.05;
      WHEN 'MK_REP' THEN emp_sal.sal_raise := 0.04;
      ELSE NULL;
    END CASE;

    IF (emp_sal.sal_raise != 0) THEN

      salary_schedule(emp_sal);
    END IF;
  ELSE
    eval_freq := 2;
  END IF;

  RETURN eval_freq;
END eval_frequency;

单击 Compile 。

关于游标

数据库运行 SQL 语句时，它将结果集和处理信息存储在未命名的专用 SQL 区域中。指向此未命名区域的指针称为游标，使用游标可以从结果集中一次检索一行。游标属性可返回有关游标状态的信息。

每次运行 SQL DML 语句或 PL/SQL SELECT INTO 语句时，PL/SQL 都会打开一个隐式游标。您可以从游标属性中获取有关此游标的信息，但无法控制此信息。在语句运行之后，数据库会关闭游标；但是，其属性值仍将保持可用，直至另一个 DML 或 SELECT INTO 语句运行。

还可以使用 PL/SQL 声明游标。显式游标具有名称并与一个查询（SQL SELECT 语句）关联，而且这种搜索通常可以返回多行。声明光标后，必须以隐式或显式方式对其进行处理。要隐式处理游标，请使用游标 FOR LOOP。语法如下：

FOR record_name IN cursor_name LOOP
  statement
  [ statement ]...
END LOOP;

要显式处理游标，请将其打开（使用 OPEN 语句），从结果集中一次提取一行或批量提取（使用 FETCH 语句），然后关闭游标（使用 CLOSE 语句）。关闭游标后，您将无法从结果集中提取记录，也无法查看游标属性值。

隐式游标属性值的语法是 SQL%attribute（例如 SQL%FOUND）。SQL%attribute 始终引用最近运行的 DML 或 SELECT INTO 语句。

声明的游标属性值的语法是 cursor_name%attribute（例如 c1%FOUND）。表 5-1 列出了各种游标属性以及它们可以返回的值。(隐式游标还具有其他属性，本文不再赘述。)

表 1 游标属性值

属性	声明光标的值	隐式游标的值
已找到 %FOUND	如果游标已打开但尚未尝试进行提取，则为 NULL 。如果最近的提取返回了一行，则为 TRUE 。如果最近的提取未返回行，则为 FALSE 。	如果未运行任何 DML 或 SELECT INTO 语句，则为 NULL 。如果最近的 DML 或 SELECT INTO 语句返回了一行，则为 TRUE 。如果最近的 DML 或 SELECT INTO 语句未返回行，则为 FALSE 。
% 未找到	如果游标已打开但尚未尝试进行提取，则为 NULL 。如果最近的提取返回了一行，则为 FALSE 。如果最近的提取未返回行，则为 TRUE 。	如果未运行任何 DML 或 SELECT INTO 语句，则为 NULL 。如果最近的 DML 或 SELECT INTO 语句返回了一行，则为 `FALSE`。如果最近的 DML 或 SELECT INTO 语句未返回行，则为 `TRUE`。
%ROWCOUNT	如果游标已打开，则为大于或等于零的数字。	如果未运行任何 DML 或 SELECT INTO 语句，则为 NULL；否则，则为一个大于或等于零的数字。
打开 %S	如果游标已打开，则为 TRUE；否则为 FALSE。	始终为假。

对于 %FOUND、%NOTFOUND 和 %ROWCOUNT 属性，如果光标未打开，则该属性会引发预定义的异常错误 INVALID_CURSOR。

另请参见：

“关于查询”
“关于数据操纵语言 (DML) 语句”
Oracle AI Database PL/SQL Language Reference （了解有关 SELECT INTO 语句的详细信息）
Oracle AI Database PL/SQL Language Reference （了解有关在 PL/SQL 中管理游标的详细信息）

使用声明的游标从结果集中一次检索一行

可以使用声明的游标检索一次一行的结果集行。

下列过程以最简单的形式使用每个必要的语句，但提供了有关其完整语法的参考。

要使用声明的游标检索结果集行，请一次检索一行：

在声明部分中：
1. 声明游标：
```
CURSOR cursor_name IS query;
```
  有关完整的声明游标声明语法，请参阅《 Oracle AI Database PL/SQL Language Reference 》。
2. 声明一个用于保存游标返回的行的记录：
```
record_name cursor_name%ROWTYPE;
```
  有关 %ROWTYPE 的完整语法，请参阅 Oracle AI Database PL/SQL Language Reference 。
在可执行部分中：
1. 打开游标：
```
OPEN cursor_name;
```
  有关 OPEN 语句的完整语法，请参阅 Oracle AI Database PL/SQL Language Reference 。
2. 使用 LOOP 语句，其语法如下：从游标（结果集中的行）中一次提取一行，其语法如下：
```
LOOP
  FETCH cursor_name INTO record_name;
  EXIT WHEN cursor_name%NOTFOUND;

  -- Process row that is in record_name:

  statement;
  [ statement; ]...
END LOOP;
```
  有关完整的 FETCH 语句语法，请参阅 Oracle AI Database PL/SQL Language Reference 。
3. 关闭游标：
```
CLOSE cursor_name;
```
  有关完整的 CLOSE 语句语法，请参阅 Oracle AI Database PL/SQL Language Reference 。

教程：使用声明的游标从结果集中一次检索一行

此教程演示了如何实施过程 EMP_EVAL.EVAL_DEPARTMENT，该过程使用声明的游标 emp_cursor。

要实施 EMP_EVAL.EVAL_DEPARTMENT 过程，请执行以下步骤：

在 EMP_EVAL 程序包规范中，按照粗体字所示更改 EVAL_DEPARTMENT 过程的声明：
```
PROCEDURE eval_department(dept_id IN employees.department_id%TYPE);
```

在 EMP_EVAL 程序包体中，按照粗体字所示更改 EVAL_DEPARTMENT 过程的定义：

PROCEDURE eval_department (dept_id IN employees.department_id%TYPE)
AS

  CURSOR emp_cursor IS

    SELECT * FROM EMPLOYEES

    WHERE DEPARTMENT_ID = eval_department.dept_id;

  emp_record  EMPLOYEES%ROWTYPE;  -- for row returned by cursor

  all_evals   BOOLEAN;  -- true if all employees in dept need evaluations

  today       DATE;

BEGIN

  today := SYSDATE;

  IF (EXTRACT(MONTH FROM today) < 6) THEN

    all_evals := FALSE; -- only new employees need evaluations

  ELSE

    all_evals := TRUE;  -- all employees need evaluations

  END IF;

  OPEN emp_cursor;

  DBMS_OUTPUT.PUT_LINE (

    'Determining evaluations necessary in department # ' ||

    dept_id );

  LOOP

    FETCH emp_cursor INTO emp_record;

    EXIT WHEN emp_cursor%NOTFOUND;

    IF all_evals THEN

      add_eval(emp_record.employee_id, today);

    ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN

      add_eval(emp_record.employee_id, today);

    END IF;

  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');

  CLOSE emp_cursor;
END eval_department;

（有关更改程序包体的分步示例，请参阅“教程：在子程序中声明变量和常量”。）

编译 EMP_EVAL 程序包规范。
编译 EMP_EVAL 程序包体。

关于游标变量

游标变量类似于游标，但它不局限于一个查询。您可以为一个查询打开一个游标变量，处理结果集，然后将该游标变量用于其他查询。当子程序之间传递查询结果时，游标变量将非常有用。

有关游标的信息，请参见“关于游标”。

要声明游标变量，您需要声明一个 REF CURSOR 类型，然后声明一个该类型的变量（因此，游标变量通常称为 REF CURSOR ）。REF CURSOR 类型可以是强类型或弱。

强 REF CURSOR 类型指定了返回类型，它是其游标变量的 RECORD 类型。对于返回行不属于该返回类型行的查询，PL/SQL 编译器不允许使用这些强类型游标变量。强 REF CURSOR 类型比弱类型为更加不易出错，但弱类型为更加灵活。

弱 REF CURSOR 类型不指定返回类型。PL/SQL 编译器接受任何查询中的弱类型游标变量。弱 REF CURSOR 类型是可交换类型；因此，您可以使用预定义类型的弱游标类型 SYS_REFCURSOR，而不用创建弱 REF CURSOR 类型。

声明游标变量后，您必须为特定查询打开它（使用 OPEN FOR 语句），从结果集中一次提取一行（使用 FETCH 语句），然后关闭游标（使用 CLOSE 语句）或是为另一个特定查询打开它（使用 OPEN FOR 语句）。为另一个查询打开游标变量时，将会关闭前一个查询的游标变量。在关闭特定查询的游标变量后，您既不能从该查询的结果集中提取记录，也不能查看该查询的游标属性值。

另请参见：

Oracle AI Database PL/SQL Language Reference （了解有关使用游标变量的更多信息）
Oracle AI Database PL/SQL Language Reference （了解有关游标变量声明的语法）

使用游标变量从结果集中一次检索一行

可以使用游标变量检索一次一个结果集行。

下列过程以最简单的形式使用每个必要的语句，但提供了有关其完整语法的参考。

要使用游标变量从结果集中一次检索一行，请执行以下操作：

在声明部分中：
1. 声明 REF CURSOR 类型：
```
TYPE cursor_type IS REF CURSOR [ RETURN return_type ];
```
  有关完整的 REF CURSOR 类型声明语法，请参阅 Oracle AI Database PL/SQL Language Reference 。
2. 声明一个该类型的游标变量：
```
cursor_variable cursor_type;
```
  有关游标变量声明的完整语法，请参阅 Oracle AI Database PL/SQL Language Reference 。
3. 声明一个用于保存游标返回的行的记录：
```
record_name return_type;
```
  有关记录声明语法的完整信息，请参阅 Oracle AI Database PL/SQL Language Reference 。
在可执行部分中：
1. 为特定查询打开游标变量：
```
OPEN cursor_variable FOR query;
```
  有关 OPEN FOR 语句语法的完整信息，请参阅 Oracle AI Database PL/SQL Language Reference 。
2. 使用 LOOP 语句从游标变量（结果集行）中一次提取一行，其语法如下：
```
LOOP
  FETCH cursor_variable INTO record_name;
  EXIT WHEN cursor_variable%NOTFOUND;

  -- Process row that is in record_name:

  statement;
  [ statement; ]...
END LOOP;
```
  有关 FETCH 语句语法的完整信息，请参阅 Oracle AI Database PL/SQL Language Reference 。
3. 关闭游标变量：
```
CLOSE cursor_variable;
```
  此外，您也可以为另一个查询打开游标变量，这将对当前查询关闭游标变量。
  
  有关 CLOSE 语句语法的完整信息，请参阅 Oracle AI Database PL/SQL Language Reference 。

教程：使用游标变量从结果集中一次检索一行

本教程介绍了如何更改 EMP_EVAL.EVAL_DEPARTMENT 过程，以使其使用游标变量而不是声明游标（使其可以处理多个部门），从而让它能够更有效地处理 EMP_EVAL.EVAL_DEPARTMENT 和 EMP_EVAL.ADD_EVAL。

本教程如何提高 EMP_EVAL.EVAL_DEPARTMENT 和 EMP_EVAL.ADD_EVAL 的效率：EVAL_DEPARTMENT 不是将记录的一个字段传递给 ADD_EVAL 并让 ADD_EVAL 使用三个查询来提取相同记录的其他三个字段，而是将整个记录传递给 ADD_EVAL，然后让 ADD_EVAL 使用点表示法访问其他三个字段的值。

要将 EMP_EVAL.EVAL_DEPARTMENT 过程更改为使用游标变量，请执行以下步骤：

在 EMP_EVAL 程序包规范中，添加过程声明和 REF CURSOR 类型定义，如粗体字所示：

CREATE OR REPLACE
PACKAGE emp_eval AS

  PROCEDURE eval_department (dept_id IN employees.department_id%TYPE);

  PROCEDURE eval_everyone;

  FUNCTION calculate_score(eval_id IN scores.evaluation_id%TYPE
                        , perf_id IN scores.performance_id%TYPE)
                          RETURN NUMBER;
  TYPE SAL_INFO IS RECORD
      ( j_id jobs.job_id%type
      , sal_min jobs.min_salary%type
      , sal_max jobs.max_salary%type
      , salary employees.salary%type
      , sal_raise NUMBER(3,3));

  TYPE emp_refcursor_type IS REF CURSOR RETURN employees%ROWTYPE;
END emp_eval;

在 EMP_EVAL 程序包体中，为过程 EVAL_LOOP_CONTROL 添加一个前向声明，并更改过程 ADD_EVAL 的声明，如以下粗体字所示：

CREATE OR REPLACE
PACKAGE BODY EMP_EVAL AS

  FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
    RETURN PLS_INTEGER;

  PROCEDURE salary_schedule(emp IN sal_info);

  PROCEDURE add_eval(emp_record IN EMPLOYEES%ROWTYPE, today IN DATE);

  PROCEDURE eval_loop_control(emp_cursor IN emp_refcursor_type);
...

（有关更改程序包体的分步示例，请参阅“教程：在子程序中声明变量和常量”。）

将 EVAL_DEPARTMENT 过程更改为基于部门检索三个单独的结果集，并调用 EVAL_LOOP_CONTROL 过程，如粗体字所示：

PROCEDURE eval_department(dept_id IN employees.department_id%TYPE) AS

  emp_cursor    emp_refcursor_type;

  current_dept  departments.department_id%TYPE;

BEGIN

  current_dept := dept_id;

  FOR loop_c IN 1..3 LOOP

    OPEN emp_cursor FOR

      SELECT *

      FROM employees

      WHERE current_dept = eval_department.dept_id;

    DBMS_OUTPUT.PUT_LINE
      ('Determining necessary evaluations in department #' ||

      current_dept);

    eval_loop_control(emp_cursor);

    DBMS_OUTPUT.PUT_LINE
      ('Processed ' || emp_cursor%ROWCOUNT || ' records.');

    CLOSE emp_cursor;

    current_dept := current_dept + 10;

  END LOOP;
END eval_department;

更改 ADD_EVAL 过程，如以下粗体字所示：

PROCEDURE add_eval(emp_record IN employees%ROWTYPE, today IN DATE)
AS

-- (Delete local variables)
BEGIN
  INSERT INTO EVALUATIONS (
    evaluation_id,
    employee_id,
    evaluation_date,
    job_id,
    manager_id,
    department_id,
    total_score
  )
  VALUES (
    evaluations_sequence.NEXTVAL,   -- evaluation_id

    emp_record.employee_id,    -- employee_id
    today,                     -- evaluation_date

    emp_record.job_id,         -- job_id

    emp_record.manager_id,     -- manager_id

    emp_record.department_id,  -- department_id
    0                           -- total_score
);
END add_eval;

在 END EMP_EVAL 之前，添加以下过程，该过程中将从结果集中提取各个记录并处理它们：

PROCEDURE eval_loop_control (emp_cursor IN emp_refcursor_type) AS
  emp_record      EMPLOYEES%ROWTYPE;
  all_evals       BOOLEAN;
  today           DATE;
BEGIN
  today := SYSDATE;

  IF (EXTRACT(MONTH FROM today) < 6) THEN

    all_evals := FALSE;
  ELSE
    all_evals := TRUE;
  END IF;

  LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;

    IF all_evals THEN
      add_eval(emp_record, today);
    ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN

      add_eval(emp_record, today);
    END IF;
  END LOOP;
END eval_loop_control;

在 END EMP_EVAL 之前，添加以下过程，该过程会检索包含公司所有员工的结果集：

PROCEDURE eval_everyone AS
  emp_cursor emp_refcursor_type;
BEGIN
  OPEN emp_cursor FOR SELECT * FROM employees;
  DBMS_OUTPUT.PUT_LINE('Determining number of necessary evaluations.');
  eval_loop_control(emp_cursor);
  DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
  CLOSE emp_cursor;
END eval_everyone;

编译 EMP_EVAL 程序包规范。
编译 EMP_EVAL 程序包体。