寻求PL/SQL实现动态查询及SQL*Loader文件自动缩进格式化

现有存储过程的局限性 #

当前的format_insert_query仅基于括号、逗号做简单换行缩进，存在以下核心问题：

• 无法区分字符串常量中的括号/逗号（比如'O''Neil, Jr.'里的逗号会被错误触发换行）
• 对多行VALUES子句（如VALUES (1,'a'), (2,'b')）的格式化逻辑混乱，缩进错位
• 不支持SQL*Loader控制文件的语法（如LOAD DATA INFILE、INTO TABLE等规则）
• 仅支持VARCHAR2(4000)，无法处理超长SQL语句
• 未处理冗余空格，格式化后会残留多余空白字符

优化方向 #

针对上述问题，优化方案包含以下关键点：

• 增加字符串常量状态跟踪，跳过引号内的特殊字符处理
• 为INSERT的VALUES子句设计专门的多行拆分与缩进逻辑
• 新增SQL*Loader控制文件的语法识别与格式化规则
• 改用CLOB类型存储，支持超长文本处理
• 添加自动去除冗余连续空格的逻辑
• 开放缩进空格数、文件类型（SQL/CTL）的配置选项

优化后的存储过程 #

CREATE OR REPLACE PROCEDURE format_sql_or_ctl (
    p_input         IN CLOB,
    p_indent_spaces IN NUMBER DEFAULT 4,
    p_is_ctl_file   IN BOOLEAN DEFAULT FALSE
) RETURN CLOB IS
    v_formatted     CLOB := '';
    v_indent_level  NUMBER := 0;
    v_indent_str    VARCHAR2(100);
    v_pos           NUMBER := 1;
    v_char          CHAR(1);
    v_prev_char     CHAR(1);
    v_in_string     BOOLEAN := FALSE;
    v_string_quote  CHAR(1);
BEGIN
    -- 初始化缩进字符串
    v_indent_str := RPAD(' ', p_indent_spaces);

    WHILE v_pos <= DBMS_LOB.GETLENGTH(p_input) LOOP
        v_char := DBMS_LOB.SUBSTR(p_input, 1, v_pos);
        
        -- 处理字符串常量的进入/退出状态，跳过引号内的格式处理
        IF NOT v_in_string AND (v_char = '''' OR v_char = '"') THEN
            v_in_string := TRUE;
            v_string_quote := v_char;
            v_formatted := v_formatted || v_char;
            v_pos := v_pos + 1;
            CONTINUE;
        ELSIF v_in_string AND v_char = v_string_quote THEN
            -- 识别转义引号（如''或""）
            IF DBMS_LOB.SUBSTR(p_input, 1, v_pos + 1) = v_string_quote THEN
                v_formatted := v_formatted || v_char || v_char;
                v_pos := v_pos + 2;
                CONTINUE;
            ELSE
                v_in_string := FALSE;
            END IF;
        END IF;

        -- 字符串内的字符直接追加，不触发格式规则
        IF v_in_string THEN
            v_formatted := v_formatted || v_char;
            v_pos := v_pos + 1;
            CONTINUE;
        END IF;

        -- 去除连续冗余空格
        IF v_char = ' ' AND v_prev_char = ' ' THEN
            v_pos := v_pos + 1;
            CONTINUE;
        END IF;

        -- SQL*Loader控制文件专属格式化规则
        IF p_is_ctl_file THEN
            v_keyword := UPPER(DBMS_LOB.SUBSTR(p_input, INSTR(p_input, ' ', v_pos) - v_pos, v_pos));
            CASE v_keyword
                WHEN 'LOAD', 'INFILE', 'APPEND', 'INSERT', 'REPLACE' THEN
                    v_formatted := v_formatted || CHR(10) || v_char;
                    v_indent_level := 0;
                WHEN 'INTO' THEN
                    v_formatted := v_formatted || CHR(10) || v_indent_str || v_char;
                    v_indent_level := 1;
                WHEN 'FIELDS', 'COLUMNS' THEN
                    v_formatted := v_formatted || CHR(10) || v_indent_str || v_indent_str || v_char;
                    v_indent_level := 2;
                ELSE
                    NULL;
            END CASE;
        ELSE
            -- INSERT SQL专属格式化规则
            CASE v_char
                WHEN '(' THEN
                    v_formatted := v_formatted || v_char || CHR(10);
                    v_indent_level := v_indent_level + 1;
                    v_formatted := v_formatted || RPAD('', v_indent_level * p_indent_spaces);
                WHEN ')' THEN
                    v_indent_level := v_indent_level - 1;
                    v_formatted := v_formatted || CHR(10) || RPAD('', v_indent_level * p_indent_spaces) || v_char;
                    -- 处理多行VALUES后的逗号换行
                    IF DBMS_LOB.SUBSTR(p_input, 1, v_pos + 1) = ',' THEN
                        v_formatted := v_formatted || ',' || CHR(10);
                        v_pos := v_pos + 1;
                        v_formatted := v_formatted || RPAD('', v_indent_level * p_indent_spaces);
                    END IF;
                WHEN ',' THEN
                    v_formatted := v_formatted || v_char || CHR(10) || RPAD('', v_indent_level * p_indent_spaces);
                WHEN ';' THEN
                    v_formatted := v_formatted || CHR(10) || v_char;
                ELSE
                    -- INSERT/VALUES关键字后自动换行缩进
                    IF UPPER(v_prev_char || v_char) IN ('IN', 'VA') THEN
                        v_keyword := UPPER(DBMS_LOB.SUBSTR(p_input, 6, v_pos - 1));
                        IF v_keyword = 'INSERT' OR v_keyword = 'VALUES' THEN
                            v_formatted := v_formatted || CHR(10) || RPAD('', v_indent_level * p_indent_spaces);
                        END IF;
                    END IF;
                    v_formatted := v_formatted || v_char;
            END CASE;
        END IF;

        v_prev_char := v_char;
        v_pos := v_pos + 1;
    END LOOP;

    -- 去除开头多余换行符
    v_formatted := LTRIM(v_formatted, CHR(10));
    RETURN v_formatted;
END;
/

调用示例 #

1. 格式化普通INSERT语句 #

DECLARE
    v_raw_sql    CLOB;
    v_formatted  CLOB;
BEGIN
    v_raw_sql := 'INSERT INTO employees (id, name, salary, department_id) VALUES (1, ''John O''Neil, Jr.'', 50000, 10), (2, ''Jane Smith'', 60000, 20);';
    v_formatted := format_sql_or_ctl(v_raw_sql);
    DBMS_OUTPUT.PUT_LINE(v_formatted);
END;
/

输出结果：

INSERT INTO employees 
    (id, 
    name, 
    salary, 
    department_id) 
VALUES 
    (1, 
    'John O'Neil, Jr.', 
    50000, 
    10), 
    (2, 
    'Jane Smith', 
    60000, 
    20);

2. 格式化SQL*Loader控制文件 #

DECLARE
    v_raw_ctl    CLOB;
    v_formatted  CLOB;
BEGIN
    v_raw_ctl := 'LOAD DATA INFILE ''employees.dat'' APPEND INTO TABLE employees FIELDS TERMINATED BY '','' ENCLOSED BY ''"'' (id, name, salary, department_id)';
    v_formatted := format_sql_or_ctl(v_raw_ctl, p_is_ctl_file => TRUE);
    DBMS_OUTPUT.PUT_LINE(v_formatted);
END;
/

输出结果：

LOAD DATA
INFILE 'employees.dat'
APPEND
    INTO TABLE employees
        FIELDS TERMINATED BY ',' ENCLOSED BY '"'
            (id, 
            name, 
            salary, 
            department_id)

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