blocks|key|1445513|text|在Oracle中，我们可以通过使用TABLE()函数在FROM子句中使用管道函数。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1445514|SQL>+select+*+from+table(+get_dept_emps+(10)+)
++2++/

ENAME+++++++++++++++++++++++++++++++++SAL+MGR
------------------------------+----------+---------------------
BOEHMER++++++++++++++++++++++++++++++2450+SCHNEIDER
SCHNEIDER++++++++++++++++++++++++++++5000
KISHORE++++++++++++++++++++++++++++++1300+BOEHMER

SQL>|code-block|syntax|javascript|1445515|可以像对待任何其他表一样处理它，例如，通过连接到它：|1445516|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>|1445517|entityMap^0|H|7|R|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

In Oracle we can use a pipelined function in the <code>FROM</code> clause by using the <code>TABLE()</code> function.

<pre><code>SQL&gt; select * from table( get_dept_emps (10) )
 2 /

ENAME SAL MGR
------------------------------ ---------- ---------------------
BOEHMER 2450 SCHNEIDER
SCHNEIDER 5000
KISHORE 1300 BOEHMER

SQL&gt;
</code></pre>

This can be treated like any other table, for instance, by joining to it:

<pre><code>SQL&gt; 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&gt;
</code></pre>

blocks|key|1230656|text|我认为Oracle中APPLY子句的等价物称为横向联接。Oracle中的横向联接是指将表A与输出行的函数F联接，该函数将A列作为参数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1230657|让我们用下面的设置构建一个小示例：|1230658|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|code-block|syntax|javascript|1230659|横向联接在Oracle中是自动的，没有特殊的关键字：|1230660|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|1230661|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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:

<pre><code>SQL&gt; 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&gt; CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_row;
 2 /

Type created
SQL&gt; 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
</code></pre>

A lateral join is automatic in Oracle, there is no special keyword:

<pre><code>SQL&gt; 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
</code></pre>

blocks|key|1445583|text|从12c开始，Oracle本机支持APPLY和LATERAL：https://docs.oracle.com/database/121/NEWFT/chapter12101.htm#FEATURENO10330|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1445584|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.oracle.com/database/121/NEWFT/chapter12101.htm#FEATURENO10330^0|H|5|N|7|V|22|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]|$9|Q|A|R|B|C]]|D|@$9|S|A|T|1|U]]|E|$]]|$1|F|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

Since 12c, Oracle supports both <code>APPLY</code> and <code>LATERAL</code> natively: <a href="https://docs.oracle.com/database/121/NEWFT/chapter12101.htm#FEATURENO10330" rel="nofollow">https://docs.oracle.com/database/121/NEWFT/chapter12101.htm#FEATURENO10330</a>

I am new to Oracle. Is there a builtin keyword does the same job of SQL Server APPLY?

What is the equivalent of SQL Server APPLY in Oracle?

SQL Server

我是甲骨文新手。是否有内置关键字适用于SQL Server的相同工作？

问SQL Server在Oracle中应用的等价物是什么？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server在Oracle中应用的等价物是什么？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server在Oracle中应用的等价物是什么？
EN