如何使Oracle存储过程输出所有SELECT查询结果及批量表数据？

数据库中有多个以test_开头的表（如test1、test2等），所有表都有主键列id且值从1开始，每个表包含数万条记录。需要通过存储过程输出所有此类表名，以及这些表中的批量数据。现有存储过程因重复打开同一游标，仅返回最后执行的SELECT查询结果，需解决该问题。

原存储过程代码 #

create or replace PROCEDURE TEST (tablenamecursor out sys_refcursor,mycursorresult out sys_refcursor
)
As
 maxx number;
 startindex number := 1;
 strcountquery clob;
 strquery clob;
 strquery1 clob;
 countt number;
 rowcountt number;
 batchsize number :=100;
 tablenamequery clob;
 test clob;
Begin
select count(*) into countt from user_tables where table_name like 'test_%';
loop
    exit when (countt=1);
        test:='test_'||countt;
        tablenamequery := 'SELECT table_name FROM user_tables where table_name = '''||test||'''';
        open tablenamecursor for tablenamequery;
        strcountquery := 'select count(*) from "'||test||'"';
        execute immediate strcountquery into maxx; 
        loop
        exit when (StartIndex>=maxx);
        strquery:='SELECT a.*, Row_number() OVER (ORDER BY "id") AS lookup FROM "'||test||'" 
        a WHERE &quot;id&quot; >='||StartIndex||' and rownum <='||batchsize;
        StartIndex:= StartIndex +batchsize;
        open mycursorresult for strquery;
        End loop;
        countt:=countt-1;
    End loop;
end;

原代码核心问题 #

• 每次循环都会重新打开tablenamecursor和mycursorresult游标，之前的游标结果会被覆盖，最终仅保留最后一次循环的查询结果。
• 批量查询逻辑中，StartIndex未在每个表处理前重置，导致处理后续表时起始ID错误。

方法1：合并所有表数据为统一结果集（含表名） #

通过动态SQL拼接所有test_开头表的查询，为每条数据添加表名字段，再通过单个游标返回所有批量数据。此方法无需多次打开游标，直接返回完整结果。

create or replace PROCEDURE GET_TEST_TABLE_DATA (p_result out sys_refcursor)
As
    v_sql clob;
Begin
    -- 动态拼接所有test_表的查询，添加表名字段，按id批量获取
    select listagg(
        'SELECT ''' || table_name || ''' AS table_name, a.*, ' ||
        'Row_number() OVER (ORDER BY id) AS lookup ' ||
        'FROM "' || table_name || '" a ' ||
        'WHERE id BETWEEN 1 AND (SELECT CEIL(COUNT(*)/100)*100 FROM "' || table_name || '") ' ||
        'AND MOD(id,100) != 0 OR id = (SELECT CEIL(COUNT(*)/100)*100 FROM "' || table_name || '")',
        ' UNION ALL '
    ) within group (order by table_name)
    into v_sql
    from user_tables
    where table_name like 'TEST_%'; -- Oracle表名默认大写，注意匹配

    -- 打开游标返回结果
    open p_result for v_sql;
End;
/

方法2：使用管道化函数逐批返回数据 #

如果需要严格按每100条为一批输出，可以用管道化函数，逐批推送每个表的数据，同时包含表名：

1. 定义自定义类型 #

-- 定义行类型（若所有test_表结构不一致，可将列替换为通用类型如clob）
create or replace type test_table_row as object (
    table_name varchar2(30),
    id number,
    col1 varchar2(100), -- 替换为实际表列
    col2 number,        -- 替换为实际表列
    lookup number
);
/

-- 定义表类型
create or replace type test_table_rows as table of test_table_row;
/

2. 创建管道化函数 #

create or replace function GET_TEST_BATCH_DATA return test_table_rows pipelined
As
    v_table_name varchar2(30);
    v_max_id number;
    v_start_id number := 1;
    v_batch_size number := 100;
    -- 获取所有test_表名的游标
    cursor c_tables is
        select table_name from user_tables where table_name like 'TEST_%' order by table_name;
Begin
    for rec in c_tables loop
        v_table_name := rec.table_name;
        -- 获取当前表最大id
        execute immediate 'SELECT MAX(id) FROM "' || v_table_name || '"' into v_max_id;
        
        v_start_id := 1;
        while v_start_id <= v_max_id loop
            -- 逐批获取数据并推送
            for data_rec in (
                select v_table_name as table_name, a.*,
                       row_number() over (order by id) as lookup
                from "' || v_table_name || '" a
                where id >= v_start_id and id <= least(v_start_id + v_batch_size - 1, v_max_id)
            ) loop
                pipe row(test_table_row(
                    data_rec.table_name,
                    data_rec.id,
                    data_rec.col1,
                    data_rec.col2,
                    data_rec.lookup
                ));
            end loop;
            v_start_id := v_start_id + v_batch_size;
        end loop;
    end loop;
    return;
End;
/

使用方式 #

-- 调用函数获取所有批次数据
select * from table(GET_TEST_BATCH_DATA());

方法3：返回表名与对应数据游标集合 #

如果需要分开获取表名和对应的数据批次，可以定义包含游标对象的集合类型，存储过程返回表名列表和对应的数据游标：

-- 定义游标类型
create or replace type data_cursor is ref cursor;
/

-- 定义表名-游标映射类型
create or replace type table_data_map as object (
    table_name varchar2(30),
    batch_cursor data_cursor
);
/

create or replace type table_data_maps as table of table_data_map;
/

-- 存储过程
create or replace PROCEDURE GET_TEST_TABLES_AND_DATA (p_result out table_data_maps)
As
    v_result table_data_maps := table_data_maps();
    v_table_name varchar2(30);
    v_max_id number;
    v_start_id number := 1;
    v_batch_size number := 100;
    cursor c_tables is
        select table_name from user_tables where table_name like 'TEST_%' order by table_name;
    v_cursor data_cursor;
Begin
    for rec in c_tables loop
        v_table_name := rec.table_name;
        execute immediate 'SELECT MAX(id) FROM "' || v_table_name || '"' into v_max_id;
        
        v_start_id := 1;
        while v_start_id <= v_max_id loop
            -- 为每个批次创建游标
            open v_cursor for '
                SELECT a.*, Row_number() OVER (ORDER BY id) AS lookup 
                FROM "' || v_table_name || '" a 
                WHERE id >= :1 and id <= :2
            ' using v_start_id, least(v_start_id + v_batch_size - 1, v_max_id);
            
            -- 添加到结果集合
            v_result.extend();
            v_result(v_result.count) := table_data_map(v_table_name, v_cursor);
            
            v_start_id := v_start_id + v_batch_size;
        end loop;
    end loop;
    
    p_result := v_result;
End;
/

内容的提问来源于stack exchange，提问作者Lakshmi Vinod