Author: rainny

Date:2009-2-18

CREATE OR REPLACE FUNCTION F_ISDATE(
P_COLVALUE VARCHAR2,
P_mandatory VARCHAR2) RETURN PLS_INTEGER IS
v_cn PLS_INTEGER;BEGIN
IF P_mandatory = 'yes' THEN
IF P_COLVALUE IS NULL THEN
RETURN 2;
ELSE
BEGIN
SELECT 1
INTO V_CN
FROM DUAL
WHERE REGEXP_LIKE(P_COLVALUE, '^[1-9][[:digit:]]{3}-[[:digit:]]{1,2}-[[:digit:]]{1,2}$')
AND NOT
REGEXP_LIKE(P_COLVALUE, '^[1-9][[:digit:]]{3}-((00|0)-((00|0)|[[:digit:]]{1,2})|[[:digit:]]{1,2}-(00|0))$')
AND REGEXP_SUBSTR(P_COLVALUE, '[[:digit:]]{1,2}', 6) <= 12
AND REGEXP_SUBSTR(P_COLVALUE, '[[:digit:]]{1,2}$') <= 31;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
END IF;
ELSIF P_mandatory = 'no' THEN
IF P_COLVALUE IS NULL THEN
RETURN 1;
ELSE
BEGIN
SELECT 1
INTO V_CN
FROM DUAL
WHERE REGEXP_LIKE(P_COLVALUE, '^[1-9][[:digit:]]{3}-[[:digit:]]{1,2}-[[:digit:]]{1,2}$')
AND NOT
REGEXP_LIKE(P_COLVALUE, '^[1-9][[:digit:]]{3}-((00|0)-((00|0)|[[:digit:]]{1,2})|[[:digit:]]{1,2}-(00|0))$')
AND REGEXP_SUBSTR(P_COLVALUE, '[[:digit:]]{1,2}', 6) <= 12
AND REGEXP_SUBSTR(P_COLVALUE, '[[:digit:]]{1,2}$') <= 31;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
END IF;
END IF;
END F_ISDATE;[@more@]

利用ORACLE正規表示式判斷字串是否為日期格式

相關文章