Oracle中使用TO_CHAR进行数字格式设置 [英] Number formatting in Oracle using 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屋!