分区表触发器
##创建分区表分两步，第一步创建分区表的主分区以及子分区框架。
CREATE OR REPLACE PROCEDURE P_PARTITIONS_inital_table(V_PAR_DATE IN VARCHAR2) AS
–对CLD_SITE表字段platform_ident结果集进行查询
V_BEGIN_DATE DATE;
V_END_DATE DATE;
V_SQL VARCHAR2(1000);
min_time date;
max_time date;
max_num int;
tab_exist int;
sql1 varchar2 (2000);
sql2 varchar2 (2000);
BEGIN
–判断是否存在需要新建的表USER_FUND_CHG_DTL，没有继续，有则退出
select COUNT(*) into tab_exist from tab where tabtype=’TABLE’ AND UPPER(TNAME)=’USER_FUND_CHG_DTL_TEST’;
if (tab_exist > 0 ) then
DBMS_OUTPUT.PUT_LINE(‘ exists table USER_FUND_CHG_DTL,please check it!!!’ );
else
–避免buffer too small
DBMS_OUTPUT.ENABLE (buffer_size=>null);
–对分区的起始日期赋值
SELECT TRUNC(ADD_MONTHS(TO_DATE(V_PAR_DATE, ‘YYYYMMDD’), 0), ‘MM’)
INTO V_BEGIN_DATE
FROM DUAL;
–对分区的结束日期赋值
SELECT LAST_DAY(ADD_MONTHS(TO_DATE(V_PAR_DATE+1, ‘YYYYMMDD’), 2))
INTO V_END_DATE
FROM DUAL;
–创建原始分区表，且每个主分区只有一个子分区
SELECT ‘create table USER_FUND_CHG_DTL_TEST ‘ || ‘(‘ || ‘ ‘ ||
‘PK_SRC_ID INTEGER ,’ || ‘PK_SRC_NAME VARCHAR2(50) ,’ ||
‘RPT_DATE DATE ,’ || ‘PLATFORM_IDENT VARCHAR2(45) ,’ ||
‘USER_INFO_ID INTEGER , ‘ ||
‘USER_NAME VARCHAR2(50) ,’ ||
‘ORDER_NO VARCHAR2(100),’ ||
‘OPT_TYPE_CD VARCHAR2(30) ,’ ||
‘OPT_NAME VARCHAR2(200),’ ||
‘AMT NUMBER(22,2) ,’ ||
‘ACC_BEFORE_BAL NUMBER(22,2) ,’ ||
‘ACC_AFTER_BAL NUMBER(22,2) ,’ ||
‘ORDER_MAKE_DTT TIMESTAMP(0) ,’ ||
‘INS_DT TIMESTAMP(0) )’ ||
‘ partition by list (PLATFORM_IDENT)’ ||
‘ subpartition by range (RPT_DATE) (‘
into sql1 from dual;
SELECT wm_concat( ‘partition t_list_’ ||
PLATFORM_IDENT || ‘ values (”’ || PLATFORM_IDENT || ”’) ‘ || ‘ (‘ ||
‘ SUBPARTITION ‘ || PLATFORM_IDENT || ‘_’ ||
TO_CHAR(V_BEGIN_DATE, ‘YYYY_MM_DD’) || ‘ VALUES LESS THAN (TO_DATE(”’ ||
TO_CHAR(V_BEGIN_DATE + 1, ‘YYYYMMDD’) || ”’,”YYYYMMDD”)))’)||’);’
into sql2 FROM CLD_SITE ;
C_SQL := C_TBA;
— EXECUTE IMMEDIATE C_SQL;
–取出最大的max(rownum)
end if;
END P_PARTITIONS_inital_table;
CREATE OR REPLACE PROCEDURE P_PARTITIONS_ADD(V_PAR_DATE IN VARCHAR2) AS
–取含有子分区的数据表信息
CURSOR C_SUBPART_TABLES IS
SELECT DISTINCT TABLE_NAME,
PARTITION_NAME,
SUBSTR(SUBPARTITION_NAME,
1,
LENGTH(SUBPARTITION_NAME) – 10) AS SUBPARTITION_NAME
FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME IN (‘INT_BET_DETAIL’, ‘LOT_BET_DETAIL’);
–取只有一级分区的数据表信息
CURSOR C_PART_TABLES IS
SELECT DISTINCT TABLE_NAME,
SUBSTR(PARTITION_NAME, 1, LENGTH(PARTITION_NAME) – 10) AS PARTITION_NAME
FROM USER_TAB_PARTITIONS
WHERE SUBPARTITION_COUNT = 0
AND PARTITION_NAME NOT LIKE ‘%MAX%’
AND TABLE_NAME IN (‘INT_BET_DETAIL’, ‘LOT_BET_DETAIL’);
V_BEGIN_DATE DATE;
V_END_DATE DATE;
V_SQL VARCHAR2(1000);
V_SUBPART_TABLES C_SUBPART_TABLES%ROWTYPE;
V_PART_TABLES C_PART_TABLES%ROWTYPE;
V_IS_EXISTS INT := 0;
BEGIN
–1、对含有子分区的数据表添加子分区
FOR V_SUBPART_TABLES IN C_SUBPART_TABLES LOOP
–对分区的起始日期赋值
SELECT TRUNC(ADD_MONTHS(TO_DATE(V_PAR_DATE, ‘YYYYMMDD’), 1), ‘MM’)
INTO V_BEGIN_DATE
FROM DUAL;
–对分区的结束日期赋值
SELECT LAST_DAY(ADD_MONTHS(TO_DATE(V_PAR_DATE, ‘YYYYMMDD’), 1))
INTO V_END_DATE
FROM DUAL;
–循环加分区
WHILE V_BEGIN_DATE <= V_END_DATE LOOP
V_SQL := ‘ALTER TABLE ‘ || V_SUBPART_TABLES.TABLE_NAME ||
‘ MODIFY PARTITION ‘ || V_SUBPART_TABLES.PARTITION_NAME ||
‘ ADD SUBPARTITION ‘ || V_SUBPART_TABLES.SUBPARTITION_NAME ||
TO_CHAR(V_BEGIN_DATE, ‘YYYY_MM_DD’) ||
‘ VALUES LESS THAN (TO_DATE(”’ ||
TO_CHAR(V_BEGIN_DATE, ‘YYYYMMDD’) || ”’,”YYYYMMDD”))’;
V_IS_EXISTS := 0;
–判断是否子分区有存在
select count(1)
INTO V_IS_EXISTS
from USER_TAB_SUBPARTITIONS
where TABLE_NAME = V_SUBPART_TABLES.TABLE_NAME
and PARTITION_NAME = V_SUBPART_TABLES.PARTITION_NAME
and SUBPARTITION_NAME = V_SUBPART_TABLES.SUBPARTITION_NAME ||
TO_CHAR(V_BEGIN_DATE, ‘YYYY_MM_DD’);
–0为不存在
IF V_IS_EXISTS = 0 THEN
EXECUTE IMMEDIATE V_SQL;
P_LOG_BATCH_DETAIL(‘ALL’,V_PAR_DATE,’P_PARTITIONS_ADD’,V_SUBPART_TABLES.TABLE_NAME,
V_SUBPART_TABLES.SUBPARTITION_NAME ||TO_CHAR(V_BEGIN_DATE, ‘YYYY_MM_DD’),’子分区已增加：’ || V_SQL,’1′);
ELSE
P_LOG_BATCH_DETAIL(‘ALL’,V_PAR_DATE,’P_PARTITIONS_ADD’,V_SUBPART_TABLES.TABLE_NAME,
V_SUBPART_TABLES.SUBPARTITION_NAME ||TO_CHAR(V_BEGIN_DATE, ‘YYYY_MM_DD’),’子分区已存在：’ || V_SQL,’1′);
END IF;
–日期加一天
V_BEGIN_DATE := V_BEGIN_DATE + 1;
END LOOP;
END LOOP;
–2、对只有一级分区的数据表建分区
FOR V_PART_TABLES IN C_PART_TABLES LOOP
–对分区的起始日期赋值
SELECT TRUNC(ADD_MONTHS(TO_DATE(V_PAR_DATE, ‘YYYYMMDD’), 1), ‘MM’)
INTO V_BEGIN_DATE
FROM DUAL;
–对分区的结束日期赋值
SELECT LAST_DAY(ADD_MONTHS(TO_DATE(V_PAR_DATE, ‘YYYYMMDD’), 1))
INTO V_END_DATE
FROM DUAL;
–循环加分区
WHILE V_BEGIN_DATE <= V_END_DATE LOOP
V_SQL := ‘ALTER TABLE ‘ || V_PART_TABLES.TABLE_NAME ||
‘ ADD PARTITION ‘ || V_PART_TABLES.PARTITION_NAME ||
TO_CHAR(V_BEGIN_DATE, ‘YYYY_MM_DD’) ||
‘ VALUES LESS THAN (TO_DATE(”’ ||
TO_CHAR(V_BEGIN_DATE, ‘YYYYMMDD’) || ”’,”YYYYMMDD”))’;
–TO_DATE(‘ 2018-01-02 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
V_IS_EXISTS := 0;
–判断是否分区有存在
select count(1)
INTO V_IS_EXISTS
from user_tab_partitions
where table_name = V_PART_TABLES.TABLE_NAME
and PARTITION_NAME = V_PART_TABLES.PARTITION_NAME ||
TO_CHAR(V_BEGIN_DATE, ‘YYYY_MM_DD’);
–0为不存在
IF V_IS_EXISTS = 0 THEN
EXECUTE IMMEDIATE V_SQL;
P_LOG_BATCH_DETAIL(‘ALL’,V_PAR_DATE,’P_PARTITIONS_ADD’,V_PART_TABLES.TABLE_NAME,
V_PART_TABLES.PARTITION_NAME ||TO_CHAR(V_BEGIN_DATE, ‘YYYY_MM_DD’),’分区已增加：’ || V_SQL,’1′);
ELSE
P_LOG_BATCH_DETAIL(‘ALL’,V_PAR_DATE,’P_PARTITIONS_ADD’,V_PART_TABLES.TABLE_NAME,
V_PART_TABLES.PARTITION_NAME ||TO_CHAR(V_BEGIN_DATE, ‘YYYY_MM_DD’),’分区已存在：’ || V_SQL,’1′);
END IF;
–日期加一天
V_BEGIN_DATE := V_BEGIN_DATE + 1;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
P_LOG_BATCH_DETAIL(‘ALL’,V_PAR_DATE,’P_PARTITIONS_ADD’,”,”,’添加分区异常，ERROR_MSG:’ || SQLCODE || ‘—‘ || SQLERRM,’2’);
ROLLBACK;
END P_PARTITIONS_ADD;
CREATE OR REPLACE PROCEDURE P_PARTITIONS_inital_table(V_PAR_DATE IN VARCHAR2) AS
–对CLD_SITE表字段platform_ident结果集进行查询
V_BEGIN_DATE DATE;
V_END_DATE DATE;
V_SQL VARCHAR2(1000);
min_time date;
max_time date;
max_num int;
tab_exist int;
sql1 varchar2(2000);
sql2 varchar2(2000);
C_SQL varchar2(2000)
BEGIN
–判断是否存在需要新建的表USER_FUND_CHG_DTL，没有继续，有则退出
select COUNT(*) into tab_exist from tab where tabtype=’TABLE’ AND UPPER(TNAME)=’USER_FUND_CHG_DTL_TEST’;
if (tab_exist > 0 ) then
DBMS_OUTPUT.PUT_LINE(‘ exists table USER_FUND_CHG_DTL,please check it!!!’ );
else
–避免buffer too small
DBMS_OUTPUT.ENABLE (buffer_size=>null);
–对分区的起始日期赋值
SELECT TRUNC(ADD_MONTHS(TO_DATE(V_PAR_DATE, ‘YYYYMMDD’), 0), ‘MM’)
INTO V_BEGIN_DATE
FROM DUAL;
–对分区的结束日期赋值
SELECT LAST_DAY(ADD_MONTHS(TO_DATE(V_PAR_DATE+1, ‘YYYYMMDD’), 2))
INTO V_END_DATE
FROM DUAL;
–创建原始分区表，且每个主分区只有一个子分区
SELECT ‘create table USER_FUND_CHG_DTL_TEST ‘ || ‘(‘ || ‘ ‘ ||
‘PK_SRC_ID INTEGER ,’ || ‘PK_SRC_NAME VARCHAR2(50) ,’ ||
‘RPT_DATE DATE ,’ || ‘PLATFORM_IDENT VARCHAR2(45) ,’ ||
‘USER_INFO_ID INTEGER , ‘ ||
‘USER_NAME VARCHAR2(50) ,’ ||
‘ORDER_NO VARCHAR2(100),’ ||
‘OPT_TYPE_CD VARCHAR2(30) ,’ ||
‘OPT_NAME VARCHAR2(200),’ ||
‘AMT NUMBER(22,2) ,’ ||
‘ACC_BEFORE_BAL NUMBER(22,2) ,’ ||
‘ACC_AFTER_BAL NUMBER(22,2) ,’ ||
‘ORDER_MAKE_DTT TIMESTAMP(0) ,’ ||
‘INS_DT TIMESTAMP(0) )’ ||
‘ partition by list (PLATFORM_IDENT)’ ||
‘ subpartition by range (RPT_DATE) (‘
into sql1 from dual;
SELECT wm_concat( ‘partition t_list_’ ||
PLATFORM_IDENT || ‘ values (”’ || PLATFORM_IDENT || ”’) ‘ || ‘ (‘ ||
‘ SUBPARTITION ‘ || PLATFORM_IDENT || ‘_’ ||
TO_CHAR(V_BEGIN_DATE, ‘YYYY_MM_DD’) || ‘ VALUES LESS THAN (TO_DATE(”’ ||
TO_CHAR(V_BEGIN_DATE + 1, ‘YYYYMMDD’) || ”’,”YYYYMMDD”)))’)||’);’
into sql2 FROM CLD_SITE ;
— C_SQL := sql1||sql12 ;
— EXECUTE IMMEDIATE C_SQL;
–取出最大的max(rownum)
DBMS_OUTPUT.PUT_LINE(sql1||sql2);
end if;
END P_PARTITIONS_inital_table;