内容纲要
概要描述
为什么要写存过日志？
- 及时发现问题，时效性
- 执行过程统计，包括执行效率，涉及数据量
- 更加直观，支持异常输出
详细说明
一. Quark自治事务记录日志
单独记录日志的方式
PROCEDURE INSERT_TABLE1()
IS
BEGIN
--插入表
INSERT INTO TABLE1(COLUMN1,COLUMN2) VALUES('VALUE1','VALUE2') ;
--插入记录,当然，也可以将插入日志表的记录写成存储过程，然后调用
INSERT INTO LOG_TABLE(operates,description,Operates_userid,Opra_table)
VALUES('operates','description'',Operates_userid','Opra_table');
END INSERT_TABLE1;涉及大量存储过程的经常插入、更新、删除的表，可以考虑为他们单独建立日志表:
1. 创建日志表
日志表的类型选择，可以综合以下因素来选择：
- 使用orc普通表，极易出现小文件问题，需定期整改小文件问题（对存储过程调度不影响，但是查询调度日志表有可能出问题）
- 使用orc事务表，请定期关注torc compact合并情况（包括是否进入了compact黑名单），谨防进入黑名单之后，该表delta版本过多导致查询时executorlost的情况（对存储过程调度不影响，但是查询调度日志表有可能出问题）
- 使用hyperbase/hyperdrive表，请定期关注表的健康状况，如有异常无法insert，会大范围影响存储过程作业。
DROP TABLE lkw.etl_job_log;
CREATE TABLE lkw.etl_job_log(
etl_id string DEFAULT NULL COMMENT ' ',
etl_system string DEFAULT NULL COMMENT ' ',
etl_job string DEFAULT NULL COMMENT ' ',
etl_date date DEFAULT NULL COMMENT ' ',
sys_date date DEFAULT NULL COMMENT ' ',
proc_name string DEFAULT NULL COMMENT ' ',
debug_level string DEFAULT NULL COMMENT ' ',
step_num double DEFAULT NULL COMMENT ' ',
starttime string DEFAULT NULL COMMENT ' ',
endtime string DEFAULT NULL COMMENT ' ',
estimate_time string DEFAULT NULL COMMENT ' ',
return_cd string DEFAULT NULL COMMENT ' ',
return_msg string DEFAULT NULL COMMENT ' '
) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler';2. 创建日志记录存储过程
CREATE OR REPLACE PROCEDURE lkw.SP_ETL_JOB_LOG(P_I_ETL_SYSTEM IN string,P_I_ETL_JOB IN string,P_I_ETL_DATE IN date,P_I_SYS_DATE IN date,P_I_PROC_NAME IN string,P_I_DEBUG_LEVEL IN string,P_I_STEP_NUM IN string,P_I_STARTTIME IN string,P_I_ENDTIME IN string,P_I_ESTIMATE_TIME IN string,P_I_RETURN_CD IN string,P_I_RETURN_MSG IN string)
IS
v_LOG lkw.ETL_JOB_LOG%ROWTYPE
ETL_ID STRING ;
BEGIN
set_env('plsql.catch.hive.exception','true') ; --支持异常处理
set_env('transaction.type','inceptor') ; --支持事务回滚
ETL_ID := uniq() ;
V_LOG.ETL_SYSTEM := P_I_ETL_SYSTEM ;
V_LOG.ETL_JOB := P_I_ETL_JOB ;
V_LOG.ETL_DATE := P_I_ETL_DATE ;
V_LOG.SYS_DATE := P_I_SYS_DATE ;
V_LOG.PROC_NAME := P_I_PROC_NAME ;
V_LOG.DEBUG_LEVEL := P_I_DEBUG_LEVEL ;
V_LOG.STEP_NUM := P_I_STEP_NUM ;
V_LOG.STARTTIME := P_I_STARTTIME ;
V_LOG.ENDTIME := P_I_ENDTIME ;
V_LOG.ESTIMATE_TIME := P_I_ESTIMATE_TIME ;
V_LOG.RETURN_CD := P_I_RETURN_CD ;
V_LOG.RETURN_MSG := P_I_RETURN_MSG ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss') ;
-------------------------------------------------------------------------------------------------
BEGIN TRANSACTION
insert into lkw.ETL_JOB_LOG VALUES
(ETL_ID,V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,
V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG) ;
COMMIT;
-------------------------------------------------------------------------------------------------
EXCEPTION
WHEN OTHERS THEN
V_LOG.RETURN_CD := SQLCODE ;
--SQLERRM建议substr截断，否则可能会因字段超长导致insert失败，常见于search表。
V_LOG.RETURN_MSG := SUBSTR(SQLERRM, 1, 500) ;
V_LOG.DEBUG_LEVEL := '2' ;
V_LOG.ENDTIME := SYSDATE ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss') ;
ROLLBACK ;
INSERT INTO lkw.ETL_JOB_LOG VALUES
(ETL_ID,V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,
V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG) ;
COMMIT;
END;
/3. 创建表操作存储过程
S01_PROC()
CREATE OR REPLACE PROCEDURE lkw.S01_PROC(P_I_DATE IN STRING) IS
--日志表的记录型变量
V_LOG LKW.ETL_JOB_LOG%ROWTYPE ;
--%ROWCOUNT，记录DML语句成功执行的条数
V_SQLCOUNT DECIMAL(22,0) ;
V_ETL_DATE DATE ;
V_MIN_DATE DATE ;
V_MAX_DATE DATE ;
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
--PLSQL中捕获Hive异常（HIVE_EXCEPTION/OTHERS）开关
set_env('plsql.catch.hive.exception','true') ; --支持异常处理
set_env('transaction.type','inceptor') ; --支持事务回滚
V_SQLCOUNT := 0 ;
V_ETL_DATE := TDH_TODATE(P_I_DATE, 'YYYYMMDD') ;
V_MIN_DATE := '19000101' ;
V_MAX_DATE := '30001231' ;
-- Variable for ETL_JOB_LOG
V_LOG.ETL_SYSTEM := 'ETL_SYSTEM01' ;
V_LOG.ETL_JOB := 'ETL_JOB01' ;
V_LOG.ETL_DATE := V_ETL_DATE ;
V_LOG.SYS_DATE := SYSTIMESTAMP ;
V_LOG.PROC_NAME := 'S01_PROC' ;
V_LOG.DEBUG_LEVEL := '' ;
V_LOG.STEP_NUM := 0 ;
V_LOG.STARTTIME := SYSTIMESTAMP ;
V_SQLCOUNT := SQL%ROWCOUNT ;
-- 当异常发生时，返回ERROR CODE
V_LOG.RETURN_CD := SQLCODE ;
V_LOG.ENDTIME := SYSTIMESTAMP ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss');
V_LOG.RETURN_MSG := '-------Start------- ' ;
LKW.SP_ETL_JOB_LOG(V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG);
--------------------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION;
V_LOG.STEP_NUM := 1 ;
V_LOG.STARTTIME := SYSTIMESTAMP ;
--truncate 不会记录SQL%ROWCOUNT
--EXECUTE IMMEDIATE 'TRUNCATE TABLE lkw.emp_like';
EXECUTE IMMEDIATE 'delete from lkw.emp_like';
V_SQLCOUNT := SQL%ROWCOUNT ;
V_LOG.RETURN_CD := SQLCODE ;
V_LOG.ENDTIME := SYSTIMESTAMP ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss');
V_LOG.RETURN_MSG := 'Delete ' || V_SQLCOUNT || 'rows.' ;
COMMIT ;
LKW.SP_ETL_JOB_LOG(V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG);
--------------------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION;
V_LOG.STEP_NUM := 2 ;
V_LOG.STARTTIME := SYSTIMESTAMP ;
INSERT INTO lkw.emp_like
( empno,ename,job,mgr,hiredate,sal,comm,deptno)
SELECT
empno,ename,job,mgr,hiredate,sal,comm,deptno FROM lkw.emp ;
V_SQLCOUNT := SQL%ROWCOUNT ;
V_LOG.RETURN_CD := SQLCODE ;
V_LOG.ENDTIME := SYSTIMESTAMP ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss');
V_LOG.RETURN_MSG := 'Insert ' || V_SQLCOUNT || 'rows.' ;
COMMIT ;
LKW.SP_ETL_JOB_LOG(V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG);
--------------------------------------------------------------------------------------------------------------------
EXCEPTION
WHEN OTHERS THEN
V_LOG.RETURN_CD := SQLCODE ;
--当异常发生时，返回ERROR MESSAGE
V_LOG.RETURN_MSG := SUBSTR(
, 1, 500) ;
V_LOG.DEBUG_LEVEL := '2' ;
V_LOG.ENDTIME := SYSTIMESTAMP ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss');
ROLLBACK ;
LKW.SP_ETL_JOB_LOG(V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG);
--------------------------------------------------------------------------------------------------------------------
END;
/S02_PROC()
CREATE OR REPLACE PROCEDURE lkw.S02_PROC(P_I_DATE IN STRING) IS
--日志表的记录型变量
V_LOG LKW.ETL_JOB_LOG%ROWTYPE ;
--%ROWCOUNT，记录DML语句成功执行的条数
V_SQLCOUNT DECIMAL(22,0) ;
V_ETL_DATE DATE ;
V_MIN_DATE DATE ;
V_MAX_DATE DATE ;
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
--PLSQL中捕获Hive异常（HIVE_EXCEPTION/OTHERS）开关
set_env('plsql.catch.hive.exception','true') ; --支持异常处理
set_env('transaction.type','inceptor') ; --支持事务回滚
V_SQLCOUNT := 0 ;
V_ETL_DATE := TDH_TODATE(P_I_DATE, 'YYYYMMDD') ;
V_MIN_DATE := '19000101' ;
V_MAX_DATE := '30001231' ;
-- Variable for ETL_JOB_LOG
V_LOG.ETL_SYSTEM := 'ETL_SYSTEM01' ;
V_LOG.ETL_JOB := 'ETL_JOB02' ;
V_LOG.ETL_DATE := V_ETL_DATE ;
V_LOG.SYS_DATE := SYSTIMESTAMP ;
V_LOG.PROC_NAME := 'S02_PROC' ;
V_LOG.DEBUG_LEVEL := '' ;
V_LOG.STEP_NUM := 0 ;
V_LOG.STARTTIME := SYSTIMESTAMP ;
V_SQLCOUNT := SQL%ROWCOUNT ;
-- 当异常发生时，返回ERROR CODE
V_LOG.RETURN_CD := SQLCODE ;
V_LOG.ENDTIME := SYSTIMESTAMP ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss');
V_LOG.RETURN_MSG := '-------Start------- ' ;
LKW.SP_ETL_JOB_LOG(V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG);
--------------------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION;
V_LOG.STEP_NUM := 1 ;
V_LOG.STARTTIME := SYSTIMESTAMP ;
--truncate 不会记录SQL%ROWCOUNT
--EXECUTE IMMEDIATE 'TRUNCATE TABLE lkw.emp_like';
EXECUTE IMMEDIATE 'update ta1 set age=22 where 1=1';
V_SQLCOUNT := SQL%ROWCOUNT ;
V_LOG.RETURN_CD := SQLCODE ;
V_LOG.ENDTIME := SYSTIMESTAMP ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss');
V_LOG.RETURN_MSG := 'Update ' || V_SQLCOUNT || 'rows.' ;
COMMIT ;
LKW.SP_ETL_JOB_LOG(V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG);
--------------------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION;
V_LOG.STEP_NUM := 2 ;
V_LOG.STARTTIME := SYSTIMESTAMP ;
INSERT INTO lkw.ta1 SELECT 3,'c',44 FROM system.dual;
V_SQLCOUNT := SQL%ROWCOUNT ;
V_LOG.RETURN_CD := SQLCODE ;
V_LOG.ENDTIME := SYSTIMESTAMP ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss');
V_LOG.RETURN_MSG := 'Insert ' || V_SQLCOUNT || 'rows.' ;
COMMIT ;
LKW.SP_ETL_JOB_LOG(V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG);
--------------------------------------------------------------------------------------------------------------------
EXCEPTION
WHEN OTHERS THEN
V_LOG.RETURN_CD := SQLCODE ;
--当异常发生时，返回ERROR MESSAGE
V_LOG.RETURN_MSG := SUBSTR(SQLERRM, 1, 500) ;
V_LOG.DEBUG_LEVEL := '2' ;
V_LOG.ENDTIME := SYSTIMESTAMP ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss');
ROLLBACK ;
LKW.SP_ETL_JOB_LOG(V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG);
--------------------------------------------------------------------------------------------------------------------
END;
/S03_PROC()
CREATE OR REPLACE PROCEDURE lkw.S03_PROC(P_I_DATE IN STRING) IS
--日志表的记录型变量
V_LOG LKW.ETL_JOB_LOG%ROWTYPE ;
--%ROWCOUNT，记录DML语句成功执行的条数
V_SQLCOUNT DECIMAL(22,0) ;
V_ETL_DATE DATE ;
V_MIN_DATE DATE ;
V_MAX_DATE DATE ;
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
--PLSQL中捕获Hive异常（HIVE_EXCEPTION/OTHERS）开关
set_env('plsql.catch.hive.exception','true') ; --支持异常处理
set_env('transaction.type','inceptor') ; --支持事务回滚
V_SQLCOUNT := 0 ;
V_ETL_DATE := TDH_TODATE(P_I_DATE, 'YYYYMMDD') ;
V_MIN_DATE := '19000101' ;
V_MAX_DATE := '30001231' ;
-- Variable for ETL_JOB_LOG
V_LOG.ETL_SYSTEM := 'ETL_SYSTEM01' ;
V_LOG.ETL_JOB := 'ETL_JOB03' ;
V_LOG.ETL_DATE := V_ETL_DATE ;
V_LOG.SYS_DATE := SYSTIMESTAMP ;
V_LOG.PROC_NAME := 'S03_PROC' ;
V_LOG.DEBUG_LEVEL := '' ;
V_LOG.STEP_NUM := 0 ;
V_LOG.STARTTIME := SYSTIMESTAMP ;
V_SQLCOUNT := SQL%ROWCOUNT ;
-- 当异常发生时，返回ERROR CODE
V_LOG.RETURN_CD := SQLCODE ;
V_LOG.ENDTIME := SYSTIMESTAMP ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss');
V_LOG.RETURN_MSG := '-------Start------- ' ;
LKW.SP_ETL_JOB_LOG(V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG);
--------------------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION;
V_LOG.STEP_NUM := 1 ;
V_LOG.STARTTIME := SYSTIMESTAMP ;
--truncate 不会记录SQL%ROWCOUNT
--EXECUTE IMMEDIATE 'TRUNCATE TABLE lkw.emp_like';
EXECUTE IMMEDIATE 'update ta111111111 set age=22 where 1=1';
V_SQLCOUNT := SQL%ROWCOUNT ;
V_LOG.RETURN_CD := SQLCODE ;
V_LOG.ENDTIME := SYSTIMESTAMP ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss');
V_LOG.RETURN_MSG := 'Update ' || V_SQLCOUNT || 'rows.' ;
COMMIT ;
LKW.SP_ETL_JOB_LOG(V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG);
--------------------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION;
V_LOG.STEP_NUM := 2 ;
V_LOG.STARTTIME := SYSTIMESTAMP ;
INSERT INTO lkw.ta1 SELECT 3,'c',44 FROM system.dual;
V_SQLCOUNT := SQL%ROWCOUNT ;
V_LOG.RETURN_CD := SQLCODE ;
V_LOG.ENDTIME := SYSTIMESTAMP ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss');
V_LOG.RETURN_MSG := 'Insert ' || V_SQLCOUNT || 'rows.' ;
COMMIT ;
LKW.SP_ETL_JOB_LOG(V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG);
--------------------------------------------------------------------------------------------------------------------
EXCEPTION
WHEN OTHERS THEN
V_LOG.RETURN_CD := SQLCODE ;
--当异常发生时，返回ERROR MESSAGE
V_LOG.RETURN_MSG := SUBSTR(SQLERRM, 1, 500) ;
V_LOG.DEBUG_LEVEL := '2' ;
V_LOG.ENDTIME := SYSTIMESTAMP ;
V_LOG.ESTIMATE_TIME := UNIX_TIMESTAMP(V_LOG.ENDTIME,'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(V_LOG.STARTTIME,'yyyy-MM-dd HH:mm:ss');
ROLLBACK ;
LKW.SP_ETL_JOB_LOG(V_LOG.ETL_SYSTEM,V_LOG.ETL_JOB,V_LOG.ETL_DATE,V_LOG.SYS_DATE,V_LOG.PROC_NAME,V_LOG.DEBUG_LEVEL,V_LOG.STEP_NUM,V_LOG.STARTTIME,V_LOG.ENDTIME,V_LOG.ESTIMATE_TIME,V_LOG.RETURN_CD,V_LOG.RETURN_MSG);
--------------------------------------------------------------------------------------------------------------------
END;
/4. 执行并查看日志
BEGIN
BEGIN TRANSACTION
lkw.S01_PROC('20190709')
lkw.S02_PROC('20190709')
lkw.S03_PROC('20190709')
COMMIT
END# SELECT t.proc_name,t.step_num,t.starttime,t.endtime,t.estimate_time,t.return_cd,t.return_msg FROM lkw.etl_job_log t ORDER BY starttime;
FAQ. ORACLE存储过程记录日志的几种方式：
1. dbms_output.put_line ★
oracle需要手动开启set serveroutput on
缺点：执行才会展示结果，无法保留，后期无法管理维护
2. 写日志表 ★★★
使用%ROWCOUNT隐式游标，以及SQLCODE,SQLERRM等预定义函数实现对DML操作的日志记录以及异常捕捉
缺点： 无法与主事务隔离
3. 自治事务写日志表 ★★★★★
与直接写日志表有所不同，防止主事务也一并被回滚，保证了主事务的完整性。
PRAGMA AUTONOMOUS_TRANSACTION
4. 开启触发器记录日志 ★★★
缺点： 1.尤其是行级触发器对每一行都会处理一个事务，大批量的DML操作性能影响较大
5. 开通审计日志 ★★
缺点：1. 数据库性能下降20%~30% 2.审计日志文件*.aud占用空间
支持sql语句/PLSQL块/存过
--开启审计功能，并重启数据库实例
oracle@database ~$ sqlplus / as sysdba
SQL> show parameter audit;
SQL> alter system set audit_sys_operations=TRUE scope=spfile;
SQL> alter system set audit_trail=db,extended scope=spfile;
SQL> shutdown immediate;
SQL> startup
SQL> audit delete on test by access;
SQL> audit insert on test by access;--对表做DML操作，检查审计日志 sql_text列
select t.*,rowid from test t;
delete from test t where t_id=104;
insert into test select 104,'xue',22,'M' from dual;
select t.* from dba_audit_trail t where username='LKW' and userhost='WORKGROUP\KEVIN' order by timestamp desc;
select * from SYS.AUD$ t where userid='LKW' and userhost='WORKGROUP\KEVIN' order by ntimestamp# desc;6. Utl_file工具包将日志写到本地 ★★★
缺点：只能在数据库服务器使用，需要创建目录的权限
--创建utl_file日志路径名（oracle所在服务器）
SQL> create or replace directory "background_dump_dest"as '/data/oracle/admin/orcl/utl_file';--给用户赋目录的读写权限
SQL> grant read on directory "background_dump_dest" to lkw;
SQL> grant write on directory "background_dump_dest" to lkw;--utl_file包授权，添加执行权限
SQL> grant execute on utl_file to lkw;--执行语句块
declare
OutputFile UTL_FILE.FILE_TYPE;
vFileName varchar2(100);
v_out number(3);
begin
select 'rfid_'||t.ename||'.log' into vFileName from emp t where t.empno=8000;
-- fopen方法找到文件，创建文件，给出写入规则（’a’没文件的话创建）
OutputFile := utl_file.fopen('background_dump_dest',vFileName, 'a');
-- put_line方法写入内容
UTL_FILE.put_line(OutputFile,'输出日志信息');
v_out:=1/0;
-- fclose方法关闭文件，结束写入
UTL_FILE.FCLOSE(OutputFile);
EXCEPTION
WHEN zero_divide THEN
IF utl_file.is_open(OutputFile) THEN
utl_file.PUT_LINE(OutputFile,'Exception: SQLCODE=' || SQLCODE || ' SQLERRM=' ||SQLERRM);
utl_file.fclose(OutputFile);
END IF;
end;##输出日志信息
# oracle@database /data/oracle/admin/orcl/utl_file$ cat rfid_lkw1.log
Exception: SQLCODE=-1476 SQLERRM=ORA-01476: divisor is equal to zero