I am new to Oracle. Is there a builtin keyword does the same job of SQL Server APPLY?
Asked
Active
Viewed 1.4k times
3 Answers
12
I think the equivalent of the APPLY clause in Oracle is called a lateral JOIN. A lateral join in Oracle is when you join a table A with a function F that outputs rows and this function has columns of A as parameters.
Let's build a small example with this setup:
SQL> CREATE OR REPLACE TYPE emp_row AS OBJECT (
2 empno NUMBER(4),
3 ename VARCHAR(10),
4 job VARCHAR(9),
5 deptno NUMBER(2)
6 );
7 /
Type created
SQL> CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_row;
2 /
Type created
SQL> CREATE OR REPLACE FUNCTION get_emp_dept(p_deptno NUMBER) RETURN emp_tab IS
2 l_result emp_tab;
3 BEGIN
4 SELECT emp_row(empno, ename, job, deptno)
5 BULK COLLECT INTO l_result
6 FROM emp
7 WHERE deptno = p_deptno;
8 RETURN l_result;
9 END get_emp_dept;
10 /
Function created
A lateral join is automatic in Oracle, there is no special keyword:
SQL> SELECT dept.dname, emp.empno, emp.ename, emp.job
2 FROM dept
3 CROSS JOIN TABLE(get_emp_dept(dept.deptno)) emp;
DNAME EMPNO ENAME JOB
-------------- ----- ---------- ---------
ACCOUNTING 7782 CLARK MANAGER
ACCOUNTING 7839 KING PRESIDENT
ACCOUNTING 7934 MILLER CLERK
RESEARCH 7369 SMITH CLERK
RESEARCH 7566 JONES MANAGER
RESEARCH 7788 SCOTT ANALYST
RESEARCH 7876 ADAMS CLERK
RESEARCH 7902 FORD ANALYST
SALES 7499 ALLEN SALESMAN
SALES 7521 WARD SALESMAN
SALES 7654 MARTIN SALESMAN
SALES 7698 BLAKE MANAGER
SALES 7844 TURNER SALESMAN
SALES 7900 JAMES CLERK
14 rows selected
Vincent Malgrat
- 66,725
- 9
- 119
- 171
-
Isn't it better to use varchar2() instead of varchar() in the type definition? – tuinstoel Sep 25 '09 at 09:56
-
@tuinstoel: you're right it's a good habit to use VARCHAR2 in Oracle. – Vincent Malgrat Sep 25 '09 at 10:09
-
The question is a bit too abstract. There are cases where this approach doesn't help at all, e.g. http://stackoverflow.com/questions/19009476/converting-a-pivot-table-to-flat-table-in-oracle-sql – Andrew not the Saint Sep 25 '13 at 16:11
-
@andrew Nothing prevents you from writing a function that returns 2 rows with the parameters from the table, as I have done above =) Not as practical but still doable. – Vincent Malgrat Sep 26 '13 at 08:52
-
Does anyone have a link to official documentation for this behavior? I agree the CROSS JOIN is treated as a LATERAL join in Oracle 11. In 12c I suspect we could use CROSS APPLY to be clearer, but in prior versions the CROSS JOIN behavior transformation into a lateral join is misleading. – bdeem Dec 22 '14 at 17:44
-
This is a PL/SQL code correct? I'm trying to run it in TOAD (over VPN) havin issues – Caffeinated May 08 '15 at 15:18
-
@coffee: this is direct copy+paste from SQL*Plus. What version of Oracle are you running? – Vincent Malgrat May 11 '15 at 07:11
7
In Oracle we can use a pipelined function in the FROM clause by using the TABLE() function.
SQL> select * from table( get_dept_emps (10) )
2 /
ENAME SAL MGR
------------------------------ ---------- ---------------------
BOEHMER 2450 SCHNEIDER
SCHNEIDER 5000
KISHORE 1300 BOEHMER
SQL>
This can be treated like any other table, for instance, by joining to it:
SQL> select t.*
2 , e.empno
3 from
4 table( get_dept_emps (10) ) t
5 join emp e
6 on e.ename = t.ename
7 /
ENAME SAL MGR EMPNO
---------- ---------- ---------- ----------
BOEHMER 2450 SCHNEIDER 7782
SCHNEIDER 5000 7839
KISHORE 1300 BOEHMER 7934
SQL>
APC
- 144,005
- 19
- 170
- 281
0
Since 12c, Oracle supports both APPLY and LATERAL natively: https://docs.oracle.com/database/121/NEWFT/chapter12101.htm#FEATURENO10330
Jiri Tousek
- 12,211
- 5
- 29
- 43