Oracle中SQL Server APPLY的等效项是什么? [英] What is the equivalent of SQL Server APPLY in Oracle?
本文介绍了Oracle中SQL Server APPLY的等效项是什么?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我是Oracle新手.是否有内置关键字可以执行SQL Server APPLY的相同工作?
I am new to Oracle. Is there a builtin keyword does the same job of SQL Server APPLY?
推荐答案
我认为Oracle中APPLY子句的等效项称为侧向JOIN.在Oracle中,横向联接是指使用功能F将表A连接到表A,该功能F输出行,并且该函数将A的列作为参数.
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
Oracle中的横向联接是自动的,没有特殊关键字:
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
这篇关于Oracle中SQL Server APPLY的等效项是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文