使用Oracle PL/SQL将Quill Editor生成的HTML转换为标准嵌套列表HTML

功能说明 #

• 解决Quill Editor生成的平级列表（通过ql-indent-n类标记缩进）转原生嵌套<ul>/<li>结构的需求
• 支持最大缩进值50，适配Quill官方缩进范围

实现代码 #

CREATE OR REPLACE FUNCTION quill_list_to_nested_html(p_quill_html IN CLOB) RETURN CLOB IS
    TYPE t_stack IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    l_stack t_stack;
    l_stack_top PLS_INTEGER := 0;
    l_current_indent NUMBER := 0;
    l_prev_indent NUMBER := 0;
    l_result CLOB := '<ul>'||CHR(10);
    l_li_content CLOB;
    l_indent_val NUMBER;
    
    -- 游标提取所有li元素和对应缩进值
    CURSOR c_li_nodes IS
        WITH li_raw AS (
            SELECT REGEXP_SUBSTR(p_quill_html, '<li[^>]*>.*?</li>', 1, LEVEL, 'in') li_str
            FROM dual
            CONNECT BY LEVEL <= REGEXP_COUNT(p_quill_html, '<li[^>]*>.*?</li>', 1, 'in')
        )
        SELECT 
            REGEXP_REPLACE(li_str, '<li[^>]*>(.*?)</li>', '\1', 1, 1, 'in') li_content,
            NVL(TO_NUMBER(REGEXP_SUBSTR(li_str, 'ql-indent-(\d+)', 1, 1, 'i', 1)), 0) indent_val
        FROM li_raw;
BEGIN
    -- 遍历所有li节点
    FOR li_rec IN c_li_nodes LOOP
        l_current_indent := li_rec.indent_val;
        l_li_content := li_rec.li_content;
        
        -- 缩进层级升高：新增ul节点
        WHILE l_current_indent > l_prev_indent LOOP
            l_result := l_result || RPAD(' ', (l_prev_indent + 1)*2) || '<ul>'||CHR(10);
            l_stack_top := l_stack_top + 1;
            l_stack(l_stack_top) := l_prev_indent;
            l_prev_indent := l_prev_indent + 1;
        END LOOP;
        
        -- 缩进层级降低：闭合多余的ul和li节点
        WHILE l_current_indent < l_prev_indent LOOP
            l_result := l_result || RPAD(' ', l_prev_indent*2) || '</li>'||CHR(10);
            l_result := l_result || RPAD(' ', l_stack(l_stack_top)*2 + 2) || '</ul>'||CHR(10);
            l_stack_top := l_stack_top - 1;
            l_prev_indent := l_prev_indent - 1;
        END LOOP;
        
        -- 插入当前li节点
        l_result := l_result || RPAD(' ', (l_current_indent + 1)*2) || '<li>' || l_li_content;
        l_prev_indent := l_current_indent;
    END LOOP;
    
    -- 闭合所有剩余的节点
    WHILE l_stack_top > 0 LOOP
        l_result := l_result || CHR(10) || RPAD(' ', l_prev_indent*2) || '</li>';
        l_result := l_result || CHR(10) || RPAD(' ', l_stack(l_stack_top)*2 + 2) || '</ul>';
        l_stack_top := l_stack_top - 1;
        l_prev_indent := l_prev_indent - 1;
    END LOOP;
    
    -- 闭合最后一个li和根ul
    l_result := l_result || CHR(10) || '  </li>' || CHR(10) || '</ul>';
    
    RETURN l_result;
END;
/

使用示例 #

DECLARE
    l_quill_input CLOB := '<ul> <li>Parent</li> <li>Parent</li> <li class="ql-indent-1">Child</li> <li class="ql-indent-1">Child</li> <li class="ql-indent-2">Child</li> <li class="ql-indent-2">Child</li> <li class="ql-indent-2">Child</li> <li class="ql-indent-3">Child</li> <li class="ql-indent-3">Child</li> <li class="ql-indent-1">Child</li> </ul>';
    l_nested_html CLOB;
BEGIN
    l_nested_html := quill_list_to_nested_html(l_quill_input);
    DBMS_OUTPUT.PUT_LINE(l_nested_html);
END;
/

注意事项 #

• 函数默认返回带缩进格式化的HTML，不需要格式化可以删除代码中RPAD(' ', ...)和CHR(10)相关逻辑
• 如果输入HTML包含特殊转义字符（如</>），可在处理前调用UTL_I18N.UNESCAPE_REFERENCE函数转义后再传入
• 适配Oracle 11g及以上版本，缩进最大支持到50层，可根据需求自行调整

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