--判断是否为整数

create or replace function is_number(param VARCHAR2) return NUMBER is

  v_num NUMBER;

begin

  v_num := to_number(NVL(param,'a'));

  RETURN 0;

EXCEPTION

  WHEN OTHERS THEN

    RETURN 1;

end is_number; 

--判断是否为日期类型

create or replace function is_date(param VARCHAR2) return NUMBER  is

  v_date Date;

begin

  v_date := TO_DATE(NVL(param, 'a'), 'yyyy-mm-dd hh24:mi:ss');

  RETURN 0;

EXCEPTION

  WHEN OTHERS THEN

    RETURN 1;

end is_date;

--去除空格

create or replace function getStringValue(text IN varchar2) RETURN varchar2 IS

begin

   RETURN to_single_byte(replace(text, ' ', '')) ;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

     RETURN '';

    WHEN OTHERS THEN

     RETURN '';

end getStringValue;

----

create or replace function getWzkf(wzkf IN varchar2) RETURN varchar2 IS

v_wzkf varchar2(10) ;

begin

   --去掉去掉两头空格

   v_wzkf := ltrim(rtrim(wzkf)) ;

   IF v_wzkf = '无'

   THEN

      SELECT REPLACE(v_wzkf,'无','0') into v_wzkf  FROM dual;

   ELSIF  v_wzkf = '/'

   THEN

          SELECT REPLACE(v_wzkf,'/','0') into v_wzkf  FROM dual;

   ELSIF  v_wzkf IS NULL

   THEN

         v_wzkf := '0' ;

   ELSE

          SELECT REPLACE(v_wzkf,'分','') into v_wzkf  FROM dual;

    END IF;

   RETURN v_wzkf ;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

     RETURN '';

    WHEN OTHERS THEN

     RETURN '';

end getWzkf;

---

create or replace function specDate(wzkf IN varchar2) RETURN varchar2 IS

v_wzkf varchar2(10) ;

cLength number;

v_date date ;

begin

    v_wzkf := replace(replace(replace(wzkf,'年', '-'),'月','-'),'日',' ') ;

    v_wzkf := ltrim(rtrim(v_wzkf)) ;

    v_wzkf := replace(v_wzkf,'/','-') ;

    v_wzkf := replace(v_wzkf,'.','-') ;

    select length(v_wzkf) into cLength from dual;

    if(cLength=8 and instr(v_wzkf,'-')<1 )

        THEN

        SELECT substr(v_wzkf, 1,4 )||'-'||substr(v_wzkf, 5,2 )||'-'||substr(v_wzkf, 7,2 ) into v_wzkf FROM dual;

    END IF ;

--    v_date := to_char(to_date(v_wzkf,'yyyy-mm-dd'),'yyyy-mm-dd') ;

    RETURN to_char(to_date(v_wzkf,'yyyy-mm-dd'),'yyyy-mm-dd') ;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

     RETURN  '@@@111';

    WHEN OTHERS THEN

     RETURN  '@@@222';

end specDate;

---

select instr('ewew点e','点'） from dual