如何实现C#传递List<int>至Oracle存储过程批量查询订单？

PL/SQL 实现步骤 #

1. 创建通用的PL/SQL关联数组类型 #

先在数据库中创建可复用的整数类型关联数组，无需为每个单独过程创建专属类型：

CREATE OR REPLACE TYPE NUMBER_ARRAY AS TABLE OF NUMBER INDEX BY PLS_INTEGER;
/

2. 在包中新增批量查询存储过程 #

修改my_package包，添加GetOrders过程：

包声明部分

CREATE OR REPLACE PACKAGE my_package IS
    -- 原有单个查询过程
    PROCEDURE GetOrder(Id IN NUMBER, results OUT SYS_REFCURSOR);
    -- 新增批量查询过程
    PROCEDURE GetOrders(Ids IN NUMBER_ARRAY, results OUT SYS_REFCURSOR);
END my_package;
/

包体部分

CREATE OR REPLACE PACKAGE BODY my_package IS
    PROCEDURE GetOrder(Id IN NUMBER, results OUT SYS_REFCURSOR) IS
    BEGIN
        OPEN results FOR
            SELECT buyer, orderDate, cost
            FROM "Order"
            WHERE "Id" = Id;
    END GetOrder;

    PROCEDURE GetOrders(Ids IN NUMBER_ARRAY, results OUT SYS_REFCURSOR) IS
    BEGIN
        OPEN results FOR
            SELECT buyer, orderDate, cost
            FROM "Order"
            WHERE "Id" MEMBER OF Ids; -- 用MEMBER OF匹配数组中的ID
    END GetOrders;
END my_package;
/

注：若你的Oracle版本不支持MEMBER OF，可替换查询条件为IN (SELECT COLUMN_VALUE FROM TABLE(Ids))

C# 实现代码 #

async Task<List<Order>> GetOrders(List<int> orderIds)
{
    var parameters = new List<OracleParameter>
    {
        new OracleParameter("Ids", OracleDbType.Int32)
        {
            Direction = ParameterDirection.Input,
            CollectionType = OracleCollectionType.PLSQLAssociativeArray,
            Value = orderIds.ToArray(),
            Size = orderIds.Count, // 指定数组元素数量
            ArrayBindSize = orderIds.Select(_ => 4).ToArray() // int类型固定4字节长度
        },
        new OracleParameter("results", OracleDbType.RefCursor)
        {
            Direction = ParameterDirection.Output
        }
    };

    using var reader = await GetDataReaderAsync(connection, "my_package.GetOrders", parameters);
    var orders = new List<Order>();
    while (reader.Read())
    {
        orders.Add(new Order
        {
            Buyer = reader.GetString(reader.GetOrdinal("buyer")),
            OrderDate = reader.GetDateTime(reader.GetOrdinal("orderDate")),
            Cost = reader.GetDecimal(reader.GetOrdinal("cost"))
            // 其他字段按需映射
        });
    }
    return orders;
}

关键说明 #

• OracleParameter.CollectionType需设为PLSQLAssociativeArray，对应PL/SQL中的关联数组类型
• Size必须设置为数组元素个数，否则Oracle无法识别批量参数
• ArrayBindSize用于指定每个元素的字节长度，int类型固定为4字节
• 确保Oracle客户端版本支持关联数组参数传递

内容的提问来源于stack exchange，提问作者Mr. Boy