Oracle 把秒轉成時分秒格式(呵呵4)

標籤：

 

 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 IS12 /*檢測字串是否是數字 null:-1; notnumeric:0; isnumeric:113 */14   v_str FLOAT;15 BEGIN16   IF str IS NULL THEN17     RETURN -1;18   ELSE19   BEGIN20     SELECT TO_NUMBER (str) INTO v_str FROM DUAL;21   EXCEPTION22     WHEN INVALID_NUMBER THEN23       RETURN 0;24   END;25   RETURN 1;26   END IF;27 END isnumeric;

 

Oracle 把秒轉成時分秒格式(呵呵4)