Oracle 中的 SQL Server APPLY 相当于什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1476191/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the equivalent of SQL Server APPLY in Oracle?
提问by oscar
I am new to Oracle. Is there a builtin keyword does the same job of SQL Server APPLY?
我是 Oracle 的新手。是否有内置关键字与 SQL Server APPLY 执行相同的工作？
采纳答案by Vincent Malgrat
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.
我认为相当于 Oracle 中的 APPLY 子句称为横向联接。Oracle 中的横向连接是将表 A 与输出行的函数 F 连接起来，该函数将 A 的列作为参数。
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:
横向连接在 Oracle 中是自动的，没有特殊的关键字：
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
回答by APC
In Oracle we can use a pipelined function in the FROMclause by using the TABLE()function.
在 Oracle 中，我们可以FROM通过使用函数在子句中使用流水线TABLE()函数。
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>
回答by Jiri Tousek
Since 12c, Oracle supports both APPLYand LATERALnatively: https://docs.oracle.com/database/121/NEWFT/chapter12101.htm#FEATURENO10330
由于12C，甲骨文支持APPLY和LATERAL原生：https://docs.oracle.com/database/121/NEWFT/chapter12101.htm#FEATURENO10330
