Oracle SQL如何将多行查询结果合并为带换行的单行并导出TXT

当然可以实现，以下是几种实用方案，涵盖SQL和PL/SQL场景：

1. SQL方式：用XMLAGG生成CLOB（适配大结果集） #

XMLAGG可以直接生成CLOB类型，避免VARCHAR2长度限制，同时支持添加文本换行符（CHR(10)）或HTML <br/>标签：

SELECT 
  XMLAGG(
    XMLELEMENT(E, apex_string.format('%s  %s', empno, rpad(ename,5,' ')), CHR(10)).EXTRACT('//text()') 
    ORDER BY empno
  ) AS merged_clob
FROM emp;

• 替换CHR(10)为'<br/>'即可生成HTML换行格式
• ORDER BY empno保证结果顺序和原查询一致

2. SQL方式：LISTAGG+TO_CLOB（适配小结果集） #

如果拼接后的总长度不超过VARCHAR2上限（12c+为32767字节，低版本为4000字节），可以用LISTAGG快速拼接后转CLOB：

SELECT 
  TO_CLOB(
    LISTAGG(
      apex_string.format('%s  %s', empno, rpad(ename,5,' ')), 
      CHR(10)
    ) WITHIN GROUP (ORDER BY empno)
  ) AS merged_clob
FROM emp;

• 若超出长度会触发ORA-01489错误，此时优先用XMLAGG方案

3. PL/SQL方式：灵活拼接并导出TXT #

如果需要直接将结果写入TXT文件，可通过PL/SQL结合UTL_FILE包实现：

DECLARE
  v_clob CLOB := EMPTY_CLOB();
  v_file UTL_FILE.FILE_TYPE;
BEGIN
  -- 逐行拼接结果到CLOB
  FOR rec IN (
    SELECT apex_string.format('%s  %s', empno, rpad(ename,5,' ')) AS out_str 
    FROM emp 
    ORDER BY empno
  ) LOOP
    IF v_clob != EMPTY_CLOB() THEN
      v_clob := v_clob || CHR(10); -- 添加换行符
    END IF;
    v_clob := v_clob || rec.out_str;
  END LOOP;

  -- 写入TXT文件（需提前创建目录并授权）
  v_file := UTL_FILE.FOPEN('EXPORT_DIR', 'emp_output.txt', 'W', 32767);
  UTL_FILE.PUT_LINE(v_file, v_clob);
  UTL_FILE.FCLOSE(v_file);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(v_file) THEN
      UTL_FILE.FCLOSE(v_file);
    END IF;
    RAISE;
END;
/

• 需先创建目录对象：CREATE DIRECTORY EXPORT_DIR AS '/your/local/path';
• 给用户授权：GRANT READ, WRITE ON DIRECTORY EXPORT_DIR TO your_username;

导出TXT的补充技巧 #

如果用SQL*Plus导出，设置以下参数即可直接生成TXT：

SET LONG 1000000 -- 允许显示大CLOB内容
SET PAGESIZE 0 -- 隐藏表头
SET FEEDBACK OFF -- 隐藏统计信息
SPOOL emp_output.txt
SELECT XMLAGG(...) AS merged_clob FROM emp; -- 替换为你用的SQL语句
SPOOL OFF

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