How do I create an Oracle PL/SQL function FUTUREDATE which accepts 4 parameters - first one is a date, rest are integers - to calculate the new date?
Here is the PL/SQL function:
create or replace FUNCTION FutureDate (
p_date IN date,
p_year in int,
p_month in int,
p_day in int
WHEN 'mm' THEN to_date('((extract(month from p_date)) + p_month)','mm')
WHEN 'yy' THEN to_date('((extract(year from p_date)) + p_year)','yy')
WHEN 'dd' THEN to_date('((extract(day from p_date)) + p_day)','dd')
Before we get to the details of how to do this let's take a step back and consider good programming practice (in any language) which is to not pass flags (control variable) into a subroutine. This Oracle PL/SQL function, as it stands, has one parameter p_date which is used to control the operation of the subroutine. This is not good practice, it is much better to have the control/routing in the calling routine rather than the called routine.
Looking at this PL/SQL code in detail, the function also tries to use this parameter as if it contains a date value and a character string value at the same time. This is not possible. To achieve this you would need one parameter to hold one of "MM", "YY", or "DD" and another to hold the date. As we said earlier though this is not a good idea.
Another issue with the function as it stands is that there is no check to see if the new value for month is greater than 12 or the new value for days is more than the number of days in the month.
So how do we do we get round this? With a little tweak to the design we can eliminate the need to pass in a flag to the subroutine,
we can make the function more flexible and we can eliminate any need to check if the month value is more than 12 or the number of days is greater than the number of days in the month. All we need to do is to supply default values for the last 3 parameters and then use the Oracle PL/SQL built-in date function ADD_MONTHS and date arithmetic functionality to do all the hard work for us. The code to do this is as follows:CREATE OR REPLACE FUNCTION FutureDate (
p_date IN date,
p_year IN int := 0,
p_month IN int := 0,
p_day IN int := 0
RETURN date IS
This PL/SQL code is not the exact equivalent of the original because you can now add years, months and days simultaneously, making it more flexible. There is also very little code that we have to write as we're just using functionality already in the Oracle database. In fact you don't really need a function at all now because it's so simple.
If you take note of nothing else, you should note that it's much easier and quicker to not re-invent the wheel. To add error checking to make sure the number of days is correct for the month and the month isn't greater than 12 would take much longer and make the function much more complex. Fortunately we don't need to - Oracle does it all for us!
For more help see our PL/SQL tutorials
and refer to the Oracle PL/SQL Language Reference Manual
If you're interested in formal Oracle training (either on-line or in a classroom) then see our Oracle training
page for more information.