How would I extend INTERSECT for an arbitrary number of intersections in a stored procedure?

Mr. Boy 0
Viewed 16

I have a table like StockItem { StoreId, ItemId } (where both fields are foreign keys), which details all items stocked in each store.

To answer the question "which stores stock X and Y" I can use INTERSECT like:

CREATE OR REPLACE PROCEDURE FindStoresStockingBothItems  (
   X IN NUMBER, Y IN NUMBER
) AS 
BEGIN    
    select StoreId from StockItem where ItemId = X
    INTERSECT
    select StoreId from StockItem where ItemId = Y;
END;

My use case is to call from C# in a method like List<int> FindStoresStockingBothItems(int x,int y)

But what if I want to extend this to a variable number of items? My C# method signature might now be List<int> FindStoresSellingAllItems(List<int> items) but I've no idea how to do this in PL/SQL - either how to do the multiple intersections or how to pass in a variable number of input item Ids.

What might a PL/SQL stored procedure look like?

plsql
1 Answers

A very simple solution, albeit not beautiful, would be to pass a string with the values:

CREATE OR REPLACE PROCEDURE FindStoresStockingBothItems (p_ids VARCHAR2) AS 
  v_storeid INTEGER;
BEGIN    
  SELECT storeid
  INTO v_storeid
  FROM stockitem
  WHERE ',' || p_ids || ',' LIKE '%,' || ItemId || ',%'
  GROUP BY storeid
  HAVING COUNT(*) = REGEXP_COUNT(p_ids, ',') + 1;
END;
Thorsten Kettner0
Related