Working with Numbers in PL/SQL（在PL/SQL中使用数字）(四)
nd it returns the string representation of that number, exactly long enough to contain all of its significant digits.
Listing 5 includes a block that shows some TO_CHAR examples. As you can see, leading and trailing zeros are not in the string representation of the number.
Code Listing 5: Calls to TO_CHAR
BEGIN
DBMS_OUTPUT.put_line (TO_CHAR (100.55));
DBMS_OUTPUT.put_line (TO_CHAR (000100.5500));
DBMS_OUTPUT.put_line (TO_CHAR (10000.00));
END;
And here is the output from this block:
100.55 100.55 10000
To specify a format for the string to which the number is converted, provide as the second argument to TO_CHAR a string that contains a combination of special format elements. Suppose, for example, that I want to display large numbers with a “1000s” delimiter. In other words, I want to display “10,000” instead of “10000” so I would use the following format: Often, when you have to convert a number to a string, you need that number to fit a certain format. You might, for example, want to display the number as a currency, so that even if there are no cents, you need to include the “.00”—in such cases, you will need to add a second argument in your call to TO_CHAR: the format mask.
BEGIN
DBMS_OUTPUT.put_line (
'Amount='||
TO_CHAR (
10000
, '9G999G999'));
END;
The G element indicates where in the string I would like to place the group separator. (The character used for the separator is determined by the National Language Settings database parameter NLS_NUMERIC_CHARACTERS.) The 9 element tells Oracle Database to put a significant digit or a blank space in that location. As a result, the output from this block is
Amount= 10,000
If I want to have zeros appear instead of blanks, I can use 0 instead of 9, as in
BEGIN
DBMS_OUTPUT.put_line (
'Amount=' ||
TO_CHAR (
10000
, '0G000G999'));
END;
Amount= 0,010,000
If I do not want any leading zeros, extra blanks, or white space appearing in my converted number, I will use the FM element, as in
BEGIN
DBMS_OUTPUT.put_line (
'Amount=' ||
TO_CHAR (
10000
, 'FM9G999G999'));
END;
Amount=10,000
Suppose that my number is actually a monetary unit consisting of dollars (or euros) and cents and I want to show the currency symbol as well as the cents portion. I can use the following format:
BEGIN
DBMS_OUTPUT.put_line (
'Salary=' ||
TO_CHAR (
14500.77
, 'FML999G999D99'));
END;
Salary=$14,500.77
The L element specifies the location of the local currency symbol (such as $ or ) in the return value (the NLS_CURRENCY parameter specifies the local currency symbol). The D element indicates the location of the decimal point. (The character used for the decimal point is specified by the database parameter NLS_NUMERIC_CHARACTERS.)
It is outside the scope of this article to explain all of the many elements available for use in number formats (there are, for example, at least four elements just for denoting monetary units). Check Oracle Database SQL Language Reference 11g Release 2 (11.2), for a complete description.
------------------------------
present by dylan.