建分区

CREATE OR REPLACE PROCEDURE p_dtl_bat_add_partition
(
    i_table_name VARCHAR2,
    i_month_id   NUMBER
)
/*本程序主要对目标表创建一个以DATE_ID分区的月表:如musicdw.dwd_online_cdr_201210*/
 AS
    v_begin_date        NUMBER; -- 月份的开始日期
    v_end_date          NUMBER; -- 月份的结束日期
    l_sql               VARCHAR2(8000);
    v_table_name        VARCHAR2(300); -- 结果月表的表名 -- 不带用户名
    v_target_table_name VARCHAR2(300); -- 结果月表的表名 -- 带用户名
    v_table_user        VARCHAR2(200); -- 表所属的用户
    v_tablespace        VARCHAR2(200); -- 表所属的表空间

BEGIN

    /*获取原表的用户,结果表保持跟原表一致*/
    /*用户要具有访问dba_table的权限*/
    BEGIN
        SELECT t.owner,
               t.tablespace_name
        INTO   v_table_user,
               v_tablespace
        FROM   dba_tables t
        WHERE  t.table_name = upper(i_table_name);
    
        v_tablespace := NVL(v_tablespace, 'TBS_DW_ORD');
    
    EXCEPTION
        /*表不存在的情况,默认是建在DW用户下*/
        WHEN OTHERS THEN
            v_table_user := 'MUSICDW';
            v_tablespace := 'TBS_DW_ORD';
    END;

    /*获取标准的表名,如原表是 dwd_xxxx_dm ,新创建的表不要_DM了.*/
    BEGIN
        SELECT table_user || '.' || tatget_name || i_month_id,
               tatget_name || i_month_id
        INTO   v_target_table_name,v_table_name   /*获取标准的用户名.表名*/
        FROM   dic_backup_table_list
        WHERE  upper(table_name) = upper(i_table_name) AND
               rownum <= 1;
    END;

    /*判断目标月表是否存在,存在的话则drop表*/
    IF pkg_yyjd.func_exist_table(v_table_user, v_table_name)
    THEN
        EXECUTE IMMEDIATE 'drop table ' || v_target_table_name;
    END IF;

    /*初始化建表首先创建当月1号的分区*/
    BEGIN
        /*判断原表是否包含DATE_ID字段,有则按DATE_ID分区,无则不分区*/
        l_sql := 'select * from ' || i_table_name || ' where date_id = 19900101 and 2 < 1';
        EXECUTE IMMEDIATE l_sql;
    
        v_begin_date := i_month_id || '01';
        v_end_date   := i_month_id || '02';
    
        /*创建表和初始分区*/
        l_sql := 'create table ' || v_target_table_name || '  partition by range(DATE_ID)
                 (partition P' || v_begin_date || ' values less THAN (' ||
                 v_end_date || ') 
                  tablespace ' || v_tablespace || ' )
                  as select * from ' || i_table_name || ' where 1 =2';
        EXECUTE IMMEDIATE l_sql;
    
        /*再次初始化数据*/
        v_begin_date := i_month_id || '02';
        v_end_date   := pkg_yyjd.func_get_last_day(i_month_id);
    
        /*从当月二号开始增加分区*/
        WHILE v_begin_date <= v_end_date
        LOOP
            l_sql := 'ALTER TABLE ' || v_target_table_name || ' ADD PARTITION P' || v_begin_date ||
                     ' values less than (' ||
                     to_char(TO_DATE(v_begin_date, 'yyyymmdd') + 1, 'YYYYMMDD') || ')';
        
            EXECUTE IMMEDIATE l_sql;
        
            /*while本来有这个功能,保险功能*/
            IF v_begin_date = v_end_date
            THEN
                EXIT;
            END IF;
        
            /*循环增加1天*/
            v_begin_date := to_number(to_char(TO_DATE(v_begin_date, 'yyyymmdd') + 1, 'YYYYMMDD'));
        
        END LOOP;
    
    EXCEPTION
        WHEN OTHERS THEN
            /*如果不存在DATE_ID字段的，则不建分区*/
            l_sql := 'create table ' || v_target_table_name || ' as 
                      select * from ' || i_table_name || ' where 1 = 2';
            EXECUTE IMMEDIATE l_sql;
    END;

END;
/