将PL/SQL数字转单词函数转换为SQL并解析其工作原理

一、原函数工作原理拆解 #

• 基础准备：定义数组l_str，依次存放不同量级的英文后缀——空字符串对应百位级，之后是千、百万、十亿直到千百万兆级（duodecillion）。输入数字先被截断为整数，存入l_num变量。
• 边界判断：如果输入数字为空或0，直接返回Zero。
• 循环处理逻辑：遍历量级数组，每次执行以下操作：
  1. 从l_num末尾截取3位数字（比如数字123456，第一次截取456）；
  2. 若这3位数字非0，利用Oracle日期函数转成英文单词：to_date(数字, 'J')将数字转为儒略日，再用to_char(..., 'Jsp')格式把儒略日转成对应英文数字表述（比如123会变成One Hundred Twenty-Three）；
  3. 把转换后的单词、对应量级后缀（第一次用空，第二次用thousand）和当前结果字符串拼接，拼在结果前面（保证高位在前）；
  4. 把l_num去掉最后3位，继续循环直到l_num为空。

二、转换为SQL语句的实现 #

SQL没有PL/SQL的循环结构，可通过递归CTE模拟分组处理数字的逻辑，最终拼接出结果：

WITH RECURSIVE num_parts AS (
    -- 初始行：处理输入数字，初始化层级
    SELECT 
        TRUNC(:p_number) AS remaining_num,
        1 AS level_idx,
        CASE WHEN TRUNC(:p_number) <> 0 
             THEN TO_CHAR(TO_DATE(SUBSTR(TRUNC(:p_number), LENGTH(TRUNC(:p_number))-2, 3), 'J'), 'Jsp')
             ELSE ''
        END AS part_word
    FROM dual
    WHERE TRUNC(:p_number) <> 0
    UNION ALL
    -- 递归行：逐步截断数字，处理每一组3位
    SELECT
        CASE WHEN LENGTH(remaining_num) > 3 THEN SUBSTR(remaining_num, 1, LENGTH(remaining_num)-3) ELSE NULL END AS remaining_num,
        level_idx + 1 AS level_idx,
        CASE WHEN remaining_num IS NOT NULL AND LENGTH(remaining_num) > 0 
             THEN TO_CHAR(TO_DATE(SUBSTR(remaining_num, LENGTH(remaining_num)-2, 3), 'J'), 'Jsp')
             ELSE ''
        END AS part_word
    FROM num_parts
    WHERE remaining_num IS NOT NULL AND LENGTH(remaining_num) > 3
),
-- 生成对应层级的量级后缀
level_suffixes AS (
    SELECT 
        LEVEL AS idx,
        CASE LEVEL
            WHEN 1 THEN ''
            WHEN 2 THEN ' thousand '
            WHEN 3 THEN ' million '
            WHEN 4 THEN ' billion '
            WHEN 5 THEN ' trillion '
            WHEN 6 THEN ' quadrillion '
            WHEN 7 THEN ' quintillion '
            WHEN 8 THEN ' sextillion '
            WHEN 9 THEN ' septillion '
            WHEN 10 THEN ' octillion '
            WHEN 11 THEN ' nonillion '
            WHEN 12 THEN ' decillion '
            WHEN 13 THEN ' undecillion '
            WHEN 14 THEN ' duodecillion '
        END AS suffix
    FROM dual
    CONNECT BY LEVEL <= 14
)
-- 拼接最终结果
SELECT 
    CASE WHEN :p_number IS NULL OR :p_number = 0 THEN 'Zero'
         ELSE LISTAGG(CASE WHEN part_word <> '' THEN part_word || suffix ELSE '' END, '') WITHIN GROUP (ORDER BY level_idx DESC)
    END AS number_to_word
FROM num_parts
JOIN level_suffixes ON num_parts.level_idx = level_suffixes.idx
GROUP BY :p_number
UNION ALL
SELECT 'Zero' AS number_to_word FROM dual WHERE :p_number IS NULL OR :p_number = 0;

代码说明 #

1. num_parts递归CTE：从输入数字末尾开始，每次截取3位转成英文单词，同时记录当前处理层级；
2. level_suffixes：生成和原函数数组对应的量级后缀，按层级匹配；
3. 最后用LISTAGG按层级从高到低（逆序）拼接结果，保证数字高位在前，和原函数输出一致。

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