You have a procedure or function returning computed values in out parameters. And you’d like to call it in SQL. How’s that done?
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