Oracle 将秒转换为时分秒

http://m.bubuko.com/infodetail-1098393.html

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;