请问 select to_char(empno,'c99999') from emp;是什么意思,

[洋溢2020]

请问 select to_char(empno,'c99999') from emp;是什么意思,
书本解释说是 数值转换为字符串,并使用当前符号做前缀~
我怎么总看不懂了,晕~~

[kinglin_zy]

来晚了,楼上的正解,将普通数值类型转换成的当地货币数值。

[shangqiao]

C:Specifies the location of the local currency symbol (such as $) in the return value. The NLS_CURRENCY parameter specifies the local currency symbol.

9:Each 9 represents a significant digit to be returned. Leading zeros in a number are displayed as blanks.

所以意思是最多为5位数字，以本地货币符号作为前缀

[shangqiao]

9
Each 9 represents a significant digit to be returned. Leading zeros in a number are displayed as blanks.

0
Each zero represents a significant digit to be returned. Leading zeros in a number are displayed as zeros.

$
Prefix: puts a dollar sign in front of a number.

B
Prefix: returns a zero value as blanks, even if the 0 format element is used to show leading zeros.

MI
Suffix: places a minus sign (-) after the number if it is negative. If the number is positive, a trailing space is placed after the number.

S
Prefix: places a plus sign (+) in front of a positive number and a minus sign (-) in front of a negative number.

PR
Suffix: places angle brackets (< and >) around a negative value. Positive values are given a leading and a trailing space.

D
Specifies the location of the decimal point in the returned value. All format elements to the left of the D format the integer component of the value. All format elements to the right of the D format the fractional part of the value. The character used for the decimal point is determined by the database parameter NLS_NUMERIC_CHARACTERS.

G
Specifies the location of the group separator (for example, a comma to separate thousands as in 6,754) in the returned value. The character used for the group separator is determined by the database parameter NLS_NUMERIC_CHARACTERS.

C
Specifies the location of the ISO currency symbol in the returned value. The NLS_ISO_CURRENCY parameter specifies the ISO currency symbol.

L
Specifies the location of the local currency symbol (such as $) in the return value. The NLS_CURRENCY parameter specifies the local currency symbol.

, (comma)
Places a comma into the return value. This comma is used as a group separator (see the G format element).

. (period)
Places a period into the return value. This period is used as a decimal point (see the D format element).

V
Multiplies the number to the left of the V in the format model by 10 raised to the nth power, where n is the number of 9s found after the V in the format model.

EEEE
Suffix: specifies that the value be returned in scientific notation.

RN or rn
Specifies that the return value be converted to upper- or lowercase Roman numerals. The range of valid numbers for conversion to Roman numerals is between 1 and 3999. The value must be an integer. RN returns uppercase Roman numerals, while rn returns lowercase Roman numerals.

FM
Prefix: removes any leading or trailing blanks from the return value.

TM
Prefix: returns a number using the minimum number of characters. TM stands for "text minimum." Follow TM with one 9 if you want a regular, decimal notation (the default). Follow TM with one E if you want scientific notation.

U
Places a Euro symbol at the specified location. The NLS_DUAL_CURRENCY parameter controls the character returned by this format element.

X
Returns a number in hexadecimal value. You can precede this element with 0s to return leading zeros or with FM to trim leading and trailing blanks. X cannot be used in combination with any other format elements.

[tgm78]

C:Specifies the location of the local currency symbol (such as $) in the return value. The NLS_CURRENCY parameter specifies the local currency symbol.

9:Each 9 represents a significant digit to be returned. Leading zeros in a number are displayed as blanks.

所以意思是最多为5位数字，以本地货币符号作为前缀

正解

[Eric_1999]

SQL> select to_char(1123234,'c9999999') from dual
2 /

TO_CHAR(112
-----------
USD1123234

SQL>

转化成系统货币形式。

[伍子V5]

将数字转化为人民币(CNY)字符串

[start_again]

将普通数值类型转换成人民币格式的数值