需求：用VARRAY或其他方式动态逐个替换Oracle代码中的特殊字符

DECLARE
    table_or_view_does_not_exist EXCEPTION;
    PRAGMA EXCEPTION_INIT(table_or_view_does_not_exist, -00942);
    -- 定义VARRAY存储特殊字符与替换值的映射
    TYPE char_map_type IS VARRAY(10) OF VARCHAR2(20);
    v_char_maps char_map_type := char_map_type('&,#', '@,!', '*,$'); -- 可扩展更多字符对，格式：原字符,替换值
    d_table VARCHAR2(200);
    q_table VARCHAR2(200);
    v_sql VARCHAR2(4000);
    v_update_sql VARCHAR2(4000);
    v_orig_char VARCHAR2(10);
    v_replace_char VARCHAR2(10);
    v_count NUMBER;
BEGIN
    DBMS_OUTPUT.ENABLE(buffer_size => NULL);
    -- 重建日志表
    BEGIN
        d_table := 'DROP TABLE fms_addr_Spcl_Char PURGE';
        EXECUTE IMMEDIATE d_table;
    EXCEPTION
        WHEN table_or_view_does_not_exist THEN
            NULL;
    END;

    q_table := 'CREATE TABLE fms_addr_Spcl_Char 
                 (table_name VARCHAR2(50), 
                  column_name VARCHAR2(50),
                  original_char VARCHAR2(500),
                  replace_char VARCHAR2(500),
                  process_count VARCHAR2(50))';
    EXECUTE IMMEDIATE q_table;

    -- 遍历目标表的所有列
    FOR r IN (
        SELECT table_name, column_name
        FROM all_tab_columns
        WHERE table_name = UPPER('fms_user_address_book')
          AND owner = 'FMS_ADMIN'
    ) LOOP
        -- 遍历每个特殊字符映射对
        FOR i IN 1..v_char_maps.COUNT LOOP
            -- 拆分原字符和替换值
            v_orig_char := SUBSTR(v_char_maps(i), 1, INSTR(v_char_maps(i), ',') - 1);
            v_replace_char := SUBSTR(v_char_maps(i), INSTR(v_char_maps(i), ',') + 1);

            -- 查询包含当前特殊字符的数据并统计数量
            v_sql := 'SELECT COUNT(*) 
                      FROM "' || r.table_name || '"
                      WHERE REGEXP_LIKE("' || r.column_name || '", ''' || v_orig_char || ''') 
                        AND zone = ''SZ''';
            EXECUTE IMMEDIATE v_sql INTO v_count;

            -- 如果存在匹配数据，执行替换并记录日志
            IF v_count > 0 THEN
                -- 构造更新语句，替换特殊字符
                v_update_sql := 'UPDATE "' || r.table_name || '"
                                 SET "' || r.column_name || '" = REPLACE("' || r.column_name || '", ''' || v_orig_char || ''', ''' || v_replace_char || ''')
                                 WHERE REGEXP_LIKE("' || r.column_name || '", ''' || v_orig_char || ''') 
                                   AND zone = ''SZ''';
                EXECUTE IMMEDIATE v_update_sql;

                -- 记录处理日志
                EXECUTE IMMEDIATE 'INSERT INTO fms_addr_Spcl_Char 
                                   VALUES (:param1, :param2, :param3, :param4, :param5)'
                USING r.table_name, r.column_name, v_orig_char, v_replace_char, v_count;
            END IF;
        END LOOP;
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('特殊字符替换完成，日志已记录到fms_addr_Spcl_Char表');
END;
/

关键改动说明 #

• VARRAY管理字符映射：定义char_map_type类型存储字符替换规则，v_char_maps中可添加多组原字符,替换值格式的条目，无需修改核心逻辑即可扩展支持更多特殊字符。
• 动态拆分字符对：通过字符串函数拆分VARRAY中的每个条目，自动分离原字符和替换目标值。
• 高效批量处理：先统计匹配数据量，再执行批量UPDATE替换，避免逐行游标遍历，提升执行效率。
• 日志表扩展：新增replace_char字段记录替换后的字符，方便追溯处理详情。
• 双层循环覆盖所有场景：外层遍历目标表的列，内层遍历每个特殊字符，实现全列全特殊字符的处理。

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