Oracle 19c不修改存储过程实现分页获取数据的方法咨询

我熟悉Microsoft SQL Server，刚接触Oracle 19c数据库。现有C#代码能成功调用包存储过程PKG_GET_COMPONENT_DETAIL.pr_get_wip_comp_list_sorted，但这个存储过程返回超百万行数据，导致DataSet填充速度极慢。希望在不修改该存储过程的前提下，实现类似SQL Server的分页效果：

SELECT * FROM STORED_PROCEDURE OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY

比如把存储过程作为子查询来做分页查询。

现有C#调用代码 #

public async Task<List<DbModels.DocumentWipList>> GetWipDocumentsAsync(string sort = "limited_dodiss ASC")
{
    using (var connection = new OracleConnection(_configuration.GetConnectionString("OracleDev")))
    {
        using (var command = connection.CreateCommand())
        {
            connection.Open();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "PKG_GET_COMPONENT_DETAIL.pr_get_wip_comp_list_sorted";
            command.Parameters.Add("arg_sort", OracleDbType.Varchar2).Value = sort;
            command.Parameters.Add("io_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output; 

            using (var da = new OracleDataAdapter())
            {
                da.SelectCommand = command;
                var dt = new DataTable();
                await Task.Run(() => da.Fill(dt)); 

                return MapDocumentWipList(dt);
            }
        }
    }
}

解决方案 #

方案1：数据库端分页（推荐） #

Oracle无法直接从存储过程结果集做SELECT查询，但可以通过PL/SQL块先调用存储过程获取游标，将游标数据批量收集到集合中，再对集合做分页查询（仅返回分页后的数据，性能最优）。

实现代码

public async Task<List<DbModels.DocumentWipList>> GetWipDocumentsPagedAsync(int pageIndex, int pageSize, string sort = "limited_dodiss ASC")
{
    using (var connection = new OracleConnection(_configuration.GetConnectionString("OracleDev")))
    {
        await connection.OpenAsync();
        // 先确定存储过程返回的字段，定义匹配的PL/SQL记录类型（示例字段需替换为实际字段）
        var sql = @"
DECLARE
    v_cursor SYS_REFCURSOR;
    -- 定义和存储过程返回结构匹配的记录类型
    TYPE wip_comp_record IS RECORD (
        limited_dodiss VARCHAR2(100),
        doc_id NUMBER,
        create_date DATE
        -- 补充存储过程返回的其他字段
    );
    -- 定义对应的集合类型
    TYPE wip_comp_tab IS TABLE OF wip_comp_record;
    v_wip_comps wip_comp_tab;
BEGIN
    -- 调用原存储过程获取全量数据游标
    PKG_GET_COMPONENT_DETAIL.pr_get_wip_comp_list_sorted(:arg_sort, v_cursor);
    -- 将游标数据批量收集到集合中
    FETCH v_cursor BULK COLLECT INTO v_wip_comps;
    CLOSE v_cursor;
    -- 打开分页后的输出游标（Oracle 12c+支持OFFSET语法）
    OPEN :paged_cursor FOR
        SELECT * FROM TABLE(v_wip_comps)
        OFFSET :offset_rows ROWS FETCH NEXT :page_size ROWS ONLY;
END;";

        using (var command = new OracleCommand(sql, connection))
        {
            command.CommandType = CommandType.Text;
            // 原存储过程的排序参数
            command.Parameters.Add("arg_sort", OracleDbType.Varchar2).Value = sort;
            // 分页参数：offset_rows是跳过的行数，page_size是每页行数（pageIndex从0开始）
            int offsetRows = pageIndex * pageSize;
            command.Parameters.Add("offset_rows", OracleDbType.Int32).Value = offsetRows;
            command.Parameters.Add("page_size", OracleDbType.Int32).Value = pageSize;
            // 分页后的输出游标
            var pagedCursorParam = command.Parameters.Add("paged_cursor", OracleDbType.RefCursor);
            pagedCursorParam.Direction = ParameterDirection.Output;

            using (var da = new OracleDataAdapter(command))
            {
                var dt = new DataTable();
                await Task.Run(() => da.Fill(dt));
                return MapDocumentWipList(dt);
            }
        }
    }
}

方案2：应用端分页（应急用） #

如果不想编写PL/SQL逻辑，可以用OracleDataReader逐行读取数据，跳过前N行后取指定数量的行。但这种方式依然会把全量数据从数据库传到应用端，仅适合临时调试或小数据量场景：

public async Task<List<DbModels.DocumentWipList>> GetWipDocumentsPagedAsync(int pageIndex, int pageSize, string sort = "limited_dodiss ASC")
{
    using (var connection = new OracleConnection(_configuration.GetConnectionString("OracleDev")))
    {
        await connection.OpenAsync();
        using (var command = connection.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "PKG_GET_COMPONENT_DETAIL.pr_get_wip_comp_list_sorted";
            command.Parameters.Add("arg_sort", OracleDbType.Varchar2).Value = sort;
            var cursorParam = command.Parameters.Add("io_cursor", OracleDbType.RefCursor);
            cursorParam.Direction = ParameterDirection.Output;

            var result = new List<DbModels.DocumentWipList>();
            int skipCount = pageIndex * pageSize;
            int currentCount = 0;

            using (var reader = await command.ExecuteReaderAsync())
            {
                // 跳过不需要的行
                while (skipCount > 0 && await reader.ReadAsync())
                {
                    skipCount--;
                }
                // 读取分页数据
                while (currentCount < pageSize && await reader.ReadAsync())
                {
                    // 替换为你的实体字段映射逻辑
                    var item = new DbModels.DocumentWipList();
                    item.LimitedDoDiss = reader.GetString(reader.GetOrdinal("limited_dodiss"));
                    item.DocId = reader.GetInt32(reader.GetOrdinal("doc_id"));
                    // ...其他字段映射
                    result.Add(item);
                    currentCount++;
                }
            }
            return result;
        }
    }
}

关键说明 #

• 方案1是数据库端分页，仅返回分页后的数据，是生产环境的首选，需注意PL/SQL中定义的记录类型要和存储过程返回的字段完全匹配。
• 方案2本质是内存分页，依然会传输百万级数据，只适合临时场景。

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