Oracle Database 12c has extended the SQL syntax and functionality for joining with collections in such a way that the call to the PL/SQL function that creates the collection can take an input parameter taken from the records from the table to which it is joined. That is a little abstract, so let’s quickly look at an example:
Here we perform a join between table DEPT and a PL/SQL function that returns a collection. This function takes an input parameter – a department number – and returns in the collection the names of all employees in that department.
Here we perform a join between table DEPT and a PL/SQL function that returns a collection. This function takes an input parameter – a department number – and returns in the collection the names of all employees in that department.
create or replace type str_tbl as table of varchar2(200);
/
create or replace funtion xxcross_fun(p_dept in number)
) return str_tbl
as
l_enames str_tbl;
begin
select cast(collect(ename) as str_tbl) into
l_enames from emp
where depnot=p_dept;
return l_enames;
end;
) return str_tbl
as
l_enames str_tbl;
begin
select cast(collect(ename) as str_tbl) into
l_enames from emp
where depnot=p_dept;
return l_enames;
end;
Usage:
SELECT * FROM DEPT d CROSS APPLY xxcross_fun(deptno)
Now we have seen how the CROSS APPLY will help us join with the collection result from function that takes a join-value as its input. However, in the process, we have lost department 40 (because it does not have any employees and results in an empty collection in the employees_in_department function).
