版本：Oracle 11.2.0.4.0
操作系统：linux redhat 6.4
前言：
以下是关于对存储过程记录日志的两种实现方式，第一是普通加载数据方式。第二种是用游标for循环加载数据。表结构中包含了Insert 及Upate 条数设定，例子中只做了Insert 操作。对于Meger Into 加载也可适用，但是只能统计对数据条数的影响。
表结构
日志表结构如下，其中ETL_DATE 是对输入时间参数进行记录。
数据构造
创建记录日志信息表：
-- Create table
create table SYS_SP_MONITOR_LOG
(
sp_proc_name VARCHAR2 (100 ),
start_date DATE ,
end_date DATE ,
etl_date DATE ,
sp_status VARCHAR2 (50 ),
sp_insert_num NUMBER ,
sp_update_num NUMBER ,
sp_subject VARCHAR2 (100 ),
sp_sub_subject VARCHAR2 (200 ),
sp_error_info VARCHAR2 (4000 )
)
tablespace USERS;
-- Add comments to the columns
comment on column SYS_SP_MONITOR_LOG.sp_proc_name
is '存储过程名称' ;
comment on column SYS_SP_MONITOR_LOG.start_date
is '程序开始日期' ;
comment on column SYS_SP_MONITOR_LOG.end_date
is '程序结束日期' ;
comment on column SYS_SP_MONITOR_LOG.etl_date
is '业务输入日期' ;
comment on column SYS_SP_MONITOR_LOG.sp_status
is '程序运行状态' ;
comment on column SYS_SP_MONITOR_LOG.sp_insert_num
is '程序插入条数' ;
comment on column SYS_SP_MONITOR_LOG.sp_update_num
is '程序条数更新' ;
comment on column SYS_SP_MONITOR_LOG.sp_subject
is '程序所属主题' ;
comment on column SYS_SP_MONITOR_LOG.sp_sub_subject
is '程序所属分析主题' ;
comment on column SYS_SP_MONITOR_LOG.sp_error_info
is '错误信息' ;
====================================================以下是构造加载数据表结构，及初始化==================================
DROP TABLE EMP CASCADE CONSTRAINTS PURGE;
DROP TABLE DEPT CASCADE CONSTRAINTS PURGE;
DROP TABLE AREA CASCADE CONSTRAINTS PURGE;
DROP TABLE COUNTY CASCADE CONSTRAINTS PURGE;
DROP TABLE SRC CASCADE CONSTRAINTS PURGE;
DROP TABLE EMP_DEPT_AREA_COUNTY CASCADE CONSTRAINTS PURGE ;
CREATE TABLE EMP (
EMPNO NUMBER( 8 ),
ENAME VARCHAR2( 20 ),
HIREDATE DATE,
SAL NUMBER( 7 ,2 ),
DEPTNO NUMBER( 6 ) );
CREATE TABLE DEPT (
DEPTNO NUMBER( 6 ),
DNAME VARCHAR2( 20 ),
DNAMEINFO VARCHAR2 (20 ),
AREANO NUMBER( 6 ));
CREATE TABLE AREA (
AREANO NUMBER( 6 ),
AREANAME VARCHAR2 (20 ),
AREAINFO VARCHAR2 (20 ),
COUNTYNO NUMBER (6 ));
CREATE TABLE COUNTY (
COUNTYNO NUMBER( 6 ),
COUNTYNAME VARCHAR2 (20 ),
COUNTYINFO VARCHAR2 (20 ));
ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (EMPNO);
ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY (DEPTNO);
ALTER TABLE AREA ADD CONSTRAINT AREA_PK PRIMARY KEY (AREANO);
ALTER TABLE COUNTY ADD CONSTRAINT COUNTY_PK PRIMARY KEY (COUNTYNO);
CREATE TABLE SRC ( X VARCHAR2( 10 ));
BEGIN
FOR I IN 1 .. 2000000 LOOP
INSERT INTO SRC VALUES ( 'x');
END LOOP ;
END;
/
COMMIT ;
--想插入多少行，就修改这个ROWNUM小于等于的值，在200000内
INSERT INTO EMP
SELECT ROWNUM ,
'Name'|| ROWNUM ,
SYSDATE+ ROWNUM /100 ,
FLOOR(DBMS_RANDOM.VALUE( 7500 ,10000 )),
FLOOR(DBMS_RANDOM.VALUE( 1 ,50 ))
FROM SRC
WHERE ROWNUM <= 100000 ;
--想插入多少行，就修改这个ROWNUM小于等于的值，在200000内
INSERT INTO DEPT
SELECT ROWNUM , 'Dname' ||ROWNUM , 'dnameinfo'|| ROWNUM ,FLOOR (DBMS_RANDOM.VALUE( 1, 20 ))
FROM SRC
WHERE ROWNUM <= 5000 ;
--想插入多少行，就修改这个ROWNUM小于等于的值，在200000内
INSERT INTO AREA
SELECT ROWNUM , 'area' ||ROWNUM , 'areainfo'|| ROWNUM ,FLOOR (DBMS_RANDOM.VALUE( 1, 10 ))
FROM SRC
WHERE ROWNUM <= 2000 ;
--想插入多少行，就修改这个ROWNUM小于等于的值，在200000内
INSERT INTO COUNTY
SELECT ROWNUM , 'county' || ROWNUM, 'countyinfo' ||ROWNUM
FROM SRC
WHERE ROWNUM <= 1000 ;
COMMIT ;
===========================================================创建目标表=================================================================
CREATE TABLE EMP_DEPT_AREA_COUNTY
AS
SELECT T1.*,
T2.DNAME,
T2.DNAMEINFO,
T2.AREANO,
T3.AREANAME,
T3.AREAINFO,
T3.COUNTYNO,
T4.COUNTYNAME,
T4.COUNTYINFO
FROM EMP T1, DEPT T2, AREA T3, COUNTY T4
WHERE T1.DEPTNO = T2.DEPTNO(+)
AND T2.AREANO = T3.AREANO(+)
AND T3.COUNTYNO = T4.COUNTYNO(+)
AND 1=2;
创建记录日志的程序包
CREATE OR REPLACE PACKAGE PKG_ETL_PROC_LOG_LET IS
-- AUTHOR : 要选就选S型
-- CREATED : 2016-07-04 13:36:02
-- PURPOSE : 实现对存储过程日志的记录(记录成功)
PROCEDURE SP_ETL_PROC_LOG_RECORD(V_PROC_NAME IN VARCHAR2 , --程序名称
V_START_DATE IN TIMESTAMP , --程序运行开始日期
V_END_DTTM IN TIMESTAMP , --程序运行结束日期
V_ETL_DATE IN TIMESTAMP , --输入ETL_DATE参数
V_INSERT_NUM IN NUMBER , --插入目标表条数
V_UPDATE_NUM IN NUMBER , --插入目标表条数
V_SUBJECT IN VARCHAR2 ,
V_SUB_SUBJECT IN VARCHAR2 , --所属子主题域
V_ERROR_INFO IN VARCHAR2 ); --所属子主题域
-- AUTHOR : 要选就选S型
-- CREATED : 2016-07-04 13:36:02
-- PURPOSE : 实现对存储过程日志的记录 (记录失败)
PROCEDURE SP_ETL_PROC_LOG_ABNORMITY(V_PROC_NAME IN VARCHAR2 , --程序名称
V_START_DATE IN TIMESTAMP , --程序运行开始日期
V_END_DTTM IN TIMESTAMP , --程序运行结束日期
V_ETL_DATE IN TIMESTAMP , --输入ETL_DATE参数
V_INSERT_NUM IN NUMBER , --插入目标表条数
V_UPDATE_NUM IN NUMBER , --插入目标表条数
V_SUBJECT IN VARCHAR2 ,
V_SUB_SUBJECT IN VARCHAR2 , --所属子主题域
V_ERROR_INFO IN VARCHAR2 ); --所属子主题域
END PKG_ETL_PROC_LOG_LET;
CREATE OR REPLACE PACKAGE BODY PKG_ETL_PROC_LOG_LET IS
-- AUTHOR : 要选就选S型
-- CREATED : 2016-07-04 13:36:02
-- PURPOSE : 实现对存储过程日志的记录(记录成功)
PROCEDURE SP_ETL_PROC_LOG_RECORD(V_PROC_NAME IN VARCHAR2 , --程序名称
V_START_DATE IN TIMESTAMP , --程序运行开始日期
V_END_DTTM IN TIMESTAMP , --程序运行结束日期
V_ETL_DATE IN TIMESTAMP , --输入ETL_DATE参数
V_INSERT_NUM IN NUMBER , --插入目标表条数
V_UPDATE_NUM IN NUMBER , --插入目标表条数
V_SUBJECT IN VARCHAR2 ,
V_SUB_SUBJECT IN VARCHAR2 , --所属子主题域
V_ERROR_INFO IN VARCHAR2 ) --所属子主题域
IS
BEGIN
INSERT INTO SYS_SP_MONITOR_LOG
(SP_PROC_NAME,
START_DATE,
END_DATE,
ETL_DATE,
SP_STATUS,
SP_INSERT_NUM,
SP_UPDATE_NUM,
SP_SUBJECT,
SP_SUB_SUBJECT,
SP_ERROR_INFO)
VALUES
(V_PROC_NAME,
V_START_DATE,
V_END_DTTM,
V_ETL_DATE,
'程序执行成功' ,
V_INSERT_NUM,
V_UPDATE_NUM,
V_SUBJECT,
V_SUB_SUBJECT,
V_ERROR_INFO);
COMMIT ;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
END SP_ETL_PROC_LOG_RECORD;
-- AUTHOR : 要选就选S型
-- CREATED : 2016-07-04 13:36:02
-- PURPOSE : 实现对存储过程日志的记录(记录成功)
PROCEDURE SP_ETL_PROC_LOG_ABNORMITY(V_PROC_NAME IN VARCHAR2 , --程序名称
V_START_DATE IN TIMESTAMP , --程序运行开始日期
V_END_DTTM IN TIMESTAMP , --程序运行结束日期
V_ETL_DATE IN TIMESTAMP , --输入ETL_DATE参数
V_INSERT_NUM IN NUMBER , --插入目标表条数
V_UPDATE_NUM IN NUMBER , --插入目标表条数
V_SUBJECT IN VARCHAR2 ,
V_SUB_SUBJECT IN VARCHAR2 , --所属子主题域
V_ERROR_INFO IN VARCHAR2 ) --所属子主题域
IS
BEGIN
INSERT INTO SYS_SP_MONITOR_LOG
(SP_PROC_NAME,
START_DATE,
END_DATE,
ETL_DATE,
SP_STATUS,
SP_INSERT_NUM,
SP_UPDATE_NUM,
SP_SUBJECT,
SP_SUB_SUBJECT,
SP_ERROR_INFO)
VALUES
(V_PROC_NAME,
V_START_DATE,
V_END_DTTM,
V_ETL_DATE,
'程序执行失败' ,
V_INSERT_NUM,
V_UPDATE_NUM,
V_SUBJECT,
V_SUB_SUBJECT,
V_ERROR_INFO);
COMMIT ;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
END SP_ETL_PROC_LOG_ABNORMITY;
END PKG_ETL_PROC_LOG_LET;
普通加载数据存储
CREATE OR REPLACE PROCEDURE SP_FCT_DEPT_EMP IS
V_START_DATE TIMESTAMP; --开始时间
V_END_DATE TIMESTAMP; --结束时间
V_INSERT_NUM NUMBER; --插入条数
V_UPDATE_NUM NUMBER; --更新条数
V_SP_NAME VARCHAR2(50) := 'SP_FCT_DEPT_EMP'; --存储过程名称
V_SQL_CODE VARCHAR2(50); --保存编码
V_SQL_ERRM VARCHAR2(4000); --保存信息
V_DATA_TIME DATE; --如有日期参数，可以记录
BEGIN
V_INSERT_NUM := 0;
V_UPDATE_NUM := 0;
V_START_DATE := SYSDATE;
EXECUTE IMMEDIATE 'TRUNCATE TABLE SCOTT.EMP_DEPT_AREA_COUNTY'; --清空目标表
INSERT INTO SCOTT.EMP_DEPT_AREA_COUNTY
select t1.*,
t2.dname,
t2.dnameinfo,
t2.areano,
t3.areaname,
t3.areainfo,
t3.countyno,
t4.countyname,
t4.countyinfo
from emp t1, dept t2, area t3, county t4
where t1.DEPTNO = t2.DEPTNO(+)
and t2.areano = t3.areano(+)
and t3.countyno = t4.countyno(+) ;
V_INSERT_NUM := SQL%ROWCOUNT;
V_END_DATE := SYSDATE;
V_UPDATE_NUM := SQL%ROWCOUNT;
/*执行成功记录日志 */
PKG_ETL_PROC_LOG_LET.SP_ETL_PROC_LOG_RECORD(V_PROC_NAME => V_SP_NAME,
V_START_DATE => V_START_DATE,
V_END_DTTM => V_END_DATE,
V_ETL_DATE => V_DATA_TIME,
V_INSERT_NUM => V_INSERT_NUM,
V_UPDATE_NUM => V_UPDATE_NUM,
V_SUBJECT => '测试主题',
V_SUB_SUBJECT => '日志记录',
V_ERROR_INFO => '');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_SQL_CODE := SQLCODE;
V_SQL_ERRM := SUBSTR(SQLERRM, 1, 40000);
V_END_DATE := SYSDATE;
/*执行失败记录日志 */
PKG_ETL_PROC_LOG_LET.SP_ETL_PROC_LOG_ABNORMITY(V_SP_NAME,
V_START_DATE,
V_END_DATE,
V_DATA_TIME,
V_INSERT_NUM,
V_UPDATE_NUM,
'测试主题',
'日志记录',
'ERROR CODE ' ||
V_SQL_CODE || ': ' ||
V_SQL_ERRM);
END SP_FCT_DEPT_EMP;
游标FOR循环加载数据
CREATE OR REPLACE PROCEDURE SP_FCT_DEPT_EMP_Y IS
V_START_DATE TIMESTAMP ; --开始时间
V_END_DATE TIMESTAMP ; --结束时间
V_INSERT_NUM NUMBER ; --插入条数
V_UPDATE_NUM NUMBER ; --更新条数
V_SP_NAME VARCHAR2 (50 ) := 'SP_FCT_DEPT_EMP_Y'; --存储过程名称
V_SQL_CODE VARCHAR2 (50 ); --保存编码
V_SQL_ERRM VARCHAR2 (4000 ); --保存信息
V_DATA_TIME DATE ; --如有日期参数，可以记录
BEGIN
V_INSERT_NUM := 0 ;
V_UPDATE_NUM := 0 ;
V_START_DATE := SYSDATE ;
EXECUTE IMMEDIATE 'TRUNCATE TABLE SCOTT.EMP_DEPT_AREA_COUNTY' ; --清空目标表
DECLARE
CURSOR EMP_CUR IS
SELECT EMPNO,
ENAME,
HIREDATE,
SAL,
T2.DEPTNO,
DNAME,
DNAMEINFO,
T3.AREANO,
AREANAME,
AREAINFO,
T4.COUNTYNO,
COUNTYNAME,
COUNTYINFO
FROM EMP T1, DEPT T2, AREA T3, COUNTY T4
WHERE T1.DEPTNO = T2.DEPTNO(+)
AND T2.AREANO = T3.AREANO(+)
AND T3.COUNTYNO = T4.COUNTYNO(+);
BEGIN
FOR EMP_ROW IN EMP_CUR LOOP
INSERT INTO EMP_DEPT_AREA_COUNTY
VALUES
(EMP_ROW.EMPNO,
EMP_ROW.ENAME,
EMP_ROW.HIREDATE,
EMP_ROW.SAL,
EMP_ROW.DEPTNO,
EMP_ROW.DNAME,
EMP_ROW.DNAMEINFO,
EMP_ROW.AREANO,
EMP_ROW.AREANAME,
EMP_ROW.AREAINFO,
EMP_ROW.COUNTYNO,
EMP_ROW.COUNTYNAME,
EMP_ROW.COUNTYINFO);
V_INSERT_NUM := V_INSERT_NUM + 1 ; --循环记录日志条数
END LOOP ;
COMMIT ;
END;
V_END_DATE := SYSDATE ;
V_UPDATE_NUM := SQL %ROWCOUNT ;
/*执行成功记录日志 */
PKG_ETL_PROC_LOG_LET.SP_ETL_PROC_LOG_RECORD(V_PROC_NAME => V_SP_NAME,
V_START_DATE => V_START_DATE,
V_END_DTTM => V_END_DATE,
V_ETL_DATE => V_DATA_TIME,
V_INSERT_NUM => V_INSERT_NUM,
V_UPDATE_NUM => V_UPDATE_NUM,
V_SUBJECT => '测试主题' ,
V_SUB_SUBJECT => '日志记录' ,
V_ERROR_INFO => '' );
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
V_SQL_CODE := SQLCODE ;
V_SQL_ERRM := SUBSTR( SQLERRM , 1 , 40000);
V_END_DATE := SYSDATE ;
/*执行失败记录日志 */
PKG_ETL_PROC_LOG_LET.SP_ETL_PROC_LOG_ABNORMITY(V_SP_NAME,
V_START_DATE,
V_END_DATE,
V_DATA_TIME,
V_INSERT_NUM,
V_UPDATE_NUM,
'测试主题' ,
'日志记录' ,
'ERROR CODE ' ||
V_SQL_CODE || ': ' ||
V_SQL_ERRM);
END SP_FCT_DEPT_EMP_Y;
调度存储
begin
-- Call the procedure
sp_fct_dept_emp_y;
sp_fct_dept_emp;
end;
查看日志
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。
