如何在表中插入记录的每一个组合

Question

我正在处理一个Oracle过程，需要根据传递给参数的customerid将客户和产品的每个组合插入到特定的表中。

输入:

Customers参数(p_customers -逗号分隔):即:(1,2,3,4)

订单参数(p_order - single value)

必须写入的输出表:

CustomerID, ProductID

我希望下面的查询能给我一个需要插入的产品列表:

select ProductID from orders where order_id = p_order_id

写insert语句最有效的方法是什么?

谢谢!

Answer 1

根据您的"伪"代码，尝试如下操作。我从http://www.oratechinfo.co.uk/delimited_lists_to_collections.html

假设这些结构:

CREATE TABLE orders (
    orderId    VARCHAR2(100) primary key
  , customerId VARCHAR2(100)
  , productId  VARCHAR2(100)
);
CREATE TABLE output_table (customerId VARCHAR2(100), productId VARCHAR2(100));

代码:

CREATE TYPE varchar_table IS TABLE OF VARCHAR2(100);
/
CREATE OR REPLACE FUNCTION csv_convert(p_list IN VARCHAR2)
  RETURN varchar_table
AS
  l_string       VARCHAR2(32767) := p_list || ',';
  l_comma_index  PLS_INTEGER;
  l_index        PLS_INTEGER := 1;
  l_tab          varchar_table := varchar_table();
BEGIN
  LOOP
    l_comma_index := INSTR(l_string, ',', l_index);
    EXIT WHEN l_comma_index = 0;
    l_tab.EXTEND;
    l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);
    l_index := l_comma_index + 1;
  END LOOP;
  RETURN l_tab;
END csv_convert;
/

新创建的csv_convert函数的简单单元测试:

SELECT * FROM TABLE(csv_convert('1,2,3,4'));
COLUMN_VALUE
------------
1
2
3
4

现在是使用csv_convert函数的存储进程:

CREATE OR REPLACE PROCEDURE cust_products_process (p_customers IN VARCHAR2)
IS
BEGIN
  INSERT INTO output_table (customerId, productId)
    SELECT customerId, productId
      FROM orders o
      JOIN TABLE(csv_convert(p_customers)) c
        ON (o.customerId = c.column_value);
END;
/