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
)
RETURN date
is
date_returned date;
BEGIN
  date_returned :=
    CASE p_date
      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')
    END;
RETURN(date_returned);
END;
/

Answer:
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 into a subroutine. This 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, control/routing should be in the calling routine not in the called routine.

Looking at the code itself, 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
BEGIN
   RETURN ADD_MONTHS(ADD_MONTHS((p_date+p_day),p_month),p_year*12);
END FutureDate; 

This code is not the exact equivalent of the original because you can now add years, months and days simultaneously. 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!