[Oracle] Use to_char to format a number type column to currency format

Requirement

Before formatting.....
User hopes to add 1000 separator in amount-related columns, including AMT_A, AMT_B, and AMT_C

SELECT A.YEAR AS YEAR,--年度\n
       A.YYY_MM AS YYY_MM, --資料年月\n
       A.ASP, --性質別\n
       A.ITEM_CD AS ITEM_CD, --特別預算支出項目代號\n
       TRIM(A.ITEM_NM) AS ITEM_NM, --特別預算支出項目名稱\n
       F_FMS420R_GET_ACCUM_AMTA('10201', '10302', A.ASP) AS AMT_A, --實支數\n
       F_FMS420R_GET_ACCUM_AMTB('10201', '10302', A.ASP) AS AMT_B,
       --年度經費滕餘數\n
       F_FMS420R_GET_ACCUM_AMTC('10201', '10302', A.ASP) AS AMT_C --淨數\n
FROM FMS420FA A
WHERE A.YEAR = '103' AND A.YYY_MM = '10302'
ORDER BY TRIM(A.ITEM_NM) ;

Solution
We can use Oracle build-in function, TO_CHAR, to do currency format.

SELECT A.YEAR AS YEAR,--年度\n
       A.YYY_MM AS YYY_MM, --資料年月\n
       A.ASP, --性質別\n
       A.ITEM_CD AS ITEM_CD,
       --特別預算支出項目代號\n
       TRIM(A.ITEM_NM) AS ITEM_NM,
       --特別預算支出項目名稱\n
       TRIM(TO_CHAR(F_FMS420R_GET_ACCUM_AMTA('10201', '10302', A.ASP), '999,999,999,990.9999')) AS AMT_A_F,
       --實支數\n
       TRIM(TO_CHAR(F_FMS420R_GET_ACCUM_AMTB('10201', '10302', A.ASP), '999,999,999,990.9999')) AS AMT_B_F,
       --年度經費滕餘數\n
       TRIM(TO_CHAR(F_FMS420R_GET_ACCUM_AMTC('10201', '10302', A.ASP), '999,999,999,990.9999')) AS AMT_C_F --淨數\n
FROM FMS420FA A
WHERE A.YEAR = '103' AND A.YYY_MM = '10302'
ORDER BY TRIM(A.ITEM_NM) ;