使用EXECUTE IMMEDIATE结合REGEXP_LIKE时的特殊字符转义问题

你在编写带SUM统计的EXECUTE IMMEDIATE语句时，因REGEXP_LIKE的正则表达式特殊字符转义、PL/SQL变量引用方式错误导致问题，以下是针对两种写法的修正方案：

写法a的问题与修正 #

原写法错误地将dbms_assert.enquote_name(v_column_name, false)放在q'[]'动态SQL内部，导致执行时把v_column_name当作表列名而非PL/SQL变量值；同时正则表达式里的小数点未转义（若需匹配小数点而非任意字符）。

修正后的代码：

sql_stmt := q'[
    SELECT
        COUNT(*) AS count_rows,
        MAX(' || dbms_assert.enquote_name(v_column_name, false) || ') AS max_primary_key,
        MIN(' || dbms_assert.enquote_name(v_column_name, false) || ') AS min_primary_key,
        SUM(
            CASE
                WHEN REGEXP_LIKE(' || dbms_assert.enquote_name(v_column_name, false) || ', ' || q'['^(\+|-)?[0-9]*((\.)?[0-9])[0-9]*$']' || ') THEN
                    TO_NUMBER(' || dbms_assert.enquote_name(v_column_name, false) || ')
                ELSE
                    0
            END
        ) AS sum_primary_key
    FROM
        ' || dbms_assert.enquote_name(v_owner, false) || '.' || dbms_assert.enquote_name(v_table_name, false)
]';

• 用q'['^...$']'包裹正则表达式，避免单引号转义问题
• 将dbms_assert调用移到q'[]'外部拼接，确保PL/SQL先计算出带引号的列名、表名再拼入动态SQL
• 把正则中的.转义为\.，确保仅匹配小数点（若无需匹配小数点可忽略此修改）

写法b的问题与修正 #

原写法中REGEXP_LIKE的正则字符串单引号未正确转义，PL/SQL中字符串内的单引号需写成两个才能表示一个单引号；同时小数点未转义。

修正后的代码：

sql_stmt := 'SELECT COUNT(*) as count_rows,max('
            || dbms_assert.enquote_name(v_column_name, false)
            || ') as max_primary_key,min('
            || dbms_assert.enquote_name(v_column_name, false)
            || ') as min_primary_key, SUM( CASE WHEN REGEXP_LIKE('
            || dbms_assert.enquote_name(v_column_name, false)
            || ', ''^(\+|-)?[0-9]*((\.)?[0-9])[0-9]*$'') THEN  to_number('
            || dbms_assert.enquote_name(v_column_name, false)
            || ')  ELSE    0        END    )    as sum_primary_key FROM '
            || DBMS_ASSERT.ENQUOTE_NAME(v_owner, FALSE)
            || '.'
            || DBMS_ASSERT.ENQUOTE_NAME(v_table_name, FALSE);

• 将正则表达式的单引号转义为''，确保PL/SQL正确解析为带单引号的字符串
• 转义小数点为\.，匹配数字中的小数点

更优方案：用绑定变量处理正则表达式 #

若不想处理字符串拼接的转义问题，可将正则表达式作为绑定变量传入，仅拼接列名、表名：

sql_stmt := q'[
    SELECT
        COUNT(*) AS count_rows,
        MAX(:col) AS max_primary_key,
        MIN(:col) AS min_primary_key,
        SUM(
            CASE
                WHEN REGEXP_LIKE(:col, :regex) THEN
                    TO_NUMBER(:col)
                ELSE
                    0
            END
        ) AS sum_primary_key
    FROM
        :owner.:table
]';

-- 替换占位符为带引号的对象名
sql_stmt := REPLACE(sql_stmt, ':col', dbms_assert.enquote_name(v_column_name, false));
sql_stmt := REPLACE(sql_stmt, ':owner', dbms_assert.enquote_name(v_owner, false));
sql_stmt := REPLACE(sql_stmt, ':table', dbms_assert.enquote_name(v_table_name, false));

-- 执行动态SQL，传入正则表达式作为绑定变量
EXECUTE IMMEDIATE sql_stmt USING '^(\+|-)?[0-9]*((\.)?[0-9])[0-9]*$';

此方式无需处理正则的转义，代码更简洁可靠。

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