人大金仓数据库KingbaseES PLSQL的游标变量声明

PL/SQL的游标变量声明

关键字：

KingbaseES、PLSQL、CURSOR、人大金仓

游标变量的介绍

游标变量就像一个显式游标，不限于一个查询。

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

1. 游标变量的声明语法

ref_cursor_type_definition ::=

TYPE type IS REF CURSOR

[ RETURN

{ {db_table_or_view | cursor | cursor_variable}%ROWTYPE | record%TYPE

| record_type

| ref_cursor_type

}

] ;

cursor_variable_declaration ::=

cursor_variable type;

1. 游标变量声明语法的解读

ref_cursor_type_definition：

• • type:正在定义的REF CURSOR类型的名称
  • RETURN: 指定 RETURN 以定义强 REF CURSOR 类型。省略 RETURN 定义弱 REF CURSOR 类型。
  • db_table_or_view：数据库表或视图的名称，在声明时必须可访问。
  • cursor：先前声明的显式游标的名称。
  • cursor_variable：先前声明的游标变量的名称。
  • record：用户定义记录的名称。
  • record_type：使用数据类型声明符 RECORD 定义的用户定义类型的名称。
  • ref_cursor_type：使用数据类型声明符 REF CURSOR 定义的用户定义类型的名称。

cursor_variable_declaration

• • cursor_variable：您要声明的游标变量的名称

1. 游标变量的限制

• 不能在包规范中声明游标变量。也就是说，包不能有公共游标变量（可以从包外部引用的游标变量）。
• 不能将游标变量的值存储在集合或数据库列中。
• 不能使用比较运算符来测试游标变量是否相等、不等或为空。

游标变量声明的使用场景

1. 游标变量声明

\set SQLTERM /

DECLARE

TYPE strong_cursor IS REF CURSOR RETURN t1%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 t1%ROWTYPE;

stu_csr stu_cur_typ; -- 强游标

BEGIN

NULL;

END;

/

1. 具有用户定义的返回类型的游标变量

\set SQLTERM /

DECLARE

TYPE StuRecTyp IS RECORD (

id int,

name varchar);

TYPE StuCurTyp IS REF CURSOR RETURN StuRecTyp;

stu_cv StuCurTyp;

BEGIN

NULL;

END;

/

（3）为所选查询打开游标变量（返回类型相同）

存储过程为所选查询打开其游标变量参数。查询具有相同的返回类型。

DROP TABLE IF EXISTS stu_temp;

CREATE TABLE stu_temp(id int, name varchar(100));

insert into stu_temp values(1, 'simba'),(3, 'nano');

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

/

游标变量声明的注意事项

• 游标变量的声明必须在任何引用该游标变量之前进行。在声明之前引用游标变量会导致编译错误。
• 在声明游标变量时，需要指定游标的返回类型。游标的返回类型可以是记录类型、表类型或REF CURSOR类型。
• 如果使用记录类型作为游标的返回类型，需要确保游标的查询语句和记录类型的定义是一致的，即查询结果的列和记录类型的字段数量、类型和顺序相匹配。
• 如果使用REF CURSOR类型作为游标的返回类型，需要在声明游标变量时指定具体的游标类型。

参考资料

《 KingbaseES_PLSQL过程语言参考手册》