openGauss游标：结果集遍历

【免费下载链接】openGauss-server openGauss kernel ~ openGauss is an open source relational database management system 项目地址: https://gitcode.com/opengauss/openGauss-server

引言

在数据库开发中，我们经常需要逐行处理查询结果集。你是否曾经遇到过这样的场景：需要处理大量数据但又不想一次性加载到内存中？或者需要在存储过程中对查询结果进行复杂的逐行处理？openGauss的游标（Cursor）功能正是解决这些问题的利器。

本文将深入探讨openGauss游标的使用，从基础概念到高级技巧，帮助你掌握结果集遍历的核心技术。

游标基础概念

什么是游标？

游标（Cursor）是数据库系统中用于对查询结果集进行逐行处理的机制。它就像一个指针，可以在结果集中移动，一次处理一行数据。

游标的主要作用

• 逐行处理：对大量数据进行分析和处理
• 内存优化：避免一次性加载所有数据到内存
• 事务控制：在事务中保持数据一致性
• 复杂逻辑：实现复杂的业务逻辑处理

游标类型详解

1. 显式游标（Explicit Cursor）

显式游标需要手动声明、打开、获取和关闭，提供了最精细的控制。

-- 显式游标声明示例
DECLARE
    company_name    VARCHAR(100);
    company_loc     VARCHAR(100);
    company_no      INTEGER;
    
    -- 声明无参数游标
    CURSOR c1_all IS 
        SELECT name, loc, no FROM company ORDER BY 1, 2, 3;
BEGIN
    -- 打开游标
    OPEN c1_all;
    
    -- 循环获取数据
    LOOP
        FETCH c1_all INTO company_name, company_loc, company_no;
        EXIT WHEN c1_all%NOTFOUND;
        
        -- 处理数据
        RAISE NOTICE '% : % : %', company_name, company_loc, company_no;
    END LOOP;
    
    -- 关闭游标
    CLOSE c1_all;
END;

2. 带参数游标（Parameterized Cursor）

带参数游标可以根据传入参数动态过滤数据。

-- 带参数游标示例
DECLARE
    company_name    VARCHAR(100);
    company_loc     VARCHAR(100);
    company_no      INTEGER;
    
    -- 声明带参数游标
    CURSOR c2_no_range(no_1 INTEGER, no_2 INTEGER) IS 
        SELECT name, loc, no FROM company 
        WHERE no >= no_1 AND no <= no_2 
        ORDER BY 1, 2, 3;
BEGIN
    -- 打开游标并传入参数
    OPEN c2_no_range(5, 10);
    
    LOOP
        FETCH c2_no_range INTO company_name, company_loc, company_no;
        EXIT WHEN c2_no_range%NOTFOUND;
        
        RAISE NOTICE '% : % : %', company_name, company_loc, company_no;
    END LOOP;
    
    CLOSE c2_no_range;
END;

3. 引用游标（REF CURSOR）

引用游标是动态游标，可以在运行时决定查询语句。

-- 引用游标示例
DECLARE
    company_name    VARCHAR(100);
    company_loc     VARCHAR(100);
    company_no      INTEGER;
    
    TYPE ref_cur_type IS REF CURSOR;  -- 声明游标类型
    my_cur ref_cur_type;              -- 声明游标变量
    sqlstr VARCHAR(100);
BEGIN
    -- 动态打开游标
    OPEN my_cur FOR 'SELECT name, loc, no FROM company ORDER BY 1, 2, 3';
    
    FETCH my_cur INTO company_name, company_loc, company_no;
    WHILE my_cur%FOUND LOOP
        RAISE NOTICE '% : % : %', company_name, company_loc, company_no;
        FETCH my_cur INTO company_name, company_loc, company_no;
    END LOOP;
    
    CLOSE my_cur;
END;

4. SYS_REFCURSOR系统游标

openGauss还提供了预定义的SYS_REFCURSOR类型。

-- SYS_REFCURSOR示例
DECLARE
    c1 SYS_REFCURSOR;
    temp NUMBER(4);
BEGIN
    OPEN c1 FOR SELECT section_ID FROM sections ORDER BY section_ID;
    
    LOOP
        FETCH c1 INTO temp;
        EXIT WHEN c1%NOTFOUND;
        RAISE NOTICE 'Row %: %', c1%ROWCOUNT, temp;
    END LOOP;
    
    CLOSE c1;
END;

游标属性详解

openGauss游标提供了丰富的属性来监控和控制游标状态。

游标属性表

属性	描述	返回值
%ISOPEN	游标是否打开	BOOLEAN
%FOUND	最近一次FETCH是否成功	BOOLEAN
%NOTFOUND	最近一次FETCH是否失败	BOOLEAN
%ROWCOUNT	当前已获取的行数	INTEGER

属性使用示例

DECLARE
    company_name    VARCHAR(100);
    company_loc     VARCHAR(100);
    company_no      INTEGER;
    
    CURSOR c1_all IS 
        SELECT name, loc, no FROM company ORDER BY 1, 2, 3;
BEGIN
    -- 检查游标是否打开
    IF NOT c1_all%ISOPEN THEN
        RAISE NOTICE '游标未打开，正在打开...';
        OPEN c1_all;
    END IF;
    
    LOOP
        FETCH c1_all INTO company_name, company_loc, company_no;
        
        -- 检查是否找到数据
        IF c1_all%NOTFOUND THEN
            RAISE NOTICE '没有更多数据';
            EXIT;
        END IF;
        
        -- 显示当前行数和数据
        IF c1_all%FOUND THEN
            RAISE NOTICE '第%行: %  %  %', 
                c1_all%ROWCOUNT, company_name, company_loc, company_no;
        END IF;
    END LOOP;
    
    -- 关闭游标
    IF c1_all%ISOPEN THEN
        CLOSE c1_all;
        RAISE NOTICE '游标已关闭';
    END IF;
END;

隐式游标属性

除了显式游标，openGauss还提供了隐式游标属性，用于监控最近的SQL语句执行情况。

DECLARE
BEGIN
    -- 执行DML操作
    DELETE FROM company WHERE loc = 'china';
    
    -- 检查隐式游标属性
    IF NOT SQL%ISOPEN THEN 
        RAISE NOTICE '隐式游标已关闭';
    END IF;
    
    IF SQL%FOUND THEN
        RAISE NOTICE '删除操作影响了 % 行', SQL%ROWCOUNT;
    END IF;
    
    IF SQL%NOTFOUND THEN
        RAISE NOTICE '没有找到匹配的数据';
    END IF;
END;

游标遍历模式

1. 基本LOOP循环

DECLARE
    CURSOR emp_cursor IS 
        SELECT employee_id, first_name, last_name FROM employees;
    v_emp_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
BEGIN
    OPEN emp_cursor;
    
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_first_name, v_last_name;
        EXIT WHEN emp_cursor%NOTFOUND;
        
        -- 处理逻辑
        DBMS_OUTPUT.PUT_LINE(v_emp_id || ': ' || v_first_name || ' ' || v_last_name);
    END LOOP;
    
    CLOSE emp_cursor;
END;

2. WHILE循环

DECLARE
    CURSOR dept_cursor IS 
        SELECT department_id, department_name FROM departments;
    v_dept_id departments.department_id%TYPE;
    v_dept_name departments.department_name%TYPE;
BEGIN
    OPEN dept_cursor;
    FETCH dept_cursor INTO v_dept_id, v_dept_name;
    
    WHILE dept_cursor%FOUND LOOP
        -- 处理逻辑
        DBMS_OUTPUT.PUT_LINE(v_dept_id || ': ' || v_dept_name);
        
        FETCH dept_cursor INTO v_dept_id, v_dept_name;
    END LOOP;
    
    CLOSE dept_cursor;
END;

3. FOR循环（推荐）

-- 最简洁的游标遍历方式
BEGIN
    FOR emp_rec IN (SELECT employee_id, first_name, last_name FROM employees) 
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp_rec.employee_id || ': ' || 
            emp_rec.first_name || ' ' || 
            emp_rec.last_name
        );
    END LOOP;
END;

动态游标与参数绑定

动态游标允许在运行时构建SQL语句，特别适合复杂的查询场景。

DECLARE
    name          VARCHAR2(20);
    phone_number  VARCHAR2(20);
    salary        NUMBER(8,2);
    sqlstr        VARCHAR2(1024);

    TYPE app_ref_cur_type IS REF CURSOR;  
    my_cur app_ref_cur_type;  
    
BEGIN
    -- 动态构建SQL语句
    sqlstr := 'SELECT first_name, phone_number, salary FROM staffs WHERE section_id = :1';
    
    -- 打开游标并绑定参数
    OPEN my_cur FOR sqlstr USING '30'; 
    
    IF my_cur%ISOPEN THEN
        RAISE NOTICE '动态游标已打开';
    END IF;

    -- 遍历结果
    FETCH my_cur INTO name, phone_number, salary; 
    WHILE my_cur%FOUND LOOP
        RAISE NOTICE '% # % # %', name, phone_number, salary;
        FETCH my_cur INTO name, phone_number, salary;
    END LOOP;
    
    CLOSE my_cur; 
END;

游标作为函数参数

游标可以作为参数传递给函数，实现更灵活的代码组织。

-- 创建接收游标参数的函数
CREATE OR REPLACE FUNCTION process_cursor_data(
    p_cursor SYS_REFCURSOR
) RETURN VARCHAR2
AS
    v_emp_id NUMBER;
    v_count NUMBER := 0;
BEGIN
    IF p_cursor%ISOPEN THEN
        LOOP
            FETCH p_cursor INTO v_emp_id;
            EXIT WHEN p_cursor%NOTFOUND;
            v_count := v_count + 1;
        END LOOP;
        RETURN '处理了 ' || v_count || ' 条记录';
    ELSE
        RETURN '游标未打开';
    END IF;
END;
/

-- 使用示例
DECLARE
    v_cursor SYS_REFCURSOR;
    v_result VARCHAR2(100);
BEGIN
    OPEN v_cursor FOR SELECT employee_id FROM employees;
    v_result := process_cursor_data(v_cursor);
    DBMS_OUTPUT.PUT_LINE(v_result);
    CLOSE v_cursor;
END;

多游标操作

在某些复杂场景下，可能需要同时操作多个游标。

DECLARE
    company_name    VARCHAR(100);
    company_loc     VARCHAR(100);
    company_no      INTEGER;

    -- 声明多个游标
    CURSOR c1_all IS 
        SELECT name, loc, no FROM company ORDER BY 1, 2, 3;
    
    CURSOR c2_no_range(no_1 INTEGER, no_2 INTEGER) IS 
        SELECT name, loc, no FROM company 
        WHERE no >= no_1 AND no <= no_2 
        ORDER BY 1, 2, 3;
BEGIN
    -- 打开第一个游标
    OPEN c1_all; 
    
    -- 打开第二个游标（参数不同）
    OPEN c2_no_range(50, 100);  -- 结果可能为空
    
    -- 分别获取数据
    FETCH c1_all INTO company_name, company_loc, company_no;
    FETCH c2_no_range INTO company_name, company_loc, company_no;
    
    -- 检查游标状态
    IF c1_all%FOUND THEN
        RAISE NOTICE 'c1_all找到数据';
    END IF;
    
    IF c2_no_range%NOTFOUND THEN
        RAISE NOTICE 'c2_no_range未找到数据';
    END IF;
    
    RAISE NOTICE 'c1_all行数: %', c1_all%ROWCOUNT;
    RAISE NOTICE 'c2_no_range行数: %', c2_no_range%ROWCOUNT;
    
    -- 关闭所有游标
    CLOSE c1_all;
    CLOSE c2_no_range;
END;

游标性能优化技巧

1. 批量处理

DECLARE
    CURSOR emp_cursor IS 
        SELECT employee_id, first_name, last_name FROM employees;
    
    TYPE emp_table_type IS TABLE OF employees%ROWTYPE;
    emp_table emp_table_type;
    
    BATCH_SIZE CONSTANT NUMBER := 100;
BEGIN
    OPEN emp_cursor;
    
    LOOP
        -- 批量获取数据
        FETCH emp_cursor BULK COLLECT INTO emp_table LIMIT BATCH_SIZE;
        EXIT WHEN emp_table.COUNT = 0;
        
        -- 批量处理
        FOR i IN 1..emp_table.COUNT LOOP
            -- 处理逻辑
            NULL;
        END LOOP;
    END LOOP;
    
    CLOSE emp_cursor;
END;

2. 使用合适的游标类型

3. 游标属性监控表

场景	推荐属性	说明
循环控制	%NOTFOUND	检测是否到达结果集末尾
进度显示	%ROWCOUNT	显示当前处理的行数
错误处理	%ISOPEN	确保游标正确打开/关闭
性能监控	隐式游标属性	监控DML操作影响行数

常见问题与解决方案

问题1：游标未关闭导致资源泄漏

解决方案：

DECLARE
    CURSOR c1 IS SELECT * FROM table1;
BEGIN
    OPEN c1;
    -- 处理逻辑
    
    -- 确保游标关闭
    IF c1%ISOPEN THEN
        CLOSE c1;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        -- 异常时也要关闭游标
        IF c1%ISOPEN THEN
            CLOSE c1;
        END IF;
        RAISE;
END;

问题2：游标参数传递错误

解决方案：

-- 正确定义和使用参数游标
DECLARE
    CURSOR emp_cursor (p_dept_id NUMBER) IS
        SELECT * FROM employees WHERE department_id = p_dept_id;
BEGIN
    -- 正确传递参数
    OPEN emp_cursor(10);  -- 传递部门ID
    -- ...
    CLOSE emp_cursor;
END;

问题3：动态游标SQL注入风险

解决方案：

DECLARE
    v_sql VARCHAR2(1000);
    v_cursor SYS_REFCURSOR;
    v_param NUMBER := 100;
BEGIN
    -- 使用参数绑定避免SQL注入
    v_sql := 'SELECT * FROM employees WHERE salary > :sal_param';
    OPEN v_cursor FOR v_sql USING v_param;
    -- ...
    CLOSE v_cursor;
END;

最佳实践总结

1. 及时关闭游标：始终在使用完毕后关闭游标，避免资源泄漏
2. 使用FOR循环：尽可能使用FOR循环简化代码
3. 批量处理：对于大数据量，使用BULK COLLECT提高性能
4. 参数绑定：动态SQL务必使用参数绑定防止SQL注入
5. 异常处理：在异常处理块中确保游标正确关闭
6. 性能监控：使用游标属性监控处理进度和性能

结语

openGauss游标是处理结果集的强大工具，通过本文的详细讲解，你应该已经掌握了游标的各种用法和最佳实践。无论是简单的数据遍历还是复杂的业务逻辑处理，游标都能提供灵活而高效的解决方案。

记住，选择合适的游标类型、遵循最佳实践、注意性能优化，你就能充分发挥openGauss游标的威力，构建出高效可靠的数据库应用程序。

进一步学习建议：

• 实践文中的代码示例，理解各种游标的使用场景
• 在真实项目中应用游标处理复杂的数据操作
• 关注游标性能，学习如何优化大数据量的处理
• 探索游标与其他openGauss高级特性的结合使用

【免费下载链接】openGauss-server openGauss kernel ~ openGauss is an open source relational database management system 项目地址: https://gitcode.com/opengauss/openGauss-server