Recursividade com a cláusula WITH
Um bom exemplo do uso da cláusula WITH usando consultas recursivas, que mostra os empregados que reportam diretamente ou indiretamente para o empregado 101 e seu respectivo nível de subordinação.
01.WITH
02. reports_to_101 (eid, emp_last, mgr_id, reportLevel) AS
03. (
04. SELECT employee_id, last_name, manager_id, 0 reportLevel
05. FROM employees
06. WHERE employee_id = 101
07. UNION ALL
08. SELECT e.employee_id, e.last_name, e.manager_id, reportLevel+1
09. FROM reports_to_101 r, employees e
10. WHERE r.eid = e.manager_id
11. )
12.SELECT eid, emp_last, mgr_id, reportLevel
13.FROM reports_to_101
14.ORDER BY reportLevel, eid;
A claúsula WITH nesta consulta, chamada de* reports_to_101*, tem duas subconsultas. A primeira consulta é a âncora, a qual retorna o id do empregado, último nome, id do gerente e o nível de subordinação do empregado 101 (valor fixo em zero como ele é o nível mais alto da hierarquia). A segunda subconsulta é a subconsulta recursiva que faz o join com o conteúdo de *reports_to_101 *e da primeira consulta; e o resultado é adicionado em *reports_to_101. *A operação irá terminar quando não mais linhas foram encontradas pela subconsulta recursiva. Por fim, temos uma consulta que retornará o resultado final da claúsula WITH *reports_to_101.
*
EID EMP_LAST MGR_ID REPORTLEVEL
---------- ------------------------- ---------- -----------
101 Kochhar 100 0
108 Greenberg 101 1
200 Whalen 101 1
203 Mavris 101 1
204 Baer 101 1
205 Higgins 101 1
109 Faviet 108 2
110 Chen 108 2
111 Sciarra 108 2
112 Urman 108 2
113 Popp 108 2
206 Gietz 205 2