--准备新的分区表
--做的是安月进行分区

CREATE TABLE FACT_BOND_PROFIT_NEW (
  D_DATE                DATE,
  VC_CURVE_NAME         VARCHAR2(100),
  VC_CURVE_CODE         VARCHAR2(10),
  VC_CURVE_CLASSIFY     VARCHAR2(20),
  F_CURVE_FUTURE        NUMBER(19,4),
  F_STANDARD_TERM       NUMBER(19,4),
  VC_STANDARD_TERM_DESC VARCHAR2(20),
  F_PROFITRATIO         NUMBER(19,4),
  VC_SOURCE             VARCHAR2(20),
  D_UPDATETIME          DATE
)
PARTITION BY RANGE (D_DATE)  
 INTERVAL (NUMTOYMINTERVAL(1, ‘MONTH‘))   
  (  
   PARTITION P1 VALUES LESS THAN (TO_DATE(‘2013-1-1‘, ‘YYYY-MM-DD‘))  
  );

-- Add comments to the table 
comment on table FACT_BOND_PROFIT_NEW
  is ‘债券收益率曲线‘;
-- Add comments to the columns 
comment on column FACT_BOND_PROFIT_NEW.d_date
  is ‘业务日期‘;
comment on column FACT_BOND_PROFIT_NEW.vc_curve_name
  is ‘曲线名称‘;
comment on column FACT_BOND_PROFIT_NEW.vc_curve_code
  is ‘曲线代码‘;
comment on column FACT_BOND_PROFIT_NEW.vc_curve_classify
  is ‘曲线分类，数据字典：CURVE_CLASSIFY‘;
comment on column FACT_BOND_PROFIT_NEW.f_curve_future
  is ‘远期期限‘;
comment on column FACT_BOND_PROFIT_NEW.f_standard_term
  is ‘标准期限‘;
comment on column FACT_BOND_PROFIT_NEW.vc_standard_term_desc
  is ‘标准期限描述‘;
comment on column FACT_BOND_PROFIT_NEW.f_profitratio
  is ‘收益率‘;
comment on column FACT_BOND_PROFIT_NEW.vc_source
  is ‘数据源‘;
comment on column FACT_BOND_PROFIT_NEW.d_updatetime
  is ‘更新时间‘;
-- Create/Recreate indexes 
create index IDX_FACT_BOND_PROFIT_NEW1 on FACT_BOND_PROFIT_NEW (D_DATE, VC_CURVE_CODE, VC_SOURCE)
  tablespace TPA_DW_INDEX;
-- Grant/Revoke object privileges 
GRANT SELECT ON FACT_BOND_PROFIT_NEW TO TPA_QUERY;

--创建分区信息的临时表
CREATE TABLE TMP_DBA_TAB_PARTITIONS
(
  TABLE_NAME     VARCHAR2(30),
  PARTITION_NAME VARCHAR2(30),
  HIGH_VALUE     CLOB
);

--FACT_BOND_PROFIT的各月数据表
CREATE TABLE FACT_BOND_PROFIT1 (D_DATE DATE); 

--过程运行时间信息
CREATE TABLE TMP_SYSDIME
(
  TIME1 VARCHAR2(30),
  TIME2 DATE
);

-- Created on 2017/4/11 by ZW_HUANGZHIYA 
declare 
  -- Local variables here
  PARTITION_NAME VARCHAR2(30); --分区名称
  MIN_DATE DATE; --原表中的最小日期
  MAX_DATE DATE; --原表中的最大日期
  V_DATE  DATE;
  V_DATE1 VARCHAR2(20);
  LS_SQL VARCHAR2(2000); 
begin
 -- Test statements her
 SELECT MIN(D_DATE),MAX(D_DATE)
   INTO MIN_DATE,MAX_DATE
   FROM FACT_BOND_PROFIT; 
   
 V_DATE := MIN_DATE;
 
 --在新表中生成分区，方便交换
 WHILE V_DATE <= MAX_DATE LOOP
   INSERT INTO FACT_BOND_PROFIT_NEW (D_DATE)
   SELECT V_DATE FROM DUAL; 
   COMMIT;
   V_DATE := V_DATE + 1;
 END LOOP; 
 
 --清空刚刚插入的数据
 EXECUTE IMMEDIATE ‘TRUNCATE TABLE FACT_BOND_PROFIT_NEW‘;
 EXECUTE IMMEDIATE ‘TRUNCATE TABLE TMP_DBA_TAB_PARTITIONS‘;
 
 --将分区信息的数据插入临时表
 INSERT INTO TMP_DBA_TAB_PARTITIONS 
 SELECT A.TABLE_NAME, A.PARTITION_NAME, TO_LOB(A.HIGH_VALUE) HIGH_VALUE
   FROM DBA_TAB_PARTITIONS A
  WHERE A.TABLE_NAME = ‘FACT_BOND_PROFIT_NEW‘
    AND A.TABLE_OWNER = ‘TPA_DW‘;
 COMMIT;
 
 --循环对各个分区进行交换
 FOR AA IN (SELECT * FROM TMP_DBA_TAB_PARTITIONS) LOOP
   
   --获取各个月对应的分区名称
   SELECT TO_CHAR(SUBSTR(AA.HIGH_VALUE,11,10)),AA.PARTITION_NAME
     INTO V_DATE1,PARTITION_NAME
     FROM DUAL;
   
   --创建对应符合分区的数据表，其实进行了交换分区，表中就没有了数据，清空表是习惯
   EXECUTE IMMEDIATE ‘TRUNCATE TABLE FACT_BOND_PROFIT1‘;
   EXECUTE IMMEDIATE ‘DROP TABLE FACT_BOND_PROFIT1 PURGE‘; 
   
   --创建各月信息表，一般创表的速度要高于向表中插入的速度
   LS_SQL := ‘CREATE TABLE FACT_BOND_PROFIT1 NOLOGGING AS
   SELECT * FROM FACT_BOND_PROFIT WHERE D_DATE BETWEEN ADD_MONTHS(TO_DATE(‘‘‘||V_DATE1||‘‘‘,‘‘YYYY-MM-DD‘‘),-1) AND TO_DATE(‘‘‘||V_DATE1||‘‘‘,‘‘YYYY-MM-DD‘‘) - 1‘;
   EXECUTE IMMEDIATE LS_SQL;
   
   --进行交换分区
   EXECUTE IMMEDIATE ‘ALTER TABLE FACT_BOND_PROFIT_NEW EXCHANGE PARTITION ‘||PARTITION_NAME||‘ WITH TABLE FACT_BOND_PROFIT1‘;
   
   --生成日志
   INSERT INTO TMP_SYSDIME 
   SELECT V_DATE1,SYSDATE FROM DUAL; 
   COMMIT;
 END LOOP;
 
 EXECUTE IMMEDIATE ‘DROP TABLE TMP_DBA_TAB_PARTITIONS‘;
 EXECUTE IMMEDIATE ‘DROP TABLE FACT_BOND_PROFIT1‘;

exception when others then 
  DBMS_OUTPUT.PUT_LINE(SQLERRM); 
end;

--日志
--DROP TABLE TMP_SYSDIME;
--SELECT * FROM TMP_SYSDIME;
--FACT_BOND_PROFIT对应的新表
--SELECT * FROM FACT_BOND_PROFIT_NEW;