金仓数据库 KingbaseES PL/SQL 过程语言参考手册（7. PL/SQL 静态 SQL）

金仓数据库 KingbaseES PL/SQL 过程语言参考手册（7. PL/SQL 静态 SQL）
7. PL/SQL 静态 SQL

静态 SQL是 PL/SQL 的一种功能，它允许在 PL/SQL 语句中直接使用 SQL 语法。

本章描述静态 SQL 并解释如何使用它。
- "静态 SQL 语句中的名称解析"
7.1. 静态SQL说明

静态 SQL 与 SQL 具有相同的语法，除非另有说明。

7.1.1. 声明

这些是 PL/SQL 静态 SQL 语句，它们的语法与相应的 SQL 语句相同，除非另有说明： - SELECT（此语句也称为查询）

有关 PL/SQL 语法，请参阅“ SELECT INTO 语句”。
- 数据操作语言 (DML) 语句：
  - INSERT
    
    有关 PL/SQL 语法，请参阅“ INSERT 语句扩展”。
  - UPDATE
    
    有关 PL/SQL 语法，请参阅“ UPDATE 语句扩展”。
  - DELETE
    
    有关 PL/SQL 语法，请参阅“ DELETE 语句扩展”。
  - MERGE（有关语法，请参阅SQL 语言参考）
- 事务控制语言 (TCL) 语句：
  - COMMIT（有关语法，请参阅SQL 语言参考）
  - ROLLBACK（有关语法，请参阅SQL 语言参考）
  - SAVEPOINT（有关语法，请参阅SQL 语言参考）
  - SET TRANSACTION（有关语法，请参阅SQL 语言参考）
  - LOCK TABLE（有关语法，请参阅SQL 语言参考）
一个 PL/SQL 静态SQL语句可以有一个 PL/SQL标识符，只要它的SQL对应项允许有绑定变量的占位符。

PL/SQL 标识符必须标识变量或形式参数。要将 PL/SQL 标识符用于表名、列名等，请使用 EXECUTE IMMEDIATE 语句，如“Native dynamic SQL”中所述。

注意

PL/SQL 代码运行 DML 语句后，某些变量的值是未定义的。

例如： - 在FETCH or SELECT语句引发异常后，该语句之后的定义变量的值未定义。 - 在影响零行的 DML 语句之后，OUT绑定变量的值是未定义的，除非 DML 语句是一个BULK或多行操作。

示例 7-1 静态 SQL 语句

在这个例子中，一个 PL/SQL 匿名块声明了三个 PL/SQL 变量，并在静态 SQL 语句 INSERT、UPDATE、DELETE 中使用它们。该块还使用静态 SQL 语句 COMMIT。
```
    DROP TABLE IF EXISTS students;
    DROP TABLE IF EXISTS student_temp;
    CREATE TABLE student (id int PRIMARY KEY, name text, score number);
    insert into student values (1, 'xx', 99);
    CREATE TABLE student_temp AS
SELECT id, name
FROM student;

    \set SQLTERM /
    DECLARE
        stu_id    student_temp.id%TYPE := 2;
        stu_name  student_temp.name%TYPE  := 'xm';
    BEGIN
        INSERT INTO student_temp (id, name)
        VALUES (stu_id, stu_name);

        UPDATE student_temp
        SET name = 'xh'
        WHERE id = stu_id;

        DELETE FROM student_temp
        WHERE id = stu_id
        RETURNING name
        INTO stu_name;

        COMMIT;
        RAISE NOTICE 'stu_name：%',stu_name;
    END;
    /
```
结果：
```
NOTICE:  stu_name：xh
```
7.1.2. 伪列

伪列的行为类似于表列，但它不存储在表中。

有关伪列的一般信息，包括限制，请参阅SQL 语言参考。

静态 SQL 包括这些 SQL 伪列：
- CURRVAL并且NEXTVAL，在“ PL/SQL 中的 CURRVAL 和 NEXTVAL ”中进行了描述
- LEVEL，在SQL 语言参考中描述
- OBJECT_VALUE，在SQL 语言参考中描述
另请参阅

“ OBJECT_VALUE Pseudocolumn ”获取有关OBJECT_VALUE在触发器中使用的信息
- ROWID，在SQL 语言参考中描述
另请参阅

"用 ROWID 伪列模拟 CURRENT OF Clause "ROWNUM，在SQL 语言参考中描述

7.1.2.1. PL/SQL 中的 CURRVAL 和 NEXTVAL

创建序列后，您可以在 SQL 语句中使用返回序列当前值的 CURRVAL 伪列或递增序列并返回新值的 NEXTVAL 伪列访问其值。要引用这些伪列，请使用点表示法，例如sequence_name.CURRVAL。

注意

每次引用sequence_name.NEXTVAL，无论您提交还是回滚事务，序列都会立即且永久地递增。

只要可以使用 NUMBER 表达式，就可以在 PL/SQL 表达式中使用 sequence_name.CURRVAL 和 sequence_name.NEXTVAL。然而： - 使用 sequence_name.CURRVAL 或 sequence_name.NEXTVAL 为 ADT 方法参数提供默认值会导致编译错误。 - PL/SQL 对每一个出现的 sequence_name.CURRVAL 和 sequence_name.NEXTVAL 求值（和SQL不同，它为出现的每一行求值一个序列表达式）。
- SQL 语言参考，了解有关序列的一般信息
- SQL 语言参考和CURRVAL的NEXTVAL语法
示例 7-2 CURRVAL 和 NEXTVAL 伪列

此示例为序列 HR.student_SEQ 生成一个序列号，并在多个语句中引用该编号。
```
    DROP TABLE IF EXISTS student_temp;
    CREATE TABLE student_temp AS
      SELECT id, name
      FROM student;

    DROP TABLE IF EXISTS student_temp2;
    CREATE TABLE student_temp2 AS
      SELECT id, name
      FROM student;

    CREATE SEQUENCE student_seq START 1;

    \set SQLTERM /
    DECLARE
      seq NUMBER;
    BEGIN
    -- Generate initial sequence number

seq := student_seq.NEXTVAL;

-- Print initial sequence number:

    RAISE NOTICE 'Initial sequence value: %', seq;

-- Use NEXTVAL to create unique number when inserting data:

INSERT INTO student_temp (id, name)
VALUES (student_seq.NEXTVAL, 'xq');

-- Use CURRVAL to store same value somewhere else:

INSERT INTO student_temp2 VALUES (student_seq.CURRVAL, 'xw');

/* Because NEXTVAL values might be referenced
   by different users and applications,
   and some NEXTVAL values might not be stored in database,
   there might be gaps in sequence. */

-- Use CURRVAL to specify record to delete:

   seq := student_seq.CURRVAL;

   DELETE FROM student_temp2
   WHERE id = seq;

-- Update id with NEXTVAL for specified record:

   UPDATE student_temp
   SET id = student_seq.NEXTVAL
   WHERE name = 'xq';

    -- Display final value of CURRVAL:

   seq := student_seq.CURRVAL;

       RAISE NOTICE 'End sequence value: %', seq;
    END;
    /
```
7.2. 游标概述

游标是指向私有 SQL 区域的指针，该区域存储有关处理特定SELECT或 DML 语句的信息。

注意

本主题介绍的游标是会话游标。会话游标一直存在于会话内存中，直到会话结束。

由 PL/SQL 构造和管理的游标是隐式游标。您构造和管理的游标是显式游标。您可以从任何会话游标的属性（您可以在过程语句中引用，但不能在 SQL 语句中引用）获取有关任何会话游标的信息。一个会话可以同时打开的游标数量由以下因素决定： - 会话可用的内存量 - 初始化参数的值 ORA_OPEN_CURSORS（仅 PLSQL 内部游标生效）

注意

通常PL/SQL仅在会话第一次打开显式游标时解析它，并且仅在语句第一次运行时解析SQL语句（创建隐式游标。）

所有解析的 SQL 语句都被缓存。只有当一条 SQL 语句由于新的 SQL 语句导致缓存失效，该语句才会被重新解析。尽管必须先关闭显式游标才能重新打开，但 PL/SQL 不需要重新解析相关联的查询。如果您关闭并立即重新打开显式游标，PL/SQL 不会重新分析关联的查询。

7.2.1. 隐式游标

隐式游标是由 PL/SQL 构造和管理的会话游标。

每次运行 SELECT 或 DML 语句时，PL/SQL 都会打开一个隐式游标。您无法控制隐式游标，但可以从其属性中获取信息。

隐式游标属性值的语法是 SQL%attribute（因此，隐式游标也称为 SQL 游标。）SQL%attribute 总是指最近运行的 SELECT 或 DML 语句。如果没有运行这样的语句，则 SQL%attribute 的值为 NULL。

隐式游标在其关联语句运行后关闭。但是，在另一个 SELECT 或 DML 语句运行之前，它的属性值仍然可用。

最近运行的SELECT或DML语句可能在不同的范围内。要保存属性值以供以后使用，请立即将其分配给局部变量。否则，其他操作（例如子程序调用）可能会在您测试之前更改属性的值。

隐式游标属性是：
- SQL%ISOPEN 属性：游标是否打开？
- SQL%FOUND 属性：是否有任何行受到影响？
- SQL%NOTFOUND 属性：没有行受到影响？
- SQL%ROWCOUNT 属性：有多少行受到影响？
- SQL%BULK_ROWCOUNT（请参阅“获取受 FORALL 语句影响的行数”
- SQL%BULK_EXCEPTIONS（请参阅“在 FORALL 语句完成后处理 FORALL 异常”
另请参阅

隐式游标属性

7.2.1.1. SQL%ISOPEN 属性：游标是否打开？

SQL%ISOPEN 总是返回 FALSE，因为隐式游标总是在其关联语句运行后关闭。

7.2.1.2. SQL%FOUND 属性：是否有任何行受到影响？

SQL%FOUND返回： - 如果没有运行 SELECT 或 DML 语句，则为 NULL - 如果 SELECT 语句返回一或多行或 DML 语句影响一或多行，则为 TRUE - 否则为 FALSE

示例 7-3用SQL%FOUND确定DELETE语句是否影响任何行

示例 7-3 SQL%FOUND 隐式游标属性
```
DROP TABLE IF EXISTS stu_temp;
CREATE TABLE stu_temp AS
SELECT * FROM student;

\set SQLTERM /
CREATE OR REPLACE PROCEDURE p (stu_no NUMBER) AS
BEGIN
  DELETE FROM stu_temp WHERE id = stu_no;

  IF SQL%FOUND THEN
    RAISE NOTICE 'Delete succeeded for department number %', stu_no;
  ELSE
    RAISE NOTICE 'No department number %', stu_no;
  END IF;
END;
/
BEGIN
  p(1);
  p(2);
END;
/
```
结果：
```
NOTICE:  Delete succeeded for department number 1
NOTICE:  No department number 2
```
7.2.1.3. SQL%NOTFOUND 属性：没有行受到影响？

SQL%NOTFOUND（SQL%FOUND 的逻辑反义词）返回： - 如果没有运行 SELECT 或 DML 语句，则为 NULL - 如果 SELECT 语句返回一或多行或 DML 语句影响一或多行，则为 FALSE - 否则为TRUE

SQL%NOTFOUND 属性对 PL/SQL SELECT INTO 语句没有用处，因为: - 如果 SELECT INTO 语句没有返回任何行，PL/SQL 会在您检查 SQL%NOTFOUND 之前立即引发预定义的异常 NO_DATA_FOUND。 - 调用 SQL 聚合函数的 SELECT INTO 语句始终返回一个值（可能为 NULL）。在这样的语句之后，SQL%NOTFOUND 属性始终为 FALSE，因此不需要检查它。

7.2.1.4. SQL%ROWCOUNT 属性：有多少行受到影响？

SQL%ROWCOUNT返回： - 如果没有运行 SELECT 或 DML 语句，则为 NULL - 否则，SELECT 语句返回或受 DML 语句影响的行数(整数)

示例 7-4用于SQL%ROWCOUNT确定已删除的行数。

如果没有 BULK COLLECT 子句的 SELECT INTO 语句返回多行，PL/SQL 会引发预定义的异常 TOO_MANY_ROWS 并且 SQL%ROWCOUNT 返回 1，而不是满足查询的实际行数。

SQL%ROWCOUNT 属性的值与事务的状态无关。所以： - 当事务回滚到保存点时，SQL%ROWCOUNT 的值不会恢复到保存点之前的值。 - 当自治事务结束时，SQL%ROWCOUNT 不会恢复到父事务中的原始值。

示例 7-4 SQL%ROWCOUNT 隐式游标属性
```
DROP TABLE student_temp;
CREATE TABLE student_temp AS
SELECT * FROM student;

\set SQLTERM /
DECLARE
  stu_no NUMBER(6) := 1;
BEGIN
  DELETE FROM student_temp WHERE id = stu_no;
  RAISE NOTICE 'Number of student deleted: %', SQL%ROWCOUNT;
END;
/
```
结果：
```
NOTICE:  Number of student deleted: 1
```
7.2.2. 显式游标

显式游标是用户构建和管理的会话游标。您必须声明并定义一个显式游标，为其命名并将其与查询相关联（通常，查询返回多行）。然后可以通过以下任一方式处理查询结果集： - 打开显式游标（使用OPEN语句），从结果集中获取行（使用FETCH语句），然后关闭显式游标（使用CLOSE语句）。 - 在游标FOR LOOP语句中使用显式游标（请参阅“使用游标 FOR LOOP 语句处理查询结果集”。）

您不能为显式游标赋值、在表达式中使用它，或者将其用作正式的子程序参数或主变量。您可以使用游标变量来执行这些操作（请参阅“游标变量”）。

与隐式游标不同，您可以通过名称引用显式游标或游标变量。因此，显式游标或游标变量称为命名游标。

7.2.2.1. 声明和定义显式游标

您可以先声明一个显式游标，然后在同一个块、子程序或包中定义它，或者同时声明和定义它。仅声明游标的显式游标声明具有以下语法：
```
CURSOR cursor_name [ parameter_list ] RETURN return_type;
```
显式游标定义具有以下语法：
```
    CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
IS select_statement;
```
如果您之前声明了游标，那么显式游标定义将定义它；否则，它既声明又定义它。

示例 7-5声明并定义了三个显式游标。

另请参阅
- 显示游标的声明和定义
示例 7-5 显式游标声明和定义
```
\set SQLTERM /
DECLARE
  CURSOR c1 RETURN student%ROWTYPE;    -- 声明 c1

  CURSOR c2 IS                             -- 声明和定义c2
    SELECT id, name, score FROM student
    WHERE score > 90;

  CURSOR c1 RETURN student%ROWTYPE IS  -- 定义c1,
    SELECT * FROM student              -- 定义时带 return type
    WHERE id = 1;
BEGIN
  NULL;
END;
/
```
结果:
```
ANONYMOUS BLOCK
```
```
\set SQLTERM /
DECLARE
  CURSOR c3 RETURN student%ROWTYPE;      -- 声明 c3

  CURSOR c3 IS                           -- 定义 c3,
    SELECT * FROM student                -- 定义时省略 return type，错误
    WHERE id = '1';
BEGIN
  NULL;
END;
/
```
结果:
```
ERROR:  declare or define cursor 'c3' parameters or return dismatch
CONTEXT:  compilation of PL/SQL function "inline_code_block" near line 3
```
7.2.2.2. 打开和关闭显式游标

声明和定义显式游标后，您可以使用以下语句打开它，该OPEN语句执行以下操作： - 分配数据库资源来处理查询 - 处理查询；其中包括:
- 标识结果集
  
  如果查询引用变量或游标参数，它们的值会影响结果集。有关详细信息，请参阅“显式游标查询中的变量”和“接受参数的显式游标”。
- 如果查询有FOR UPDATE子句，则锁定结果集的行
  
  有关详细信息，请参阅“ SELECT FOR UPDATE 和 FOR UPDATE 游标”。
- 将游标定位在结果集的第一行之前
您可以使用 CLOSE 语句关闭打开的显式游标，从而允许重用其资源。关闭游标后，您无法从其结果集中获取记录或引用其属性。如果你尝试，PL/SQL 会引发预定义的异常 INVALID_CURSOR。

您可以重新打开已关闭的游标。您必须先关闭显式游标，然后再尝试重新打开它。否则，PL/SQL 会引发预定义的异常。

7.2.2.3. 使用显式游标获取数据

打开显式游标后，您可以使用该FETCH语句获取查询结果集的行。FETCH返回一行的语句的基本语法是：
```
FETCH cursor_name INTO into_clause
```
into_clause 是变量列表或单个记录变量。对于查询返回的每一列，变量列表或记录必须具有对应的类型兼容变量或字段。%TYPE 和 %ROWTYPE 属性用于声明在 FETCH 语句中使用的变量和记录很有用。

FETCH 语句检索结果集的当前行，将该行的列值存储到变量或记录中，并将游标前进到下一行。

通常，您在 LOOP 语句中使用 FETCH 语句，当 FETCH 语句取完所有行时退出该语句。要检测此退出条件，请使用游标属性 %NOTFOUND（在“%NOTFOUND 属性：未提取行吗？”中进行了描述）。当 FETCH 语句不返回任何行时，PL/SQL 不会引发异常。

示例 7-6 在 LOOP 语句中使用 FETCH 和 %NOTFOUND 一次一行地获取两个显式游标的结果集。第一个 FETCH 语句将列值检索到变量中。第二个 FETCH 语句将列值检索到记录中。变量和记录分别用 %TYPE 和 %ROWTYPE 声明。

示例 7-7 使用五次 FETCH 语句将结果集的前五行提取到五个记录中，每个语句提取到一个不同的记录变量中。记录变量用 %ROWTYPE 声明。

示例 7-6 LOOP 语句中的 FETCH 语句
```
    DROP TABLE IF EXISTS stu_temp;
    CREATE TABLE stu_temp AS
            SELECT id, name FROM student;
    insert into stu_temp values (1001, 'xs');
    insert into stu_temp values (1002, 'xd');
    insert into stu_temp values (1003, 'xc');
    insert into stu_temp values (1004, 'xg');
    insert into stu_temp values (1005, 'xl');

    \set SQLTERM /
    DECLARE
CURSOR c1 IS
      SELECT id, name FROM stu_temp
      WHERE id > 1000;

      v_id  stu_temp.id%TYPE;
      v_name     stu_temp.name%TYPE;

      CURSOR c2 IS
        SELECT * FROM stu_temp
        WHERE id > 1002;

      v_student stu_temp%ROWTYPE;  -- record variable for row of table
    BEGIN
      OPEN c1;
      LOOP
        FETCH c1 INTO v_id, v_name;
        EXIT WHEN c1%NOTFOUND;
        RAISE NOTICE 'id: %, name: %', v_id, v_name;
      END LOOP;
      CLOSE c1;
      RAISE NOTICE '-------------------------------------';

      OPEN c2;
      LOOP  -- Fetches entire row into the v_student record
        FETCH c2 INTO v_student;
        EXIT WHEN c2%NOTFOUND;
        RAISE NOTICE 'id: %, name: %', v_student.id, v_student.name;
      END LOOP;
      CLOSE c2;
    END;
    /
```
结果：
```
NOTICE:  id: 1001, name: xs
NOTICE:  id: 1002, name: xd
NOTICE:  id: 1003, name: xc
NOTICE:  id: 1004, name: xg
NOTICE:  id: 1005, name: xl
NOTICE:  -------------------------------------
NOTICE:  id: 1003, name: xc
NOTICE:  id: 1004, name: xg
NOTICE:  id: 1005, name: xl
```
示例 7-7 将相同的显式游标提取到不同的变量中
```
\set SQLTERM /
DECLARE
  CURSOR c IS
  SELECT id, name
    FROM stu_temp
    WHERE id > 1000;

  -- Record variables for rows of cursor result set:
  stu1 c%ROWTYPE;
  stu2 c%ROWTYPE;
  stu3 c%ROWTYPE;
  stu4 c%ROWTYPE;
  stu5 c%ROWTYPE;
BEGIN
  OPEN c;
  FETCH c INTO stu1;
  FETCH c INTO stu2;
  FETCH c INTO stu3;
  FETCH c INTO stu4;
  FETCH c INTO stu5;
  CLOSE c;

  RAISE NOTICE 'stu1: %', stu1;
  RAISE NOTICE 'stu2: %', stu2;
  RAISE NOTICE 'stu3: %', stu3;
  RAISE NOTICE 'stu4: %', stu4;
  RAISE NOTICE 'stu5: %', stu5;
END;
/
```
结果：
```
NOTICE:  stu1: (1001,xs)
NOTICE:  stu2: (1002,xd)
NOTICE:  stu3: (1003,xc)
NOTICE:  stu4: (1004,xg)
NOTICE:  stu5: (1005,xl)
```
7.2.2.4. 显式游标查询中的变量

显式游标查询可以引用其范围内的任何变量。当您打开一个显式游标时，PL/SQL 计算查询中的任何变量并在识别结果集时使用这些值。稍后更改变量的值不会更改结果集。

在:ref:示例 7-8 中，显式游标查询引用了变量因子。当游标打开时，num 的值为2。因此，score_add_num 始终是 score 的 2 倍，尽管该 num 在每次获取后都会递增。

要更改结果集，您必须关闭游标，更改变量的值，然后再次打开游标，如例 7-9 所示。

示例 7-8 显式游标查询中的变量 - 无结果集更改
```
DROP TABLE IF EXISTS stu_temp;
CREATE TABLE stu_temp AS
  SELECT id, name, score FROM student;
insert into stu_temp values (1001, 'xs', 80);
insert into stu_temp values (1002, 'xd', 82);
insert into stu_temp values (1003, 'xc', 74);
insert into stu_temp values (1004, 'xg', 93);
insert into stu_temp values (1005, 'xl', 61);

\set SQLTERM /
DECLARE
  score           stu_temp.score%TYPE;
  score_add_num  stu_temp.score%TYPE;
  num        INTEGER := 5;

CURSOR c1 IS
  SELECT score, score + num FROM stu_temp
  WHERE id > 1000;
BEGIN
  OPEN c1;  -- PL/SQL evaluates num
  LOOP
    FETCH c1 INTO score, score_add_num;
    EXIT WHEN c1%NOTFOUND;
    RAISE NOTICE 'num = %', num;
    RAISE NOTICE 'score = %', score;
    RAISE NOTICE 'score_add_num = %', score_add_num;
    num := num + 1;  -- 不影响 score_add_num 的值
  END LOOP;
  CLOSE c1;
END;
/
```
结果：
```
NOTICE:  num = 5
NOTICE:  score = 80
NOTICE:  score_add_num = 85
NOTICE:  num = 6
NOTICE:  score = 82
NOTICE:  score_add_num = 87
NOTICE:  num = 7
NOTICE:  score = 74
NOTICE:  score_add_num = 79
NOTICE:  num = 8
NOTICE:  score = 93
NOTICE:  score_add_num = 98
NOTICE:  num = 9
NOTICE:  score = 61
```
NOTICE: score_add_num = 66

示例 7-9 显式游标查询中的变量 - 结果集更改
```
\set SQLTERM /
DECLARE
  score           stu_temp.score%TYPE;
  score_add_num  stu_temp.score%TYPE;
  num        INTEGER := 5;

CURSOR c1 IS
  SELECT score, score+num FROM stu_temp
  WHERE id > 1000;

BEGIN
  RAISE NOTICE 'num = %', num;
  OPEN c1;
  LOOP
    FETCH c1 INTO score, score_add_num;
    EXIT WHEN c1%NOTFOUND;
    RAISE NOTICE 'score = %', score;
    RAISE NOTICE 'score_add_num = %', score_add_num;
  END LOOP;
  CLOSE c1;

  num := num + 5;

  RAISE NOTICE 'num = %', num;
  OPEN c1;
  LOOP
    FETCH c1 INTO score, score_add_num;
    EXIT WHEN c1%NOTFOUND;
    RAISE NOTICE 'score = %', score;
    RAISE NOTICE 'score_add_num = %', score_add_num;
  END LOOP;
  CLOSE c1;
END;
/
```
结果：
```
NOTICE:  num = 5
NOTICE:  score = 80
NOTICE:  score_add_num = 85
NOTICE:  score = 82
NOTICE:  score_add_num = 87
NOTICE:  score = 74
NOTICE:  score_add_num = 79
NOTICE:  score = 93
NOTICE:  score_add_num = 98
NOTICE:  score = 61
NOTICE:  score_add_num = 66
NOTICE:  num = 10
NOTICE:  score = 80
NOTICE:  score_add_num = 90
NOTICE:  score = 82
NOTICE:  score_add_num = 92
NOTICE:  score = 74
NOTICE:  score_add_num = 84
NOTICE:  score = 93
NOTICE:  score_add_num = 103
NOTICE:  score = 61
NOTICE:  score_add_num = 71
```
7.2.2.5. 当显式游标查询需要列别名时

当显式游标查询包含虚拟列（表达式）时，如果满足以下任一条件，则该列必须具有别名： - 您使用游标来获取使用 %ROWTYPE 声明的记录。 - 您想在程序中引用虚拟列。

在:ref:示例 7-10 中，显式游标中的虚拟列需要一个别名。

示例 7-10 具有需要别名的虚拟列的显式游标
```
\set SQLTERM /
DECLARE
  CURSOR c1 IS
    SELECT id, name, (score * 0.2) fina_score
    FROM student
    WHERE id = 1;
  stu_rec c1%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO stu_rec;
    EXIT WHEN c1%NOTFOUND;
        RAISE NOTICE 'student %:% fina_score =  %',
              stu_rec.id, stu_rec.name, stu_rec.fina_score;
  END LOOP;
  CLOSE c1;
END;
/
```
结果：
```
NOTICE:  student 1:xx fina_score =  19.6
```
7.2.2.6. 接受参数的显式游标

您可以创建一个具有形参的显式游标，然后在每次打开游标时将不同的实际参数传递给游标。在游标查询中，您可以在可以使用常量的任何地方使用形式游标参数。在游标查询之外，您不能引用正式游标参数。

提示

为避免混淆，请对正式游标参数和实际游标参数使用不同的名称。

示例 7-11创建了一个显式游标，它的形式参数代表它的得分标准线。查询得分超过得分标准线的学生。接下来，该示例创建一个打印游标查询结果集的过程（有关过程的信息，请参阅 PL/SQL 子程序）。最后，例子传入实参打开游标，打印结果集，关闭游标，用不同的实参打开游标，打印结果集，关闭游标。

示例 7-11 接受参数的显式游标
```
\set SQLTERM /
DECLARE
  CURSOR c (std_score NUMBER) IS
    SELECT id, name, (score - std_score) overstd
    FROM stu_temp
    WHERE score > std_score
    ORDER BY score;

  PROCEDURE show_overstd IS
    id stu_temp.id%TYPE;
    name  student.name%TYPE;
    overstd     student.score%TYPE;
  BEGIN
    LOOP
      FETCH c INTO id, name, overstd;
      EXIT WHEN c%NOTFOUND;
              RAISE NOTICE 'id: %, name: %, overstd: %',id ,name ,overstd;
    END LOOP;
  END show_overstd;
  std_score number;
BEGIN
  std_score = 80;
  RAISE NOTICE 'std_score = %',std_score;
  OPEN c(80);
  show_overstd;
  CLOSE c;

      std_score = 90;
  RAISE NOTICE '-------------------------------';
      RAISE NOTICE 'std_score = %',std_score;
  OPEN c(90);
  show_overstd;
  CLOSE c;
END;
/
```
结果：
```
NOTICE:  std_score = 80
NOTICE:  id: 1002, name: xd, overstd: 2
NOTICE:  id: 1004, name: xg, overstd: 13
NOTICE:  id: 1, name: xx, overstd: 18
NOTICE:  -------------------------------
NOTICE:  std_score = 90
NOTICE:  id: 1004, name: xg, overstd: 3
NOTICE:  id: 1, name: xx, overstd: 8
```
7.2.2.6.1. 具有默认值的正式游标参数

当您使用形式参数创建显式游标时，您可以为它们指定默认值。当形参有默认值时，其对应的实参是可选的。如果打开游标而不指定实参，则形参有其默认值。

示例 7-12 创建了一个显式游标，其形参表示得分。该参数的默认值为 60，表示默认及格分数线。

示例 7-12 具有默认值的游标参数
```
\set SQLTERM /
DECLARE
  CURSOR c (std_score NUMBER DEFAULT 60) IS
  SELECT id, name, score
    FROM stu_temp
    WHERE score > std_score;

  PROCEDURE show_up_to_std IS
    id  student.id%TYPE;
    name   student.name%TYPE;
    score  student.score%TYPE;
  BEGIN
    LOOP
      FETCH c INTO id, name, score;
      EXIT WHEN c%NOTFOUND;
      RAISE NOTICE 'id: %, name: %, score: %',id ,name ,score;
    END LOOP;
  END show_up_to_std;

BEGIN
  RAISE NOTICE 'default std_score';
  OPEN c;
  show_up_to_std;
  RAISE NOTICE '--------------------------------';
  CLOSE c;

  RAISE NOTICE 'std_score = 70';
  RAISE NOTICE '--------------------------------';
  OPEN c(70);
  show_up_to_std;
  CLOSE c;

  RAISE NOTICE 'std_score = 80';
  OPEN c(80);
  show_up_to_std;
  CLOSE c;
END;
/
```
结果：
```
NOTICE:  default std_score
NOTICE:  id: 1, name: xx, score: 98
NOTICE:  id: 1001, name: xs, score: 80
NOTICE:  id: 1002, name: xd, score: 82
NOTICE:  id: 1003, name: xc, score: 74
NOTICE:  id: 1004, name: xg, score: 93
NOTICE:  id: 1005, name: xl, score: 61
NOTICE:  --------------------------------
NOTICE:  std_score = 70
NOTICE:  --------------------------------
NOTICE:  id: 1, name: xx, score: 98
NOTICE:  id: 1001, name: xs, score: 80
NOTICE:  id: 1002, name: xd, score: 82
NOTICE:  id: 1003, name: xc, score: 74
NOTICE:  id: 1004, name: xg, score: 93
NOTICE:  std_score = 80
NOTICE:  id: 1, name: xx, score: 98
NOTICE:  id: 1002, name: xd, score: 82
NOTICE:  id: 1004, name: xg, score: 93
```
7.2.2.6.2. 添加带默认值的正式游标参数

如果将形参添加到游标，并为添加的参数指定默认值，则无需更改对游标的现有引用。比较例 7-13 和例 7-11。

示例 7-13 向现有游标添加形式参数
```
      \set SQLTERM /
      DECLARE
      CURSOR c (std_score NUMBER, extra_score NUMBER default 0) IS
    SELECT id, name, (score + extra_score - std_score) overstd
    FROM stu_temp
    WHERE score > std_score - extra_score;

  PROCEDURE show_overstd IS
    id   student.id%TYPE;
    name  student.name%TYPE;
    overstd      student.score%TYPE;
  BEGIN
    LOOP
      FETCH c INTO id, name, overstd;
      EXIT WHEN c%NOTFOUND;
      RAISE NOTICE 'id: %, name: %, overstd: %',id ,name ,overstd;
    END LOOP;
  END show_overstd;

  extra_score NUMBER = 10;
BEGIN
      RAISE NOTICE 'default extra_score';
  OPEN c(80);
  show_overstd;
  CLOSE c;

  RAISE NOTICE 'extra_score = %', extra_score;
  OPEN c(80, extra_score);
                          -- new reference
  show_overstd;
  CLOSE c;
END;
/
```
结果：
```
NOTICE:  default extra_score
NOTICE:  id: 1, name: xx, overstd: 18
NOTICE:  id: 1002, name: xd, overstd: 2
NOTICE:  id: 1004, name: xg, overstd: 13
NOTICE:  extra_score = 10
NOTICE:  id: 1, name: xx, overstd: 28
NOTICE:  id: 1001, name: xs, overstd: 10
NOTICE:  id: 1002, name: xd, overstd: 12
NOTICE:  id: 1003, name: xc, overstd: 4
NOTICE:  id: 1004, name: xg, overstd: 23
```
7.2.2.7. 显式游标属性

显式游标属性值的语法 cursor_name 紧跟在后面 attribute （例如，c1%ISOPEN）。

注意

显式游标和游标变量（命名游标）具有相同的属性。本主题适用于所有命名游标，除非另有说明。

显式游标属性是：
- %ISOPEN 属性：游标是否打开？
- %FOUND 属性：是否提取了一行？
- %NOTFOUND 属性：没有提取任何行吗？
- %ROWCOUNT 属性：提取了多少行？
如果未打开显式游标，则引用除此之外的任何属性%ISOPEN都会引发预定义异常INVALID_CURSOR。

7.2.2.7.1. %ISOPEN 属性：游标是否打开？

如果显式游标打开，%ISOPEN 返回 TRUE；否则为FALSE。

%ISOPEN适用于： - 在尝试打开显式游标之前检查它是否尚未打开。 - 如果你试图打开一个已经打开的显式游标，PL/SQL 会引发预定义的异常CURSOR_ALREADY_OPEN。您必须先关闭显式游标，然后才能重新打开它。

注意

前一段不适用于游标变量。
- 在尝试关闭显式游标之前检查它是否打开。
示例 7-14仅当显式游标c1未打开时才打开它，仅当它打开时才将其关闭。

示例 7-14 %ISOPEN 显式游标属性
```
\set SQLTERM /
DECLARE
  CURSOR c1 IS
    SELECT id, score FROM stu_temp
    WHERE ROWNUM < 3;

  id    stu_temp.id%TYPE;
  score stu_temp.score%TYPE;
BEGIN
  IF NOT c1%ISOPEN THEN
    OPEN c1;
  END IF;

  FETCH c1 INTO id, score;

  IF c1%ISOPEN THEN
    CLOSE c1;
  END IF;
END;
/
```
7.2.2.7.2. %FOUND 属性：是否已获取行？

%FOUND返回： - 在显式游标打开之后但在第一次提取之前为 NULL - 如果最近从显式游标中提取返回一行，则为 TRUE - 否则为 FALSE

%FOUND对于确定是否有要处理的提取行很有用。

示例 7-15循环遍历一个结果集，打印每个提取的行并在没有更多行可提取时退出。

示例 7-15 %FOUND 显式游标属性
```
\set SQLTERM /
DECLARE
  CURSOR c1 IS
    SELECT id, score FROM stu_temp
    WHERE ROWNUM < 3
    ORDER BY id;

  my_id   student.id%TYPE;
  my_score  student.score%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO my_id, my_score;
    IF c1%FOUND THEN  -- fetch succeeded
              RAISE NOTICE 'id = %, score = %',my_id ,my_score;
    ELSE  -- fetch failed
      EXIT;
    END IF;
  END LOOP;
END;
/
```
结果：
```
NOTICE:  id = 1, score = 98
NOTICE:  id = 1001, score = 80
```
7.2.2.7.3. %NOTFOUND 属性：没有获取行吗？

%NOTFOUND（与 %FOUND 逻辑相反）返回： - 在显式游标打开之后但在第一次提取之前为 NULL - 如果最近从显式游标中提取返回一行，则为 FALSE - 否则为TRUE

当 FETCH 无法返回一行时，%NOTFOUND 对于退出循环很有用，如示例 7-16 所示。

示例 7-16 %NOTFOUND 显式游标属性
```
\set SQLTERM /
DECLARE
  CURSOR c1 IS
    SELECT id, score FROM stu_temp
    WHERE ROWNUM < 3;

   my_id   student.id%TYPE;
   my_score  student.score%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO my_id, my_score;
    IF c1%NOTFOUND THEN -- fetch failed
      EXIT;
    ELSE  -- fetch succeeded
      RAISE NOTICE 'id = %, score = %',my_id ,my_score;
    END IF;
  END LOOP;
END;
/
```
结果：
```
NOTICE:  id = 1, score = 98
NOTICE:  id = 1001, score = 80
```
7.2.2.7.4. %ROWCOUNT 属性：获取了多少行？

%ROWCOUNT返回： - 在显式游标打开之后但在第一次获取之前为零 - 否则，获取的行数 (整数)

示例 7-17编号并打印它获取的行，并在获取第五行后打印一条消息。

示例 7-17 %ROWCOUNT 显式游标属性
```
\set SQLTERM /
DECLARE
  CURSOR c1 IS
    SELECT name FROM stu_temp
    WHERE ROWNUM < 10
    ORDER BY name;

  name  student.name%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO name;
    EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
        RAISE NOTICE '%. %',c1%ROWCOUNT ,name;
    IF c1%ROWCOUNT = 3 THEN
       RAISE NOTICE 'FETCH 3 ROWS';
    END IF;
  END LOOP;
  CLOSE c1;
END;
/
```
结果：
```
NOTICE:  1. xc
NOTICE:  2. xd
NOTICE:  3. xg
NOTICE:  FETCH 3 ROWS
NOTICE:  4. xl
NOTICE:  5. xs
NOTICE:  6. xx
```
7.3. 处理查询结果集

在 PL/SQL 中，与在传统数据库编程中一样，您使用游标来处理查询结果集。但是，在 PL/SQL 中，您可以使用隐式或显式游标。

前者需要更少的代码，但后者更灵活。例如，显式游标可以接受参数。

以下 PL/SQL 语句使用 PL/SQL 为您定义和管理的隐式游标： - SELECT INTO - 隐式游标FOR LOOP

以下 PL/SQL 语句使用显式游标： - 显式游标FOR LOOP

您定义显式游标，但 PL/SQL 在语句运行时对其进行管理。
- OPEN, FETCH, 和CLOSE
  
  您定义和管理显式游标。
注意

如果查询没有返回任何行，PL/SQL 就会引发异常NO_DATA_FOUND。

7.3.1. 使用 SELECT INTO 语句处理查询结果集

使用隐式游标，SELECT INTO 语句从一个或多个数据库表中检索值（就像 SQL SELECT 语句所做的那样）并将它们存储在变量中（SQL SELECT 语句不这样做）。

7.3.1.1. 处理单行结果集

如果您希望查询只返回一行，则使用 SELECT INTO 语句将该行的值存储在一个或多个标量变量或一个记录变量中。

如果查询可能返回多行，但您只关心第 n 行，则使用子句 WHERE ROWNUM=n 将结果集限制为该行。

7.3.1.2. 处理大型多行结果集

如果必须将大量表数据分配给变量，KingbaseES 建议使用带有 BULK COLLECT 子句的 SELECT INTO 语句。

此语句将整个结果集检索到一个或多个集合变量中。

7.3.2. 使用游标 FOR LOOP 语句处理查询结果集

游标 FOR LOOP 语句允许您运行 SELECT 语句，然后立即循环遍历结果集的行。

此语句可以使用隐式或显式游标（但不能使用游标变量）。

如果仅在游标 FOR LOOP 语句中使用 SELECT 语句，则在游标 FOR LOOP 语句中指定 SELECT 语句。这种形式的游标 FOR LOOP 语句使用隐式游标，称为隐式游标 FOR LOOP 语句。因为隐式游标在语句内部，所以不能使用名称 SQL 来引用它。

如果在同一个 PL/SQL 单元中多次使用 SELECT 语句，则为它定义一个显式游标并在游标 FOR LOOP 语句中指定该游标。这种形式的游标 FOR LOOP 语句称为显式游标 FOR LOOP 语句。您可以在同一 PL/SQL 单元的其他地方使用相同的显式游标。

游标 FOR LOOP 语句将其循环索引隐式声明为其游标返回的类型的 %ROWTYPE记录变量。此记录是循环本地的，仅在循环执行期间存在。循环内的语句可以引用记录及其字段。他们只能通过别名引用虚拟列。

声明循环索引记录变量后，FOR LOOP 语句打开指定的游标。对于循环的每次迭代，FOR LOOP 语句从结果集中取出一行并将其存储在记录中。当没有更多行要获取时，游标 FOR LOOP 语句将关闭游标。如果循环内的语句将控制转移到循环外或 PL/SQL 引发异常，则游标也会关闭。

注意

当游标语句中引发异常时FOR LOOP，游标会在异常处理程序运行之前关闭。因此，显式游标属性的值在处理程序中不可用。

示例 7-18 隐式游标 FOR LOOP 语句

在此示例中，隐式游标 FOR LOOP 语句打印学生 ID 大于 1000 的每个学生的姓名和得分。
```
\set SQLTERM /
BEGIN
  FOR item IN (
    SELECT name, score
    FROM stu_temp
    WHERE id > 1000
  )
  LOOP
    RAISE NOTICE 'name: %, score: %',item.name, item.score;
  END LOOP;
END;
/
```
结果：
```
NOTICE:  name: xs, score: 80
NOTICE:  name: xd, score: 82
NOTICE:  name: xc, score: 74
NOTICE:  name: xg, score: 93
NOTICE:  name: xl, score: 61
```
示例 7-19 显式游标 FOR LOOP 语句

此示例与示例 7-18类似，不同之处在于它使用了显式游标FOR LOOP语句。
```
\set SQLTERM /
DECLARE
  CURSOR c1 IS
    SELECT name, score FROM stu_temp
    WHERE id > 1000;
BEGIN
  FOR item IN c1
  LOOP
    RAISE NOTICE 'name: %, score: %',item.name, item.score;
  END LOOP;
END;
/
```
结果：
```
NOTICE:  name: xs, score: 80
NOTICE:  name: xd, score: 82
NOTICE:  name: xc, score: 74
NOTICE:  name: xg, score: 93
NOTICE:  name: xl, score: 61
```
示例 7-20 将参数传递给显式游标 FOR LOOP 语句

此示例声明并定义了一个显式游标，该游标接受两个参数，然后在显式游标 FOR LOOP 语句中使用它来显示支付给指定部门中收入超过指定工资的员工的工资。
```
\set SQLTERM /
DECLARE
  CURSOR c1 (std_score NUMBER) IS
    SELECT * FROM stu_temp
    WHERE id > 1000
    AND score > std_score;
BEGIN
  FOR person IN c1(70)
  LOOP
    RAISE NOTICE 'id = %, name: %, score: %',person.id, person.name, person.score;
  END LOOP;
END;
/
```
结果：
```
NOTICE:  id = 1001, name: xs, score: 80
NOTICE:  id = 1002, name: xd, score: 82
NOTICE:  id = 1003, name: xc, score: 74
NOTICE:  id = 1004, name: xg, score: 93
```
示例 7-21 游标 FOR 循环引用虚拟列

在此示例中，隐式游标 FOR LOOP 通过其别名 id_name 和 dream_score 引用虚拟列。
```
\set SQLTERM /
BEGIN
  FOR item IN (
    SELECT id || '.' || name AS id_name,
           score * 10 AS dream_score
    FROM stu_temp
    WHERE ROWNUM <= 5
  ) LOOP
    RAISE NOTICE 'id_name: %, dream_score: %', item.id_name, item.dream_score;
  END LOOP;
END;
/
```
结果：
```
NOTICE:  id_name: 1.xx, dream_score: 980
NOTICE:  id_name: 1001.xs, dream_score: 800
NOTICE:  id_name: 1002.xd, dream_score: 820
NOTICE:  id_name: 1003.xc, dream_score: 740
NOTICE:  id_name: 1004.xg, dream_score: 930
```
7.3.3. 使用显式游标、OPEN、FETCH 和 CLOSE 处理查询结果集

要完全控制查询结果集处理，请声明显式游标并使用语句 OPEN、FETCH 和 CLOSE 管理它们。

这种结果集处理技术比其他技术更复杂，但也更灵活。例如，您可以： - 使用多个游标并行处理多个结果集。 - 在单个循环迭代中处理多行、跳过行或将处理拆分为多个循环。 - 在一个 PL/SQL 单元中指定查询，但在另一个单元中检索行。

7.3.4. 处理带有子查询的查询结果集

如果您通过循环处理查询结果集并为每一行运行另一个查询，那么您可以通过从循环内部删除第二个查询并使其成为第一个查询的子查询来提高性能。

每个表都执行了一个普通的子查询，表中每一行也执行了一个相关的子查询。

示例 7-22 父查询的 FROM 子句中的子查询

此示例使用其 FROM 子句包含子查询的查询定义显式游标 c1。
```
\set SQLTERM /
DECLARE
  CURSOR c1 IS
    SELECT t1.id, t1.name, t1.score
    FROM student t1,
         ( SELECT id, COUNT(*) AS count
           FROM stu_temp
           GROUP BY id
         ) t2
    WHERE (t1.id = t2.id) AND count >= 1
    ORDER BY count;

BEGIN
   FOR item IN c1
   LOOP
     RAISE NOTICE 'id: %, name: %, score: %', item.id, item.name, item.score;
   END LOOP;
END;
/
```
结果：
```
NOTICE:  id: 1, name: xx, score: 98
```
示例 7-23 相关子查询

此示例返回得分超过学生平均得分的每位学生的姓名和得分。对于表中的每一行，相关子查询计算相应部门的平均工资。
```
\set SQLTERM /
DECLARE
  CURSOR c1 IS
    SELECT id, name, score
    FROM stu_temp t
    WHERE score > ( SELECT AVG(score)
                     FROM stu_temp
                  )
    ORDER BY id;
BEGIN
  FOR person IN c1
  LOOP
    RAISE NOTICE 'Making above-average score = %', person.name;
  END LOOP;
END;
/
```
结果：
```
NOTICE:  Making above-average score = xx
NOTICE:  Making above-average score = xd
NOTICE:  Making above-average score = xg
```
7.4. 游标变量

游标变量类似于显式游标，除了：
- 它不限于一个查询。
  
  您可以为查询打开游标变量，处理结果集，然后将游标变量用于另一个查询。
- 您可以为其分配一个值。
- 您可以在表达式中使用它。
- 它可以是子程序参数。
  
  您可以使用游标变量在子程序之间传递查询结果集。
- 它可以是主机变量。
  
  您可以使用游标变量在 PL/SQL 存储的子程序及其客户端之间传递查询结果集。
- 它不能接受参数。
  
  您不能将参数传递给游标变量，但可以将整个查询传递给它。查询可以包括变量。
游标变量具有这种灵活性，因为它是一个指针；也就是说，它的值是一个项目的地址，而不是项目本身。

在您可以引用游标变量之前，您必须使其指向一个 SQL 工作区，方法是打开它或为其分配一个打开的 PL/SQL 游标变量或打开的宿主游标变量的值。

注意

游标变量和显式游标是不可互换的——你不能在期望一个的地方使用另一个。

7.4.1. 创建游标变量

要创建游标变量，要么声明预定义类型 SYS_REFCURSOR 的变量，要么定义 REF CURSOR 类型，然后声明该类型的变量。

注意

非正式地，游标变量有时称为 REF CURSOR)。

REF CURSOR类型定义的基本语法是：
```
TYPE type_name IS REF CURSOR [ RETURN return_type ]
```
有关完整的语法和语义，请参阅“游标变量声明”。

如果指定return_type，则该类型的REF CURSOR类型和游标变量是强游标；如果不是，他们就是弱游标。SYS_REFCURSOR和该类型的游标变量就是弱游标。

使用强游标变量，您只能关联返回指定类型的查询。使用弱游标变量，您可以关联任何查询。

弱游标变量比强游标变量更容易出错，但它们也更灵活。弱REF CURSOR类型可以相互互换，也可以与预定义类型SYS_REFCURSOR互换。您可以将弱游标变量的值分配给任何其他弱游标变量。

只有当两个游标变量具有相同的类型（不仅仅是相同的返回类型）时，您才能将强游标变量的值分配给另一个强游标变量。

注意

您只能使用 PARTITION BY ANY 子句将弱游标变量参数分区到表函数，而不能使用 PARTITION BY RANGE 或 PARTITION BY HASH。

有关语法和语义，请参阅“ PARALLEL_ENABLE 子句”。

示例 7-24 游标变量声明

此示例定义了强和弱REF CURSOR类型、这些类型的变量以及预定义类型SYS_REFCURSOR的变量。
```
\set SQLTERM /
DECLARE
  TYPE strong_cursor IS REF CURSOR RETURN student%ROWTYPE;
  TYPE weak_cursor IS REF CURSOR;

  cursor1  strong_cursor;   -- 强游标
  cursor2  weak_cursor;     -- 弱游标
  my_cursor SYS_REFCURSOR;  -- 弱游标

  TYPE stu_cur_typ IS REF CURSOR RETURN student%ROWTYPE;
  stu_csr stu_cur_typ;  -- 强游标
BEGIN
  NULL;
END;
/
```
示例 7-25 具有用户定义的返回类型的游标变量

在本例中，StuRecTyp是用户定义的RECORD类型。
```
\set SQLTERM /
DECLARE
  TYPE StuRecTyp IS RECORD (
    id NUMBER,
    name TEXT,
    score   NUMBER);

  TYPE StuCurTyp IS REF CURSOR RETURN StuRecTyp;
  stu_cv StuCurTyp;
BEGIN
  NULL;
END;
/
```
7.4.2. 打开和关闭游标变量

声明游标变量后，您可以使用 OPEN FOR 语句打开它，该语句执行以下操作：
1. 将游标变量与查询相关联（通常，查询返回多行）
  
  查询可以包含绑定变量的占位符，您在 OPEN FOR 语句的 USING 子句中指定其值。
2. 分配数据库资源来处理查询
3. 处理查询，其中包括：
  
  a.标识结果集
  
  如果查询引用变量，它们的值会影响结果集。有关详细信息，请参阅“游标变量查询中的变量”。
  
  b.如果查询有 FOR UPDATE 子句，则锁定结果集的行
  
  有关详细信息，请参阅“SELECT FOR UPDATE 和 FOR UPDATE 游标”。
4. 将游标定位在结果集的第一行之前
在重新打开游标变量之前不需要关闭它（即在另一个 OPEN FOR 语句中使用它）。重新打开游标变量后，之前与其关联的查询将丢失。

当您不再需要游标变量时，使用 CLOSE 语句将其关闭，从而允许重用其资源。关闭游标变量后，您无法从其结果集中获取记录或引用其属性。如果你尝试，PL/SQL 会引发预定义的异常 INVALID_CURSOR。

您可以重新打开已关闭的游标变量。

7.4.3. 使用游标变量获取数据

打开游标变量后，您可以使用 FETCH 语句获取查询结果集的行。

游标变量的返回类型必须与 FETCH 语句的 into_clause 兼容。如果游标变量为强游标，PL/SQL 会在编译时捕获不兼容性。如果游标变量是弱游标，PL/SQL 会在运行时捕获不兼容性，在第一次 FETCH 之前引发预定义的异常 ROWTYPE_MISMATCH。

示例 7-26 使用游标变量获取数据

这个例子使用一个游标变量来完成例 7-6 对两个显式游标所做的事情。第一个 OPEN FOR 语句包括查询本身。第二个 OPEN FOR 语句引用一个其值为查询的变量。
```
\set SQLTERM /
DECLARE
  cv SYS_REFCURSOR;

  v_id  stu_temp.id%TYPE;
  v_name     stu_temp.name%TYPE;

  query TEXT :=
    'SELECT * FROM stu_temp
    WHERE id > 1000';

  v_stu stu_temp%ROWTYPE;  -- record variable row of table

BEGIN
  OPEN cv FOR
    SELECT id, name FROM stu_temp
    WHERE id > 1002;

  LOOP
    FETCH cv INTO v_id, v_name;
    EXIT WHEN cv%NOTFOUND;
    RAISE NOTICE 'v_id: %, v_name: %', v_id, v_name;
  END LOOP;

  RAISE NOTICE '-------------------------------------';

  OPEN cv FOR query;

  LOOP
    FETCH cv INTO v_stu;
    EXIT WHEN cv%NOTFOUND;
        RAISE NOTICE 'v_id: %, v_name: %', v_stu.id, v_stu.name;
  END LOOP;

  CLOSE cv;
END;
/
```
结果：
```
NOTICE:  v_id: 1003, v_name: xc
NOTICE:  v_id: 1004, v_name: xg
NOTICE:  v_id: 1005, v_name: xl
NOTICE:  -------------------------------------
NOTICE:  v_id: 1001, v_name: xs
NOTICE:  v_id: 1002, v_name: xd
NOTICE:  v_id: 1003, v_name: xc
NOTICE:  v_id: 1004, v_name: xg
NOTICE:  v_id: 1005, v_name: xl
```
示例 7-27 从游标变量中提取到集合中

此示例使用 FETCH 语句的 BULK COLLECT 子句从游标变量中提取到两个集合（嵌套表）中。
```
\set SQLTERM /
DECLARE
  TYPE stucurtyp IS REF CURSOR;
  TYPE namelist IS TABLE OF student.name%TYPE;
  TYPE scorelist IS TABLE OF student.score%TYPE;
  stu_cv  stucurtyp;
  names   namelist;
  scores    scorelist;
BEGIN
  OPEN stu_cv FOR
    SELECT name, score FROM stu_temp
    WHERE id > 1000
    ORDER BY score DESC;

  FETCH stu_cv BULK COLLECT INTO names, scores;
  CLOSE stu_cv;
  -- loop through the names and scores collections
  FOR i IN names.FIRST .. names.LAST
  LOOP
        RAISE NOTICE 'Name = %, Score = %', names(i), scores(i);
  END LOOP;
END;
/
```
结果：
```
NOTICE:  Name = xg, Score = 93
NOTICE:  Name = xd, Score = 82
NOTICE:  Name = xs, Score = 80
NOTICE:  Name = xc, Score = 74
NOTICE:  Name = xl, Score = 61
```
7.4.4. 为游标变量赋值

您可以将另一个 PL/SQL 游标变量或宿主游标变量的值赋给 PL/SQL 游标变量。

语法是：
```
target_cursor_variable := source_cursor_variable;
```
如果 source_cursor_variable 是开放的，那么在赋值之后，target_cursor_variable 也是开放的。这两个游标变量指向同一个 SQL 工作区。

如果 source_cursor_variable 没有打开，赋值后打开 target_cursor_variable 不会打开 source_cursor_variable。

7.4.5. 游标变量查询中的变量

与游标变量关联的查询可以引用其范围内的任何变量。

当您使用 OPEN FOR 语句打开游标变量时，PL/SQL 会计算查询中的任何变量并在识别结果集时使用这些值。稍后更改变量的值不会更改结果集。

要更改结果集，您必须更改变量的值，然后为相同的查询再次打开游标变量，如示例 7-29 所示。

示例 7-28 游标变量查询中的变量 - 无结果集更改

此示例为引用变量 num 的查询打开一个游标变量，该变量的值为 5。因此，score_add_num 始终比 score 大 5，尽管每次提取后该因子都会递增。
```
\set SQLTERM /
DECLARE
  score           student.score%TYPE;
  score_add_num  student.score%TYPE;
  num        INTEGER := 5;
  cv SYS_REFCURSOR;
BEGIN
  OPEN cv FOR
    SELECT score, score+num
    FROM stu_temp
    WHERE id > 1000;

  LOOP
    FETCH cv INTO score, score_add_num;
    EXIT WHEN cv%NOTFOUND;
    RAISE NOTICE 'num = %', num;
    RAISE NOTICE 'score = %', score;
    RAISE NOTICE 'score_add_num = %', score_add_num;
    num := num + 1;
  END LOOP;

  CLOSE cv;
END;
/
```
结果：
```
NOTICE:  num = 5
NOTICE:  score = 80
NOTICE:  score_add_num = 85
NOTICE:  num = 6
NOTICE:  score = 82
NOTICE:  score_add_num = 87
NOTICE:  num = 7
NOTICE:  score = 74
NOTICE:  score_add_num = 79
NOTICE:  num = 8
NOTICE:  score = 93
NOTICE:  score_add_num = 98
NOTICE:  num = 9
NOTICE:  score = 61
NOTICE:  score_add_num = 66
```
示例 7-29 游标变量查询中的变量——结果集更改
```
\set SQLTERM /
DECLARE
  score           stu_temp.score%TYPE;
  score_add_num  stu_temp.score%TYPE;
  num        INTEGER := 5;

  cv SYS_REFCURSOR;

BEGIN
  RAISE NOTICE 'num = %', num;

  OPEN cv FOR
    SELECT score, score+num
    FROM stu_temp
    WHERE id > 1000;

  LOOP
    FETCH cv INTO score, score_add_num;
    EXIT WHEN cv%NOTFOUND;
    RAISE NOTICE 'score = %', score;
    RAISE NOTICE 'score_add_num = %', score_add_num;
  END LOOP;

  num := num + 1;

  RAISE NOTICE 'num = %', num;

  OPEN cv FOR
    SELECT score, score+num
    FROM stu_temp
    WHERE id > 1000;

  LOOP
    FETCH cv INTO score, score_add_num;
    EXIT WHEN cv%NOTFOUND;
    RAISE NOTICE 'score = %', score;
    RAISE NOTICE 'score_add_num = %', score_add_num;
  END LOOP;

  CLOSE cv;
END;
/
```
结果：
```
NOTICE:  num = 5
NOTICE:  score = 80
NOTICE:  score_add_num = 85
NOTICE:  score = 82
NOTICE:  score_add_num = 87
NOTICE:  score = 74
NOTICE:  score_add_num = 79
NOTICE:  score = 93
NOTICE:  score_add_num = 98
NOTICE:  score = 61
NOTICE:  score_add_num = 66
NOTICE:  num = 6
NOTICE:  score = 80
NOTICE:  score_add_num = 86
NOTICE:  score = 82
NOTICE:  score_add_num = 88
NOTICE:  score = 74
NOTICE:  score_add_num = 80
NOTICE:  score = 93
NOTICE:  score_add_num = 99
NOTICE:  score = 61
NOTICE:  score_add_num = 67
```
7.4.6. 查询集合

如果满足以下所有条件，则可以查询集合： - 集合的数据类型要么在模式级别创建，要么在包规范中声明。 - 集合元素的数据类型可以是标量数据类型、用户定义类型或记录类型。

在查询 FROM 子句中，集合作为 TABLE 运算符的参数出现在 table_collection_expression 中。

注意

在 SQL 上下文中，您不能使用其返回类型已在包规范中声明的函数。

示例 7-30 使用静态 SQL 查询集合

在此示例中，游标变量与对关联记录数组的查询相关联。嵌套表类型 ass_type 在包规范中声明。
```
\set SQLTEMR /
CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
  TYPE rec IS RECORD(f1 NUMBER, f2 TEXT);
  TYPE nest_type IS TABLE OF rec;
END;
/
DECLARE
  v1 pkg.nest_type = pkg.nest_type((1, 'test'));  -- collection of records
  v2 pkg.rec;
  c1 SYS_REFCURSOR;
BEGIN
  OPEN c1 FOR SELECT * FROM TABLE(v1);
  FETCH c1 INTO v2;
  CLOSE c1;
  RAISE NOTICE 'Values in record are % and %', v2.f1, v2.f2;
END;
/
```
结果：
```
NOTICE:  Values in record are 1 and test
```
7.4.7. 游标变量属性

游标变量具有与显式游标相同的属性（请参阅显式游标属性。）。游标变量属性值的语法是 cursor_variable_name 后跟属性（例如，cv%ISOPEN）。如果游标变量未打开，则引用除 %ISOPEN 之外的任何属性都会引发预定义异常 INVALID_CURSOR。

7.4.8. 游标变量作为子程序参数

您可以将游标变量用作子程序参数，这对于在子程序之间传递查询结果很有用。

例如： - 您可以在一个子程序中打开一个游标变量并在另一个子程序中处理它。 - 在多语言应用程序中，PL/SQL 子程序可以使用游标变量将结果集返回给以不同语言编写的子程序。

注意

调用子程序和被调用子程序必须在同一个数据库实例中。您不能将游标变量传递或返回到通过数据库链接调用的子程序。

警告

因为游标变量是指针，所以将它们用作子程序参数会增加子程序参数别名的可能性，这可能会产生意想不到的结果。有关详细信息，请参阅“使用游标变量参数的子程序参数别名”。

将游标变量声明为子程序的形参时： - 如果子程序打开或为游标变量赋值，则参数模式必须为IN OUT。 - 如果子程序只取回或关闭游标变量，则参数模式可以是IN或IN OUT。

相应的形式和实际游标变量参数必须具有兼容的返回类型。否则，PL/SQL 会引发预定义的异常ROWTYPE_MISMATCH。

要在不同 PL/SQL 单元的子程序之间传递游标变量参数，请在包中定义参数的 REF CURSOR 类型。当类型在一个包中时，多个子程序可以使用它。一个子程序可以声明该类型的形式参数，而其他子程序可以声明该类型的变量并将它们传递给第一个子程序。

示例 7-31 为一个查询打开游标变量的过程

此示例在包中定义了 REF CURSOR 类型和打开该类型游标变量参数的过程。
```
\set SQLTERM /
CREATE OR REPLACE PACKAGE stu_data AUTHID DEFINER AS
  TYPE stucurtyp IS REF CURSOR RETURN student%ROWTYPE;
  PROCEDURE open_stu_cv (stu_cv IN OUT stucurtyp);
END stu_data;
/
CREATE OR REPLACE PACKAGE BODY stu_data AS
  PROCEDURE open_stu_cv (stu_cv IN OUT stucurtyp) IS
  BEGIN
    OPEN stu_cv FOR SELECT * FROM student;
  END open_stu_cv;
END stu_data;
/
```
示例 7-32 为所选查询打开游标变量（返回类型相同）

在此示例中，存储过程为所选查询打开其游标变量参数。查询具有相同的返回类型。
```
\set SQLTERM /
CREATE OR REPLACE PACKAGE stu_data AUTHID DEFINER AS
  TYPE stucurtyp IS REF CURSOR RETURN stu_temp%ROWTYPE;
  PROCEDURE open_stu_cv (stu_cv IN OUT stucurtyp, choice INT);
END stu_data;
/
CREATE OR REPLACE PACKAGE BODY stu_data AS
  PROCEDURE open_stu_cv (stu_cv IN OUT stucurtyp, choice INT) IS
  BEGIN
    IF choice = 1 THEN
      OPEN stu_cv FOR SELECT *
      FROM stu_temp
      WHERE score > 60;
    ELSIF choice = 2 THEN
      OPEN stu_cv FOR SELECT *
      FROM stu_temp
      WHERE score > 70;
    ELSIF choice = 3 THEN
      OPEN stu_cv FOR SELECT *
      FROM stu_temp
      WHERE id = 1001;
    END IF;
  END;
END stu_data;
/
```
示例 7-33 为选择的查询打开游标变量（不同的返回类型）

在此示例中，存储过程为所选查询打开其游标变量参数。查询具有不同的返回类型。
```
\set SQLTERM /
CREATE OR REPLACE PACKAGE admin_data AUTHID DEFINER AS
  TYPE stucurtyp IS REF CURSOR;
  PROCEDURE open_cv (stu_cv IN OUT stucurtyp, choice INT);
END admin_data;
/
CREATE OR REPLACE PACKAGE BODY admin_data AS
  PROCEDURE open_cv (stu_cv IN OUT stucurtyp, choice INT) IS
  BEGIN
    IF choice = 1 THEN
      OPEN stu_cv FOR SELECT * FROM stu_temp;
    ELSIF choice = 2 THEN
      OPEN stu_cv FOR SELECT * FROM student;
    ELSIF choice = 3 THEN
      OPEN stu_cv FOR SELECT * FROM stu_temp WHERE id > 1000;
    END IF;
  END;
END admin_data;
/
```
7.5. 事务处理和控制

事务处理是 KingbaseES 数据库的一项功能，它允许多个用户同时处理数据库，并确保每个用户看到一致的数据版本，并且所有更改都以正确的顺序应用。

事务是 KingbaseES 数据库将其视为一个单元的一个或多个 SQL 语句的序列：要么执行所有语句，要么不执行任何语句。

不同的用户可以写入相同的数据结构，而不会损害彼此的数据或相互协调，因为 KingbaseES 数据库会自动锁定数据结构。为了最大限度地提高数据可用性，KingbaseES 数据库在最短的时间内锁定最少量的数据。

您很少需要编写额外的代码来防止多个用户同时访问数据时出现问题。但是，如果您确实需要这种级别的控制，您可以手动覆盖 KingbaseES 数据库的默认锁定机制。

7.5.1. COMMIT 语句

COMMIT语句结束当前事务，使其更改永久且对其他用户可见。

注意

一个交易可以跨越多个区块，一个区块可以包含多个交易。

示例 7-34 COMMIT 语句

在这个例子中，一笔交易将钱从一个银行账户转移到另一个银行账户。
```
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
  account_id  NUMBER(6),
  balance     NUMBER (10,2)
);

INSERT INTO accounts VALUES (2001, 8000.00);

INSERT INTO accounts VALUES (2101, 7100.00);

\set SQLTERM /
CREATE OR REPLACE PROCEDURE p (
  from_acc_id  NUMBER,
  to_acc_id    NUMBER,
  amount     NUMBER
) AS
BEGIN
  UPDATE accounts
  SET balance = balance - amount
  WHERE account_id = from_acc_id;

  UPDATE accounts
  SET balance = balance + amount
  WHERE account_id = to_acc_id;

  COMMIT;
END;
/
```
转账前查询：
```
SELECT * FROM accounts;
```
结果：
```
 account_id | balance
------------+---------
       2001 | 8000.00
       2101 | 7100.00
(2 rows)

\set SQLTERM /
BEGIN
  p(2001, 2101, 1000);
END;
/
```
转账后查询：
```
SELECT * FROM accounts;
```
结果：
```
 account_id | balance
------------+---------
       2001 | 7000.00
       2101 | 8100.00
(2 rows)
```
7.5.2. ROLLBACK 语句

ROLLBACK 语句结束当前事务并撤消在该事务期间所做的任何更改。

如果您犯了错误，例如从表中删除了错误的行，则回滚会恢复原始数据。如果由于 SQL 语句失败或 PL/SQL 引发异常而无法完成事务，则回滚可让您采取纠正措施并可能重新开始。

示例 7-35 ROLLBACK 语句

此示例将有关学生的信息插入到两个不同的表中。如果 INSERT 语句试图存储重复的学生编号，PL/SQL 将引发预定义的异常 DUP_VAL_ON_INDEX。为确保撤消对所有两个表的更改，异常处理程序运行 ROLLBACK。
```
DROP TABLE IF EXISTS stu_name;
CREATE TABLE stu_name AS
  SELECT id, name
  FROM student;

CREATE UNIQUE INDEX stuname_ix
ON stu_name (id);


DROP TABLE IF EXISTS stu_score;
CREATE TABLE stu_score AS
  SELECT id, score
  FROM student;

CREATE UNIQUE INDEX stuscore_ix
ON stu_score (id);

\set SQLTERM /
DECLARE
  stu_id        NUMBER(6);
  stu_name  VARCHAR2(25);
  stu_score    NUMBER(8,2);
BEGIN
  SELECT id, name, score
  INTO stu_id, stu_name, stu_score
  FROM student
  WHERE id = 1;

  INSERT INTO stu_name (id, name)
  VALUES (stu_id, stu_name);

  INSERT INTO stu_score (id, score)
  VALUES (stu_id, stu_score);

EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    ROLLBACK;
        RAISE NOTICE 'Inserts were rolled back';
END;
/
```
7.5.3. 隐式回滚

在运行 INSERT、UPDATE、DELETE 或 MERGE 语句之前，数据库会标记一个隐式保存点（对您不可用）。如果语句失败，数据库将回滚到保存点。

通常，只是回滚失败的 SQL 语句，而不是整个事务。如果语句引发未处理的异常，则主机环境确定回滚的内容。

数据库还可以回滚单个 SQL 语句以打破死锁。数据库向参与的事务发出错误信号并回滚该事务中的当前语句。

在运行 SQL 语句之前，数据库必须解析它，即检查它以确保它遵循语法规则并引用有效的模式对象。运行 SQL 语句时检测到的错误会导致回滚，但解析语句时检测到的错误不会。

如果您退出存储的子程序并出现未处理的异常，PL/SQL 不会将值分配给 OUT 参数，并且不会进行任何回滚。

有关处理异常的信息，请参阅PL/SQL 错误处理。

7.5.4. SET TRANSACTION 语句

您使用 SET TRANSACTION 语句开始只读或读写事务、建立隔离级别或将当前事务分配给指定的回滚段。

只读事务对于在其他用户更新同一个表时运行多个查询很有用。

在只读事务期间，所有查询都引用数据库的同一个快照，提供多表、多查询、读一致的视图。其他用户可以像往常一样继续查询或更新数据。通过提交或回滚可以结束事务。

SET TRANSACTION 语句必须是只读事务中的第一条 SQL 语句，并且在事务中只能出现一次。如果您将事务设置为 READ ONLY，则后续查询只会看到在事务开始之前提交的更改。 READ ONLY 的使用不会影响其他用户或事务。

在只读事务中只允许使用 SELECT、OPEN、FETCH、CLOSE、LOCK TABLE、COMMIT 和 ROLLBACK 语句。SELECT 语句不能是 FOR UPDATE。

示例 7-36 只读事务中的 SET TRANSACTION 语句

在此示例中，只读事务收集学生表中得分70以上、80以上和90以上的学生信息。
```
\set SQLTERM /
DECLARE
  mt_70 int;
      mt_80 int;
      mt_90 int;
BEGIN
   COMMIT; -- end previous transaction
   SET TRANSACTION READ ONLY;

   SELECT COUNT(*)
   INTO mt_70
   FROM stu_temp
   WHERE score > 70;

   SELECT COUNT(*)
   INTO mt_80
   FROM stu_temp
   WHERE score > 80;

   SELECT COUNT(*)
   INTO mt_90
   FROM stu_temp
   WHERE score > 90;

   COMMIT; -- ends read-only transaction
END;
/
```
7.5.5. 覆盖默认锁定

默认情况下，KingbaseES 数据库自动锁定数据结构，这允许不同的应用程序写入相同的数据结构，而不会损害彼此的数据或相互协调。

如果您必须在事务期间对数据具有独占访问权限，则可以使用以下 SQL 语句覆盖默认锁定： - LOCK TABLE，它显式地锁定整个表。 - 带有 FOR UPDATE 子句 (SELECT FOR UPDATE) 的 SELECT，它显式锁定表的特定行。

7.5.5.1. LOCK TABLE 语句

LOCK TABLE 语句以指定的锁定模式显式锁定一个或多个表，以便您可以共享或拒绝对它们的访问。

锁模式决定了可以在表上放置哪些其他锁。例如，许多用户可以同时获取一张表的行共享锁，但一次只能有一个用户获取排他锁。当一个用户拥有一个表的排他锁时，没有其他用户可以插入、删除或更新该表中的行。

表锁永远不会阻止其他用户查询表，查询永远不会获取表锁。只有当两个不同的事务尝试修改同一行时，一个事务才会等待另一个事务完成。 LOCK TABLE 语句允许您指定等待另一个事务完成的时间。

当获取它们的事务被提交或回滚时，表锁被释放。

7.5.5.2. SELECT FOR UPDATE 和 FOR UPDATE 游标

带有 FOR UPDATE 子句的 SELECT 语句（SELECT FOR UPDATE 语句）选择结果集中的行并锁定它们。 SELECT FOR UPDATE 允许您基于行中的现有值进行更新，因为它确保在您更新它们之前没有其他用户可以更改这些值。您还可以使用 SELECT FOR UPDATE 锁定您不想更新的行，如:ref:示例 10-6 所示。

默认情况下，SELECT FOR UPDATE 语句一直等待，直到获得请求的行锁。若要更改此行为，请使用 SELECT FOR UPDATE 语句的 NOWAIT、WAIT 或 SKIP LOCKED 子句。有关这些子句的信息，请参阅数据库 SQL 语言参考。

当 SELECT FOR UPDATE 与显式游标关联时，该游标称为 FOR UPDATE 游标。只有 FOR UPDATE 游标可以出现在 UPDATE 或 DELETE 语句的 CURRENT OF 子句中。（CURRENT OF 子句是 SQL 语句 UPDATE 和 DELETE 的 WHERE 子句的 PL/SQL 扩展，将语句限制为游标的当前行。）

当 SELECT FOR UPDATE 查询多个表时，它只锁定列出现在 FOR UPDATE 子句中的行。

7.5.5.3. 用ROWID伪列模拟CURRENT OF Clause

结果集的行在您打开 FOR UPDATE 游标时被锁定，而不是在它们被获取时被锁定。当您提交或回滚事务时，这些行被解锁。行解锁后，您无法从 FOR UPDATE 游标中获取，如:ref:示例 7-37 所示（如果将 ROLLBACK 替换为 COMMIT，结果相同）。

解决方法是使用 ROWID 伪列模拟 CURRENT OF 子句（在 KingbaseES 数据库 SQL 语言参考中描述）。将每一行的 rowid 选择到一个 tid 变量中，并在后续的更新和删除过程中使用 rowid 来标识当前行，如示例 7-42 所示。（要打印 UROWID 变量的值，请使用 KingbaseES 数据库 SQL 语言参考中描述的 ROWIDTOCHAR 函数将其转换为 VARCHAR2。）

注意

因为没有 FOR UPDATE 子句锁定获取的行，其他用户可能会无意中覆盖您的更改。

读取一致性所需的额外空间在游标关闭之前不会释放，这会减慢大型更新的处理速度。

示例 7-37 COMMIT 语句后带有 FOR UPDATE 游标的 FETCH
```
DROP TABLE IF EXISTS stu;
CREATE TABLE stu AS SELECT * FROM student;

\set SQLTERM /
DECLARE
  CURSOR c1 IS
    SELECT * FROM stu FOR UPDATE;

  stu_rec  stu%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO stu_rec;
    EXIT WHEN c1%NOTFOUND;
        RAISE NOTICE 'stu_rec.id = %',stu_rec.id;

    UPDATE stu
    SET score = score * 1.05
    WHERE id = 1;

    COMMIT;  -- releases locks
  END LOOP;
END;
/
```
结果：
```
NOTICE:  stu_rec.id = 1
ERROR:  unexpected end of tuple stream
```
示例 7-38 使用 ROWID 伪列模拟 CURRENT OF 子句
```
DROP TABLE IF EXISTS stu;
CREATE TABLE stu AS SELECT * FROM student;

\set SQLTERM /
DECLARE
  CURSOR c1 IS
    SELECT id, name, rowid
    FROM stu;  -- no FOR UPDATE clause

  my_id   student.id%TYPE;
  my_name      student.name%TYPE;
  my_rowid      tid;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO my_id, my_name, my_rowid;
    EXIT WHEN c1%NOTFOUND;

    UPDATE stu
    SET score = score * 1.02
    WHERE rowid = my_rowid;

    COMMIT;
  END LOOP;
  CLOSE c1;
END;
/
```
7.6. 自治事务

自治事务是由另一个事务（主事务）启动的独立事务。

自治事务执行 SQL 操作并提交或回滚，而不提交或回滚主事务。

注意

虽然一个自治事务是由另一个事务启动的，但它不是嵌套事务，因为： - 它不与主事务共享事务资源（例如锁）。 - 它不依赖于主事务。

例如，如果主事务回滚，嵌套事务会回滚，但自治事务不会。
- 其提交的更改立即对其他事务可见。
  
  在主事务提交之前，嵌套事务的提交更改对其他事务不可见。
- 自治事务中引发的异常会导致事务级回滚，而不是语句级回滚。
7.6.1. 自治事务的优势

启动后，自治事务是完全独立的。它与主事务不共享锁、资源或提交依赖项。即使主事务回滚，您也可以记录事件、增加重试计数器等。

自治事务可帮助您构建模块化、可重用的软件组件。您可以将自治事务封装在存储的子程序中。调用应用程序不需要知道由存储的子程序完成的操作是成功还是失败。

7.6.2. 事务上下文

主事务与嵌套例程共享其上下文，但不与自治事务共享。当一个自治例程调用另一个（或递归调用自身）时，这些例程不共享事务上下文。当自治例程调用非自治例程时，这些例程共享相同的事务上下文。

7.6.3. 交易可见性

当自治事务提交时，由自治事务所做的更改对其他事务可见。如果主事务的隔离级别设置为 READ COMMITTED（默认值），则这些更改在它恢复时对主事务可见。

如果将主事务的隔离级别设置为 SERIALIZABLE，则其自治事务所做的更改在主事务恢复时对主事务不可见：
```
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
```
注意

事务属性仅适用于设置它们的事务。

游标属性不受自治事务的影响。

7.6.4. 声明自治例程

要声明自治例程，请使用 PRAGMA AUTONOMOUS_TRANSACTION 。

提示

为了便于阅读，请将 PRAGMA AUTONOMOUS_TRANSACTION 放在声明性部分的顶部。（在声明部分的任何地方都允许使用 PRAGMA。）

您不能将 PRAGMA AUTONOMOUS_TRANSACTION 应用于整个包或 ADT，但可以将其应用于包中的每个子程序或 ADT 的每个方法。

示例 7-39 在包中声明自治函数

此示例将包功能标记为自治。
```
DROP TABLE IF EXISTS stu;
CREATE TABLE stu AS SELECT * FROM student;
\set SQLTERM /
CREATE OR REPLACE PACKAGE stu_actions AUTHID DEFINER AS  -- package specification
  FUNCTION raise_score (stu_id NUMBER, score_raise NUMBER) RETURN NUMBER;
END stu_actions;
/
CREATE OR REPLACE PACKAGE BODY stu_actions AS  -- package body
  -- code for function raise_score
  FUNCTION raise_score (stu_id NUMBER, score_raise NUMBER)
  RETURN NUMBER IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    new_score NUMBER(8,2);
  BEGIN
    UPDATE stu SET score =
      score + score_raise WHERE id = stu_id;
    COMMIT;
    SELECT score INTO new_score FROM student
      WHERE id = stu_id;
    RETURN new_score;
  END raise_score;
END stu_actions;
/
```
示例 7-40 声明自治独立过程

这个例子将一个独立的子程序标记为自治的。
```
\set SQLTERM /
CREATE OR REPLACE PROCEDURE add_score (stu_id NUMBER, num NUMBER)
AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE stu
  SET score =  score + num
  WHERE id = stu_id;

  COMMIT;
END add_score;
/
```
示例 7-41 声明自治 PL/SQL 块

此示例将模式级 PL/SQL 块标记为自治的。（嵌套的 PL/SQL 块不能是自治的。）
```
DROP TABLE IF EXISTS stu;
CREATE TABLE stu AS SELECT * FROM student;

\set SQLTERM /
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  stu_id int   := 1;
  num NUMBER := 5;
BEGIN
  UPDATE stu
  SET score =  score + num
  WHERE id = stu_id;

  COMMIT;
END;
/
```
7.6.5. 控制自治事务

自治例程中的第一条 SQL 语句开始一个事务。当一个事务结束时，下一个 SQL 语句开始另一个事务。自上次提交或回滚以来运行的所有 SQL 语句都包含当前事务。要控制自治事务，请使用这些仅适用于当前（活动）事务的语句： - COMMIT - ROLLBACK - SET TRANSACTION

7.6.6. 进入和退出自治程序

当您进入自治例程的可执行部分时，主事务暂停。当您退出例程时，主事务将恢复。

如果出现异常未处理，或者事务由于其他未处理的异常而结束，则事务回滚。

7.6.7. 提交和回滚自治事务

COMMIT 和 ROLLBACK 结束活动的自治事务，但不退出自治例程。当一个事务结束时，下一个 SQL 语句开始另一个事务。如果单个自治例程发出多个 COMMIT 语句，则它可以包含多个自治事务。

7.6.8. 从 SQL 调用自治函数

从 SQL 语句调用的函数必须遵守旨在控制副作用的规则。

根据定义，自治例程从不读取或写入数据库状态（即它既不查询也不修改任何数据库表）。

示例 7-42 调用自治函数

包函数 log_msg 是自治的。因此，当查询调用该函数时，该函数将一条消息插入到数据库表 dbg 中，而不会违反写数据库状态的规则（修改数据库表）。
```
DROP TABLE IF EXISTS dbg;
CREATE TABLE dbg (msg TEXT);

\set SQLTERM /
CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
  FUNCTION log_msg (msg TEXT) RETURN TEXT;
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
  FUNCTION log_msg (msg TEXT) RETURN TEXT IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO dbg VALUES (msg);
    RETURN msg;
  END;
END pkg;
/
-- Invoke package function from query
DECLARE
  my_stu_id    INTEGER;
  my_stu_name  TEXT;
  my_stu_score     NUMBER;
BEGIN
  my_stu_id := 1;

  SELECT pkg.log_msg(name)
  INTO my_stu_name
  FROM student
  WHERE id = my_stu_id;

  ROLLBACK;
END;
/
```
相关阅读:
LeetCode每日一练 —— 160. 相交链表
 2022年最新浙江建筑特种工（施工升降机）真题题库及答案
 C++ string 类相关知识
 前端面试手撕编程之ES+算法
 内网穿透的应用-使用eXtplorer本地搭建免费在线文件管理器并实现远程登录
 C\C++基础
 逻辑回归算法
 SpringCloud微服务实践之三创建子项目UserService
pinia的使用
 Windows版本Anaconda安装教程
原文地址：https://blog.csdn.net/arthemis_14/article/details/126411338

7. PL/SQL 静态 SQL

7.1. 静态SQL说明

7.1.1. 声明

7.1.2. 伪列

7.2. 游标概述

7.2.1. 隐式游标

7.2.2. 显式游标

7.3. 处理查询结果集

7.3.1. 使用 SELECT INTO 语句处理查询结果集

7.3.2. 使用游标 FOR LOOP 语句处理查询结果集

7.3.3. 使用显式游标、OPEN、FETCH 和 CLOSE 处理查询结果集

7.3.4. 处理带有子查询的查询结果集

7.4. 游标变量

7.4.1. 创建游标变量

7.4.2. 打开和关闭游标变量

7.4.3. 使用游标变量获取数据

7.4.4. 为游标变量赋值

7.4.5. 游标变量查询中的变量

7.4.6. 查询集合

7.4.7. 游标变量属性

7.4.8. 游标变量作为子程序参数

7.5. 事务处理和控制

7.5.1. COMMIT 语句

7.5.2. ROLLBACK 语句

7.5.3. 隐式回滚

7.5.4. SET TRANSACTION 语句

7.5.5. 覆盖默认锁定

7.6. 自治事务

7.6.1. 自治事务的优势

7.6.2. 事务上下文

7.6.3. 交易可见性

7.6.4. 声明自治例程

7.6.5. 控制自治事务

7.6.6. 进入和退出自治程序

7.6.7. 提交和回滚自治事务

7.6.8. 从 SQL 调用自治函数