Oracle Date Check function

Use the background:The company has a stored procedure, insert is always unsuccessful, then debug, see the INSERT statement has a function for date processing,
To_char (To_date (v_slot_date, ' yyyy-mm-dd '), ' yyyy-mm ')
Check out into the exception when the V_slot_date value, found that v_slot_date is ' 2013.12 months ', only to realize that the original is the incoming
The date string is not canonical, causing inset to appear unexpectedly. So just prepare a function to determine the correctness of the entry date.

1, how to use this function
(1), used in SQL statements:

Sql> SELECT fn_isdate (REPLACE (' 2015-05-12 ', '-', ') ') from DUAL; Fn_isdate (replace (' 2015-05-12 '------------------------------                             1sql> SELECT fn_isdate (replace (' 2015-05-32 '), '-', ')) from DUAL; Fn_isdate (REPLACE (' 2015-05-32 '------------------------------                             

(2), used in stored procedures:

CREATE OR REPLACE PROCEDURE isbeginif fn_isdate (slotdate) =1    then    INSERT into Pesk. R_hr_slot (...) VALUES (...);    COMMIT; END IF; END

2, the contents of the storage function are as follows:

Create or Replace function fn_isdate (v_datestr VARCHAR2--Date Entry parameter) return number--return 1 is correct, 0 is error. as/*------------------------------------------------------------------------Common function: Date check function invocation Example: Select Fn_isdate (' 20140501 ') from dual;------------------------------------------------------------------------*/i_year number; --year i_month number; --month i_day number;   --day D_TJRQ date; --Date Type date beginif v_datestr is null then return 0;end if;if Length (Trim (v_datestr)) <> Ten then return 0;end if;--Award The break date consists of the number if REGEXP_SUBSTR (Trim (V_DATESTR), ' [[:d igit:]]+ ') is null then return 0;end if;--the year i_year:=to_number (substr (RTrim (V_DATESTR), 1,4)); --intercept the Month I_month:=to_number (substr (RTrim (V_DATESTR), 6,2));--intercept the date i_day:=to_number (substr (RTrim (V_DATESTR), 9,2));    -To judge the month, it must be within the range of January to December if i_month not between 1 and then begin return 0;        End;end if;--Judgment of the date, 1,3,5,7,8,10,12 month maximum day is 31,4,6,9,11 month Maximum day is 30, February if leap year is 29, other years is 28.if i_day between 1 and then BEGIN If i_day=31 and i_month not in (1,3,5,7,8,10,12) then begin return 0;        End        End If;    If i_month=2 then BEGIN--Rules 1: The average year can be divisible by 4 and not divisible by 100 for leap years.    --Rules 2: The century can be divisible by 400 is a leap year. --Rules 3: For a large number of years, if the year can be divisible by 3200, and divisible by 172800 is a leap.                If 172,800 is a leap year, 86,400 is not a leap year. if (mod (i_year,4) =0 and mod (i_year,100) <>0) or mod (i_year,400) =0 or (mod (i_year,3200) =0 and mod (i_year,17                            2800) =0) THEN BEGIN--if it is a leap year, the maximum day of February is if i_day>29 then                            Begin return 0;                        End                    End If;                End                            Else begin--if not a leap year, the maximum day of February is i_day>28 then                            Begin return 0;                        End                    End If; End;            End If;        End        End If;    return 1; End;else return 0;end if;end;

you are welcome to suggest better ideas for improvement.