oracle人民币大写转换

CREATE OR REPLACE FUNCTION num2rmb(je NUMBER) RETURN VARCHAR2 IS
result varchar2(100);
i pls_integer;
snum varchar2(20) := ltrim(replace(to_char(abs(je), '9999999999999990.99'), '.'));
len pls_integer := length(snum);
sch varchar2(40) := '零壹贰叁肆伍陆柒捌玖';
sjin varchar2(150) := '分角圆拾佰仟万拾佰仟亿拾佰仟万拾佰仟';
srmb varchar2(100):= '';
num pls_integer;
jin varchar(2);
s_num pls_integer := 0; --'0'起始位置
e_num pls_integer := 0; --'0'结束位置
begin
for i in 1..len loop
num := to_number(substr(snum, i, 1));
if num <> 0 then --非'0'时处理：
if s_num = 0 then
srmb := srmb||substr(sch, num + 1, 1)||substr(sjin, len - i + 1, 1); --前面字符非'0', 正常联接...
else
srmb := srmb|| --否则：
case when s_num = e_num then --前面只有一个'0'
case s_num when 7 then '万' --只处理进位
when 11 then '亿'
when 15 then '万' end
when e_num < 12 then --否则(多个'0'处理)
case when s_num < 7 then '' --万以内..
when s_num < 11 then case when e_num < 8 and s_num < 10 then '万' end
when s_num < 15 then case when e_num < 12 then '亿' end
else '万亿' end
when e_num < 16 and s_num > 14 then '万' end||
case when s_num > 3 and e_num < 3 then '圆零'
when e_num = 3 then '圆'
when e_num not in (7, 11, 15) or s_num - e_num > 2 then '零' end;
srmb := srmb||substr(sch, num + 1, 1)||substr(sjin, len - i + 1, 1);
end if;
s_num := 0;
e_num := 0;
else if s_num = 0 then --当s_num = 0时'0'串起始，
s_num := len - i + 1; --记录开始
e_num := s_num; --各结整位置。
else
e_num := len - i + 1; --否则新的结整位置。
end if;
end if;
end loop;
if s_num <>0 then --此时以'0'结尾
srmb := srmb||
case when s_num = len then '零圆整' --全'0'串，加...
when s_num = 1 or s_num = 2 then '整' --分(1)，角(2)后加...
when s_num < 7 or s_num = 10 then '圆整'
when s_num <11 then '万圆整'
when s_num <15 then '亿圆整'
else '万亿圆整' end;
else
srmb := srmb||'整'; --不以'0'结尾，加...
end if;
if je < 0 then
srmb:='负'||srmb;
end if;
RETURN srmb;
end num2rmb;

SQL> select num2rmb(12.345) as 大写 from dual;

大写
---------------------------------
壹拾贰圆叁角伍分整

-------------------------------------------------------练习1--------------------------------------------
SQL> create table tb_shop_sell
  2  (
  3  id number primary key,
  4  shopid varchar2(10) not null,
  5  price number(6,2) not null,
  6  quantity number(2) not null
  7  );

Table created

SQL> insert into tb_shop_sell values(1,'s001',1200,1);

1 row inserted

SQL> insert into tb_shop_sell values(2,'s002',1300,1);

1 row inserted

SQL> insert into tb_shop_sell values(3,'s003',23.4,3);

1 row inserted

SQL> insert into tb_shop_sell values(4,'s004',73.4,2);

1 row inserted

SQL> insert into tb_shop_sell values(5,'s005',23,1);

1 row inserted

SQL> create view v_sell
  2  as
  3  select
  4  shopid 商品编号,
  5  price 商品单价,
  6  quantity 数量,
  7  price*quantity 销售总额,
  8  num2rmb(price*quantity) 销售总额大写
  9  from tb_shop_sell;

SQL> select * from v_sell;

商品编号   商品单价 数量   销售总额 销售总额大写
---------- -------- ---- ---------- ---------------------------------
s001        1200.00    1       1200 壹仟贰佰圆整
s002        1300.00    1       1300 壹仟叁佰圆整
s003          23.40    3       70.2 柒拾圆贰角整
s004          73.40    2      146.8 壹佰肆拾陆圆捌角整
s005          23.00    1         23 贰拾叁圆整