如何将Oracle硬编码存储过程改写为可通用共享的版本？

以下两种方案均兼容原有业务调用逻辑，无需修改旧代码，同时不会增加冗余入参，可快速满足多业务复用需求：

方案1：可选入参+动态SQL（灵活度最高，适合表结构不固定的场景） #

仅增加3个带默认值的可选入参，原有调用逻辑完全不变，新业务按需传入自定义表、列信息即可：

CREATE OR REPLACE PROCEDURE lob_append(
    p_id IN NUMBER,
    p_text IN VARCHAR2,
    -- 可选入参默认匹配原硬编码逻辑，旧调用无需感知
    p_table_name IN VARCHAR2 DEFAULT 'T',
    p_pk_col IN VARCHAR2 DEFAULT 'SEQ_NUM',
    p_clob_col IN VARCHAR2 DEFAULT 'C'
) AS
    l_clob CLOB;
    l_text VARCHAR2(32760);
    l_system_date_time VARCHAR2(50);
    -- 校验对象名防止SQL注入
    v_valid_table VARCHAR2(100) := DBMS_ASSERT.SQL_OBJECT_NAME(p_table_name);
    v_valid_pk_col VARCHAR2(100) := DBMS_ASSERT.SIMPLE_SQL_NAME(p_pk_col);
    v_valid_clob_col VARCHAR2(100) := DBMS_ASSERT.SIMPLE_SQL_NAME(p_clob_col);
BEGIN
    -- 动态拼接查询语句，主键值用绑定变量规避注入风险
    EXECUTE IMMEDIATE 
        'SELECT ' || v_valid_clob_col || ' FROM ' || v_valid_table || 
        ' WHERE ' || v_valid_pk_col || ' = :1 FOR UPDATE' 
        INTO l_clob USING p_id;

    SELECT TO_CHAR(SYSDATE, 'MMDDYYYY HH24:MI:SS') INTO l_system_date_time FROM dual;
    l_text := CHR(10) || p_text || CHR(10) || '['||l_system_date_time||']'||CHR(10);
    DBMS_LOB.WRITEAPPEND(l_clob, LENGTH(l_text), l_text);
END;
/

• 原有调用方式保持不变：exec lob_append(1, rpad('Z',20,'Z'));
• 新业务调用示例：exec lob_append(1001, '业务日志', p_table_name => 'biz_log', p_pk_col => 'log_id', p_clob_col => 'content');

方案2：配置表映射（入参最少，适合多业务场景固定的情况） #

仅需1个可选场景标识入参，所有表、列映射关系存在配置表中，调用方无需感知底层表结构：

1. 先创建场景配置表：

CREATE TABLE lob_append_config(
    scene_id VARCHAR2(50) PRIMARY KEY,
    table_name VARCHAR2(100) NOT NULL,
    pk_col VARCHAR2(100) NOT NULL,
    clob_col VARCHAR2(100) NOT NULL
);
-- 插入默认场景匹配原有逻辑
INSERT INTO lob_append_config VALUES('DEFAULT', 'T', 'SEQ_NUM', 'C');
-- 按需插入其他业务场景配置
INSERT INTO lob_append_config VALUES('BIZ_ORDER', 'order_oper_log', 'oper_id', 'log_content');

2. 改造存储过程：

CREATE OR REPLACE PROCEDURE lob_append(
    p_id IN NUMBER,
    p_text IN VARCHAR2,
    p_scene_id IN VARCHAR2 DEFAULT 'DEFAULT'
) AS
    l_clob CLOB;
    l_text VARCHAR2(32760);
    l_system_date_time VARCHAR2(50);
    l_table_name VARCHAR2(100);
    l_pk_col VARCHAR2(100);
    l_clob_col VARCHAR2(100);
BEGIN
    -- 读取对应场景的表列配置
    SELECT table_name, pk_col, clob_col 
    INTO l_table_name, l_pk_col, l_clob_col
    FROM lob_append_config WHERE scene_id = DBMS_ASSERT.SIMPLE_SQL_NAME(p_scene_id);

    EXECUTE IMMEDIATE 
        'SELECT ' || l_clob_col || ' FROM ' || l_table_name || 
        ' WHERE ' || l_pk_col || ' = :1 FOR UPDATE' 
        INTO l_clob USING p_id;

    SELECT TO_CHAR(SYSDATE, 'MMDDYYYY HH24:MI:SS') INTO l_system_date_time FROM dual;
    l_text := CHR(10) || p_text || CHR(10) || '['||l_system_date_time||']'||CHR(10);
    DBMS_LOB.WRITEAPPEND(l_clob, LENGTH(l_text), l_text);
END;
/

• 原有调用方式不变：exec lob_append(1, rpad('Z',20,'Z'));
• 新业务调用示例：exec lob_append(2001, '订单操作日志', 'BIZ_ORDER');

注意事项 #

• 所有动态拼接的表名、列名都需要经过DBMS_ASSERT包校验，避免SQL注入风险
• 可按需添加异常捕获逻辑，处理表不存在、主键值不存在等异常场景
• 权限控制：如果存储过程使用默认AUTHID DEFINER权限，需要过程所有者拥有所有操作表的UPDATE权限；如果配置为AUTHID CURRENT_USER，则调用者需要拥有对应表的操作权限

内容的提问来源于stack exchange，提问作者Pugzly