我写个存储，要求根据源库表更新目标库表，俩库的表名字段完全一样， 但是每次merger上张表和下张表的字段名，字段个数，都不一样，怎么写个循环，merge所有表？
我现在写成这样 ，sql拼不下去了，谁帮帮我补全
create or replace procedure P_MERGE_TABLES is
/* MERGE 同步*/
C_OWNER varchar2(30);
C_TABLE_XH NUMBER;
C_TABLE_NAME varchar2(30);
C_COLUMN_XH NUMBER;
C_COLUMN_NAME VARCHAR2(30);
str_sql varchar2(1000);
--循环
cursor curaa is
SELECT A.XH as table_xh, A.OWNER, A.TABLE_NAME, B.COLUMN_ID, B.COLUMN_NAME
FROM (SELECT ROWNUM XH, T.OWNER, T.TABLE_NAME
FROM ALL_TABLES T
WHERE T.TABLE_NAME LIKE 'HX%') A,
ALL_TAB_COLUMNS B
WHERE A.TABLE_NAME = B.TABLE_NAME
ORDER BY A.XH, B.COLUMN_ID;
begin
for cu in curaa loop
C_OWNER := cu.owner;
C_TABLE_XH := cu.table_xh;
C_TABLE_NAME := cu.table_name;
C_COLUMN_XH := cu.column_xh;
C_COLUMN_NAME := cu.column_name;
str_sql := 'MERGE INTO HX_ZSJ.c_table_name MB
USING HX_BAK.c_table_name@DB_LINKS Y
ON (MB.PZZL_DM = Y.PZZL_DM)
WHEN MATCHED THEN
UPDATE
SET MB.C_COLUMN_NAME = Y.C_COLUMN_NAME,
..................................................................
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
WHEN NOT MATCHED THEN
INSERT
VALUES
(Y.C_COLUMN_NAME,
....................................
。。。。。。。。。。。。。。。。。。。。。。。。。。。。);'
execute immediate str_sql; --插入统计数据
commit;
end loop;
end P_MERGE_TABLES;
/