Oracle SQL拼接货币与金额时数值前导0丢失问题求助

问题原因 #

Oracle执行字符串拼接操作时，会自动将NUMBER类型的amount返回值隐式转换为VARCHAR2类型。对于0到1之间的正数，Oracle内置的隐式转换逻辑会省略整数位的0，因此0.1会被转换为.1，和currency返回的GBP拼接后就得到了GBP.1的结果。
你单独执行Select amount(DATA) from dual时看到返回0.1，是SQL客户端对数值类型做了显示层格式化，和隐式类型转换的逻辑不一致。

解决方法 #

两种常用方案按需选择：

• 方案1：调用时显式指定数字转字符串的格式，用TO_CHAR函数强制保留整数位的0
  改写后的拼接查询代码：

  SELECT currency(DATA) || TO_CHAR(amount(DATA), 'FM999999990.9999')
  FROM dual;
  

  格式符说明：FM用于去除转换后字符串前后的多余空格，9代表可选数字位，0代表强制显示的数字位，整数位写0就会强制保留整数位的0，后面的.9999可以根据你业务中金额的最大小数位数调整长度。
• 方案2：修改amount函数的返回逻辑（适合所有调用amount的场景都需要字符串格式金额的情况）
  直接在amount函数内部完成数值到格式化字符串的转换，返回VARCHAR2类型，后续所有调用拼接时不需要额外做类型处理。如果你的业务中存在需要用amount返回值做数值计算的场景，不推荐用这个方案。

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