人大金仓数据库KingbaseES --PLSQL 的显示游标

关键字：

Cursor、人大金仓、KingbaseES、

显式游标

显式游标是用户构建和管理的会话游标。您必须声明并定义一个显式游标，为其命名并将其与查询相关联（通常，查询返回多行），然后可以通过以下任一方式处理查询结果集：

• 打开显式游标（使用 OPEN 语句），从结果集中获取行（使用 FETCH 语句），然后关闭显式游标（使用CLOSE 语句）。

• 在游标 FOR LOOP 语句中使用显式游标

您不能为显式游标赋值、在表达式中使用它，或者将其用作正式的子程序参数，但是可以使用游标变量来执行这些操作。与隐式游标不同，您可以通过名称引用显式游标或游标变量。因此，显式游标或游标变量称为命名游标。

声明和定义显示游标

1. 显示游标声明具有以下语法：

CURSOR cursor_name [ parameter_list ] RETURN return_type;

1. 显示游标定义具有以下语法：

CURSOR cursor_name [ parameter_list ] [ RETURN return_type ] IS select_statement;

1. 示例：
2. 显示游标的声明和定义

\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;

/

②省略return type

\set SQLTERM /

DECLARE

CURSOR c3 RETURN student%ROWTYPE; -- 声明 c3

CURSOR c3 IS -- 定义 c3,

SELECT * FROM student -- 定义时省略 return type，错误

WHERE id = '1';

BEGIN

NULL;

END;

/

使用显示游标获取数据

打开显式游标后，您可以使用该 FETCH 语句获取查询结果集的行。FETCH 返回一行的语句的基本语法是：FETCH cursor_name INTO into_clause

into_clause 是变量列表或单个记录变量。对于查询返回的每一列，变量列表或记录变量必须具有对应的类型兼容变量或字段。%TYPE 和%ROWTYPE 属性用于声明在 FETCH 语句中使用的变量和记录变量很有用。FETCH 语句检索结果集的当前行，将该行的列值存储到变量或记录变量中，并将游标前进到下一行。

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

1. LOOP语句中的FETCH语句

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

\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;

/

（2）将相同的显式游标提取到不同的变量中

以下示例中，使用五次 FETCH 语句将结果集的前五行提取到五个记录中，每个语句提取到一个不同的记录变量中。记录变量用%ROWTYPE 声明。 \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;

/

显示游标查询中的变量

显式游标查询可以引用其作用域中的任何变量。打开显示游标时，PL/SQL 会计算查询中的变量，并在查询结果集时使用这些值，当查询到结果集后再更改这些值则不会影响结果集。

1. 显式游标查询中的变量 - 无结果集更改

在以下示例中，显式游标查询引用了变量因子。当游标打开时，num 的值为 2。因此，score_add_num 始终是

score 的 2 倍，尽管该 num 在每次获取后都会递增。

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;

/

1. 显式游标查询中的变量 - 结果集更改

更改结果集，您必须关闭游标，更改变量的值，然后再次打开游标。操作如下：

\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;

/

当显示游标查询需要列别名时

当显式游标查询包含虚拟列（表达式）时，如果满足以下任一条件，则该列必须具有别名：

• 使用游标来获取使用%ROWTYPE 声明的记录。

• 想在程序中引用虚拟列。

\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;

/

参考资料