不说废话,贴代码:
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;