执行日志记录表

-- 创建序列
CREATE SEQUENCE Seq_su_ProcExecErrorInfo_ID
    START WITH 1 INCREMENT BY 1
    NOCYCLE NOCACHE;
/
-- 删除表
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE su_ProcExecErrorInfo';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
END;
/
-- 创建表
CREATE TABLE su_ProcExecErrorInfo
(
    ID        NUMBER NOT NULL PRIMARY KEY,
    ProcName  VARCHAR2(200),
    InParam   VARCHAR2(400),
    ErrorTime TIMESTAMP DEFAULT SYSTIMESTAMP,
    ErrorMsg  VARCHAR2(4000),
    ErrorLine VARCHAR2(4000)
);
/
-- 主键自增触发器
CREATE OR REPLACE TRIGGER TR_su_ProcExecErrorInfo_ID
    BEFORE INSERT
    ON su_ProcExecErrorInfo
    FOR EACH ROW
BEGIN
    :NEW.ID := Seq_su_ProcExecErrorInfo_ID.NEXTVAL;
END;
/

存储过程模板

CREATE OR REPLACE PROCEDURE (
    p_ID IN NUMBER,
    p_UserID IN NUMBER,
    p_Type IN NUMBER,
    p_RetVal OUT VARCHAR2
)
/*
    ========================================================================
    Stored Procedure Name:  
    Description:            
    Created Date:           
    Created By:             your_name
    Version:                1.0
    Modified Date:          2025/3/20 10:51
    Modified By:            you_name
    Modification Details:   New Created
    Input Parameters:       P_ID NUMBER 主键ID
                            p_UserID NUMBER 当前用户的ID
                            p_Type NUMBER 
    Output Parameters:      p_RetVal VARCHAR2 存储过程返回值
    Error Handling:         错误信息记录到表su_ProcExecErrorInfo中
    ========================================================================
*/
AS
    v_ErrMsg  VARCHAR2(4000);
    v_ErrLine VARCHAR(4000);
    v_Count   NUMBER := 0;
BEGIN
    p_RetVal := 0;

    -- 检查
    IF p_Type = 1 THEN
        BEGIN
            IF v_Count > 0 THEN
                p_RetVal := -1;
            END IF;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- 如果没有数据，v_Count 默认是 0
                v_Count := 0;
        END;
    END IF;

    -- 执行业务逻辑
    IF p_Type = 2 THEN

    END IF;

    -- 提交事务
    COMMIT;

-- 异常处理
EXCEPTION
    WHEN OTHERS THEN
        v_ErrMsg := REPLACE(REGEXP_SUBSTR(SQLERRM, ':\s*(.+)', 1, 1, NULL, 1), 'PL/SQL: ', '');
        v_ErrLine := REGEXP_SUBSTR(SYS.DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, '\d+$');
        -- 回滚事务
        ROLLBACK;
        -- 其他异常处理
        p_RetVal := '操作失败！详情如下：<br><br>1.错误代码' || v_ErrMsg || '<br>2.错误行号：' || v_ErrLine ||
                    '<br><br>请联系系统管理员或相关运维人员！';

        INSERT INTO su_ProcExecErrorInfo
        (
            ProcName, ErrorMsg, ErrorLine, InParam
        )
        VALUES
            (
                '', v_ErrMsg, v_ErrLine,
                'p_ID = ' || TO_CHAR(p_ID) || ', p_UserID = ' || TO_CHAR(p_UserID) || ', p_Type = ' || TO_CHAR(p_Type)
            );

        COMMIT;
END ;
/

创建表

CREATE SEQUENCE Seq_tableName_ID
    START WITH 1 INCREMENT BY 1
    NOCYCLE NOCACHE
/
CREATE TABLE tableName
(
    ID                   NUMBER PRIMARY KEY,
    
)
/

CREATE OR REPLACE TRIGGER TR_tableName_ID
    BEFORE INSERT
    ON tableName
    FOR EACH ROW
BEGIN
    :NEW.ID := Seq_tableName_ID.NEXTVAL;
END;