Oracle 11g中SPOOL文件VARCHAR2函数返回列格式异常求助

在使用Oracle SQL 11g编写PL/SQL脚本完成作业时，遇到SPOOL输出格式异常：VARCHAR2类型函数的返回列标题及内容均分行显示，需要调整为多列同行且带分隔符的指定格式。

原PL/SQL脚本 #

SET echo on
SET wrap off
SET trimspool on
SET linesize 80
SET colsep '|'
SET serveroutput on

spool @assignment.txt

CREATE OR REPLACE FUNCTION dollar_fmt_sf(
    p_num NUMBER
)RETURN VARCHAR2 IS
    lv_amt_txt VARCHAR2(20);
BEGIN
    lv_amt_txt := to_char(p_num, '$99,999.99');
    RETURN lv_amt_txt;
END;
/

SELECT
    idbasket,
    dollar_fmt_sf(shipping),
    dollar_fmt_sf(total)
FROM
    bb_basket
WHERE
    idbasket = 3;

spool off

当前异常SPOOL输出 #

IDBASKET
----------
DOLLAR_FMT_SF(SHIPPING)                                                          
--------------------------------------------------------------------------------
DOLLAR_FMT_SF(TOTAL)                                                            
--------------------------------------------------------------------------------
         3
                                                                           $5.00
                                                                          $32.40

调整方案及修改后的脚本 #

问题根源在于默认列标题过长、行宽不足，以及格式不匹配期望输出，修改如下：

SET echo on
SET wrap off
SET trimspool on
SET linesize 120  -- 增大行宽，确保三列+分隔符能在一行容纳
SET colsep '|'
SET serveroutput on

spool assignment.txt

CREATE OR REPLACE FUNCTION dollar_fmt_sf(
    p_num NUMBER
)RETURN VARCHAR2 IS
    lv_amt_txt VARCHAR2(20);
BEGIN
    lv_amt_txt := to_char(p_num, '99,999.99');  -- 去掉$符号，匹配期望输出格式
    RETURN lv_amt_txt;
END;
/

SELECT
    idbasket,
    dollar_fmt_sf(shipping) AS SHIPPING,  -- 给函数返回列指定简短别名，避免标题换行
    dollar_fmt_sf(total) AS TOTAL         -- 指定别名统一列标题格式
FROM
    bb_basket
WHERE
    idbasket = 3;

spool off

修改说明 #

• 指定列别名：默认用函数全名作为列标题，长度远超行宽导致分行，给每列指定简短别名后，标题能在同一行显示。
• 调整linesize：原80的行宽不足以容纳三列内容和分隔符，增大到120确保所有列内容同行显示。
• 调整数值格式：根据期望输出去掉了$符号，若需要保留可保留原$99,999.99格式，同时确保列宽足够。

调整后期望输出 #

IDBASKET|  SHIPPING|     TOTAL
----------|----------|----------
         3|         5|      32.4

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