如何在C# OracleCommand中用单个CURSOR返回多个DataTable？

完全可行，以下是两种实用的实现方式，无需新增多个CURSOR：

方法一：UNION ALL + 来源标识列（推荐） #

通过给每个查询添加固定的来源表标识字段，将多表查询结果合并到同一个CURSOR中，在C#端再根据标识字段拆分出不同表的数据。这种方式无需修改Oracle端对象，实现成本最低。

SQL示例（SQL文件内容） #

DECLARE
  v_result_cursor SYS_REFCURSOR;
BEGIN
  OPEN v_result_cursor FOR
    -- 第一张表数据，添加source_table标识
    SELECT 
      'TABLE_A' AS source_table,
      id,
      user_name,
      create_date,
      NULL AS order_code  -- 填充第二张表独有的字段，保证列数/类型匹配
    FROM user_info
    WHERE id = :p_user_id

    UNION ALL

    -- 第二张表数据，添加source_table标识
    SELECT 
      'TABLE_B' AS source_table,
      id,
      NULL AS user_name,  -- 填充第一张表独有的字段
      create_date,
      order_code
    FROM user_orders
    WHERE user_id = :p_user_id;
  
  :p_output_cursor := v_result_cursor;
END;

C#代码处理 #

// 读取SQL文件并完成变量替换（实现逻辑略）
string sqlContent = LoadAndReplaceSqlVariables("your_sql_file.sql");

using (OracleConnection conn = new OracleConnection("your_connection_string"))
using (OracleCommand cmd = new OracleCommand(sqlContent, conn))
{
    // 添加输入参数
    cmd.Parameters.Add("p_user_id", OracleDbType.Int32).Value = 1001;
    // 添加输出游标参数
    cmd.Parameters.Add("p_output_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

    conn.Open();
    using (OracleDataReader reader = cmd.ExecuteReader())
    {
        // 将游标数据加载到合并后的DataTable
        DataTable combinedTable = new DataTable();
        combinedTable.Load(reader);

        // 拆分出TABLE_A的数据
        DataTable tableA = combinedTable.AsEnumerable()
            .Where(row => row["source_table"].ToString() == "TABLE_A")
            .CopyToDataTable();
        // 移除标识列（可选操作）
        tableA.Columns.Remove("source_table");

        // 拆分出TABLE_B的数据
        DataTable tableB = combinedTable.AsEnumerable()
            .Where(row => row["source_table"].ToString() == "TABLE_B")
            .CopyToDataTable();
        tableB.Columns.Remove("source_table");
    }
}

方法二：自定义Oracle集合类型（适用于表结构差异极大的场景） #

如果多表结构差异过大，用NULL填充会导致大量冗余列，可以在Oracle端自定义记录类型和集合类型，将多表数据封装到集合中，再通过单个CURSOR返回。

第一步：在Oracle端创建类型 #

-- 定义包含所有表字段的记录类型
CREATE OR REPLACE TYPE multi_table_record AS OBJECT (
    source_table VARCHAR2(30),
    id NUMBER,
    user_name VARCHAR2(50),
    order_code VARCHAR2(30),
    create_date DATE
);

-- 定义集合类型
CREATE OR REPLACE TYPE multi_table_collection AS TABLE OF multi_table_record;

第二步：SQL文件内容 #

DECLARE
  v_collection multi_table_collection := multi_table_collection();
  v_result_cursor SYS_REFCURSOR;
BEGIN
  -- 批量插入TABLE_A的数据
  FOR rec IN (SELECT id, user_name, create_date FROM user_info WHERE id = :p_user_id) LOOP
    v_collection.EXTEND;
    v_collection(v_collection.COUNT) := multi_table_record(
      'TABLE_A', rec.id, rec.user_name, NULL, rec.create_date
    );
  END LOOP;

  -- 批量插入TABLE_B的数据
  FOR rec IN (SELECT id, order_code, create_date FROM user_orders WHERE user_id = :p_user_id) LOOP
    v_collection.EXTEND;
    v_collection(v_collection.COUNT) := multi_table_record(
      'TABLE_B', rec.id, NULL, rec.order_code, rec.create_date
    );
  END LOOP;

  -- 打开游标返回集合数据
  OPEN v_result_cursor FOR SELECT * FROM TABLE(v_collection);
  :p_output_cursor := v_result_cursor;
END;

C#处理逻辑 #

和方法一完全一致，读取合并后的DataTable后按source_table字段拆分即可。

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