Oracle 创建从各种表插入数据的过程

嗨，

我有这个参数表tb_parameter。

+----+----------+-----+-----------+---------------------------------------------------------------------------------------
| ID_SRC| TABLE_NAME  | VIEW_NAME     | COLUMN_VIEW             | COLUMN_TABLE
+----+----------+-----+-----------+---------------------------------------------------------------------------------------
|  1  | CUSTOMER    | VIEW_CUSTOMER    | id,name,reference       |  id,name,reference      
|  2  | PRODUCT     | VIEW_PRODUCT   | id_product,type_product        |  id_product,type_product   
|  3  | OWNER      | VIEW_OWNER      | id_owner,product_owned            |  id_owner,product_owned           
|  4  | CITY      | VIEW_CITY      | name,adress        |  name,adress    
|----+----------+-----+-----------+---------------------------------------------------------------------------------------------+ 
复制

我想创建一个过程pl/sql来做到这一点:

插入表名称 (column_tab) 值
从view_name @ dblink中选择列视图

对于参数表tb_parameter的每一行。

我需要一个dblink来访问视图

-我尝试了这个，但它不起作用
错误 (40,1): PL/SQL: SQL语句已忽略
错误 (40,13): PL/SQL: ORA-00942: 表或视图不存在

create or replace PROCEDURE pr_load_tab 
IS

v_views tb_parameter.view_name%TYPE;
v_tab tb_parameter.table_name%TYPE;
v_column_view tb_parameter.column_view%TYPE;
v_column_table tb_parameter.column_table%TYPE;
v_source tb_parameter.id_src%TYPE;


i NUMBER(2) := 0;

CURSOR c1 IS SELECT view_name FROM tb_parameter where id_src=v_source;
CURSOR c2 IS  select table_name from tb_parameter where id_src=v_source;
CURSOR c3 IS  select column_view from tb_parameter where id_src=v_source;
CURSOR c4 IS  select column_table from tb_parameter where id_src=v_source;
CURSOR c5 IS  select column_table from tb_parameter where id_src=v_source;
 

BEGIN
OPEN c1;
OPEN c2;
OPEN c3;
OPEN c4;
LOOP
FETCH c1  INTO v_views;
FETCH c2  INTO v_tab;
FETCH c3 INTO v_column_view;
FETCH c4  INTO v_column_table;
IF (c1%FOUND) AND (c2%FOUND) AND (c3%FOUND) AND (c4%FOUND)THEN
v_source := v_source +1;
INSERT INTO v_tab VALUES (v_column_table);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE c1;
CLOSE c2;
CLOSE c3;
CLOSE c4;
END;
/
复制

如果在 * 编译 * 时不知道表名和列名，则需要使用动态SQL来构建insert语句，例如:

SQL> declare
  2      l_table varchar2(100) := 'EMP';
  3      l_query varchar2(32767) := 'select * from '||l_table;
  4
  5      l_theCursor     integer default dbms_sql.open_cursor;
  6      l_descTbl       dbms_sql.desc_tab;
  7      l_colCnt        number;
  8      l_insert      varchar2(4000) := 'insert into '||l_table||' select ';
  9  begin
 10      dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
 11      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 12
 13      for i in 1 .. l_colCnt loop
 14        l_insert := l_insert || case when i > 1 then ',' end|| l_descTbl(i).col_name;
 15      end loop;
 16      l_insert := l_insert || chr(10) ||' from '||l_table||'@dblink';
 17      dbms_output.put_line(l_insert);
 18  end;
 19  /
insert into EMP select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
 from EMP@dblink

复制