自己写的存储过程,先在此备份下。
--
Author : DSJ
-- Created : 2008-2-27 10:22:51
-- Purpose : 数字转换成大写(205.23 --> 二百零五点二三 )
create or replace procedure sp_digit2speech
(
digit in float ,
speech out varchar2
) is
begin
declare
left_Str varchar2 ( 100 ); -- -左边字符串
right_Str varchar2 ( 100 ); -- -右边字符串
unit_str varchar2 ( 100 ); -- -位
number_str varchar2 ( 100 );
vT varchar2 ( 1000 );
vN Number ;
left_length number ; -- -左边字符长度
i number ;
n number ;
m number ;
t number ;
new_digit number ;
-- 12988744.00
--
begin
left_Str : = '' ;
right_Str : = '' ;
unit_str : = ' 十百千万十百千亿十百千 ' ;
number_str : = ' 一二三四五六七八九 ' ;
m : = 0 ;
new_digit : = round (digit, 2 );
i : = 1 ;
vT : = '' ;
if instr(to_char(new_digit), ' . ' ) > 0 then
left_Str : = substr(to_char(new_digit), 1 , instr(to_char(new_digit), ' . ' ) - 1 );
right_str : = substr(to_char(new_digit), instr(to_char(new_digit), ' . ' ) + 1 , length(to_char(new_digit)) - instr(to_char(new_digit), ' . ' ));
else
left_Str : = to_Char(new_digit) ;
right_str : = '' ;
end if ;
left_length : = length(left_Str);
n : = left_length;
while i < left_length loop
vN : = to_number(substr(left_str, i, 1 ));
if vN > 0 then
vT : = vT || substr(number_str, vN, 1 ) || substr(unit_str, n - 1 , 1 );
m : = 1 ;
t : = 0 ;
else -- begin 零
begin
if m > 0 then
vT : = vT || ' 零 ' ;
else
begin
if n < 5 and t > 1 then
vT : = substr(vT, 1 , length(vT) - 1 );
-- vT := vT;
end if ;
end ;
t : = t + 1 ;
end if ;
if n = 5 then
vT : = substr(vT, 1 , length(vT) - 1 );
vT : = vT || ' 万 ' ;
end if ;
m: = 0 ;
end ;
end if ; -- end 零
-- dbms_output.put_line(to_char(n) || ', ' || substr(left_str, 1, i) || ' = ' || vT);
i : = i + 1 ;
n : = n - 1 ;
end loop;
vN : = to_number(substr(left_str, left_length, 1 ));
if vN > 0 then
vT : = vT || substr(Number_str, vN, 1 );
end if ;
if length(right_str) > 0 then
vT : = vT || ' 点 ' ;
i : = 1 ;
while i <= length(right_str) loop
vN : = to_number(substr(right_str, i, 1 ));
vT : = vT || substr(number_str, vN, 1 ) ;
i : = i + 1 ;
end loop;
end if ;
-- speech := 'number = ' || to_char(digit) || ' vt=' || vt;
speech : = vt;
end ;
end sp_digit2speech;
-- Created : 2008-2-27 10:22:51
-- Purpose : 数字转换成大写(205.23 --> 二百零五点二三 )
create or replace procedure sp_digit2speech
(
digit in float ,
speech out varchar2
) is
begin
declare
left_Str varchar2 ( 100 ); -- -左边字符串
right_Str varchar2 ( 100 ); -- -右边字符串
unit_str varchar2 ( 100 ); -- -位
number_str varchar2 ( 100 );
vT varchar2 ( 1000 );
vN Number ;
left_length number ; -- -左边字符长度
i number ;
n number ;
m number ;
t number ;
new_digit number ;
-- 12988744.00
--
begin
left_Str : = '' ;
right_Str : = '' ;
unit_str : = ' 十百千万十百千亿十百千 ' ;
number_str : = ' 一二三四五六七八九 ' ;
m : = 0 ;
new_digit : = round (digit, 2 );
i : = 1 ;
vT : = '' ;
if instr(to_char(new_digit), ' . ' ) > 0 then
left_Str : = substr(to_char(new_digit), 1 , instr(to_char(new_digit), ' . ' ) - 1 );
right_str : = substr(to_char(new_digit), instr(to_char(new_digit), ' . ' ) + 1 , length(to_char(new_digit)) - instr(to_char(new_digit), ' . ' ));
else
left_Str : = to_Char(new_digit) ;
right_str : = '' ;
end if ;
left_length : = length(left_Str);
n : = left_length;
while i < left_length loop
vN : = to_number(substr(left_str, i, 1 ));
if vN > 0 then
vT : = vT || substr(number_str, vN, 1 ) || substr(unit_str, n - 1 , 1 );
m : = 1 ;
t : = 0 ;
else -- begin 零
begin
if m > 0 then
vT : = vT || ' 零 ' ;
else
begin
if n < 5 and t > 1 then
vT : = substr(vT, 1 , length(vT) - 1 );
-- vT := vT;
end if ;
end ;
t : = t + 1 ;
end if ;
if n = 5 then
vT : = substr(vT, 1 , length(vT) - 1 );
vT : = vT || ' 万 ' ;
end if ;
m: = 0 ;
end ;
end if ; -- end 零
-- dbms_output.put_line(to_char(n) || ', ' || substr(left_str, 1, i) || ' = ' || vT);
i : = i + 1 ;
n : = n - 1 ;
end loop;
vN : = to_number(substr(left_str, left_length, 1 ));
if vN > 0 then
vT : = vT || substr(Number_str, vN, 1 );
end if ;
if length(right_str) > 0 then
vT : = vT || ' 点 ' ;
i : = 1 ;
while i <= length(right_str) loop
vN : = to_number(substr(right_str, i, 1 ));
vT : = vT || substr(number_str, vN, 1 ) ;
i : = i + 1 ;
end loop;
end if ;
-- speech := 'number = ' || to_char(digit) || ' vt=' || vt;
speech : = vt;
end ;
end sp_digit2speech;