Oracle存储过程动态更新字段报ORA-00904错误求助

你遇到的问题根源在于静态SQL无法直接使用变量作为列名。Oracle会把你写的p_column当成一个实际的列名来解析，但你的book表中并没有名为p_column的字段，所以抛出了ORA-00904无效标识符的错误。

要实现动态指定列名更新，你需要使用动态SQL（通过EXECUTE IMMEDIATE语句）来拼接SQL语句。不过直接拼接字符串有SQL注入的风险，所以最好结合绑定变量和列名合法性校验来处理，具体步骤如下：

解决方案步骤 #

• 验证列名合法性：先检查传入的p_column_name是否确实是book表中的有效列，避免恶意输入或错误列名。
• 使用动态SQL拼接并执行：用EXECUTE IMMEDIATE拼接UPDATE语句，将列名作为字符串拼接，而p_id_book和p_value用绑定变量传递，降低SQL注入风险。

完整示例代码 #

CREATE OR REPLACE PROCEDURE update_book(
    p_id_book IN NUMBER,
    p_column_name VARCHAR2,
    p_value VARCHAR2
) AS
    v_valid_column BOOLEAN := FALSE;
BEGIN
    -- 验证传入的列名是否存在于book表中
    SELECT CASE WHEN EXISTS (
        SELECT 1 
        FROM user_tab_columns 
        WHERE table_name = 'BOOK' 
          AND column_name = UPPER(p_column_name)
    ) THEN TRUE ELSE FALSE END INTO v_valid_column FROM dual;

    IF NOT v_valid_column THEN
        RAISE_APPLICATION_ERROR(-20001, 'Invalid column name: ' || p_column_name);
    END IF;

    -- 执行动态SQL更新，使用绑定变量处理参数
    EXECUTE IMMEDIATE 
        'UPDATE book SET ' || p_column_name || ' = :1 WHERE id_book = :2'
        USING p_value, p_id_book;

    -- 若需要由调用者控制事务，可移除下面的COMMIT语句
    COMMIT;
END;
/

关键说明 #

• 列名合法性校验：通过查询user_tab_columns系统视图确认列名存在，防止传入不存在的列或恶意字符串（比如包含SQL注入语句的内容）。
• 绑定变量的使用：USING子句传递p_value和p_id_book，避免直接拼接这些值到SQL字符串中，有效防范SQL注入。
• 大小写处理：Oracle默认表名和列名是大写存储的，所以校验时用UPPER(p_column_name)统一转换，避免因大小写不匹配导致的错误。

内容的提问来源于stack exchange，提问作者Sebastián Vašš