达梦存储过程模板和达梦创建分区存储过程

CREATE TABLE "ETL_LOG_INFO"

(

"V_JOB_NAME" VARCHAR2(100),

"V_POINT" NUMBER(10,0),

"V_START_TM" TIMESTAMP(0),

"V_END_TM" TIMESTAMP(0),

"V_STEPDESC" VARCHAR2(200),

"V_RUN_RESULT" VARCHAR2(1500),

"V_DATA_DT" TIMESTAMP(0),

"V_MSG" CLOB,

"V_RCOUNT" NUMBER(11,0)) ;

--创建一个分区的存储过程

CREATE OR REPLACE PROCEDURE "SP_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_PARTITION_CREATE_PARENT';

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参数，用当前日期

v_date_pre_seven:=TO_DATE(IN_DATA_DT)-7;

out_succeed := '0';

v_run_result := '执行成功';

-- 开始记录日志

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;

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

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

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 || ''')';

-- 字符串值需加单引号

EXECUTE IMMEDIATE exe_sql;

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;

RAISE;

END SP_PARTITION;