CREATE OR REPLACE FUNCTION to_time(sec IN NUMBER) RETURN VARCHAR2 IS

 /*把秒转成时分秒格式

 auth lzpong 201/09/16

 */

 BEGIN

   RETURN TRUNC(NVL(sec,0)/3600)||':'||SUBSTR(NUMTODSINTERVAL(NVL(sec,0), 'second'),15,5);

 EXCEPTION WHEN OTHERS THEN RETURN '0:00:00';

 END to_time;

 CREATE OR REPLACE FUNCTION isnumeric(str IN VARCHAR2) RETURN NUMBER IS

 /*检测字符串是否是数字 null:-1; notnumeric:0; isnumeric:1

 */

   v_str FLOAT;

 BEGIN

   IF str IS NULL THEN

     RETURN -1;

   ELSE

   BEGIN

     SELECT TO_NUMBER (str) INTO v_str FROM DUAL;

   EXCEPTION

     WHEN INVALID_NUMBER THEN

       RETURN 0;

   END;

   RETURN 1;

   END IF;

 END isnumeric;

 CREATE OR REPLACE FUNCTION TO_NUM(str IN VARCHAR2) RETURN NUMBER IS

 --将字符串转成数字

 --lzpong 2015/09/16

 BEGIN

   IF(str IS NULL)THEN

     RETURN 0;

   ELSE

     RETURN TO_NUMBER(str);

   END IF;

 EXCEPTION

   WHEN OTHERS THEN

     RETURN 0;

 END;

CREATE OR REPLACE FUNCTION TO_NUM(str IN VARCHAR2) RETURN NUMBER IS

str2 varchar2(160);

--将字符串(提取数字)转成数字

--lzpong 2015/09/16

BEGIN

  str2:=REGEXP_SUBSTR(str,'[[:digit:]]{1,100}\.?[[:digit:]]{0,10}');

  IF(str2 IS NULL)THEN

    RETURN 0;

  ELSE

    RETURN TO_NUMBER(str2);

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    RETURN 0;

END;