Oracle UDF Accumulation

Oracle custom function accumulation 1. Determine if it is a number www.2cto.com 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. Determine whether the date is www.2cto.com 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. Determine whether the ID card number is create or replace function IS_IDCard (p_IDcard varchar2) return booleanisIDcardlen 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; www.2cto.com 4. Get the age create or replace function get_age (workerid in varchar2) return varchar2isagevalue varchar2 (10) from the ID card number ); 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; return agevalue; end; www.2cto.com 5. Obtain the Gender create or replace function get_Sex (p_IDCard varchar2) return varchar2isIDCardLen integer from the ID card number; 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 (, 9) then return ('male'); end if; if IDCardLen = 18 and substr (p_IDCard, 17,1) in (, 0) then return ('femal'); end if; if IDCardLen = 15 and substr (p_IDCard, 15, 1) in (, 9) then return ('male'); end if; if IDCardLen = 15 and substr (p_IDCard,) in (, 0) then return ('female '); end if; end get_Sex;