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@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/684234/viewspace-1030231/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/684234/viewspace-1030231/