Oracle SQL中使用AskTom的spell_number函数转换小数为英文单词时的异常问题排查

我来帮你拆解一下这个问题的核心原因，主要有两点：

1. 数值存储的精度丢失 #

当你在查询中写select 58.80 tax_amt from dual时，Oracle的NUMBER类型会自动忽略末尾无意义的零，实际存储的是58.8。这就导致你用INSTR和SUBSTR提取小数部分时，拿到的是8而不是80，传入spell_number后自然被转换为“Eight”。

2. 小数部分的处理逻辑缺陷 #

原spell_number函数是为处理整数设计的，但你当前的调用方式没有把小数部分当作**两位整数（金额中的“分”）**来处理。即使能拿到80，如果直接传入字符串80转成数值，函数是可以正确处理的，但问题出在你没拿到完整的两位小数。

步骤1：修正小数部分的提取逻辑 #

使用TO_CHAR将数值格式化为固定两位小数的字符串，确保末尾的零不会丢失。比如用TO_CHAR(abs(tax_amt), 'FM999999999999.00')，其中FM用来去掉多余的空格，.00强制保留两位小数。

步骤2：调整spell_number的调用方式 #

从格式化后的字符串中提取小数部分，转成整数后传入函数，这样两位数字就能被正确转换为对应的英文单词。

修改后的测试SQL #

select 
    jaaw.tax_amt 
    , case when abs(jaaw.tax_amt) <> 0 then zx_wtrl_jl_extract_pkg.spell_number (abs(jaaw.tax_amt)) else null end whole_num 
    , case 
        when INSTR(fmt_amt, '.') > 0 then 
            case when SUBSTR(fmt_amt, INSTR(fmt_amt, '.')+1) <> '00' 
                 then zx_wtrl_jl_extract_pkg.spell_number (TO_NUMBER(SUBSTR(fmt_amt, INSTR(fmt_amt, '.')+1))) 
                 else null end 
        else null 
      end DCIMAL 
    , regexp_substr(fmt_amt, '\d+$') decimal_new 
from (
    select 
        tax_amt,
        TO_CHAR(abs(tax_amt), 'FM999999999999.00') as fmt_amt
    from (
        select 58.80 tax_amt from dual union all 
        select 31.36 from dual union all 
        select 117.6 from dual 
    )
) jaaw;

额外优化：修复原函数的异常处理逻辑 #

原函数的异常块中，return null写在了日志输出之前，导致异常发生时日志永远不会被执行，这会给排查问题带来困难。调整后的异常块如下：

EXCEPTION 
WHEN OTHERS THEN 
    IF ( FND_LOG.LEVEL_STATEMENT >= ZX_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN 
        ZX_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ZX.TRL.ZX_WTRL_JL_EXTRACT_PKG.SPELL_NUMBER', SUBSTR('Exception Occured in SPELL_NUMBER: '||sqlerrm, 1, 3000)); 
    END IF; 
    FND_FILE.PUT_LINE(FND_FILE.LOG, SUBSTR('Exception Occured in SPELL_NUMBER: '||sqlerrm, 1, 3000)); 
    return null; 

这样修改后，58.80的小数部分会被正确提取为80，传入spell_number后就会转换为“Eighty”，完全符合你的预期。

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