达梦数据库自动创建和删除分区子分区和独立操作子分区两部分内容

CREATE OR REPLACE PROCEDURE "SP_CREATE_MAIN_SUB_PARTITION"("IN_TABLE_NAME" IN VARCHAR2(50),"IN_DATA_DT" IN VARCHAR2(50),"OUT_SUCCEED" OUT VARCHAR2(50),"V_MSG" OUT VARCHAR2(50))

AUTHID DEFINER

IS

v_job_name VARCHAR2(60) DEFAULT 'SP_CREATE_MAIN_SUB_PARTITION_';

v_point NUMBER := 0; -- 记录点

v_date DATE; -- 数据日期

v_start_tm TIMESTAMP DEFAULT CURRENT_TIMESTAMP; -- 开始执行时间

v_end_tm TIMESTAMP; -- 结束执行时间

v_sql VARCHAR2(200); -- 步骤描述

v_run_result VARCHAR2(20); -- 执行结果

v_rcount NUMBER; -- DML语句作用记录数

dm_error_code VARCHAR2(500) DEFAULT '00000'; -- 显示定义数据库变量SQLCODE

dm_error_msg VARCHAR2(500); -- 显示定义数据库变量SQLSTATE

v_pa_name VARCHAR2(30);

v_count NUMBER;

exe_sql VARCHAR2(1000);

v_subpart_name VARCHAR2(1000);

v_pre_seven_name VARCHAR2(100);

v_date_pre_seven date;

BEGIN

-- 开始处理数据

v_date := SYSDATE; -- 未定义in_data_dt参数，用当前日期

out_succeed := '0';

v_run_result := '执行成功';

v_pa_name := 'P' || IN_DATA_DT;

v_pre_seven_name := 'P' || TO_CHAR(TO_DATE(IN_DATA_DT)-7,'yyyymmdd');

-- 开始记录日志

v_point := v_point + 1;

v_sql := '存储过程开始';

INSERT INTO ETL_LOG_INFO(V_JOB_NAME, V_POINT, V_START_TM, V_END_TM, V_STEPDESC, V_RUN_RESULT, V_DATA_DT, V_MSG, V_RCOUNT)

VALUES(v_job_name, v_point, v_start_tm, CURRENT_TIMESTAMP, v_sql, v_run_result, v_date, v_msg, v_rcount);

COMMIT;

v_pa_name := 'P' || IN_DATA_DT;

-- 修正分区存在性检查（严格匹配用户表）

SELECT COUNT(1) INTO v_count

FROM user_tab_partitions

WHERE table_name = UPPER(in_table_name)

AND partition_name = v_pa_name;

IF v_count != 0 THEN

exe_sql := 'ALTER TABLE ' || in_table_name ||' DROP PARTITION ' || v_pa_name ;

EXECUTE IMMEDIATE exe_sql;

END IF;

-- 创建主分区（列表分区示例）

exe_sql := 'ALTER TABLE ' || in_table_name ||

' ADD PARTITION ' || v_pa_name ||

' VALUES (''' || IN_DATA_DT || ''')'||

'('||' SUBPARTITION ' ||v_pa_name || '_' ||'A'||

' VALUES (''' || 'A' || ''') ' || -- 子分区值

')';

-- 字符串值需加单引号

EXECUTE IMMEDIATE exe_sql;

FOR rs IN (SELECT DISTINCT i_dept_id FROM ORG_CODE )

LOOP

-- 生成合法子分区名称（确保30字节内）

v_subpart_name := SUBSTR(v_pa_name || '_' || rs.i_dept_id, 1, 30); -- 示例：p_2023Q1_dept1001

-- 构建动态SQL添加子分区

exe_sql := 'ALTER TABLE ' || in_table_name ||

' MODIFY PARTITION ' || v_pa_name || -- 指定已存在的主分区

' ADD SUBPARTITION ' || v_subpart_name ||

' VALUES (''' || rs.i_dept_id || ''')'; -- 子分区值

EXECUTE IMMEDIATE exe_sql;

END LOOP;

SELECT COUNT(1) INTO v_count

FROM user_tab_partitions

WHERE table_name = UPPER(in_table_name)

AND partition_name = v_pre_seven_name;

if v_count != 0 then

if(v_date_pre_seven>=LAST_DAY(ADD_MONTHS(v_date_pre_seven,-1)) -INTERVAL '2' DAY

and v_date_pre_seven < LAST_DAY(ADD_MONTHS(v_date_pre_seven,-1)) +INTERVAL '2' DAY)

then

null;

ELSE

exe_sql := 'ALTER TABLE ' || in_table_name ||' DROP PARTITION ' || v_pre_seven_name ;

EXECUTE IMMEDIATE exe_sql;

end if;

end if ;

/***************************本脚本运行成功,写相应的日志***************************/

v_start_tm := CURRENT_TIMESTAMP;

v_point := v_point + 1 ;

v_sql := '存储过程结束' ;

INSERT INTO ETL_LOG_INFO(V_JOB_NAME,V_POINT,V_START_TM,V_END_TM,V_STEPDESC,V_RUN_RESULT,V_DATA_DT,V_MSG,V_RCOUNT)

VALUES(v_job_name,v_point,v_start_tm,CURRENT_TIMESTAMP,v_sql,v_run_result,v_date,v_msg,v_rcount);

COMMIT;

-- 异常处理

--1.定义针对SQL异常情况的句柄(EXIT方式).

--2.将出现SQL异常时在存储过程中的位置(V_POINT),位置描述(v_sql)，错误代码SQLCODE(V_MSG)记入表ETL_LOG_INFO中作调试用.

--3.调用RESIGNAL重新引发异常,跳出存储过程执行体,对引发SQL异常之前存储过程体中所完成的操作进行回滚.

EXCEPTION

WHEN OTHERS THEN

dm_error_code := SQLCODE;

dm_error_msg := SQLERRM;

out_succeed := '1';

v_run_result := '执行失败';

v_msg := 'EXECUTE PROCEDURE FAILED! REASON: ' || 'dm_error_code: ' || dm_error_code || ', dm_error_msg: ' || dm_error_msg || '.';

ROLLBACK;

INSERT INTO ETL_LOG_INFO (V_JOB_NAME, V_POINT, V_START_TM, V_END_TM, V_STEPDESC, V_RUN_RESULT, V_DATA_DT, V_MSG, V_RCOUNT)

VALUES (v_job_name, v_point, v_start_tm, SYSTIMESTAMP, v_sql, v_run_result, v_date, v_msg, v_point);

COMMIT; UPDATE SYS_RUN_RECORD_DTL

SET BATCH_STATUS=3;

COMMIT;

RAISE;

END SP_CREATE_MAIN_SUB_PARTITION;

==============================================

CREATE OR REPLACE PROCEDURE "SP_MODIFY_SUB_PARTITION"("IN_TABLE_NAME" IN VARCHAR2(32767),"IN_DATA_DT" IN VARCHAR2(32767),"IN_ORG_CODE" IN VARCHAR2(32767),"OUT_SUCCEED" OUT VARCHAR2(32767),"V_MSG" OUT VARCHAR2(32767))

AUTHID DEFINER

IS

/*开发日期：

作 者：

描 述：

*/

v_job_name VARCHAR2(60) DEFAULT 'SP_MODIFY_SUB_PARTITION';

v_point NUMBER := 0; -- 记录点

v_date DATE; -- 数据日期

v_start_tm TIMESTAMP DEFAULT CURRENT_TIMESTAMP; -- 开始执行时间

v_end_tm TIMESTAMP; -- 结束执行时间

v_sql VARCHAR2(200); -- 步骤描述

v_run_result VARCHAR2(200); -- 执行结果

v_rcount NUMBER; -- DML语句作用记录数

dm_error_code VARCHAR2(1500) ; -- 显示定义数据库变量SQLCODE

dm_error_msg VARCHAR2(500); -- 显示定义数据库变量SQLSTATE

v_pa_name VARCHAR2(100);

v_count NUMBER;

exe_sql VARCHAR2(1000);

BEGIN

-- 开始处理数据

v_date := SYSDATE; -- 未定义in_data_dt参数，用当前日期

out_succeed := '0';

v_run_result := '执行成功';

v_count:='0';

-- 开始记录日志

v_point := v_point + 1;

v_sql := '存储过程开始';

INSERT INTO ETL_LOG_INFO(V_JOB_NAME, V_POINT, V_START_TM, V_END_TM, V_STEPDESC, V_RUN_RESULT, V_DATA_DT, V_MSG, V_RCOUNT)

VALUES(v_job_name, v_point, v_start_tm, CURRENT_TIMESTAMP, v_sql, v_run_result, v_date, v_msg, v_rcount);

COMMIT;

v_pa_name := 'P' || IN_DATA_DT;

SELECT COUNT(1) INTO v_count

FROM ALL_TAB_PARTITIONS

WHERE TABLE_OWNER = USER

AND TABLE_NAME = upper(in_table_name)

AND PARTITION_NAME = v_pa_name;

IF v_count = 0 THEN

exe_sql := 'ALTER TABLE '|| in_table_name||' ADD PARTITION ' || v_pa_name ||' VALUES ('||IN_DATA_DT ||' ( SUBPARTITION '||v_pa_name||'_'||in_org_code||' VALUES ('''||in_org_code||''') )' ;

EXECUTE IMMEDIATE exe_sql;

ELSE

-----------新增

v_count:=0;

SELECT COUNT(1) INTO v_count

FROM ALL_TAB_SUBPARTITIONS

WHERE TABLE_OWNER = USER

AND TABLE_NAME = upper(in_table_name)

AND PARTITION_NAME = v_pa_name;

if v_count=1 then

exe_sql := 'ALTER TABLE ' || in_table_name ||' MODIFY PARTITION ' || v_pa_name

||' ADD SUBPARTITION '||v_pa_name||'_0 VALUES (0)' ;

EXECUTE IMMEDIATE exe_sql;

end if;

-------------

v_count:=0;

SELECT COUNT(1) INTO v_count

FROM ALL_TAB_SUBPARTITIONS

WHERE TABLE_OWNER = USER

AND TABLE_NAME = upper(in_table_name)

AND PARTITION_NAME = v_pa_name

AND SUBPARTITION_NAME=v_pa_name ||'_'||in_org_code;

IF v_count > 0 THEN

exe_sql := 'ALTER TABLE ' || in_table_name ||' DROP SUBPARTITION ' || v_pa_name ||'_'||in_org_code;

EXECUTE IMMEDIATE exe_sql;

END IF;

exe_sql := 'ALTER TABLE ' || in_table_name ||' MODIFY PARTITION ' || v_pa_name

||' ADD SUBPARTITION '||v_pa_name||'_'||in_org_code||' VALUES ('''||in_org_code||''')' ;

EXECUTE IMMEDIATE exe_sql;

END IF;

/***************************本脚本运行成功,写相应的日志***************************/

v_start_tm := CURRENT_TIMESTAMP;

v_point := v_point + 1 ;

v_sql := '存储过程结束' ;

INSERT INTO ETL_LOG_INFO(V_JOB_NAME,V_POINT,V_START_TM,V_END_TM,V_STEPDESC,V_RUN_RESULT,V_DATA_DT,V_MSG,V_RCOUNT)VALUES(v_job_name,v_point,v_start_tm,CURRENT_TIMESTAMP,v_sql,v_run_result,v_date,v_msg,v_rcount);

COMMIT;

-- 异常处理

--1.定义针对SQL异常情况的句柄(EXIT方式).

--2.将出现SQL异常时在存储过程中的位置(V_POINT),位置描述(v_sql)，错误代码SQLCODE(V_MSG)记入表ETL_LOG_INFO中作调试用.

--3.调用RESIGNAL重新引发异常,跳出存储过程执行体,对引发SQL异常之前存储过程体中所完成的操作进行回滚.

EXCEPTION

WHEN OTHERS THEN

dm_error_code := SQLCODE;

dm_error_msg := SQLERRM;

out_succeed := '1';

v_run_result := '执行失败';

v_msg := 'EXECUTE PROCEDURE FAILED! REASON: ' || 'dm_error_code: ' || dm_error_code || ', dm_error_msg: ' || dm_error_msg || '.';

ROLLBACK;

INSERT INTO ETL_LOG_INFO (V_JOB_NAME, V_POINT, V_START_TM, V_END_TM, V_STEPDESC, V_RUN_RESULT, V_DATA_DT, V_MSG, V_RCOUNT)

VALUES (v_job_name, v_point, v_start_tm, SYSTIMESTAMP, v_sql, v_run_result, v_date, v_msg, v_point);

COMMIT;

RAISE;

END SP_MODIFY_SUB_PARTITION;