从源数据库抽取数据到中间库

----------------------------------------------------------------------------------------------------------
-- PROCEDURE NAME : PROC_SYN_DATA
-- DESCRIPTION : 同步抽取数据
----------------------------------------------------------------------------------------------------------
IS
cnt INTEGER;
BEGIN


--判断表是否已存在，如果存在先删除
SELECT COUNT(*) INTO cnt FROM USER_TABLES WHERE TABLE_NAME = 'ETL_PRJ_CONTRACTPLANCOSTBILL';
IF cnt >0 THEN

   EXECUTE IMMEDIATE 'DROP TABLE ETL_PRJ_CONTRACTPLANCOSTBILL';
   DBMS_OUTPUT.PUT_LINE('ETL_PRJ_CONTRACTPLANCOSTBILL表存在，删除成功!');
END IF;


SELECT COUNT(*) INTO cnt FROM USER_TABLES WHERE TABLE_NAME = 'ETL_PRJ_PRJCONTRACT';
IF cnt > 0 THEN
   EXECUTE IMMEDIATE 'DROP TABLE ETL_PRJ_PRJCONTRACT';
   DBMS_OUTPUT.PUT_LINE('ETL_PRJ_PRJCONTRACT表存在，删除成功!');
END IF;


--抽取数据并存储
EXECUTE IMMEDIATE 'CREATE TABLE ETL_PRJ_CONTRACTPLANCOSTBILL AS SELECT * FROM PBYL1030.CT_PRJ_CONTRACTPLANCOSTBILL@eas_220_dblink WHERE CFSTATE IN (''10'', ''25'', ''60'')'; --10：暂存、25：提交、60：生效
EXECUTE IMMEDIATE 'CREATE TABLE ETL_PRJ_PRJCONTRACT AS SELECT * FROM PBYL1030.CT_PRJ_PRJCONTRACT@eas_220_dblink WHERE CFSTATE = ''60'' ';


--增加同步时间字段
EXECUTE IMMEDIATE 'ALTER TABLE ETL_PRJ_CONTRACTPLANCOSTBILL ADD (CREATE_TIME DATE DEFAULT SYSDATE NOT NULL)';
EXECUTE IMMEDIATE 'ALTER TABLE ETL_PRJ_CONTRACTPLANCOSTBILL ADD (LAST_UPDATE_TIME DATE DEFAULT SYSDATE NOT NULL)';
EXECUTE IMMEDIATE 'ALTER TABLE ETL_PRJ_PRJCONTRACT ADD (CREATE_TIME DATE DEFAULT SYSDATE NOT NULL)';
EXECUTE IMMEDIATE 'ALTER TABLE ETL_PRJ_PRJCONTRACT ADD (LAST_UPDATE_TIME DATE DEFAULT SYSDATE NOT NULL)';


END  PROC_SYN_DATA;