当前位置：文江博客话题详情

Oracle 中 SQL Server APPLY 的等效项是什么？

发布于 2024-08-06 08:14:04 字数 54 浏览 2 评论 0原文

我是甲骨文新手。是否有一个内置关键字可以完成与 SQL Server APPLY 相同的工作？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

漫漫岁月 2024-08-13 08:14:04

我认为相当于 Oracle 中的 APPLY 子句称为横向 JOIN。 Oracle 中的横向联接是指将表 A 与输出行的函数 F 联接起来，并且该函数将 A 的列作为参数。

让我们用这个设置构建一个小例子：

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 中是自动的，没有特殊的关键字：

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

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

回复收藏 0 原文

旧话新听 2024-08-13 08:14:04

在 Oracle 中，我们可以通过使用 TABLE() 函数在 FROM 子句中使用管道函数。

SQL> select * from table( get_dept_emps (10) )
  2  /

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

SQL>

这可以像任何其他表一样对待，例如，通过加入它：

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>

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>

回复收藏 0 原文