How to realize the transformation of people's coins in Oracle

Oracle

How to realize the transformation of people's coins in Oracle

Author: ccbzzp

Oracle is very powerful in the reality of the report, especially in reality applications that use the issue of the size of the people's currency, I write a letter to you to introduce briefly, I hope to discuss with you, so as to move together! Common development!

1. function Dx_money ()

CREATE FUNCTION Dx_money
(
Money in number
)
Return VARCHAR2 as V_money VARCHAR2 (150);
Rv_money0 VARCHAR2 (20);
Rv_money1 VARCHAR2 (4);
Rv_money2 VARCHAR2 (4);
V_money0 VARCHAR2 (20);
V_money1 VARCHAR2 (4);
V_money2 VARCHAR2 (4);
V_money3 VARCHAR2 (4);
V_money4 VARCHAR2 (4);
V_money5 VARCHAR2 (4);
V_money6 VARCHAR2 (4);
V_money7 VARCHAR2 (4);
V_money8 VARCHAR2 (4);
V_money9 VARCHAR2 (4);
V_money10 VARCHAR2 (4);
V_money11 VARCHAR2 (4);
V_money12 VARCHAR2 (4);
Rpv_money1 VARCHAR2 (4);
Rpv_money2 VARCHAR2 (4);
Pv_money0 VARCHAR2 (14);
Pv_money1 VARCHAR2 (4);
Pv_money2 VARCHAR2 (4);
Pv_money3 VARCHAR2 (4);
Pv_money4 VARCHAR2 (4);
Pv_money5 VARCHAR2 (4);
Pv_money6 VARCHAR2 (4);
Pv_money7 VARCHAR2 (4);
Pv_money8 VARCHAR2 (4);
Pv_money9 VARCHAR2 (4);
Pv_money10 VARCHAR2 (4);
Pv_money11 VARCHAR2 (4);
Pv_money12 VARCHAR2 (4);

BEGIN
V_money0:=ltrim (RTRIM (To_char (FLOOR)));
Rv_money0:=ltrim (RTRIM (To_char (FLOOR (money*100)));
Rv_money1:=substr (Rv_money0,length (RV_MONEY0), 1);
IF LENGTH (rv_money0) >1 THEN
Rv_money2:=substr (Rv_money0,length (RV_MONEY0) -1,1);
ELSE
rv_money2:= ' 0 ';
End IF;
V_money1:=substr (V_money0,length (V_MONEY0), 1);
IF LENGTH (v_money0) -1>0 THEN
V_money2:=substr (V_money0,length (V_MONEY0) -1,1);
ELSE v_money2:=0;
End IF;
IF LENGTH (v_money0) -2>0 THEN
V_money3:=substr (V_money0,length (V_MONEY0) -2,1);
ELSE v_money3:=0;
End IF;
IF LENGTH (v_money0) -3>0 THEN
V_money4:=substr (V_money0,length (V_MONEY0) -3,1);
ELSE v_money4:=0;
End IF;
IF LENGTH (v_money0) -4>0 THEN
V_money5:=substr (V_money0,length (V_MONEY0) -4,1);
ELSE v_money5:=0;
End IF;
IF LENGTH (v_money0) -5>0 THEN
V_money6:=substr (V_money0,length (V_MONEY0) -5,1);
ELSE v_money6:=0;
End IF;
IF LENGTH (v_money0) -6>0 THEN
V_money7:=substr (V_money0,length (V_MONEY0) -6,1);
ELSE v_money7:=0;
End IF;
IF LENGTH (v_money0) -7>0 THEN
V_money8:=substr (V_money0,length (V_MONEY0) -7,1);
ELSE v_money8:=0;
End IF;
IF LENGTH (v_money0) -8>0 THEN
V_money9:=substr (V_money0,length (V_MONEY0) -8,1);
ELSE v_money9:=0;
End IF;
IF LENGTH (v_money0) -9>0 THEN
V_money10:=substr (V_money0,length (V_MONEY0) -9,1);
ELSE v_money10:=0;
End IF;
IF LENGTH (v_money0) -10>0 THEN
V_money11:=substr (V_money0,length (V_MONEY0) -10,1);
ELSE v_money11:=0;
End IF;
IF LENGTH (v_money0) -11>0 THEN
V_money12:=substr (V_money0,length (V_MONEY0) -11,1);
ELSE v_money12:=0;
End IF;

Select DECODE (rv_money1, ' 0 ', ' 0 points ', ' 1 ', ' one point ', ' 2 ', ' 貳 ', ' 3 ', ' participation ', ' 4 ', ' ' Minutes ', ' 5 ', ' The division ', ' "', ' + ', ') ',
' 6 ', ' Land division ', ' 7 ', ' Seven ', ' 8 ', ' Chop ', ' 9 ', ' nine points ' into rpv_money1 from dual;
Select DECODE (Rv_money2, ' 0 ', ' 0 corner ', ' 1 ', ' Corner ', ' 2 ', ' 貳 ', ' 3 ', ' Corner ', ' 4 ', ' Corner ', ' 5 ', ' Cape ', ' + ', ' cape '),
' 6 ', ' Continental Horn ', ' 7 ', ' Qi Kok ', ' 8 ', ' Cape Horn ', ' 9 ', ' nine-point ', into Rpv_money2 from dual;
Select DECODE (v_money1, ' 0 ', ' yuan ', ' 1 ', ' one Yuan ', ' 2 ', ' 貳 Yuan ', ' 3 ', ' Reference ', ' 4 ', ' Yuan ', ' 5 ', ' Wu Yuan ',
' 6 ', ' Continental ', ' 7 ', ' Seven ', ' 8 ', ' grilled Yuan ', ' 9 ', ' Shangyuan ' into pv_money1 from dual;
Select DECODE (V_money2, ' 0 ', ', ', ' 1 ', ' Pick up ', ' 2 ', ' 貳 ', ' 3 ', ' Join ', ' 4 ', ' Pick up ', ' 5 ', ' gather ')
' 6 ', ' Land Pick ', ' 7 ', ' qi-pick ', ' 8 ', ' Pick up ', ' 9 ', ' nine-pick ', into Pv_money2 from dual;
Select DECODE (V_money3, ' 0 ', ', ' 1 ', ' Bai ', ' 2 ', ' 貳 bai ', ' 3 ', ' participation ', ' 4 ', ' Bai ', ' 5 ', ' Wu Bai ',
' 6 ', ' Land Bai ', ' 7 ', ' Qi Bai ', ' 8 ', ' Pa bai ', ' 9 ', ' Jiu Bai ', into the pv_money3 from dual;
Select DECODE (V_money4, ' 0 ', ', ', ' 1 ', ' thousand ', ' 2 ', ' 貳 ', ' 3 ', ' Reference ', ' 4 ', ' Thousand ', ' 5 ', ' million ',
' 6 ', ' Thousand ', ' 7 ', ' Seven ', ' 8 ', ' grilled ', ' 9 ', ' Jiu Qian ' into Pv_money4 from dual;
Select DECODE (V_money5, ' 0 ', ' million ', ' 1 ', ' million ', ' 2 ', ' 貳 ', ' 3 ', ' participation ', ' 4 ', ' All ', ' 5 ', ' Wu '),
' 6 ', ' Land ', ' 7 ', ' Seven ', ' 8 ', ' Grilled million ', ' 9 ', ' nine million ' into the pv_money5 from dual;
Select DECODE (V_money6, ' 0 ', ', ', ' 1 ', ' Pick up ', ' 2 ', ' 貳 ', ' 3 ', ' Join ', ' 4 ', ' Pick up ', ' 5 ', ' gather ')
' 6 ', ' Land Pick ', ' 7 ', ' qi-pick ', ' 8 ', ' Pick up ', ' 9 ', ' nine-pick ', into Pv_money6 from dual;
Select DECODE (V_money7, ' 0 ', ', ' 1 ', ' Bai ', ' 2 ', ' 貳 bai ', ' 3 ', ' participation ', ' 4 ', ' Bai ', ' 5 ', ' Wu Bai ',
' 6 ', ' Land Bai ', ' 7 ', ' Qi Bai ', ' 8 ', ' Pa bai ', ' 9 ', ' Jiu Bai ', into the pv_money7 from dual;
Select DECODE (V_money8, ' 0 ', ', ', ' 1 ', ' thousand ', ' 2 ', ' 貳 ', ' 3 ', ' Reference ', ' 4 ', ' Thousand ', ' 5 ', ' million ',
' 6 ', ' Thousand ', ' 7 ', ' Seven ', ' 8 ', ' grilled ', ' 9 ', ' Jiu Qian ' into pv_money8 from dual;
Select DECODE (V_money9, ' 0 ', ' billions ', ' 1 ', ' billion ', ' 2 ', ' 貳 billion ', ' 3 ', ' participation ', ' 4 ', ' billion ', ' 5 ', ' $ $ '),
' 6 ', ' Land billion ', ' 7 ', ' Seven Billion ', ' 8 ', ' grilled billion ', ' 9 ', ' nine billion ' into the pv_money9 from dual;
Select DECODE (V_money10, ' 0 ', ', ', ' 1 ', ' Pick up ', ' 2 ', ' 貳 ', ' 3 ', ' Join ', ' 4 ', ' Pick up ', ' 5 ', ' gather ')
' 6 ', ' Land Pick ', ' 7 ', ' qi-pick ', ' 8 ', ' Pick up ', ' 9 ', ' nine-pick ', into pv_money10 from dual;
Select DECODE (V_money11, ' 0 ', ', ' 1 ', ' Bai ', ' 2 ', ' 貳 bai ', ' 3 ', ' participation ', ' 4 ', ' Bai ', ' 5 ', ' Wu Bai ',
' 6 ', ' Land Bai ', ' 7 ', ' Qi Bai ', ' 8 ', ' Pa bai ', ' 9 ', ' Jiu Bai ', into the pv_money11 from dual;
Select DECODE (V_money12, ' 0 ', ', ', ' 1 ', ' thousand ', ' 2 ', ' 貳 ', ' 3 ', ' Reference ', ' 4 ', ' Thousand ', ' 5 ', ' million ',
' 6 ', ' Thousand ', ' 7 ', ' Seven ', ' 8 ', ' grilled ', ' 9 ', ' Jiu Qian ' into pv_money12 from dual;
v_money:=pv_money12| | pv_money11| | pv_money10| | pv_money9| | pv_money8| |
pv_money7| | pv_money6| | pv_money5| | pv_money4| | pv_money3| | pv_money2| | pv_money1| |
rpv_money2| | Rpv_money1;
return (V_money);
End;

2. The following situations are performed:

Sql*plus:release 9.2.0.1.0-production on Tuesday July 29 15:03:36 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connections to:
Oracle8i Enterprise Edition Release 8.1.6.1.0-production
With the partitioning option
Jserver Release 8.1.6.0.0-production

Sql> Select Dx_money (324450567.89) from dual;

Dx_money (324450567.89)
--------------------------------------------------------------------------------
For billions of 貳, Wu Bai land to pick up nine points

3. The maximum number of words in this example is 999999999999.99

4. The above function is more simple to understand, the language is not necessarily simple, welcome to join the discussion!