第一步定义包体:主要是写方法定义(入参)
CREATE OR REPLACE PACKAGE PKG_TEST IS
MIDEA_LOGGED_EXCEPTION EXCEPTION;
procedure RECEIVE_ENGLISH_CALENDAR(p_calendar IN VARCHAR2 DEFAULT null,
p_user_id in varchar2 default '-1',
p_result_code OUT NUMBER /*返回码:0-成功 1-失败*/,
p_result_msg OUT VARCHAR2 /*返回错误信息*/);
PROCEDURE RECEIVE_ZH_EN_CALENDAR(p_calendar_zh IN VARCHAR2 DEFAULT null,
p_calendar_en IN VARCHAR2 DEFAULT null,
p_user_id in varchar2 default '-1',
p_result_code OUT NUMBER /*返回码:0-成功 1-失败*/,
p_result_msg OUT VARCHAR2 /*返回错误信息*/);
PROCEDURE PROCEDURE_TEST(p_user_id in varchar2,
p_result_code out number ,/*返回码:0-成功 1-失败*/
p_result_msg out varchar2 /*返回错误信息*/ );
PROCEDURE PROCEDURE_hidden_param(p_user_id in varchar2,
p_user_name in varchar2,
p_user_name_hidden in varchar2,
p_result_code out number ,/*返回码:0-成功 1-失败*/
p_result_msg out varchar2 /*返回错误信息*/ );
END ;
第二步写方法实现
CREATE OR REPLACE PACKAGE BODY PKG_TEST IS
/***********************************************************************************************
/* Name: PKG_TAX */
/* Description: A set procedures will be used by other InFuse stored packages and procedures*/
/* */
/* Version: 1.0.0 */
/* DATE: 2016-08-08 */
/* MODIFIER: wyz */
/**********************************************************************************************/
--------------------------Log_Step_Initial,begin--------------------------
procedure Log_Step_Initial(pProcess_id integer ,
pProcess_type varchar2,
pProcess_name varchar2 ,
pData_date varchar2 ,
pHour_id varchar2 ,
pStr_sql varchar2 ,
pStep_id out integer ) is PRAGMA AUTONOMOUS_TRANSACTION;
p_STEP_ID integer;
pStr_sql1 varchar2(4000);
pStr_sql2 varchar2(4000);
pStr_sql3 varchar2(4000);
pStr_sql4 varchar2(4000);
pStr_sql5 varchar2(4000);
pStr_sql6 varchar2(4000);
begin
pStr_sql1:=substr(pStr_sql,1,2000);
pStr_sql2:=substr(pStr_sql,2001,2000);
pStr_sql3:=substr(pStr_sql,4001,2000);
pStr_sql4:=substr(pStr_sql,6001,2000);
pStr_sql5:=substr(pStr_sql,8001,2000);
pStr_sql6:=substr(pStr_sql,10001,2000);
select Seq_Log_step.Nextval into p_STEP_ID from dual;
insert into log_step(process_id,step_id,process_date,process_type ,process_name ,
data_date ,status,hour_id ,
str_sql ,str_sql2,str_sql3,str_sql4 ,str_sql5,str_sql6 )
values(pProcess_id,p_STEP_ID,trunc(sysdate,'DD'),pProcess_type,pProcess_name,
pData_date,'N',pHour_id,
pStr_sql1,pStr_sql2,pStr_sql3,pStr_sql4,pStr_sql5,pStr_sql6);
commit;
pStep_id:= p_STEP_ID;
end Log_Step_Initial;
--------------------------Log_Step_Initial,end--------------------------
--------------------------Log_Step_Start,begin--------------------------
procedure Log_Step_Start(pProcess_id in integer, pStep_id in integer
)is PRAGMA AUTONOMOUS_TRANSACTION;
begin
update LOG_step t
set t.status = 'R',/*状态变为R*/
t.start_time = sysdate,/*开始时间*/
t.end_time = null,
t.process_row_count = 0,
t.error_message = null
where t.process_id = pProcess_id and t.step_id = pStep_id;
commit;
end Log_Step_Start;
--------------------------Log_Step_Start,end--------------------------
--------------------------Log_Step_End_Fail,begin--------------------------
procedure Log_Step_End_Fail(pProcess_id in integer, pStep_id in integer,
pMessage in varchar2 ) is PRAGMA AUTONOMOUS_TRANSACTION;
begin
update LOG_step t
set t.status = 'F',
t.end_time = sysdate,
t.process_row_count = 0,
t.error_message = pMessage
where t.process_id = pProcess_id and t.step_id = pStep_id ;
commit;
end Log_Step_End_Fail;
--------------------------Log_Step_End_Fail,end--------------------------
--------------------------Log_Step_End_Success,begin--------------------------
procedure Log_Step_End_Success(pProcess_id in integer, pStep_id in integer,pRecordCount in integer:=0) is PRAGMA AUTONOMOUS_TRANSACTION;
begin
update LOG_step t
set t.status = 'S',
t.end_time = sysdate,
t.process_row_count = pRecordCount
where t.process_id = pProcess_id and t.step_id = pStep_id ;
commit;
update log_process a
set a.process_row_count =
(SELECT process_row_count FROM LOG_STEP WHERE STEP_ID =
(select MAX(STEP_ID) from log_step a where a.process_id=pProcess_id
and upper(str_sql) like '%INSERT%' )
)
where a.process_id = pProcess_id;
commit;
end Log_Step_End_Success;
--------------------------Log_Step_End_Success,end--------------------------
--------------------------PROC_EXEC_SQL,end--------------------------
PROCEDURE PROC_EXEC_SQL
(
p_process_id IN NUMBER, /*ETL processID*/
p_sql IN VARCHAR2, /*execute SQL statement*/
p_result_code OUT NUMBER, /*result code, 0-succeed 1-failed*/
p_result_msg OUT VARCHAR2 , /*返回错误信息*/
p_proc_name in varchar2 DEFAULT '' /*存储过程名*/
)
/*
程序名称: PROC_EXEC_SQL
程序功能: 执行指定的SQL并记录日志
参数定义: p_process_id 任务ID
p_sql 待执行的SQL
p_result_code 返回的结果标识 0:成功 1:失败
p_result_msg 返回的错误信息
p_proc_name 存储过程名
调用举例: PROC_EXEC_SQL(220001,l_sql,:p_result_code,:p_result_msg,p_proc_name)
备 注:
版本记录:
版本号 日期 开发人员 修改内容
1.0 2016-8-1 wyz Created this procedure
*/
AS
l_effect_row_num NUMBER(22) := 0;/*操作记录条数*/
l_step_id NUMBER := 0;/*步骤*/
BEGIN
p_result_code := '0';
p_result_msg := '';
/*初始化日志*/
log_step_initial(p_process_id, '', p_proc_name, '', '', p_sql, l_step_id);
/*步骤开始日志*/
log_step_start(p_process_id, l_step_id);
----dbms_output.put_line(p_sql);
EXECUTE IMMEDIATE p_sql;
----dbms_output.put_line(p_sql);
l_effect_row_num := SQL%ROWCOUNT;
/*成功日志*/
log_step_end_success(p_process_id, l_step_id, l_effect_row_num);
RETURN;
EXCEPTION
WHEN OTHERS THEN
p_result_code := 1;
p_result_msg := '[Error Code:' || TO_CHAR(SQLCODE) || '].SQLERRM:' || SQLERRM;
/*失败日志*/
log_step_end_fail(p_process_id, l_step_id, p_result_msg);
RAISE MIDEA_LOGGED_EXCEPTION;
END PROC_EXEC_SQL;
--------------------------PROC_EXEC_SQL,end--------------------------
--------------------------PROC_LOG_DEBUG,begin--------------------------
PROCEDURE PROC_LOG_DEBUG
(
p_process_id IN NUMBER, /*ETL processID*/
p_debug_msg IN VARCHAR2, /*debug message*/
p_result_code OUT NUMBER, /*result code, 0-succeed 1-failed*/
p_result_msg OUT VARCHAR2, /*result message*/
p_proc_name in varchar2 DEFAULT '' /*存储过程名*/
)
/*
程序名称: PROC_LOG_DEBUG
程序功能: 记录调试日志
备 注:
版本记录:
版本号 日期 开发人员 修改内容
1.0 2016-8-1 wyz Created this procedure
*/
AS
l_step_id NUMBER := 0;
BEGIN
/*initialization log*/
log_step_initial(p_process_id, '', p_proc_name, '', '', p_debug_msg, l_step_id);
/*step start log*/
log_step_start(p_process_id, l_step_id);
/*succeed log*/
log_step_end_success(p_process_id, l_step_id, 0);
p_result_code := 0;
p_result_msg := '';
EXCEPTION
WHEN OTHERS THEN
p_result_code := 1;
p_result_msg := '[Error Code:' || TO_CHAR(SQLCODE) || '].SQLERRM:'
||SUBSTR(SQLERRM, 1, 200);
log_step_end_fail(p_process_id, l_step_id, p_result_msg);/*failed log*/
RAISE MIDEA_LOGGED_EXCEPTION;
END PROC_LOG_DEBUG;
--------------------------PROC_LOG_DEBUG,begin--------------------------
--------------------------RECEIVE_ENGLISH_CALENDAR,begin--------------------------
/**
*接收英文日历控件样式的定时执行任务参数
*2019-01-23
*/
PROCEDURE RECEIVE_ENGLISH_CALENDAR(p_calendar IN VARCHAR2 DEFAULT null,
p_user_id in varchar2 default '-1',
p_result_code OUT NUMBER /*返回码:0-成功 1-失败*/,
p_result_msg OUT VARCHAR2 /*返回错误信息*/)as
v_sql varchar2(32767) := '';/*SQL语句变量*/
p_process_id varchar2(200):= '10000010001';/*日志id*/
p_proc_name varchar2(200) := 'RECEIVE_ENGLISH_CALENDAR';/*日志name:接收英文日历值*/
begin
v_sql := 'select sys_guid() from dual';
p_result_code := 0;
p_result_msg := '英文calendar:{'||p_calendar ||'},用户id:{'||p_user_id||'}';
p_proc_name := p_result_msg;
PROC_EXEC_SQL(p_process_id, v_sql, p_result_code, p_result_msg, p_proc_name);
EXCEPTION
WHEN OTHERS THEN
/*释放资源,如游标,临时表;回滚事务*/
p_result_code := 1; /*传递给ETL_TOOL非0错误代码*/
p_result_msg := '[Error Code:' || to_char(SQLCODE) || '].SQLERRM:' || SQLERRM;
end RECEIVE_ENGLISH_CALENDAR;
--------------------------RECEIVE_ENGLISH_CALENDAR,end--------------------------
--------------------------RECEIVE_ZH_EN_CALENDAR,begin--------------------------
/**
*接收中英文日历控件样式的定时执行任务参数
*2019-01-23
*/
PROCEDURE RECEIVE_ZH_EN_CALENDAR(p_calendar_zh IN VARCHAR2 DEFAULT null,
p_calendar_en IN VARCHAR2 DEFAULT null,
p_user_id in varchar2 default '-1',
p_result_code OUT NUMBER /*返回码:0-成功 1-失败*/,
p_result_msg OUT VARCHAR2 /*返回错误信息*/)as
v_sql varchar2(32767) := '';/*SQL语句变量*/
p_process_id varchar2(200):= '10000010001';/*日志id*/
p_proc_name varchar2(200) := 'RECEIVE_ENGLISH_CALENDAR';/*日志name:接收中英文日历值*/
begin
v_sql := 'select sys_guid() from dual';
p_result_code := 0;
p_result_msg := '中文calendar:{'||p_calendar_zh ||'},'||
'英文calendar:{'||p_calendar_en ||'},'||
'用户id:{'||p_user_id||'}';
p_proc_name := p_result_msg;
PROC_EXEC_SQL(p_process_id, v_sql, p_result_code, p_result_msg, p_proc_name);
EXCEPTION
WHEN OTHERS THEN
/*释放资源,如游标,临时表;回滚事务*/
p_result_code := 1; /*传递给ETL_TOOL非0错误代码*/
p_result_msg := '[Error Code:' || to_char(SQLCODE) || '].SQLERRM:' || SQLERRM;
end RECEIVE_ZH_EN_CALENDAR;
--------------------------RECEIVE_ZH_EN_CALENDAR,end--------------------------
--------------------------PROCEDURE_TEST,begin--------------------------
PROCEDURE PROCEDURE_TEST(p_user_id in varchar2,
p_result_code out number ,/*返回码:0-成功 1-失败*/
p_result_msg out varchar2 /*返回错误信息*/ )as
v_sql varchar2(32767) := '';/*SQL语句变量*/
v_sysdate varchar2(100) :='';/*系统时间*/
p_process_id varchar2(200):= '10000010001';/*日志id*/
p_proc_name varchar2(200) := 'PROCEDURE_TEST';/*日志name:接收中英文日历值*/
dan_yin_hao varchar2(10):='''';/*单引号*/
v_str varchar2(32767):='';/*变量*/
v_str_return varchar2(32767):='';/*变量*/
/*select
to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') as dateStr ,--2019-02-14 17:08:53
to_char(systimestamp, 'yyyymmdd hh24:mi:ss.ff ') as dateStr2,--20190214 17:08:53.819515
to_char(systimestamp, 'yyyymmdd hh24:mi:ss.ff3') as dateStr3 --20190214 17:08:53.819
from dual;*/
begin
p_result_code := 0;
v_str := dan_yin_hao||'yyyymmdd hh24:mi:ss.ff'||dan_yin_hao;
v_sql := 'select to_char(systimestamp,'||v_str||') from dual';
EXECUTE IMMEDIATE v_sql INTO v_sysdate;
p_result_msg := '登录用户:' ||p_user_id|| '当前系统时间为:' || v_sysdate;
v_str_return := p_result_msg;
v_str := dan_yin_hao||p_result_msg||dan_yin_hao;
v_sql := 'select'||v_str||' from dual';
PROC_EXEC_SQL(p_process_id, v_sql, p_result_code, p_result_msg, p_result_msg);
p_result_msg := v_str_return;
EXCEPTION
WHEN OTHERS THEN
/*释放资源,如游标,临时表;回滚事务*/
p_result_code := 1; /*传递给ETL_TOOL非0错误代码*/
p_result_msg := '[Error Code:' || to_char(SQLCODE) || '].SQLERRM:' || SQLERRM;
end PROCEDURE_TEST;
--------------------------PROCEDURE_TEST,end--------------------------
--------------------------PROCEDURE_TEST,begin--------------------------
PROCEDURE PROCEDURE_hidden_param(p_user_id in varchar2,
p_user_name in varchar2,
p_user_name_hidden in varchar2,
p_result_code out number ,/*返回码:0-成功 1-失败*/
p_result_msg out varchar2 /*返回错误信息*/ )as
v_sql varchar2(32767) := '';/*SQL语句变量*/
v_sysdate varchar2(100) :='';/*系统时间*/
p_process_id varchar2(200):= '10000010001';/*日志id*/
p_proc_name varchar2(200) := 'PROCEDURE_TEST';/*日志name:接收中英文日历值*/
dan_yin_hao varchar2(10):='''';/*单引号*/
v_str varchar2(32767):='';/*变量*/
v_str_return varchar2(32767):='';/*变量*/
/*select
to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') as dateStr ,--2019-02-14 17:08:53
to_char(systimestamp, 'yyyymmdd hh24:mi:ss.ff ') as dateStr2,--20190214 17:08:53.819515
to_char(systimestamp, 'yyyymmdd hh24:mi:ss.ff3') as dateStr3 --20190214 17:08:53.819
from dual;*/
begin
p_result_code := 0;
v_str := dan_yin_hao||'yyyymmdd hh24:mi:ss.ff'||dan_yin_hao;
v_sql := 'select to_char(systimestamp,'||v_str||') from dual';
EXECUTE IMMEDIATE v_sql INTO v_sysdate;
p_result_msg := '登录用户:' ||p_user_id||
'用户名:'||p_user_name||
'隐藏用户名:'||p_user_name_hidden||
'当前系统时间为:' || v_sysdate;
v_str_return := p_result_msg;
v_str := dan_yin_hao||p_result_msg||dan_yin_hao;
v_sql := 'select'||v_str||' from dual';
PROC_EXEC_SQL(p_process_id, v_sql, p_result_code, p_result_msg, p_result_msg);
p_result_msg := v_str_return;
EXCEPTION
WHEN OTHERS THEN
/*释放资源,如游标,临时表;回滚事务*/
p_result_code := 1; /*传递给ETL_TOOL非0错误代码*/
p_result_msg := '[Error Code:' || to_char(SQLCODE) || '].SQLERRM:' || SQLERRM;
end PROCEDURE_hidden_param;
--------------------------PROCEDURE_TEST,end--------------------------
END;