CodePudding user response:
This want to write in a stored procedureCodePudding user response:
Who call process?Tell me about specific scenarios and specific needs
CodePudding user response:
We are written to the log in the processCodePudding user response:
The CREATE OR REPLACE PROCEDURE PROC_SAVELOG (PROGRAM_NAME VARCHAR2, process name-IS_SUCCEED VARCHAR2, success - whether Y | N
EXECUTE_MSG CLOB - process execution information
BEGIN_TIME TIMESTAMP, the start of the execution process of TIMESTAMP
) AS
V_PROGRAM_NAME VARCHAR2 (1000) :=SUBSTRB (PROGRAM_NAME, 1, 1000); Process name -
V_IS_SUCCEED CHAR (1) :=SUBSTR (IS_SUCCEED, 1, 1); - the process execution success said Y N
V_BEGIN_TIME TIMESTAMP:=BEGIN_TIME;
PRAGMA AUTONOMOUS_TRANSACTION; - log on autonomous transactions, do not affect the business logic transaction
/*
Call way:
1. Normal log: PROC_SAVELOG (V_PROC_NAME, 'Y', 'execution success,,,, V_BEGIN_TIME);
2. The abnormal log: PROC_SAVELOG (V_PROC_NAME, 'N', SQLERRM, V_BEGIN_TIME);
*/
The BEGIN
INSERT INTO PROGRAM_EXECUTE_LOG
(PROGRAM_NAME,
IS_SUCCEED,
LOG_DATE,
EXECUTE_MSG - has been changed to CLOB
TIME_CONSUMING,
EXECUTE_ORDER,
EXECUTE_BEGINTIME,
EXECUTE_ENDTIME)
VALUES
(V_PROGRAM_NAME,
V_IS_SUCCEED,
SYSDATE,
EXECUTE_MSG,
F_TIMESTAMP_DIFF (SYSTIMESTAMP, V_BEGIN_TIME),
PROGRAM_EXECUTE_LOG_SEQ NEXTVAL,
V_BEGIN_TIME,
SYSTIMESTAMP);
COMMIT;
The EXCEPTION
The WHEN OTHERS THEN
ROLLBACK;
END;
- Create table
The create table PROGRAM_EXECUTE_LOG
(
Program_name VARCHAR2 (1000),
Is_succeed CHAR (1),
Log_date DATE,
Execute_msg CLOB,
Time_consuming NUMBER,
Execute_order NUMBER,
Execute_begintime TIMESTAMP (6),
Execute_endtime TIMESTAMP (6),
Process_flag VARCHAR2 (10) the default 'N'
)
In tablespace YZB_DATA01
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64 k
Next 1 m
Minextents 1
The maxextents unlimited
);
- Add comments to the table
Comment on the table PROGRAM_EXECUTE_LOG
Is' process, function, trigger execution performance log;
- Add comments to the columns
Comment on the column PROGRAM_EXECUTE_LOG. Execute_order
Is a 'serial number program_pfmc_log_seq';
Comment on the column PROGRAM_EXECUTE_LOG. Process_flag
Is' identity (handled Y, untreated N) ';
The CREATE OR REPLACE PROCEDURE PROC_BG_CLEAR (ORG_CODE IN VARCHAR2,
TODAY IN the DATE,
The RESULT OUT VARCHAR2) AS
V_ORG_CODE VARCHAR2 (10) :=ORG_CODE;
V_TODAY DATE:=TODAY;
V_SETP INT:=1;
V_CONF_NUM INT.
V_NOTCONF_NUM INT.
V_COUNT INT.
V_ERR_MSG VARCHAR2 (1000);
V_BEGIN_TIME TIMESTAMP:=SYSTIMESTAMP;
V_PROCESS_ERPS VARCHAR2 (10);
V_CB_UPDATE_TO_0 VARCHAR2 (10) :='N'.
PROC_BG_CLEAR V_PROC_NAME VARCHAR2 (1000) :='/' | | V_ORG_CODE | | '/' | |
TO_CHAR (V_TODAY, 'YYYY - MM - DD HH24: MI: SS');
V_EXE_MSG CLOB.
/* to empty the working data [email protected] */
The BEGIN
RESULT:='OK';
DBMS_LOB. CREATETEMPORARY (V_EXE_MSG, TRUE); - initialize CLOB
The SELECT NVL (sum (decode (tc ONFIRM, 'Y', 1, 0)), 0),
NVL (sum (decode (tc ONFIRM, 'Y', 0, 1)), 0),
COUNT (1)
INTO V_CONF_NUM V_NOTCONF_NUM, V_COUNT
The FROM P_BG_WORK T
Where T.P RODUCT_DATE=V_TODAY
AND T.ORG _CODE=V_ORG_CODE;
IF V_COUNT=0 THEN
V_ERR_MSG:=to_char (V_TODAY, 'yyyy - mm - dd) | |' can delete data not found! ';
Goto PROCESS_ERRMSG;
Elsif V_CONF_NUM & gt; 0 then
V_ERR_MSG:=to_char (V_TODAY, 'yyyy - mm - dd) | |' has confirmed that cannot be deleted! ';
Goto PROCESS_ERRMSG;
end if;
The DELETE FROM P_BG_WORK T
Where T.P RODUCT_DATE=V_TODAY
AND T.ORG _CODE=V_ORG_CODE
AND tc ONFIRM='N';
- log processing
PROC_SAVELOG (V_PROC_NAME, 'Y', V_EXE_MSG V_BEGIN_TIME);
The RETURN;
- exception handling
& lt; & lt; PROCESS_ERRMSG & gt;>
PROC_PROCESS_ERRMSG (V_PROC_NAME V_EXE_MSG, V_ERR_MSG V_BEGIN_TIME, RESULT);
The EXCEPTION
The WHEN OTHERS THEN
V_ERR_MSG:=SUBSTR (SQLERRM, 1, 160);
DBMS_LOB. APPEND (V_EXE_MSG V_ERR_MSG);
PROC_SAVELOG (V_PROC_NAME, 'N', V_EXE_MSG, V_BEGIN_TIME);
RESULT:=V_ERR_MSG; - error number corresponding information
END;
CodePudding user response:
Himself in the process of logging, it is an example