Plsql lowercase amount to uppercase amount function
Create or replace function comm.F_upper_money (p_num in number default null)
Return nvarchar2 is
/ * Ver:1.0 Created By xsb on 2003-8-18 For:
Convert amount numbers (units) to uppercase (using a low-to-high algorithm)
The integer part of the number must not exceed 16 digits and can be negative.
Ver:1.1 Modified By xsb on 2003-8-20 For: single-digit processing is also placed in the For loop.
Ver:1.2 Modified By xsb on 2003-8-22 For: without whole words after division.
Ver:1.3 Modified By xsb on 2003-8-28 For: refine the test cases.
Test case:
SET HEAD OFF
SET FEED OFF
Select'='if there is no parameter | | f_upper_money () from dual
Select 'null=' | | f_upper_money (null) from dual
Select '0room' | | f_upper_money (0) from dual
Select '0.01 percent' | | f_upper_money (0.01) from dual
Select '0.126clients' | | f_upper_money (0.126) from dual
Select '01.234room' | | f_upper_money (01.234) from dual
Select'10 percent'| | f_upper_money (10) from dual
Select '100.1 percent' | | f_upper_money (100.1) from dual
Select '100.01percent' | | f_upper_money (100.01) from dual
Select '10000customers' | | f_upper_money (10000) from dual
Select '10012.12 percent' | | f_upper_money (10012.12) from dual
Select '20000020.01percent' | | f_upper_money (20000020.01) from dual
Select '3040506708.901clients' | | f_upper_money (3040506708.901) from dual
Select '40005006078.001neighbors' | | f_upper_money (40005006078.001) from dual
Select'- 123456789.98 percent'| | 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: = to_char (abs (round (p_num, 2));-- convert a number to a character of 2 decimal places (positive)
Num_left nvarchar2;-the number to the left of the decimal point
Num_right nvarchar2 (2);-- the number to the right of the decimal point
Str1 nchar (10): = '012'-- uppercase numbers
Str2 nchar (16):-- digits (from low to high)
Num_pre number (1): = 1;-- number in the previous digit
Num_current number (1);-- number in the current bit
Num_count number: = 0;-- current digits
Begin
If p_num is null then
Return null
End if;-returns null when converting digits to null
Select to_char (nvl (substr (to_char (num_round))
one,
Decode (instr (to_char (num_round),'.')
0
Length (num_round)
Instr (to_char (num_round),'.')-1))
0))
Into num_left
From dual;-get the number to the left of the decimal point
Select substr (to_char (num_round))
Decode (instr (to_char (num_round),'.')
0
Length (num_round) + 1
Instr (to_char (num_round),'.') + 1)
2)
Into num_right
From dual;-get the number to the right of the decimal point
If length (num_left) > 16 then
Return'*'
End if;-when the integer portion of a number exceeds 16 bits
-- using a low-to-high algorithm to first deal with the number to the right of the decimal point
If length (num_right) = 2 then
If to_number (substr (num_right, 1,1)) = 0 then
Result: = 'zero' | |
Substr (str1, to_number (substr (num_right, 2,1)) + 1,1) | | 'points'
Else
Result: = substr (str1, to_number (substr (num_right, 1,1)) + 1,1) | | 'corner' | |
Substr (str1, to_number (substr (num_right, 2,1)) + 1,1) | | 'points'
End if
Elsif length (num_right) = 1 then
Result: = substr (str1, to_number (substr (num_right, 1,1)) + 1,1) | | 'Corner trim'
Else
Result: = 'whole'
End if
-- then deal with the number to the left of the decimal point
For i in reverse 1.. Length (num_left) loop
-- (from low to high)
Num_count: = num_count + 1;-- current digits
Num_current: = to_number (substr (num_left, I, 1));-- the number in the current bit
If num_current > 0 then
-- if the number in the current bit is not 0, it will be processed normally.
Result: = substr (str1, num_current + 1,1) | |
Substr (str2, num_count, 1) | | result
Else
-- when the current digit is 0
If mod (num_count-1,4) = 0 then
The current bit is yuan, ten thousand or hundreds of millions of hours.
Result: = substr (str2, num_count, 1) | | result
Num_pre: = 0;-- Yuan, 10 million, 100 million are not allowed to add zero
End if
If num_pre > 0 or length (num_left) = 1 then
-- when the previous digit is not 0 or has only one digit
Result: = substr (str1, num_current + 1,1) | | result
End if
End if
Num_pre: = num_current
End loop
If p_num
