openGauss数据库游标及应用

1 openGauss游标概述

在openGauss中，游标（Cursor）是一种数据访问机制，允许用户逐行处理查询结果集。

1.2 游标的分类

根据使用方式的不同，openGauss中的游标可以分为显示类型游标、隐式游标和嵌套游标（图1）。

图1 游标的分类

（1）显式游标

显式游标需要用户手动定义、打开、读取和关闭。

（2）隐式游标

隐式游标由系统自动创建，通常用于单行查询结果的处理。用户无需手动定义和管理隐式游标，系统会在执行查询时自动创建并管理。

（3）嵌套游标

嵌套游标是指在一个游标中嵌套另一个游标。这种游标在存储过程、匿名块等复杂逻辑中非常有用，用于处理复杂的查询逻辑和逐行数据处理。例如，在一个存储过程中，可以根据外层游标获取的数据动态地创建和使用内层游标。

1.3 游标的属性

不同类型的游标它们的属性如下：

（1）显式游标属性

%FOUND：布尔型属性，当最近一次读记录时成功返回，则值为TRUE。

%NOTFOUND：布尔型属性，与%FOUND相反。

%ISOPEN：布尔型属性，当游标已打开时返回TRUE。

%ROWCOUNT：数值型属性，返回已从游标中读取的记录数。

（2）隐式游标属性

SQL%FOUND：布尔型属性，当最近一次读记录时成功返回，则值为TRUE。

SQL%NOTFOUND：布尔型属性，与SQL%FOUND相反。

SQL%ROWCOUNT：数值型属性，返回已从游标中读取的记录数。

SQL%ISOPEN：布尔型属性，取值总是FALSE，因为SQL语句执行完毕后隐式游标会立即关闭。

此外，游标的属性在openGauss数据库中都是访问同一个内部变量，事务和匿名块不支持多个游标同时访问。

2 游标的创建、使用

2.1 显式游标

使用显式游标有声明、打开、使用、关闭四个步骤（图2）。

图2 使用显式游标的四个步骤

（1）声明游标: 声明一个游标。

CURSOR cursor_name

[ BINARY ] [ NO SCROLL ] [ { WITH | WITHOUT } HOLD ]

FOR query;

其中，

BINARY：指明游标以二进制而不是文本格式返回数据。

NO SCROLL：声明该游标不能用于以倒序的方式检索数据行。

WITH HOLD/WITHOUT HOLD：声明当创建游标的事务结束后，游标是否能继续使用。

（2）打开游标：为游标赋初值。

open cursor_name;

（3）使用游标：获取游标指向的行数据并移动游标到下一行。

loop

fetch cursor_name into …

end loop；

（4）关闭游标：游标使用结束后关闭游标。

close cursor_name;

2.2 隐式游标

       在 OpenGauss 中，隐式游标是由系统自动创建和管理的游标，主要用于处理非查询语句（如 INSERT、UPDATE、DELETE 等）以及单行查询语句。用户无需手动定义、打开、读取和关闭隐式游标，而是通过隐式游标的属性来完成相应的操作。

2.3 嵌套游标

游标表达式可以嵌套在其他游标中，形成嵌套游标。游标表达式是一种特殊的游标用法，允许在查询结果中嵌套游标。这在处理复杂查询和逐行数据处理时非常有用。

（1）定义游标表达式

在`SELECT`语句中，可以使用CURSOR关键字定义游标表达式。

游标表达式可以嵌套在其他游标中，形成嵌套游标。

（2）使用游标表达式

游标表达式在每次提取一行数据时自动打开。

可以通过`FETCH`语句从游标表达式中提取数据。

游标表达式在事务结束时自动关闭。

3 应用举例

以下是一些使用openGauss中游标属性的示例。

3.1显式游标使用

[实例1 ]显式游标示例

--创建表coursetype

CREATE TABLE student.coursetype (

    coursetypeid bpchar(2) NOT NULL,

    coursetypename varchar(80) NOT NULL,

    CONSTRAINT coursetype_pkey PRIMARY KEY (coursetypeid)

)

WITH (

    orientation=row,

    compression=no

);

--创建表department

CREATE TABLE student.department (

    departmentid bpchar(2) NOT NULL,

    departmentname varchar(80) NOT NULL,

    CONSTRAINT department_pkey PRIMARY KEY (departmentid)

)

WITH (

    orientation=row,

    compression=no

);

--创建表course

CREATE TABLE student.course (

    courseid varchar(10) NOT NULL,

    coursename varchar(100) NOT NULL,

    coursetypeid bpchar(2) NOT NULL,

    credit float4 DEFAULT 4.0 NOT NULL,

    departmentid bpchar(2) NOT NULL,

    CONSTRAINT pk_course PRIMARY KEY (courseid)

)

WITH (

    orientation=row,

    compression=no

);--创建视图v_course

CREATE OR REPLACE VIEW student.v_course

AS SELECT course.*, coursetype.coursetypename, department.departmentname

FROM course

JOIN coursetype ON course.coursetypeid = coursetype.coursetypeid

JOIN department ON course.departmentid = department.departmentid

ORDER BY course.courseid;

--创建逐行打印课程信息的存储过程p_printCourseList

CREATE OR REPLACE PROCEDURE p_printCourseList()

AS

DECLARE

-- 声明游标

CURSOR c_course

FOR SELECT courseid,coursename,coursetypename,credit,departmentname

FROM v_course;

-- 声明变量

courseid varchar(10);

coursename VARCHAR(100);

coursetypename varchar(80);

credit float;

departmentname varchar(80);

BEGIN

-- 打开游标

OPEN c_course;

-- 循环提取数据

LOOP

-- 提取一行数据

FETCH c_course INTO

courseid,coursename,coursetypename,credit,departmentname;

-- 如果没有数据，则退出循环

EXIT WHEN NOT FOUND;

-- 输出结果（这里可以改为插入到临时表等操作）

RAISE NOTICE 'courseid: %,coursename: %,coursetypename: %,credit: %,

departmentname: %',

courseid,coursename,coursetypename,credit,departmentname;

END LOOP;

-- 关闭游标

CLOSE c_course;

END;

--执行存储过程p_printCourseList

CALL student.p_printCourseList();

输出结果见图3。

图3 使用显式游标结果

3.2隐式游标使用

[实例2]隐式游标示例

-- 创建一个测试表

CREATE TABLE t (

id INT,

name VARCHAR(40)

);

-- 插入一些测试数据

INSERT INTO t VALUES (1, 'stu1'), (2, 'stu2'), (3, 'stu3');

-- 在存储过程中使用隐式游标

CREATE OR REPLACE PROCEDURE p_implicitcursor()

AS

DECLARE

trecord t %ROWTYPE;

BEGIN

-- 使用隐式游标执行查询

FOR trecord IN SELECT * FROM t LOOP

-- 检查是否读取到记录

IF SQL%FOUND THEN

RAISE NOTICE '读取到记录：id= %, name= %', trecord.id, trecord.name;

END IF;

END LOOP;

-- 输出读取的记录数

RAISE NOTICE '总共读取了 % 条记录', SQL%ROWCOUNT;

END;

--调用存储过程p_implicitcursor

call p_implicitcursor();

输出结果见图4。

图4 使用隐式游标结果

3.3嵌套游标使用

[实例3]嵌套游标示例

-- 创建示例表

create table t3(id int, name varchar(40), job varchar(100));

create table t4(id int, age int);

insert into t3 values (1, 'zhang', 'worker'),(2, 'li', 'teacher'),(3, 'wang', 'engineer');

insert into t4 values (1, 20),(2, 30),(3, 40);

--在存储过程中使用嵌套游标

CREATE OR REPLACE PROCEDURE p_nestedcursor()

AS

age_temp int;

name_temp varchar;

type emp_cur_type is ref cursor;

cursor2 emp_cur_type;

cursor cursor1 is SELECT t4.age, CURSOR(SELECT name FROM t3 WHERE t3.id = t4.id) abc FROM t4;

BEGIN

OPEN cursor1;

LOOP

FETCH cursor1 INTO age_temp, cursor2;

EXIT WHEN cursor1%notfound;

RAISE NOTICE 'age : %',age_temp;

LOOP

FETCH cursor2 INTO name_temp;

EXIT WHEN cursor2%notfound;

RAISE NOTICE 'name : %', name_temp;

END LOOP;

CLOSE cursor2;

END LOOP;

CLOSE cursor1;

END;

--调用存储过程p_nestedcursor

call p_nestedcursor();

输出结果见图5。

图5 使用嵌套游标结果

4总结与展望

openGauss 游标提供了灵活的数据逐行处理机制，涵盖显式、隐式和嵌套游标三类，满足不同场景需求。显式游标通过手动控制生命周期实现精细操作，隐式游标简化单行查询处理，嵌套游标支持复杂多级数据关联。

未来 openGauss 游标可进一步优化性能，支持更大规模数据集的低开销处理；增强嵌套游标的动态适配能力，简化复杂查询逻辑的编写；结合 AI 技术实现智能游标管理，自动优化数据提取路径。此外，生态工具集成（如可视化监控游标执行）将提升开发体验，推动游标在实时分析和事务处理中的更广泛应用。