create or replace package PKG_IPLAN_LOG is
-- Author : PSM-B506000000000000
-- Created : 2021/2/4 9:04:57
-- Purpose : 日志包
PROCEDURE write_task_log(i_module_name IN VARCHAR2,
i_task_name IN VARCHAR2,
i_status IN VARCHAR2,
i_msg IN VARCHAR2 DEFAULT NULL,
i_oflow_id IN VARCHAR2 DEFAULT NULL,
i_oflow_name IN VARCHAR2 DEFAULT NULL,
i_row_count IN NUMBER DEFAULT NULL,
i_sql_text IN VARCHAR2 DEFAULT NULL,
i_get_sql_id IN VARCHAR2 DEFAULT 'N');
PROCEDURE write_task_log_once(i_module_name IN VARCHAR2,
i_task_name IN VARCHAR2,
i_msg IN VARCHAR2,
i_oflow_id IN VARCHAR2 DEFAULT NULL,
i_oflow_name IN VARCHAR2 DEFAULT NULL,
i_sql_text IN VARCHAR2 DEFAULT NULL,
i_status IN VARCHAR2 DEFAULT 'Ok');
END pkg_iplan_log;
create or replace package body PKG_IPLAN_LOG is
TYPE type_session IS RECORD(
session_id NUMBER,
serial# NUMBER,
user_name VARCHAR2(100));
TYPE type_task_log IS RECORD(
log_id NUMBER,
start_time NUMBER);
TYPE type_tb_task_log IS TABLE OF type_task_log INDEX BY VARCHAR2(1000);
gv_session type_session;
gv_task_log type_tb_task_log;
PROCEDURE p_get_session_info IS
v_session_id NUMBER;
v_serial# NUMBER;
BEGIN
--获取会话信息
v_session_id := gv_session.session_id;
IF v_session_id IS NULL THEN
SELECT t.sid, t.serial#
INTO v_session_id, v_serial#
FROM sys.v_$session t
WHERE t.sid IN (SELECT t2.sid FROM sys.v_$mystat t2 WHERE rownum < 2);
gv_session.session_id := v_session_id;
gv_session.serial# := v_serial#;
END IF;
END;
PROCEDURE p_write_task_log(i_module_name IN VARCHAR2,
i_task_name IN VARCHAR2,
i_status IN VARCHAR2,
i_msg IN VARCHAR2,
i_oflow_id IN VARCHAR2 DEFAULT NULL,
i_oflow_name IN VARCHAR2 DEFAULT NULL,
i_row_count IN NUMBER DEFAULT NULL,
i_sql_text IN VARCHAR2 DEFAULT NULL,
i_get_sql_id IN VARCHAR2 DEFAULT 'N') IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_session_id NUMBER;
v_serial# NUMBER;
v_iplan_task_run_log iplan_task_run_log%ROWTYPE;
BEGIN
p_get_session_info;
v_session_id := gv_session.session_id;
v_serial# := gv_session.serial#;
IF i_status = 'Start' THEN
v_iplan_task_run_log.log_id := seq_iplan_task_run_log.nextval;
v_iplan_task_run_log.module_name := i_module_name;
v_iplan_task_run_log.task_name := i_task_name;
v_iplan_task_run_log.start_date := SYSDATE;
v_iplan_task_run_log.status := 'Running';
v_iplan_task_run_log.oflow_id := i_oflow_id;
v_iplan_task_run_log.oflow_name := i_oflow_name;
v_iplan_task_run_log.sql_text := i_sql_text;
v_iplan_task_run_log.session_id := v_session_id;
v_iplan_task_run_log.serial# := v_serial#;
gv_task_log(i_module_name || i_task_name).log_id := v_iplan_task_run_log.log_id;
gv_task_log(i_module_name || i_task_name).start_time := dbms_utility.get_time;
INSERT INTO iplan_task_run_log VALUES v_iplan_task_run_log;
ELSIF i_status = 'End' THEN
v_iplan_task_run_log.log_id := gv_task_log(i_module_name || i_task_name)
.log_id;
v_iplan_task_run_log.elapse_time := dbms_utility.get_time - gv_task_log(i_module_name || i_task_name)
.start_time;
v_iplan_task_run_log.end_date := SYSDATE;
v_iplan_task_run_log.status := 'Ok';
v_iplan_task_run_log.row_count := i_row_count;
v_iplan_task_run_log.msg := i_msg;
IF i_get_sql_id = 'Y' THEN
SELECT t.prev_sql_id
INTO v_iplan_task_run_log.sql_id
FROM sys.v_$session t
WHERE t.sid = gv_session.session_id
AND rownum < 2;
END IF;
UPDATE iplan_task_run_log t
SET t.end_date = v_iplan_task_run_log.end_date,
t.elapse_time = v_iplan_task_run_log.elapse_time,
t.status = v_iplan_task_run_log.status,
t.row_count = v_iplan_task_run_log.row_count,
t.msg = v_iplan_task_run_log.msg,
t.sql_id = v_iplan_task_run_log.sql_id
WHERE t.log_id = v_iplan_task_run_log.log_id;
ELSIF i_status = 'Failed' THEN
v_iplan_task_run_log.log_id := gv_task_log(i_module_name || i_task_name)
.log_id;
v_iplan_task_run_log.elapse_time := dbms_utility.get_time - gv_task_log(i_module_name || i_task_name)
.start_time;
v_iplan_task_run_log.end_date := SYSDATE;
v_iplan_task_run_log.status := 'Failed';
v_iplan_task_run_log.msg := i_msg;
UPDATE iplan_task_run_log t
SET t.end_date = v_iplan_task_run_log.end_date,
t.elapse_time = v_iplan_task_run_log.elapse_time,
t.status = v_iplan_task_run_log.status,
t.msg = v_iplan_task_run_log.msg
WHERE t.log_id = v_iplan_task_run_log.log_id;
END IF;
COMMIT;
END;
PROCEDURE p_write_task_log_once(i_module_name IN VARCHAR2,
i_task_name IN VARCHAR2,
i_msg IN VARCHAR2,
i_oflow_id IN VARCHAR2 DEFAULT NULL,
i_oflow_name IN VARCHAR2 DEFAULT NULL,
i_sql_text IN VARCHAR2 DEFAULT NULL,
i_status IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_session_id NUMBER;
v_serial# NUMBER;
v_iplan_task_run_log iplan_task_run_log%ROWTYPE;
BEGIN
p_get_session_info;
v_session_id := gv_session.session_id;
v_serial# := gv_session.serial#;
v_iplan_task_run_log.log_id := seq_iplan_task_run_log.nextval;
v_iplan_task_run_log.module_name := i_module_name;
v_iplan_task_run_log.task_name := i_task_name;
v_iplan_task_run_log.start_date := SYSDATE;
v_iplan_task_run_log.end_date := SYSDATE;
v_iplan_task_run_log.status := i_status;
v_iplan_task_run_log.oflow_id := i_oflow_id;
v_iplan_task_run_log.oflow_name := i_oflow_name;
v_iplan_task_run_log.sql_text := i_sql_text;
v_iplan_task_run_log.msg := i_msg;
v_iplan_task_run_log.session_id := v_session_id;
v_iplan_task_run_log.serial# := v_serial#;
INSERT INTO iplan_task_run_log VALUES v_iplan_task_run_log;
COMMIT;
END;
PROCEDURE write_task_log(i_module_name IN VARCHAR2,
i_task_name IN VARCHAR2,
i_status IN VARCHAR2,
i_msg IN VARCHAR2 DEFAULT NULL,
i_oflow_id IN VARCHAR2 DEFAULT NULL,
i_oflow_name IN VARCHAR2 DEFAULT NULL,
i_row_count IN NUMBER DEFAULT NULL,
i_sql_text IN VARCHAR2 DEFAULT NULL,
i_get_sql_id IN VARCHAR2 DEFAULT 'N') IS
BEGIN
p_write_task_log(i_module_name,
i_task_name,
i_status,
i_msg,
i_oflow_id,
i_oflow_name,
i_row_count,
i_sql_text,
i_get_sql_id);
END;
PROCEDURE write_task_log_once(i_module_name IN VARCHAR2,
i_task_name IN VARCHAR2,
i_msg IN VARCHAR2,
i_oflow_id IN VARCHAR2 DEFAULT NULL,
i_oflow_name IN VARCHAR2 DEFAULT NULL,
i_sql_text IN VARCHAR2 DEFAULT NULL,
i_status IN VARCHAR2 DEFAULT 'Ok') IS
BEGIN
p_write_task_log_once(i_module_name,
i_task_name,
i_msg,
i_oflow_id,
i_oflow_name,
i_sql_text,
i_status);
END;
end PKG_IPLAN_LOG;
查看8道真题和解析
哔哩哔哩公司福利 596人发布