Function call containing out parameters in SQL

First, we set up an example, like a procedure computing when someone retires based on a given birth date. To add just a bit of complexity right at the start, also the retirement age is returned. Please note the procedure doesn’t necessarily compute the correct retirement date, it’s just an example.

create or replace procedure get_retirement_details
( p_in_birth_date       in    date
, p_out_retirement_date   out date
, p_out_retirement_age    out pls_integer
)
is
  c_turning_point_date constant date        := date '1954-12-31';
  c_retirement_age     constant pls_integer := 65;
  c_months_per_year    constant pls_integer := 12;
  c_retire_factor      constant pls_integer := 36;
begin
  if ( p_in_birth_date <= c_turning_point_date )
  then 
    p_out_retirement_age := c_retirement_age;
  else 
    p_out_retirement_age := c_retirement_age + 
                            months_between 
                            ( p_in_birth_date
                            , c_turning_point_date 
                            ) / c_retire_factor;
  end if; 
  p_out_retirement_date := add_months 
                           ( p_in_birth_date
                           , p_out_retirement_age*c_months_per_year 
                           );
end;
/

Note the two out parameters. Note also the declaration of constants for each literal used. Well, declaring a constant to denote the number of months per calendar year might be overdone. However, we put our procedure to the test:

declare
  l_birth_date date := date '1969-02-25';
  l_retirement_date date;
  l_retirement_age pls_integer;
begin
  get_retirement_details
  ( p_in_birth_date => l_birth_date
  , p_out_retirement_date => l_retirement_date
  , p_out_retirement_age => l_retirement_age 
  );
  dbms_output.put_line ( l_retirement_date);
  dbms_output.put_line ( l_retirement_age);
end;
/

PL/SQL procedure successfully completed.

2039-02-25
70

Suppose we have a table with person details including their birth date. How can we, in SQL, answer a question like ‘which of these persons will be 70 when they retire’ ?

Clearly, we can’t directly call the procedure in SQL due to the out parameters. Moreover, the procedure returns two values, so we can’t just get away with transforming or overloading the procedure to a function. At least not if the function would return a singular value type…but what if we would return an object containing both computed values ?

For brevity, I’ll show the solution using a plsql_declarations clause in the with clause, and leave the exercise to write a packaged function ( with pragma udf ) to you.

The table persons shown below represents our person details.

create table persons
( first_name varchar2(25)
, birth_date date
)
/

insert into persons values ( 'Jim', date '1969-02-25' );
insert into persons values ( 'Lee', date '1979-01-16' );
insert into persons values ( 'Yaz', date '1959-11-03' );

We need an object type, and a collection type, to be able to work with them in SQL:

create type retirement_details_type
is 
object
( retirement_date date
, retirement_age number(3)
)
/

create type retirement_details_list
is
table of retirement_details_type
 /

Now, we can use the with clause to specify a function returning a collection of values containing retirement details and call that function in the query:

with function find_retirement_details
( p_in_birth_date in date
)
return retirement_details_list
as
  l_retirement_date date;
  l_retirement_age pls_integer;
begin
  get_retirement_details
  ( p_in_birth_date => p_in_birth_date
  , p_out_retirement_date => l_retirement_date
  , p_out_retirement_age => l_retirement_age 
  );
  return 
    retirement_details_list
    ( retirement_details_type
      ( l_retirement_date
      , l_retirement_age
      )
    );
end; 
select  p.first_name
,       p.birth_date
,       d.retirement_date
,       d.retirement_age
from    persons p
,       lateral
        ( select  *
          from    table
                  ( find_retirement_details 
                    ( p_in_birth_date => p.birth_date
                    )
                  )
        ) d
where   d.retirement_age = 70 
/

Which will return the desired result:

FIRST_NAME BIRTH_DATE RETIREMENT_DATE RETIREMENT_AGE
---------- ---------- --------------- --------------
Jim        1969-02-25      2039-02-25             70