Oracle 把秒转成时分秒格式(hh24:mm:ss);检测字符串是否是数字;字符串转换为数字...

不说废话,贴代码:

 1 CREATE OR REPLACE FUNCTION to_time(sec IN NUMBER) RETURN VARCHAR2 IS
 2 /*把秒转成时分秒格式
 3 auth lzpong 201/09/16
 4 */
 5 BEGIN
 6   RETURN TRUNC(NVL(sec,0)/3600)||':'||SUBSTR(NUMTODSINTERVAL(NVL(sec,0), 'second'),15,5);
 7 EXCEPTION WHEN OTHERS THEN RETURN '0:00:00';
 8 END to_time;
 9 
10 
11 CREATE OR REPLACE FUNCTION isnumeric(str IN VARCHAR2) RETURN NUMBER IS
12 /*检测字符串是否是数字 null:-1; notnumeric:0; isnumeric:1
13 */
14   v_str FLOAT;
15 BEGIN
16   IF str IS NULL THEN
17     RETURN -1;
18   ELSE
19   BEGIN
20     SELECT TO_NUMBER (str) INTO v_str FROM DUAL;
21   EXCEPTION
22     WHEN INVALID_NUMBER THEN
23       RETURN 0;
24   END;
25   RETURN 1;
26   END IF;
27 END isnumeric;

 转换为数字:

如果要转换字母加数字的,得到数字, 那就得先用正则表达式过滤了:  REGEXP_SUBSTR, REGEXP_REPLACE

 1 CREATE OR REPLACE FUNCTION TO_NUM(str IN VARCHAR2) RETURN NUMBER IS
 2 --将字符串转成数字
 3 --lzpong 2015/09/16
 4 BEGIN
 5   IF(str IS NULL)THEN
 6     RETURN 0;
 7   ELSE
 8     RETURN TO_NUMBER(str);
 9   END IF;
10 EXCEPTION
11   WHEN OTHERS THEN
12     RETURN 0;
13 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;

 

转载于:https://www.cnblogs.com/lzpong/p/4813983.html