How to achieve ORACLE in uppercase conversion of RMB: CCBZZP ORACLE statements in the realization of the function is very powerful, especially in the real applications often used in case conversion of RMB issue, and I write a function to tell you a brief introduction Let me, I hope to discuss with you to make progress! Develop together!
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_money4 varchar2 (4); v_money5 varcha2 (4); v_money6 varchar2 (4); v_money7 varchar2 (4); v_money8 varcha2 (4); v_money9 varcha2 (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 (TORIM (floor * 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 longth (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 longth (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 longth (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 longth (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; endiff;
SELECT DECODE (RV_MONEY1, '0', 'zero ",' 1 ',' 壹 ',' 2 ',' ',' 3 ',' '' '' '' '' '' '' '' '' '5', '分', '6', 'landed', '7', '柒 柒', '8', '分', '9', '玖 分') INTO RPV_MONEY1 from DUAL; Select decode (rv_money2, '0', 'zero angle', '1', '壹 angle', '2', 'angle', '3', 'parade', '4', '肆', '5', 'Wujiao', '6', 'Lands', '7', '柒 柒', '8', 'Corner', '9', '玖 角') INTO RPV_MONEY2 from DUAL; Select decode (v_money1, '0', 'yuan', '1', '元', '2', '元', '3', 'ginseng', '4', '元', ' 5 ',' Wu Yuan ',' 6 ',' Lu Yuan ',' 7 ',' 柒 柒 ',' 8 ',' 元 ',' 9 ',' 元 ') INTO PV_MONEY1 from Dual; SELECT Decode (v_money2, '0', '', '1', '壹 拾', '2', '', '3', 'clockwide', '4', '肆', '5' , 'Wu Q', '6', 'Langer', '7', 'Pickup', '8', 'Pickup', '9', '') INTO PV_MONEY2 from Dual; SELECT DECODE V_money3, '0', '', '1', '壹 佰', '2', '佰', '3', '' '' '' '' '' '' '' '' '' '' '' '' '' Wu Hao ',' 6 ',' Lu Yi ',' 7 ',' 柒 柒 ',' 8 ',' Hey ',' 9 ',' ') INTO PV_MONEY3 from Dual; SELECT DECODE (V_Money4,' 0 ',' ',' 1 ',' 壹 仟 ',' 2 ',' ',' 3 ',' 11 ',' 4 ',' ',' 5 ',' Wu Yi ' , '6', 'Lu Bai', '7', '柒 柒'
, '8', '仟', '9', '玖') INTO PV_MONEY4 from Dual; SELECT DECODE (V_Money5, '0', 'Wan', '1', '壹 万', '2', '10,000', '3', 'Participate 10', '4', '万', '5', 'Wu Wan', '6', 'Luwan', '7', '柒 柒', '8', '万', '9', '万') INTO PV_MONEY5 from Dual; select decode (v_money6, '0', '', '1', '壹 拾', '2', ' Pick up ',' 3 ',' Corporation ',' 4 ',' 拾 ',' 5 ',' Wu Q ',' 6 ',' Land Picking ',' 7 ',' Pickup ',' 8 ',' 拾 ',' 9 ',' ') INTO PV_MONEY6 from Dual; select decode (v_money7,' 0 ',' ',' 1 ',' 壹 佰 ',' 2 ',' 佰 ' , '3', 'Coride', '4', '佰', '5', 'Wu Yi', '6', 'Lu Yi', '7', '柒 柒', '8', ' ',' 9 ',' 玖 佰 ') INTO PV_MONEY7 from Dual; select decode (v_money8,' 0 ',', '1', '壹 ",' 2 ',' ',' 3 ',' Ginseng ',' 4 ',' ',' 5 ',' Wu 仟 ',' 6 ',' Lu Yi ',' 7 ',' 柒 柒 ',' 8 ',' 仟',' 9 ',' 玖 ') INTO PV_MONEY8 from Dual; SELECT DECODE (V_Money9,' 0 ',' billion ',' 1 ',' 壹 ',' 2 ',' 亿 ',' 3 ' , 'Giving a billion', '4', '', '5', 'Wuyi', '6', 'Luo' ',' 7 ',' 柒 柒 ',' 8 ',' ' , '9', '亿') INTO PV_MONEY9 from Dual; SELECT DECODE (V_Money10, '0', '', '1', '壹 壹', '2', '拾', '3', ' Celepse ',' 4 ',' 肆 ','
5 ',' Wu Q ',' 6 ',' Langer ',' 7 ',' Pick up ',' 8 ',' Pickup ',' 9 ',' 拾 ') INTO PV_MONEY10 from Dual; SELECT Decode (v_money11, '0', '', '1', '佰', '2', '佰', '3', 'ginseng', '4', '佰', '5' , 'Wu Yi', '6', 'Lu Yi', '7', '柒 柒', '8', '', '9', '玖') INTO PV_MONEY11 from Dual; SELECT DECODE (V_Money12 '0', '', '1', '3', 'ginsen', '4', '仟', '5', 'Wu仟 ',' 6 ',' Lu Yi ',' 7 ',' 柒 柒 ',' 8 ',' ',' 9 ',' 玖 ') 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 performs the following conditions :
SQL * Plus: Release 9.2.0.1.0 - Production ON Tuesday 29 15:03:36 2003
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connect to: Oracle8i Enterprise Edition Release 8.1.6.1.0 - Productionwith The Partitioning OptionjServer Release 8.1.6.0.0 - Production
SQL> SELECT DX_MONEY (324450567.89) from dual;
DX_MONEY (324450567.89) ---------------------------------------------------------------------------------------------------------------------------- ---------------------------------_Bili 仟 肆 肆 万 万 万 佰 柒 柒 柒Angle
3. Maximum number of this example 999999999999.99
4. The more simple and easy to understand the above functions, the syntax is not necessarily streamlined, welcome everyone to discuss!
