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 /
