如何避免在Oracle SQL的substr函数中硬编码列长度参数？

方案1：封装获取列长度的通用函数 #

写一个可复用的函数，传入表名和列名即可返回对应列的最大定义长度，存储过程直接调用该函数，无需硬编码数值。

示例函数：

CREATE OR REPLACE FUNCTION get_col_max_length(p_table_name IN VARCHAR2, p_col_name IN VARCHAR2)
RETURN NUMBER
IS
    v_length NUMBER;
BEGIN
    SELECT data_length
    INTO v_length
    FROM user_tab_columns
    WHERE table_name = UPPER(p_table_name)
      AND column_name = UPPER(p_col_name);
    RETURN v_length;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, '目标表或列不存在');
END;
/

存储过程中调用方式：

INSERT INTO your_table (foo)
VALUES (
    SUBSTR(variable_with_additional_string || variable_with_string_from_foo_column, 1, get_col_max_length('your_table', 'foo'))
);

当表结构修改后，函数会自动返回新的列长度，存储过程无需任何改动，且该函数可复用在其他业务场景中。

方案2：存储过程内部动态获取长度 #

如果不想额外创建函数，可在存储过程内部直接查询数据字典获取列长度，赋值给变量后使用：

CREATE OR REPLACE PROCEDURE copy_and_modify_row(p_original_id IN NUMBER)
IS
    v_foo_old VARCHAR2(4000);
    v_foo_max_len NUMBER;
    v_new_foo VARCHAR2(4000);
BEGIN
    -- 获取原行的foo字段值
    SELECT foo INTO v_foo_old FROM your_table WHERE id = p_original_id;
    
    -- 查询foo列的最大定义长度
    SELECT data_length INTO v_foo_max_len FROM user_tab_columns
    WHERE table_name = 'YOUR_TABLE' AND column_name = 'FOO';
    
    -- 拼接字符串并按列长度截断
    v_new_foo := SUBSTR('xyz, ' || v_foo_old, 1, v_foo_max_len);
    
    -- 插入新行
    INSERT INTO your_table (foo) VALUES (v_new_foo);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20002, '未找到指定行或目标列不存在');
END;
/

方案3：动态SQL构建插入逻辑 #

若想进一步降低硬编码耦合度，可使用动态SQL构建插入语句，同时注意通过绑定变量避免SQL注入风险：

CREATE OR REPLACE PROCEDURE copy_and_modify_row(p_original_id IN NUMBER)
IS
    v_foo_old VARCHAR2(4000);
    v_foo_max_len NUMBER;
    v_sql VARCHAR2(1000);
BEGIN
    SELECT foo INTO v_foo_old FROM your_table WHERE id = p_original_id;
    
    SELECT data_length INTO v_foo_max_len FROM user_tab_columns
    WHERE table_name = 'YOUR_TABLE' AND column_name = 'FOO';
    
    v_sql := 'INSERT INTO your_table (foo) VALUES (SUBSTR(:new_str, 1, :max_len))';
    EXECUTE IMMEDIATE v_sql USING ('abc, ' || v_foo_old, v_foo_max_len);
END;
/

注意事项 #

• 优先使用USER_TAB_COLUMNS而非ALL_TAB_COLUMNS，前者仅查询当前用户名下的表，权限要求更低、查询性能更好；若操作跨用户表，再改用ALL_TAB_COLUMNS并确保执行者拥有对应视图的查询权限。
• 数据字典中的DATA_LENGTH字段，对于VARCHAR2类型来说，直接对应列定义的长度值（如VARCHAR2(100)的DATA_LENGTH即为100）。

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