从远程库同步字段到本地库

业务库：afsp_hf_2017
平台库：FASP_1502

目标：业务库中基础数据表添加字段 year varchar2(4)、 province varchar2(9)

从平台库同步这两个字段的值
1.查询需要同步的业务库中基础数据表
select * from t_dictable t where t.tablecode like ‘%T_PUB%’;
2.判断平台库中是否存在这样的表：(注意在平台库中查询表的时候表名为FASP_+业务库中表名)
不存在：直接创建字段
存在：
平台库是否存在这两个字段（year、province）
平台库不存在：直接跳过
平台库存在：业务库中基础数据表添加字段、同步字段值

    --本地表名格式
    T_PUB_AAA、T_PUB_BBB.....

    --平台表格式
    FASP_T_PUB_AAA、FASP_T_PUB_BBB...

1.在本地库创建dblink；
2.写存储过程

CREATE OR REPLACE PACKAGE BODY PKG_FASP2ADDPROVINCE IS
   PROCEDURE FASP2ADDPROVINCE
   IS
      CTABLE RCURSOR;
      TABLENAME VARCHAR2(200);
      TABLECOUNT NUMBER(4);
      COLUMNCOUNT NUMBER(4);
      COLUMNCOUNT_CURRENT  NUMBER(4);
      SSQL         VARCHAR2(2000);
   BEGIN
     --1.循环凭证注册的基础数据表
     OPEN CTABLE FOR SELECT T.TABLECODE FROM T_DICTABLE T WHERE T.TABLECODE LIKE 'T_PUB%';
     LOOP
           FETCH CTABLE INTO TABLENAME;
           EXIT WHEN CTABLE%NOTFOUND;
           --2.判断表中有没有字段
           SSQL := 'SELECT COUNT(1) FROM user_tab_cols WHERE TABLE_NAME = '''||TABLENAME||''' AND (COLUMN_NAME = ''PROVINCE'')';
           EXECUTE IMMEDIATE SSQL INTO COLUMNCOUNT_CURRENT;
           IF COLUMNCOUNT_CURRENT=0 THEN
             --当前库中没有字段直接添加
             SSQL := 'ALTER TABLE '||TABLENAME ||' ADD PROVINCE VARCHAR2(9)';
             EXECUTE IMMEDIATE SSQL;
           END IF;  
           --判断平台2有没有该表
           SSQL := 'SELECT COUNT(1) FROM user_tables@fasp_1502 T WHERE T.TABLE_NAME = ''FASP_'||TABLENAME||'''';
           EXECUTE IMMEDIATE SSQL INTO TABLECOUNT;
           IF TABLECOUNT>0 THEN
             --有表再判断是否有字段
             SSQL := 'SELECT COUNT(1) FROM user_tab_cols@fasp_1502 WHERE TABLE_NAME = ''FASP_'||TABLENAME||''' AND (COLUMN_NAME = ''PROVINCE'')';
             EXECUTE IMMEDIATE SSQL INTO COLUMNCOUNT;
             IF COLUMNCOUNT>0 THEN--平台表中的存在字段
               SSQL := 'UPDATE '||TABLENAME||' T SET T.PROVINCE = (SELECT F.PROVINCE FROM FASP_1502.FASP_'||TABLENAME||' F  WHERE F.STATUS = 1 AND F.CODE = T.CODE)';
               EXECUTE IMMEDIATE SSQL;
             END IF;
           END IF;

           --2.判断表中有没有字段
           SSQL := 'SELECT COUNT(1) FROM user_tab_cols WHERE TABLE_NAME = '''||TABLENAME||''' AND (COLUMN_NAME = ''YEAR'')';
           EXECUTE IMMEDIATE SSQL INTO COLUMNCOUNT_CURRENT;
           IF COLUMNCOUNT_CURRENT=0 THEN
             --当前库中没有字段直接添加
             SSQL := 'ALTER TABLE '||TABLENAME ||' ADD YEAR VARCHAR2(4)';
             EXECUTE IMMEDIATE SSQL;
           END IF;  
           --判断平台2有没有该表
           SSQL := 'SELECT COUNT(1) FROM user_tables@fasp_1502 T WHERE T.TABLE_NAME = ''FASP_'||TABLENAME||'''';
           EXECUTE IMMEDIATE SSQL INTO TABLECOUNT;
           IF TABLECOUNT>0 THEN
             --有表再判断是否有字段
             SSQL := 'SELECT COUNT(1) FROM user_tab_cols@fasp_1502 WHERE TABLE_NAME = ''FASP_'||TABLENAME||''' AND (COLUMN_NAME = ''YEAR'')';
             EXECUTE IMMEDIATE SSQL INTO COLUMNCOUNT;
             IF COLUMNCOUNT>0 THEN--平台表中的存在字段
               SSQL := 'UPDATE '||TABLENAME||' T SET T.YEAR = (SELECT F.YEAR FROM FASP_1502.FASP_'||TABLENAME||' F  WHERE F.STATUS = 1 AND F.CODE = T.CODE)';
               EXECUTE IMMEDIATE SSQL;
             END IF;
           END IF;        
      END LOOP;
      --commit;
   END ;
END PKG_FASP2ADDPROVINCE;   

注意：
上面这段代码的 FASP_1502.表名， 用到的是orcl的schema；
user_tables@fasp_1502 则用的是数据的dblink；

--部分sql
SSQL := 'UPDATE '||TABLENAME||' T SET T.YEAR = (SELECT F.YEAR FROM FASP_1502.FASP_'||TABLENAME||' F  WHERE F.STATUS = 1 AND F.CODE = T.CODE)';

--部分sql
SSQL := 'SELECT COUNT(1) FROM user_tables@fasp_1502 T WHERE T.TABLE_NAME = ''FASP_'||TABLENAME||'''';