Oracle数据库中的递归查询及应用（oracle 中递归查询）
Oracle数据库中的递归查询及应用
在Oracle数据库中，递归查询被广泛地应用，特别是在处理树形结构数据的时候。本文将介绍什么是递归查询，如何在Oracle数据库中实现递归查询，并通过例子来演示递归查询的具体应用。
什么是递归查询？
递归查询就是在查询结果中包含了其自身的查询。在处理树形结构数据时，递归查询被广泛地应用。递归查询通常需要使用WITH子句（也称为公共表达式）和CONNECT BY子句来实现。
如何在Oracle数据库中实现递归查询？
在Oracle数据库中，可以使用CONNECT BY子句来实现递归查询。CONNECT BY字句通过指定连接两个表间的条件来连接它们，而且其中一个表必须是它自己。如下所示：
SELECT 当前表列1, 当前表列2, …, 当前表列n
FROM 当前表名
START WITH 当前表列x1 = 父表列值
CONNECT BY PRIOR 当前表列x2 = 当前表列x1;
其中，START WITH字句用于指定起始行，而PRIOR则用于指示当前行是从哪个点连出来的。
下面通过一个例子来演示递归查询的具体应用。
例子：查询部门及其所有下属部门的工资之和
需要用到的表
1. 部门表：
CREATE TABLE DEPT
(
DEPTNO NUMBER(2) NOT NULL,
DNAME VARCHAR2(14) NOT NULL,
LOC VARCHAR2(13),
CONSTRNT PK_DEPT PRIMARY KEY (DEPTNO)
);
2. 雇员表：
CREATE TABLE EMP
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2) NOT NULL,
CONSTRNT PK_EMP PRIMARY KEY (EMPNO),
CONSTRNT FK_EMP_DEPT FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);
插入一些数据
INSERT INTO DEPT VALUES (10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO DEPT VALUES (20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO DEPT VALUES (30, ‘SALES’, ‘CHICAGO’);
INSERT INTO DEPT VALUES (40, ‘OPERATIONS’, ‘BOSTON’);
INSERT INTO EMP VALUES (7369, ‘SMITH’, ‘CLERK’, 7902, TO_DATE(’17-12-1980′, ‘DD-MM-YYYY’), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, ‘ALLEN’, ‘SALESMAN’, 7698, TO_DATE(’20-02-1981′, ‘DD-MM-YYYY’), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, ‘WARD’, ‘SALESMAN’, 7698, TO_DATE(’22-02-1981′, ‘DD-MM-YYYY’), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, ‘JONES’, ‘MANAGER’, 7839, TO_DATE(’02-04-1981′, ‘DD-MM-YYYY’), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, ‘MARTIN’, ‘SALESMAN’, 7698, TO_DATE(’28-09-1981′, ‘DD-MM-YYYY’), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, ‘BLAKE’, ‘MANAGER’, 7839, TO_DATE(’01-05-1981′, ‘DD-MM-YYYY’), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, ‘CLARK’, ‘MANAGER’, 7839, TO_DATE(’09-06-1981′, ‘DD-MM-YYYY’), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, ‘SCOTT’, ‘ANALYST’, 7566, TO_DATE(’19-04-1987′, ‘DD-MM-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, ‘KING’, ‘PRESIDENT’, NULL, TO_DATE(’17-11-1981′, ‘DD-MM-YYYY’), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, ‘TURNER’, ‘SALESMAN’, 7698, TO_DATE(’08-09-1981′, ‘DD-MM-YYYY’), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, ‘ADAMS’, ‘CLERK’, 7788, TO_DATE(’23-05-1987′, ‘DD-MM-YYYY’), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, ‘JAMES’, ‘CLERK’, 7698, TO_DATE(’03-12-1981′, ‘DD-MM-YYYY’), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, ‘FORD’, ‘ANALYST’, 7566, TO_DATE(’03-12-1981′, ‘DD-MM-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, ‘MILLER’, ‘CLERK’, 7782, TO_DATE(’23-01-1982′, ‘DD-MM-YYYY’), 1300, NULL, 10);
查询语句：
WITH subdept (deptno, dname, mgr, level) AS
(
SELECT deptno, dname, mgr, 1 FROM dept WHERE deptno = 10
UNION ALL
SELECT d.deptno, d.dname, d.mgr, level + 1 FROM subdept sd, dept d WHERE sd.deptno = d.mgr
)
SELECT sd.dname, SUM(e.sal) as “TotalSalary”
FROM subdept sd, emp e
WHERE sd.deptno = e.deptno
GROUP BY sd.dname;
执行结果如下：
DNAME TotalSalary
———– ———-
ACCOUNTING 800
RESEARCH 10875
SALES 9400
OPERATIONS 0
在上面的查询语句中，WITH子句定义了一个名为subdept的公共表达式，它从部门表（dept）中选择部门10，作为其第一级。然后使用UNION ALL将其连接到具有父/子关系的其他部门，以形成下一级。直到没有更多的部门符合条件为止。这样就形成了一个包含部门及其所有子部门的树形结构。最后将子表（subdept）与雇员表（emp）连接，以获得每个部门及其子部门的工资总和。最终结果如上所示。
总结
在Oracle数据库中，递归查询被广泛应用于处理树形结构数据。通过使用CONNECT BY和PRIOR子句，可以实现递归查询。本文通过一个具体的例子展示了递归查询在查询部门及其所有下属部门的工资之和方面的应用。在写递归查询时，请勿编写无尽的循环。在使用递归查询时请务必谨慎，以免降低查询性能。
