Oracle 中的 SQL Server APPLY 相当于什么? [英] What is the equivalent of SQL Server APPLY in Oracle?

问题描述

我是 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 中的横向连接是将表 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屋！