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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142

<br> /* ===============================================================================================     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 包中提取出来 。