如何在PL/SQL存储过程中扩展INTERSECT实现多商品交集查询

1. 定义数据库集合类型 #

首先需要在Oracle中创建一个可接收批量数字参数的集合类型，用于对接C#传入的List<int>：

CREATE OR REPLACE TYPE NUMBER_LIST AS TABLE OF NUMBER;
/

2. 编写支持可变参数的PL/SQL存储过程 #

无需嵌套多轮INTERSECT，用分组统计+条件筛选的方式更高效简洁：

CREATE OR REPLACE PROCEDURE FindStoresSellingAllItems(
    p_items IN NUMBER_LIST,
    p_result OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_result FOR
        SELECT StoreId
        FROM StockItem
        WHERE ItemId MEMBER OF p_items
        GROUP BY StoreId
        HAVING COUNT(DISTINCT ItemId) = CARDINALITY(p_items);
END;
/

• 逻辑说明：先筛选出包含传入商品列表中任一商品的门店记录，按门店分组后，统计该门店匹配到的不同商品数量；当数量等于传入商品的总数时，说明该门店拥有所有指定商品。
• 使用SYS_REFCURSOR作为输出参数，方便C#接收查询结果。

3. C#调用示例 #

对应方法实现如下（需使用Oracle官方数据访问组件Oracle.ManagedDataAccess.Client）：

using Oracle.ManagedDataAccess.Client;
using System.Collections.Generic;
using System.Data;

public List<int> FindStoresSellingAllItems(List<int> items)
{
    var storeIds = new List<int>();
    string connString = "你的Oracle数据库连接字符串";

    using (var conn = new OracleConnection(connString))
    {
        conn.Open();
        using (var cmd = new OracleCommand("FindStoresSellingAllItems", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            // 传入商品列表参数
            var itemsParam = new OracleParameter("p_items", OracleDbType.Array)
            {
                Direction = ParameterDirection.Input,
                UdtTypeName = "NUMBER_LIST",
                Value = items.ToArray()
            };

            // 输出结果游标参数
            var resultParam = new OracleParameter("p_result", OracleDbType.RefCursor)
            {
                Direction = ParameterDirection.Output
            };

            cmd.Parameters.Add(itemsParam);
            cmd.Parameters.Add(resultParam);

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    storeIds.Add(reader.GetInt32(0));
                }
            }
        }
    }
    return storeIds;
}

补充说明 #

如果不想用集合类型，也可以通过动态SQL拼接多轮INTERSECT，但这种方式存在SQL注入风险，且性能远不如分组统计方案，因此更推荐上述集合+分组的实现方式。

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