1、 日志表、EDW目标表及临时表的创建 --1、日志表的创建 create table EDW_ETL_LOG_DETAIL ( PROC_NAME VARCHAR2(50), P_ETLDATE VARCHAR2(20), ETL_MEMO VARCHAR2(10), ETL_RECORD_NUM INTEGER, ERR_MSG VARCHAR2(1000), ERR_SQL VARCHAR2(1000), TABLE_NAME VARCHAR2(50), START_TIMESTAMP TIMESTAMP(6), END_TIMESTAMP TIMESTAMP(6) ); --2、目标表的创建 create table EDW_T100_STATUS_H ( ID VARCHAR2(8) not null, STATUS VARCHAR2(8) not null, START_DATE DATE not null, END_DATE DATE not null ); -- Add comments to the columns comment on column EDW_T100_STATUS_H.ID is 'ID'; comment on column EDW_T100_STATUS_H.STATUS is '状态'; comment on column EDW_T100_STATUS_H.START_DATE is '开始日期'; comment on column EDW_T100_STATUS_H.END_DATE is '结束日期'; -- Create/Recreate primary, unique and foreign key constraints alter table EDW_T100_STATUS_H add constraint EDW_T100_STATUS_H primary key (ID, START_DATE); -- 临时表的创建 --(1)、之前表 create global temporary table TMP_T100_STATUS_H_PRE ( ID VARCHAR2(8) not null, STATUS VARCHAR2(8) not null, START_DATE DATE not null, END_DATE DATE not null ) on commit delete rows; --(2)、当前表 create global temporary table TMP_T100_STATUS_H_CUR ( ID VARCHAR2(8) not null, STATUS VARCHAR2(8) not null, START_DATE DATE not null, END_DATE DATE not null ) on commit delete rows; --(3)、之后表 create global temporary table TMP_T100_STATUS_H_LAT ( ID VARCHAR2(8) not null, STATUS VARCHAR2(8) not null, START_DATE DATE not null, END_DATE DATE not null ) on commit delete rows; --(4)、新当前表 create global temporary table TMP_T100_STATUS_H_NCU ( ID VARCHAR2(8) not null, STATUS VARCHAR2(8) not null, START_DATE DATE not null, END_DATE DATE not null ) on commit delete rows; 2、 ODS源表建表及测试数据的插入 --1、ODS源表的生成 create table ODS_XT_ZT ( ID VARCHAR2(8), STATUS VARCHAR2(8), ODS_DATA_DATE VARCHAR2(8) ); -- Add comments to the columns comment on column ODS_XT_ZT.ID is 'ID'; comment on column ODS_XT_ZT.STATUS is '状态'; comment on column ODS_XT_ZT.ODS_DATA_DATE is 'ODS数据日期'; --2、测试数据的插入 insert into ods_xt_zt (ID, STATUS, ODS_DATA_DATE)values ('1', '正常', '20100101'); insert into ods_xt_zt (ID, STATUS, ODS_DATA_DATE)values ('1', '正常', '20100102'); insert into ods_xt_zt (ID, STATUS, ODS_DATA_DATE)values ('1', '呆滞', '20100103'); insert into ods_xt_zt (ID, STATUS, ODS_DATA_DATE)values ('1', '呆滞', '20100104'); insert into ods_xt_zt (ID, STATUS, ODS_DATA_DATE)values ('1', '正常', '20100105'); insert into ods_xt_zt (ID, STATUS, ODS_DATA_DATE)values ('1', '正常', '20100106'); insert into ods_xt_zt (ID, STATUS, ODS_DATA_DATE)values ('1', '正常', '20100107'); insert into ods_xt_zt (ID, STATUS, ODS_DATA_DATE)values ('1', '正常', '20100108'); insert into ods_xt_zt (ID, STATUS, ODS_DATA_DATE)values ('1', '注销', '20100109'); commit; 3、 示例代码 DECLARE P_ETLDATE VARCHAR2(8) := '20100101'; --日期参数 O_RUNSTATUS NUMBER; --执行结果 O_MSG VARCHAR2(100); --错误返回