Posted on 2010-07-24 16:19
断点 阅读(297)
评论(0) 编辑 收藏 所属分类:
Oracle DBA
--函数
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal < 2000) then
return 0.10;
elsif(v_sal < 2750) then
return 0.15;
else
return 0.20;
end if;
end;
数据库定义的函数money_to_chinese ，把数字转换正中文输出。
create or replace function money_to_chinese(money in VARCHAR2)
return varchar2 is
c_money VARCHAR2(12);
m_string VARCHAR2(60) := '分角圆拾佰仟万拾佰仟亿';
n_string VARCHAR2(40) := '壹贰叁肆伍陆柒捌玖';
b_string VARCHAR2(80);
n CHAR;
len NUMBER(3);
i NUMBER(3);
tmp NUMBER(12);
is_zero BOOLEAN;
z_count NUMBER(3);
l_money NUMBER;
l_sign VARCHAR2(10);
BEGIN
l_money := abs(money);
IF money < 0 THEN
l_sign := '负' ;
ELSE
l_sign := '';
END IF;
tmp := round(l_money, 2) * 100;
c_money := rtrim(ltrim(to_char(tmp, '999999999999')));
len := length(c_money);
is_zero := TRUE;
z_count := 0;
i := 0;
WHILE i < len LOOP
i := i + 1;
n := substr(c_money, i, 1);
IF n = '0' THEN
IF len - i = 6 OR len - i = 2 OR len = i THEN
IF is_zero THEN
b_string := substr(b_string, 1, length(b_string) - 1);
is_zero := FALSE;
END IF;
IF len - i = 6 THEN
b_string := b_string || '万';
END IF;
IF len - i = 2 THEN
b_string := b_string || '圆';
END IF;
IF len = i THEN
IF (len = 1) THEN
b_string := '零圆整';
ELSE
b_string := b_string || '整';
END IF;
END IF;
z_count := 0;
ELSE
IF z_count = 0 THEN
b_string := b_string || '零';
is_zero := TRUE;
END IF;
z_count := z_count + 1;
END IF;
ELSE
b_string := b_string || substr(n_string, to_number(n), 1) ||
substr(m_string, len - i + 1, 1);
z_count := 0;
is_zero := FALSE;
END IF;
END LOOP;
b_string := l_sign || b_string ;
RETURN b_string;
exception
--异常处理
WHEN OTHERS THEN
RETURN(SQLERRM);
END;