Shulou(Shulou.com)06/03 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.