建分区 

  1 CREATE OR REPLACE PROCEDURE p_dtl_bat_add_partition
  2 (
  3     i_table_name VARCHAR2,
  4     i_month_id   NUMBER
  5 )
  6 /*本程序主要对目标表创建一个以DATE_ID分区的月表:如musicdw.dwd_online_cdr_201210*/
  7  AS
  8     v_begin_date        NUMBER; -- 月份的开始日期
  9     v_end_date          NUMBER; -- 月份的结束日期
 10     l_sql               VARCHAR2(8000);
 11     v_table_name        VARCHAR2(300); -- 结果月表的表名 -- 不带用户名
 12     v_target_table_name VARCHAR2(300); -- 结果月表的表名 -- 带用户名
 13     v_table_user        VARCHAR2(200); -- 表所属的用户
 14     v_tablespace        VARCHAR2(200); -- 表所属的表空间
 15 
 16 BEGIN
 17 
 18     /*获取原表的用户,结果表保持跟原表一致*/
 19     /*用户要具有访问dba_table的权限*/
 20     BEGIN
 21         SELECT t.owner,
 22                t.tablespace_name
 23         INTO   v_table_user,
 24                v_tablespace
 25         FROM   dba_tables t
 26         WHERE  t.table_name = upper(i_table_name);
 27     
 28         v_tablespace := NVL(v_tablespace, 'TBS_DW_ORD');
 29     
 30     EXCEPTION
 31         /*表不存在的情况,默认是建在DW用户下*/
 32         WHEN OTHERS THEN
 33             v_table_user := 'MUSICDW';
 34             v_tablespace := 'TBS_DW_ORD';
 35     END;
 36 
 37     /*获取标准的表名,如原表是 dwd_xxxx_dm ,新创建的表不要_DM了.*/
 38     BEGIN
 39         SELECT table_user || '.' || tatget_name || i_month_id,
 40                tatget_name || i_month_id
 41         INTO   v_target_table_name,v_table_name   /*获取标准的用户名.表名*/
 42         FROM   dic_backup_table_list
 43         WHERE  upper(table_name) = upper(i_table_name) AND
 44                rownum <= 1;
 45     END;
 46 
 47     /*判断目标月表是否存在,存在的话则drop表*/
 48     IF pkg_yyjd.func_exist_table(v_table_user, v_table_name)
 49     THEN
 50         EXECUTE IMMEDIATE 'drop table ' || v_target_table_name;
 51     END IF;
 52 
 53     /*初始化建表首先创建当月1号的分区*/
 54     BEGIN
 55         /*判断原表是否包含DATE_ID字段,有则按DATE_ID分区,无则不分区*/
 56         l_sql := 'select * from ' || i_table_name || ' where date_id = 19900101 and 2 < 1';
 57         EXECUTE IMMEDIATE l_sql;
 58     
 59         v_begin_date := i_month_id || '01';
 60         v_end_date   := i_month_id || '02';
 61     
 62         /*创建表和初始分区*/
 63         l_sql := 'create table ' || v_target_table_name || '  partition by range(DATE_ID)
 64                  (partition P' || v_begin_date || ' values less THAN (' ||
 65                  v_end_date || ') 
 66                   tablespace ' || v_tablespace || ' )
 67                   as select * from ' || i_table_name || ' where 1 =2';
 68         EXECUTE IMMEDIATE l_sql;
 69     
 70         /*再次初始化数据*/
 71         v_begin_date := i_month_id || '02';
 72         v_end_date   := pkg_yyjd.func_get_last_day(i_month_id);
 73     
 74         /*从当月二号开始增加分区*/
 75         WHILE v_begin_date <= v_end_date
 76         LOOP
 77             l_sql := 'ALTER TABLE ' || v_target_table_name || ' ADD PARTITION P' || v_begin_date ||
 78                      ' values less than (' ||
 79                      to_char(TO_DATE(v_begin_date, 'yyyymmdd') + 1, 'YYYYMMDD') || ')';
 80         
 81             EXECUTE IMMEDIATE l_sql;
 82         
 83             /*while本来有这个功能,保险功能*/
 84             IF v_begin_date = v_end_date
 85             THEN
 86                 EXIT;
 87             END IF;
 88         
 89             /*循环增加1天*/
 90             v_begin_date := to_number(to_char(TO_DATE(v_begin_date, 'yyyymmdd') + 1, 'YYYYMMDD'));
 91         
 92         END LOOP;
 93     
 94     EXCEPTION
 95         WHEN OTHERS THEN
 96             /*如果不存在DATE_ID字段的，则不建分区*/
 97             l_sql := 'create table ' || v_target_table_name || ' as 
 98                       select * from ' || i_table_name || ' where 1 = 2';
 99             EXECUTE IMMEDIATE l_sql;
100     END;
101 
102 END;
103 /

 

posted @ 2013-05-20 11:02  菜鸟MM  阅读(178)  评论(0)    收藏  举报