The amount of lowercase isotive (lasting for a long time)
Create Or Replace Function F_upper_money (p_num in number default null) Return nvarchar2 is / * ver: 1.0 created by xsb on 2003-8-18 for: converts the amount number (unit) to uppercase (using low to high algorithm) digital integers The portion must not exceed 16 digits, which can be negative. Ver: 1.1 modified by xsb on 2003-8-20 for: bit number processing is also placed in the For loop. Ver: 1.2 modified by xsb on 2003-8-22 for: Do not take a whole word after multiplexing. Ver: 1.3 modified by xsb on 2003-8-28 for: Perfecting test cases.
Test case: set head offset feed offselect 'None parameters =' || f_upper_money () from dual; select 'null =' || f_upper_money (null) from dual; select '0 =' || f_upper_money (0) from DUAL; select '0.01 =' || f_upper_money (0.01) from dual; select '0.126 =' || f_upper_money (0.126) from dual; select '01 .234 = '|| f_upper_money (01.234) from dual; select '10 =' || f_upper_money (10) from dual; select '100.1 =' || f_upper_money (100.1) from dual; select '100.01 =' || f_upper_money (100.01) from dual; select '10000 =' || f_upper_money (10000) from Dual; select ' 10012.12 = '|| f_upper_money (10012.12) from dual; select' 20000020.01 = '|| f_upper_money (20000020.01) from dual; select' 3040506708.901 = '|| f_upper_money (3040506708.901) from dual; select' 40005006078.001 = '|| f_upper_money (40005006078.001 ) from dual; select '-123456789.98 =' || f_upper_money (-123456789.98) from dual; select '123456789123456789.89 =' || f_upper_money (123456789123456789.89) from dual; * / Result nvarchar2 (100); - returns a string num_round nvarchar2 ( 100): = TO_CHAR (ABS (round (p_num, 2))); - convert the number of characters (positive) Num_LEFT NV after the number of decimal points Archar2 (100); - Digital NUM_RIGHT NVARCHAR2 (2) on the left of the decimal point; - Digital Str1 nchar (10) on the right side of the decix point Nchar (10): = 'Zero Ziji Wu Lu 柒捌'; - Digital uppercase str2 nchar ( 16): = 'Yuan picked 10,000 pickers 仟 万'; - Digital digits (from low to high) Num_pre number (1): = 1; - Digital on the front one Num_current Number (1); - Digital Num_Count Number: = 0 on the current location; - Current digital bits beginif p_num is null dam null; end if; - Return nullselect to_char when converting numbers (NVL (Substr) (To_Char (Num_Round), 1, Decode (Instr (TO_CHAR (NUM_ROUND), '.'), 0, Length (Num_Round), INSTR (TO_CHAR (NUM_ROUND), '.') - 1)), 0) INTO NUM_LEFT From Dual;
- Number Select Substr on the left side of the decimation point (to_char (num), decode (in_char (num), '.'), 0, length (num_round) 1, INSTR (TO_CHAR (NUM_ROUND), '.') 1), 2) INTO NUM_RIGHT from Dual; - Number if Length on the right side of the decimal point ife "********** '; END IF; - Digital integer part of more than 16 digits Time - Adopting the digital if Length (Num_Right) = 2 dam = 0 THENF TO_NUMBER (SUBSTR (NUM_RIGHT, 1, 1)) = 0 THEN RESTR (STR1, "= 0. TO_NUMBER (SUBSTR (NUM_RIGHT, 2, 1)) 1,1) || '; Else Result: = Substr (Str1, TO_NUMBER (SUBSTR (Num_Right, 1, 1)) 1, 1) || '|| Substr (STR1, TO_NUMBER (SUBSTR (NUM_RIGHT, 2, 1)) 1,1) ||' = 1; END IF; Elsif Length (NUM_RIGHT) = 1 Then Result: = Substr (Str1, TO_NUMBER (SUBSTR (NUM_RIGHT, 1, 1)) 1, 1) || 'Corner'; Else Result: = 'whole'; end if; - Re-processes the number for I in Reverse 1..LENGTH (Num_left) on the left side of the decimal point LOOP - (from low to high) Num_count: = Num_count 1; - Current numeric number num_current: = to_Number (Substr (Num_left, i, 1)); - Digital IF Num_Current> 0 THEN The current bit number is not 0 in normal processing Result: = Substr (str1, num_current 1, 1) || Substr (str2, num_count, 1) || result; Else - IF MOD (ELSE - IF MOD) Num_count-1, 4) = 0 THEN - The current position is Yuan, 10,000 or million time RESULT: = Substr (str2, num_cou NT, 1) || Result; Num_pre: = 0; - Yuan, Wan, not allowed to add end if; if Num_pre> 0 or length (num_left) = 1 THEN - The last digit is not 0 or RESULT: = Substr (STR1, NUM_CURRENT 1, 1) || Result; end if; end if; end loop; if p_num <0 THEN - conversion number is a negative number, and RESULT: = 'Negative' || Result; Endness; Return Result; Exceptionwhen Others The Raise_Application_ERROR (-20001, 'Digital Conversion Upset Error! '|| SQlerRM); End; - turn this: http://www.itpub.net/240281.html
