2019-06-05 1140 星期三
带包名的存储过程
数据库：oracle
0.使用到的表
-- 2019-06-04 1621 星期二 执行存储过程日志表
--存储过程表-ps：可以不用创建,没有使用到
drop table log_process;
create table log_process(
process_row_count number,
step_id varchar(32),
process_id varchar(32)
);
--执行存储过程日志表
drop table log_step;
create table log_step(
process_id varchar2(32),--存储过程编码
step_id varchar2(50),--步骤号
process_date date,
process_type varchar2(50),--类型
process_name varchar2(50) ,--名字
data_date varchar2(30),--数据日期
status varchar2(50),--状态
hour_id varchar2(50),
process_row_count number,--受影响的行
error_message varchar2(4000),--错误信息
start_time date,--开始时间
end_time date,--结束时间
str_sql varchar2(4000),
str_sql2 varchar2(4000),
str_sql3 varchar2(4000),
str_sql4 varchar2(4000),
str_sql5 varchar2(4000),
str_sql6 varchar2(4000)
);
-- Create table
create table T_PERSON
(
ID VARCHAR2(32) not null,
NAME VARCHAR2(32),
AGE INTEGER,
SEX VARCHAR2(10),
BIRTHDAY DATE,
CREATE_DATE DATE,
CREATE_BY VARCHAR2(32),
UPDATE_DATE DATE,
UPDATE_BY VARCHAR2(32),
SCORES NUMBER
);
-- Add comments to the table
comment on table T_PERSON
is '测试表-验证是否连接orcle';
-- Create/Recreate primary, unique and foreign key constraints
alter table T_PERSON
add primary key (ID);
1.包头定义
CREATE OR REPLACE PACKAGE PKG_TEST IS
LOGGED_EXCEPTION EXCEPTION;
PROCEDURE procedure_test(p_user_id in varchar2,
p_result_code out number ,/*返回码：0-成功 1-失败*/
p_result_msg out varchar2 /*返回错误信息*/ );
PROCEDURE procedure_add_data(p_user_id IN varchar2,
p_result_code OUT number ,/*返回码：0-成功 1-失败*/
p_result_msg OUT varchar2 /*返回错误信息*/);
END ;
2.包体内容
CREATE OR REPLACE PACKAGE BODY PKG_TEST IS
/***********************************************************************************************
/* Name: PKG_TEST */
/* Description: A set procedures will be used by other InFuse stored packages and procedures*/
/* */
/* Version: 1.0.0 */
/* DATE: 2016-08-08 */
/* MODIFIER: xxxxxx */
/**********************************************************************************************/
--------------------------日志初始化,begin--------------------------
procedure Log_Step_Initial(
p_process_id varchar2 ,/*执行的存储过程编号*/
p_process_type varchar2,/*执行的存储过程类型*/
p_process_name varchar2 ,/*执行的存储过程名字*/
p_data_date varchar2 ,/*数据日期?*/
p_hour_id varchar2 ,/*不知道有什么用*/
p_str_sql varchar2 ,/*执行的存储sql*/
p_step_id_out out varchar2 /*返回出去,步骤号*/
) is pragma autonomous_transaction;
p_step_id varchar2(32);
p_str_sql1 varchar2(4000);
p_str_sql2 varchar2(4000);
p_str_sql3 varchar2(4000);
p_str_sql4 varchar2(4000);
p_str_sql5 varchar2(4000);
p_str_sql6 varchar2(4000);
begin
p_str_sql1 := substr(p_str_sql,1,2000);
p_str_sql2 := substr(p_str_sql,2001,2000);
p_str_sql3 := substr(p_str_sql,4001,2000);
p_str_sql4 := substr(p_str_sql,6001,2000);
p_str_sql5 := substr(p_str_sql,8001,2000);
p_str_sql6 := substr(p_str_sql,10001,2000);
select sys_guid() 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(
p_process_id, p_step_id, trunc(sysdate,'DD'), p_process_type ,
p_process_name, p_data_date, 'N', p_hour_id,
p_str_sql1, p_str_sql2, p_str_sql3, p_str_sql4, p_str_sql5, p_str_sql6
);
commit;
p_step_id_out := p_step_id;
end Log_Step_Initial;
--------------------------日志初始化,end--------------------------
--------------------------日志-开始,begin--------------------------
procedure Log_Step_Start(
p_process_id in varchar2,/*执行的存储过程编号*/
p_step_id in varchar2 /*步骤号 */
)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 = p_process_id and t.step_id = p_step_id;
commit;
end Log_Step_Start;
--------------------------日志-开始,end--------------------------
--------------------------日志-失败,begin--------------------------
procedure Log_Step_End_Fail(
p_process_id in varchar2, /*执行的存储过程编号*/
p_step_id in varchar2,/*当前步骤*/
p_message 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 = p_message
where t.process_id = p_process_id and t.step_id = p_step_id;
commit;
end Log_Step_End_Fail;
--------------------------日志-失败,end--------------------------
--------------------------日志-成功,begin--------------------------
procedure Log_Step_End_Success(
p_process_id in varchar2,/*执行的存储过程编号*/
p_step_id in varchar2,/*当前步骤*/
p_record_count in integer:= 0 /*受影响的行数*/
) is pragma autonomous_transaction;
begin
update log_step t
set t.status = 'S',/*成功的标识*/
t.end_time = sysdate,
t.process_row_count = p_record_count
where t.process_id = p_process_id and t.step_id = p_step_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 = p_process_id and upper(str_sql) like '%INSERT%' )
)
where a.process_id = p_process_id;
commit;
*/
end Log_Step_End_Success;
--------------------------日志-成功,end--------------------------
--------------------------PROC_EXEC_SQL,end--------------------------
PROCEDURE PROC_EXEC_SQL(
p_process_id IN VARCHAR2, /*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 VARCHAR2(32) := '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 LOGGED_EXCEPTION;
END PROC_EXEC_SQL;
--------------------------PROC_EXEC_SQL,end--------------------------
--------------------------PROC_LOG_DEBUG,begin--------------------------
PROCEDURE PROC_LOG_DEBUG(
p_process_id IN VARCHAR2, /*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
step_id VARCHAR2(32) := '0';
BEGIN
/*initialization log*/
Log_Step_Initial(p_process_id, '', p_proc_name, '', '', p_debug_msg, step_id);
/*step start log*/
Log_Step_Start(p_process_id, step_id);
/*succeed log*/
Log_Step_End_Success(p_process_id, 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, step_id, p_result_msg);/*failed log*/
RAISE LOGGED_EXCEPTION;
END PROC_LOG_DEBUG;
--------------------------PROC_LOG_DEBUG,begin--------------------------
PROCEDURE procedure_date(p_user_id IN VARCHAR2,
p_result_code OUT number ,/*返回码：0-成功 1-失败*/
p_result_msg OUT varchar2 /*返回错误信息*/
)as
/*
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;
p_result_msg := 'success:'||p_user_id;
end procedure_date;
--------------------------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:接收中英文日历值*/
dyh varchar2(10) := '''';/*单引号*/
v_str varchar2(32767) := '';/*变量*/
v_str_return varchar2(32767) := '';/*变量*/
begin
p_result_code := 0;
v_str := dyh||'yyyy-mm-dd hh24:mi:ss.ff'||dyh;
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 := dyh||p_result_msg||dyh;
v_sql := 'select '||v_str||' from dual';
dbms_output.put_line(v_sql);
PROC_EXEC_SQL(p_process_id, v_sql, p_result_code, p_result_msg, p_proc_name);
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_add_data(
p_user_id IN varchar2,
p_result_code OUT number ,/*返回码：0-成功 1-失败*/
p_result_msg OUT varchar2 /*返回错误信息*/
)as
v_sql varchar2(32767) := '';/*SQL语句变量*/
p_process_id varchar2(200) := '10000010002';/*日志id*/
p_proc_name varchar2(200) := 'procedure_add_data';/*日志name:接收中英文日历值*/
dyh varchar2(10) := '''';/*单引号*/
v_str_return varchar2(32767) := '';/*变量*/
v_age number(3) := 0;/*变量：年龄*/
v_scores number(3) := 0;/*变量：分数*/
v_name varchar2(32) :='';/*变量：姓名*/
v_create_by varchar2(32) :='';/*变量：创建人*/
v_birthday varchar2(32767) := 'to_date('
||'to_char(sysdate,'||dyh||'yyyy-mm-dd'||dyh||')'
||','||dyh||'yyyy-mm-dd'||dyh||')';
begin
select dbms_random.value(0, 100) into v_age from dual;--随机0-100的数
select dbms_random.value(0, 100) into v_scores from dual;--随机0-100的数
select dbms_random.string('a',8) into v_name from dual;--生成由大小写字母混合出现的8位密码
p_result_code := 0;
v_create_by := dyh||'procedure_'||p_user_id||dyh;
v_sql := 'insert into t_person (
id, name, age,
scores, sex, birthday,
create_date, create_by, update_date,
update_by
) values (
sys_guid(), '||dyh||'张'||v_name||dyh||' , '||v_age||',
'||v_scores||', '||dyh||'男'||dyh|| ', '||v_birthday||',
sysdate, ' ||v_create_by||' ,sysdate ,
'||v_create_by|| '
)';
/* 执行sql如下:
insert into t_person (
id, name, age,
scores, sex, birthday,
create_date, create_by, update_date,
update_by
) values (
sys_guid(), '张fTpGqvKm' , 66,
17, '男', to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'),
sysdate, 'procedure_a1111' ,sysdate ,
'procedure_a1111'
)
*/
dbms_output.put_line(v_sql);
p_result_msg := '执行成功';
v_str_return := p_result_msg;
PROC_EXEC_SQL(p_process_id, v_sql, p_result_code, p_result_msg, p_proc_name);
p_result_msg := v_str_return;
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
/*释放资源,如游标,临时表;回滚事务*/
p_result_code := 1; /*传递给ETL_TOOL非0错误代码*/
p_result_msg := '[Error Code:' || to_char(SQLCODE) || '].SQLERRM:' || SQLERRM;
end procedure_add_data;
--------------------------PROCEDURE_TEST,end--------------------------
END;
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
