oracle自定义函数积累

1、判断是否为数字
create or replace function IS_NUMBER(string VARCHAR2) return integer is
  begin
   if(LENGTH(TRIM(TRANSLATE(string, ' +-.0123456789', ' '))) is null ) then
      return 1;
   else
      return 0;
   end if;
  end IS_NUMBER;


2、判断是否为日期
CREATE OR REPLACE FUNCTION is_date(parmin VARCHAR2) RETURN NUMBER IS
  val DATE;
BEGIN
  val := TO_DATE(NVL(parmin, 'a'), 'yyyy-mm-dd hh24:mi:ss');
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;


3、判断是否为身份证号码
create or replace function IS_IDCard
(p_IDcard varchar2)
return boolean
is
IDcardlen integer default 0;
begin
  IDcardlen:=Length(p_IDcard);
  if (IDcardlen = 18 and IS_NUMBER(SubStr(p_IDcard, 1, IDcardlen-1))=1
                     and IS_DATE(substr(p_IDcard,7,8))=1)
                    or
     (IDcardlen = 15 and IS_NUMBER(SubStr(p_IDcard, 1, IDcardlen))=1
                     and IS_DATE ('19' || subsTR(p_IDcard,7,6))=1)
  then
      return TRUE;
  ELSE
      return FALSE;
  end if;
end IS_IDCard;




4、从身份证号中获取年龄
create or replace function get_age
(workerid in varchar2)
return varchar2
is
agevalue varchar2(10);
begin
  if(length(trim(workerid))=18) then
     select (to_char(sysdate,'yyyy')-to_char(substr(workerid,7,4))) into agevalue from dual;
  else if (length(trim(workerid))=15) then
    select (to_char(sysdate,'yyyy')-to_char('19'||substr(workerid,7,2))) into agevalue from dual;
  else
    agevalue:='0';
  end if;
  end if;
  return agevalue;
end;


5、从身份证号码中获取性别
create or replace function get_Sex(p_IDCard varchar2)
return varchar2
is
IDCardLen integer;
begin
  IDCardLen:=length(p_IDCard);
  if IS_IDCARD(p_IDCard)=false then
    return null;
  end if;
  if IDCardLen=18 and substr(p_IDCard,17,1) in (1,3,5,7,9) then
    return ('男');
  end if;
  if IDCardLen=18 and substr(p_IDCard,17,1) in (2,4,6,8,0) then
    return ('女');
  end if;
   if IDCardLen=15 and substr(p_IDCard,15,1) in (1,3,5,7,9) then
    return ('男');
  end if;
  if IDCardLen=15 and substr(p_IDCard,15,1) in (2,4,6,8,0) then
    return ('女');
  end if;

end get_Sex;

不断更新中……