oracle常用方法...待续

上了年纪，记性差了，平时工作中用到了还是记下来吧~欢迎分享更好的实现方法

功能列表：

1.核对该人员是否已退休(f_isRetired)

2.金额转成大写金额(f_numbig)

3.去字符串中间的空格(f_trim)

4....待续

 

—————————————————————————————————————————————————————

1.核对人员是否已退休

create or replace function F_isRetired(
--核对该人员是否已退休
in_peopleid in varchar2--人员内码
) return varchar2 is
  v_age number;--人员年龄
  v_sex varchar2(2);--性别

begin
     select aac004, trunc( months_between(to_date(to_char(sysdate,'yyyyMM'),'yyyyMM'),to_date(to_char(birthday,'yyyyMM'),'yyyyMM'))/12)
            into v_sex,v_age from person where ac01.peopleid =in_peopleid;--根据人员内码取性别和年龄
     if v_sex = '1' then--男
        if age >= '60' then
           return '1';--1:已退休 0未退休
        end if;
     else
        if age >= '50' then
          return '1';--1:已退休 0未退休
         end if;
     end if;


  return '0';--1:已退休 0未退休
  exception when others then
  return '0';
end F_isRetired;

 

2.金额转成大写金额

CREATE OR REPLACE Function f_numbig(Money In Number) Return Varchar2 Is
  strYuan       Varchar2(150);
  strYuanFen    Varchar2(152);
  numLenYuan    Number;
  numLenYuanFen Number;
  strRstYuan    Varchar2(600);
  strRstFen     Varchar2(200);
 strRst        Varchar2(800);
  Type typeTabMapping Is Table Of Varchar2(2) Index By Binary_Integer;
  tabNumMapping  typeTabMapping;
  tabUnitMapping typeTabMapping;
  numUnitIndex   Number;
  i              Number;
  j              Number;
  charCurrentNum Char(1);
Begin
  If Money Is Null Then
   Return Null;
 End If;
  strYuan := TO_CHAR(FLOOR(Money));
  If strYuan = '0' Then
    numLenYuan := 0;
    strYuanFen := lpad(TO_CHAR(FLOOR(Money * 100)), 2, '0');
  Else
    numLenYuan := length(strYuan);
    strYuanFen := TO_CHAR(FLOOR(Money * 100));
  End If;
 If strYuanFen = '0' Then
   numLenYuanFen := 0;
  Else
    numLenYuanFen := length(strYuanFen);
  End If;
  If numLenYuan = 0 Or numLenYuanFen = 0 Then
    strRst := '零圆整';
    Return strRst;
  End If;
  tabNumMapping(0) := '零';
 tabNumMapping(1) := '壹';
  tabNumMapping(2) := '贰';
 tabNumMapping(3) := '叁';
  tabNumMapping(4) := '肆';
  tabNumMapping(5) := '伍';
  tabNumMapping(6) := '陆';
  tabNumMapping(7) := '柒';
  tabNumMapping(8) := '捌';
  tabNumMapping(9) := '玖';
  tabUnitMapping(-2) := '分';
  tabUnitMapping(-1) := '角';
  tabUnitMapping(1) := '';
 tabUnitMapping(2) := '拾';
  tabUnitMapping(3) := '佰';
  tabUnitMapping(4) := '仟';
  tabUnitMapping(5) := '万';
  tabUnitMapping(6) := '拾';
 tabUnitMapping(7) := '佰';
  tabUnitMapping(8) := '仟';
 tabUnitMapping(9) := '亿';
 For i In 1 .. numLenYuan Loop
    j:= numLenYuan - i + 1;
   numUnitIndex := Mod(i, 8);
   If numUnitIndex = 0 Then
      numUnitIndex := 8;
   End If;
    If numUnitIndex = 1 And i > 1 Then
      strRstYuan := tabUnitMapping(9) || strRstYuan;
   End If;
    charCurrentNum := substr(strYuan, j, 1);
   If charCurrentNum <> 0 Then
     strRstYuan := tabNumMapping(charCurrentNum) ||
                    tabUnitMapping(numUnitIndex) || strRstYuan;
    Else
     If (i = 1 Or i = 5) Then
       If substr(strYuan, j - 3, 4) <> '0000' Then
          strRstYuan := tabUnitMapping(numUnitIndex) || strRstYuan;
       End If;
      Else
       If substr(strYuan, j + 1, 1) <> '0' Then
          strRstYuan := tabNumMapping(charCurrentNum) || strRstYuan;
        End If;
      End If;
    End If;
  End Loop;
 For i In -2 .. -1 Loop
    j := numLenYuan - i;
    charCurrentNum := substr(strYuanFen, j, 1);
   If charCurrentNum <> '0' Then
      strRstFen := tabNumMapping(charCurrentNum) || tabUnitMapping(i) || strRstFen;
      End If;
       End Loop;
     If strRstYuan Is Not Null Then
     strRstYuan := strRstYuan || '圆';
       End If;
     If strRstFen Is Null Then
       strRstYuan := strRstYuan || '整';
       Elsif length(strRstFen) = 2 And substr(strRstFen, 2) = '角' Then
     strRstFen := strRstFen || '整';
End If;
strRst := strRstYuan || strRstFen;
--strRst := Replace(strRst, '亿零', '亿');
--strRst := Replace(strRst, '万零', '万');
Return strRst;
End f_numbig;

 

 3.去字符串中间的空格

create or replace function f_trim
( 
--除去字符串中的空格
v_str in varchar2
)
return varchar2 is  
v_name varchar2(100);
i number(4);
j number(4);
begin               

j := length(v_str); 
i := 1;
v_name := '';
while i <= j loop
	if substr(v_str,i,1) <> ' ' then
		v_name := v_name||substr(v_card,i,1);
	end if;	
	i := i + 1;
end loop;

return v_name;
end f_trim;

 

 4.....待续