ORACLE判断日期、时间的字符串是否有效日期、时间


/* =============================================================================================== Function Name:IS_DATE Description: 判斷輸入的日期和指定格式 是否為有效日期, 返回:數值類型, 1:是, 0:否; 空值:返回0. Argument: p_date_str IN DATE_string 日期的字符串, 如:"2021/07/09" p_format IN 日期格式, 如: YYYY/MM/DD History: 1.00 2021/7/09 samrv Creation *================================================================================================*/ function is_date(p_date_str varchar2, p_format varchar2) return number is v_format varchar2(20); v_date date; v_result number; begin v_result :=0; --v_format := nvl(p_format,'yyyy-mm-dd'); begin v_date := to_date(nvl(p_date_str, 'a') , p_format); v_result := 1; exception when others then return 0; end; return v_result; end; /* =============================================================================================== Function Name:IS_TIME Description: 判斷輸入的時間和指定格式 是否為有效時間, 返回:數值類型, 1:是, 0:否; 空值:返回0. Argument: p_time_str IN time_string 日期的字符串, 如:"200749" 表示: 20:07:49 p_format IN 日期格式, 如:HH24MISS 表示24小時制 HH24:MI:SS 或 12小時制 HH:MI:SS History: 1.00 2021/7/09 samrv Creation *================================================================================================*/ FUNCTION IS_TIME(P_TIME_STR IN VARCHAR2,p_format varchar2) RETURN NUMBER is v_time NUMBER; v_hh varchar2(4); v_mi varchar2(2); v_ss varchar2(2); V_RESULT NUMBER; begin V_RESULT := 0 ; BEGIN v_TIME := TO_NUMBER(nvl(P_TIME_STR, 'a') ); -- V_RESULT := 1; if p_format= 'HH24MI' THEN SELECT SUBSTR(P_TIME_STR,1,2) ,SUBSTR(P_TIME_STR,3,2) INTO V_HH,V_MI FROM DUAL; IF TO_NUMBER(V_HH)>=0 AND TO_NUMBER(V_HH)<24 AND TO_NUMBER(V_MI)>=0 AND TO_NUMBER(V_MI)<=59 THEN V_RESULT := 1; ELSE V_RESULT :=0; END IF; -- END IF; ELSif p_format= 'HH24MISS' THEN SELECT SUBSTR(P_TIME_STR,1,2) ,SUBSTR(P_TIME_STR,3,2),SUBSTR(P_TIME_STR,5,2) INTO V_HH,V_MI, V_SS FROM DUAL; IF TO_NUMBER(V_HH)>=0 AND TO_NUMBER(V_HH)<24 AND TO_NUMBER(V_MI)>=0 AND TO_NUMBER(V_MI)<=59 AND TO_NUMBER(V_SS)>=0 AND TO_NUMBER(V_SS)<=59 THEN V_RESULT := 1; ELSE V_RESULT :=0; END IF; -- END IF; ELSif p_format= 'HHMI' THEN SELECT SUBSTR(P_TIME_STR,1,2) ,SUBSTR(P_TIME_STR,3,2) INTO V_HH,V_MI FROM DUAL; IF TO_NUMBER(V_HH)>=0 AND TO_NUMBER(V_HH)<=12 AND TO_NUMBER(V_MI)>=0 AND TO_NUMBER(V_MI)<=59 THEN V_RESULT := 1; ELSE V_RESULT :=0; END IF; --END IF; ELSif p_format= 'HHMISS' THEN SELECT SUBSTR(P_TIME_STR,1,2) ,SUBSTR(P_TIME_STR,3,2),SUBSTR(P_TIME_STR,5,2) INTO V_HH,V_MI, V_SS FROM DUAL; IF TO_NUMBER(V_HH)>=0 AND TO_NUMBER(V_HH)<=12 AND TO_NUMBER(V_MI)>=0 AND TO_NUMBER(V_MI)<=59 AND TO_NUMBER(V_SS)>=0 AND TO_NUMBER(V_SS)<=59 THEN V_RESULT := 1; ELSE V_RESULT :=0; END IF; END IF; exception when others then return 0; END; RETURN V_RESULT; end; /* =============================================================================================== Function Name:IS_numeric Description: 判斷輸入數值的字符串是否為有效數值(十進制), 返回:數值類型, 1:是, 0:否; 空值:返回0. Argument: p_str IN DATE_string 日期的字符串, 如:"20107.09" History: 1.00 2021/7/09 samrv Creation *================================================================================================*/ FUNCTION is_numeric (p_str IN VARCHAR2) RETURN NUMBER IS v_str FLOAT; BEGIN IF p_str IS NULL THEN RETURN 0; ELSE BEGIN SELECT TO_NUMBER (p_str) INTO v_str FROM DUAL; EXCEPTION WHEN INVALID_NUMBER THEN RETURN 0; END; RETURN 1; END IF; END is_numeric;  

以上SQL在PLSQL 包中提取出来 。 

 

posted @ 2021-07-09 11:19  samrv  阅读(1898)  评论(0编辑  收藏  举报