oracle 之数组、嵌套表、SQL查询式实现多表数据for循环插入指定表

Molet Mysql 2023-02-27 693浏览 0

1、基础环境

创建基础表：

CREATE TABLE TEST_TAB1(
ID INT,
NAME VARCHAR2(20)
);
CREATE TABLE TEST_TAB2(
ID INT,
NAME VARCHAR2(20)
);
CREATE TABLE TEST_TAB3(
ID INT,
NAME VARCHAR2(20)
);
CREATE TABLE TEST_IN_TAB(
ID INT,
NAME VARCHAR2(20)
);

插入基础数据：

INSERT INTO TEST_TAB1 VALUES(1,'TEST1');
INSERT INTO TEST_TAB1 VALUES(2,'TEST1');
INSERT INTO TEST_TAB1 VALUES(3,'TEST1');

INSERT INTO TEST_TAB2 VALUES(1,'TEST2');
INSERT INTO TEST_TAB2 VALUES(2,'TEST2');
INSERT INTO TEST_TAB2 VALUES(3,'TEST2');

INSERT INTO TEST_TAB3 VALUES(1,'TEST3');
INSERT INTO TEST_TAB3 VALUES(2,'TEST3');
INSERT INTO TEST_TAB3 VALUES(3,'TEST3');

commit;

2、SELECT 方式

注：

1、使用 t.TABLE_NAME 作为查询表；

2、EXECUTE IMMEDIATE 不会自动提交DML事务，需要显式提交(commit)。

declare
in_sql VARCHAR2(2000);
begin
  for t in (SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE 'TEST_TAB%')
    loop
      in_sql := 'INSERT INTO TEST_IN_TAB 
                 SELECT ID,NAME
                 FROM '||t.TABLE_NAME ||' WHERE ID>=2';
      EXECUTE IMMEDIATE in_sql;
      commit;
    end loop;
end;

3、嵌套表(TABLE)

注：下标从1开始,元素个数无限制

declare
type tab is table of varchar2(20);
tab_test tab;
in_sql VARCHAR2(2000);
begin
    tab_test:=tab('TEST_TAB1','TEST_TAB2','TEST_TAB3');
    for i in 1..tab_test.count
    loop
      in_sql := 'INSERT INTO TEST_IN_TAB 
                   SELECT ID,NAME
                   FROM '||tab_test(i) ||' WHERE ID>=2';
    EXECUTE IMMEDIATE in_sql;
    commit;
   end loop;
end;

4、变长数组(ARRAY)

注：下标从1开始,元素个数有限制

declare
type arr_tab is varray(10) of varchar2(20); --  varray(3) 指定最多10个元素
tab_test arr_tab;
in_sql VARCHAR2(2000);
begin
    tab_test:=arr_tab('TEST_TAB1','TEST_TAB2','TEST_TAB3');
    for i in 1..tab_test.count
    loop
      in_sql := 'INSERT INTO TEST_IN_TAB 
                   SELECT ID,NAME
                   FROM '||tab_test(i) ||' WHERE ID>=2';
    EXECUTE IMMEDIATE in_sql;
    commit;
   end loop;
end;