Oracle 将一个表中的行插入到其他表的列中。

嗨，
希望您做得很好，开放的世界令人兴奋。

我有2个表，表1具有必须作为行插入到table2中的列和数据。
我分享了5列的示例，但是数量不是恒定的。这可以随着不同的表而改变。



如果您可以使用最好的方法来使用通用plsql而无需对列名进行硬编码，那么我将很高兴。

下面是示例，


表1
------------

CREATE TABLE data_table
  (
    table_name   VARCHAR2(100),
    column_name  VARCHAR2(100),
    column_value VARCHAR2(500)
  );



insert into data_table values ( 'TEST_TAB','ORDER_NUMBER','ORD1001');
insert into data_table values ( 'TEST_TAB','CUST_NAME','CUST1001');
insert into data_table values ( 'TEST_TAB','INVOICE_NUMBER','INV001');
insert into data_table values ( 'TEST_TAB','ORDER_DATE','04/11/2018 15:14:00');
insert into data_table values ( 'TEST_TAB','AMOUNT','1001');

commit;


select * from data_table

表名称列值
测试标签订单编号ORD1001
测试标签客户名称客户1001
测试标签发票编号INV001
TEST_TAB金额1001
测试标签订单日期4/11/2018 15:14


表2
------------

CREATE TABLE TEST_TAB
  (
    ORDER_NUMBER   VARCHAR2(100),
    CUST_NAME      VARCHAR2(100),
    INVOICE_NUMBER VARCHAR2(100),
    ORDER_DATE     DATE,
    AMOUNT         NUMBER
  );

“data_table” 中的 “column_name” 将具有 “test_tab” 中的列的相同名称。
(列数可能会动态变化)

我们如何从 “data_table” 中读取数据并将数据插入到看起来像这样的 “test_tab” 中。

金额客户名称发票数量订单日期订单数量
1001 CUST1001 INV001 4/11/2018 15:14 ORD1001

非常感谢这里的任何帮助。

谢谢。

一点DBMS_SQL在这里会有所帮助。我添加了 “row_seq” 列，以便我们可以演示多行

我正在使用listagg来建立列名列表并绑定变量列表。

SQL> CREATE TABLE data_table
  2    (
  3      row_seq      int,
  4      table_name   VARCHAR2(30),
  5      column_name  VARCHAR2(30),
  6      column_value VARCHAR2(30)
  7    );

Table created.

SQL>
SQL>
SQL>
SQL> insert into data_table values ( 1,'TEST_TAB','ORDER_NUMBER','ORD1001');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','CUST_NAME','CUST1001');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','INVOICE_NUMBER','INV001');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','ORDER_DATE','04/11/2018 15:14:00');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','AMOUNT','1001');

1 row created.

SQL>
SQL> insert into data_table values ( 2,'TEST_TAB','ORDER_NUMBER','ORD1002');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','CUST_NAME','CUST1002');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','INVOICE_NUMBER','INV002');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','ORDER_DATE','02/11/2018 15:14:00');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','AMOUNT','1002');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create table test_tab (
  2    order_number varchar2(10),
  3    cust_name varchar2(10),
  4    invoice_number varchar2(10),
  5    order_date varchar2(30),
  6    amount varchar2(10)
  7  );

Table created.

SQL>
SQL> select * from data_table;

   ROW_SEQ TABLE_NAME                     COLUMN_NAME                    COLUMN_VALUE
---------- ------------------------------ ------------------------------ ------------------------------
         1 TEST_TAB                       ORDER_NUMBER                   ORD1001
         1 TEST_TAB                       CUST_NAME                      CUST1001
         1 TEST_TAB                       INVOICE_NUMBER                 INV001
         1 TEST_TAB                       ORDER_DATE                     04/11/2018 15:14:00
         1 TEST_TAB                       AMOUNT                         1001
         2 TEST_TAB                       ORDER_NUMBER                   ORD1002
         2 TEST_TAB                       CUST_NAME                      CUST1002
         2 TEST_TAB                       INVOICE_NUMBER                 INV002
         2 TEST_TAB                       ORDER_DATE                     02/11/2018 15:14:00
         2 TEST_TAB                       AMOUNT                         1002

10 rows selected.

SQL>
SQL> declare
  2    l_sql varchar2(32000);
  3    l_cur     pls_integer := dbms_sql.open_cursor;
  4    l_execute pls_integer;
  5  begin
  6    for i in (
  7      select   table_name,
  8               column_name,
  9               row_number() over ( partition by table_name, row_seq order by column_name ) as seq,
 10               count(*) over ( partition by table_name, row_seq ) as col_cnt,
 11               listagg(column_name,',') within group ( order by column_name ) over ( partition by table_name, row_seq ) as cols,
 12               listagg(':'||column_name,',') within group ( order by column_name ) over ( partition by table_name, row_seq ) as bindcols,
 13               column_value
 14      from data_table
 15      order by table_name, row_seq, column_name
 16   ) loop
 17       if i.seq = 1 then
 18         l_sql := 'insert into '||i.table_name||'('||i.cols||') values ('||i.bindcols||')';
 19         dbms_sql.parse(l_cur,l_sql,dbms_sql.native);
 20       end if;
 21       dbms_sql.bind_variable(l_cur,i.column_name,i.column_value);
 22       if i.seq = i.col_cnt then
 23         l_execute := dbms_sql.execute(l_cur);
 24       end if;
 25   end loop;
 26   dbms_sql.close_cursor(l_cur);
 27  end;
 28  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from test_tab;

ORDER_NUMB CUST_NAME  INVOICE_NU ORDER_DATE                     AMOUNT
---------- ---------- ---------- ------------------------------ ----------
ORD1001    CUST1001   INV001     04/11/2018 15:14:00            1001
ORD1002    CUST1002   INV002     02/11/2018 15:14:00            1002

2 rows selected.

SQL>