Oracle中使用TO_CHAR进行数字格式设置 [英] Number formatting in Oracle using TO_CHAR

查看:333
本文介绍了Oracle中使用TO_CHAR进行数字格式设置的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在ORACLE存储过程中格式化数字的正确方法.

Proper way to format the numbers in ORACLE stored procedures.

我需要显示2个小数的货币字段. 预期输出如下:

I need to display currency fields with 2 decimals. Expected output is as follows:

  • 0> 0.00
  • 5> 5.00
  • 1253.6> 1253.60
  • 1253.689> 1253.69

以下为我工作:

select to_char(9876.23 , 'fm999990.00') from dual;

但这存在硬编码一堆9的问题.如果我给出更大的数字,它将显示为"###############"

But this has the issue of hard coding a bunch of 9s. If I give a larger number it will be displayed as "##############"

还有其他方法可以做到吗?

Is there any other way I can do this?

推荐答案

我需要显示两位小数的货币字段.

I need to display currency fields with 2 decimals.

确保使用数字数据类型,其小数位数和精度适合于数据,而不是使用不带小数位数和精度的NUMBER.如果您要存储美元/欧元/英镑/等.那么全球产品总值在2014年约为1亿美元.将要处理的不仅仅是这个 [需要引用] ,那么您的货币列可以是:

Ensure you use the number data-type with scale and precision appropriate to the data rather than using NUMBER without scale and precision. If you are going to be storing dollars/euroes/pounds/etc. then the Gross World Product was of the order of $100,000,000,000,000 in 2014. Lets assume that you are not going to be dealing with more than this[citation needed] then your currency column can be:

NUMBER(17,2)

如果获得的值大于该值,则需要对数据进行完整性检查,并考虑大于世界总产值的数量是否有意义.如果要以日元或津巴布韦美元等形式存储值,请适当调整比例.

If you get a value that is bigger than that then you need to perform a sanity check on your data and think whether an amount bigger than the world's gross product makes sense. If you are going to store the values as, for example, Yen or Zimbabwe dollars then adjust the scale appropriately.

您甚至可以在包中将子类型定义为:

You could even define a sub-type in a package as:

CREATE PACKAGE currencies_pkg IS
  SUBTYPE currency_type IS NUMBER(17,2);

  FUNCTION formatCurrency(
    amount IN CURRENCY_TYPE
  ) RETURN VARCHAR2;
END;
/

用于格式化的代码可以是:

And your code to format it can be:

CREATE PACKAGE BODY currencies_pkg IS
  FUNCTION formatCurrency(
    amount IN CURRENCY_TYPE
  ) RETURN VARCHAR2
  IS
  BEGIN
    RETURN TO_CHAR( currency_value, 'FM999999999999990D00' );
  END;
END;
/

然后,如果您在存储过程/程序包中引用该子类型,则不会引发货币数据类型的最大大小,而不会引发异常.用于显示值的格式模型只需要在一个地方定义,并且由于输入仅限于货币子类型,因此格式化功能将永远不会超过所施加的比例/精度,并且无法输出# s. /p>

Then if you reference that sub-type in your stored procedures/packages you will not be able to exceed the maximum size of the currency data type without an exception being raised. The format model for displaying the value only needs to be defined in a single place and since the input is limited to the currency sub-type, then the formatting function will never exceed the imposed scale/precision and cannot output #s.

CREATE PROCEDURE your_procedure(
  in_value1 IN ACCOUNTS_TABLE.ACCOUNT_BALANCE%TYPE,
  in_value2 IN ACCOUNTS_TABLE.ACCOUNT_BALANCE%TYPE
)
IS
  v_value CURRENCIES_PKG.CURRENCY_TYPE;
BEGIN
  -- Do something
  v_value := in_value1 + in_value2;
  -- Output formatted value
  DBMS_OUTPUT.PUT_LINE( CURRENCIES_PKG.formatCurrency( v_value ) );
END;
/

这篇关于Oracle中使用TO_CHAR进行数字格式设置的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆