SQL em Oracle > DML > CTE
CTE tl_logo2.jpg

CTE significa "Common Table Expression"" e pode ser considerado um conjunto de resultados temporário, recuperado no início e reutilizado mais tarde quantas vezes for necessário. O conjunto de resultados funciona como uma tabela temporária, que é necessário declarar explicitamente e dura apenas enquanto o comando estiver em execução. Um CTE é geralmente considerado mais legível que uma subconsulta. Um CTE é mais poderoso que uma subconsulta pois também pode ser auto-referiado ou mesmo referenciado várias vezes na mesma consulta. Esta funcionalidade permite executar queries hierarquicos, que desta forma ficam disponíveis nos SGBDs que não os suportavam com sintaxe própria. Neste módulo vamos ver:


CTE simples

Um exemplo simples de CTE, não muito útil, mas suficiente para mostrar o conceito.

WITH MyCTE AS (  
      SELECT employee_id, first_name, last_name, manager_id
      FROM employees
      WHERE manager_id IS NULL 
)
SELECT *
FROM MyCTE;
EMPLOYEE_ID|FIRST_NAME|LAST_NAME|MANAGER_ID|
-----------|----------|---------|----------|
        100|Steven    |King     |          |

Um exemplo mais elaborado: vamos procurar funcionários que têm um salário superior à média do departamento em que trabalham:

with average_salary as ( 
   select department_id, avg(salary) as avg_sal 
   from employees
   group by department_id
)
select e.first_name, e.last_name, e.salary, y.department_name, a.avg_sal
from employees e
inner join average_salary a on e.department_id=a.department_id
inner join departments y on e.department_id=y.department_id
where e.salary > a.avg_sal
order by 1;
FIRST_NAME|LAST_NAME|SALARY|DEPARTMENT_NAME|AVG_SAL                                  |
----------|---------|------|---------------|-----------------------------------------|
Adam      |Fripp    |  8200|Shipping       |3475.555555555555555555555555555555555556|
Alberto   |Errazuriz| 12000|Sales          |8955.882352941176470588235294117647058824|
Alexander |Hunold   |  9000|IT             |                                     5760|
Alexis    |Bull     |  4100|Shipping       |3475.555555555555555555555555555555555556|
Allan     |McEwen   |  9000|Sales          |8955.882352941176470588235294117647058824|
Britney   |Everett  |  3900|Shipping       |3475.555555555555555555555555555555555556|
Bruce     |Ernst    |  6000|IT             |                                     5760|
Clara     |Vishney  | 10500|Sales          |8955.882352941176470588235294117647058824|
Daniel    |Faviet   |  9000|Finance        |8601.333333333333333333333333333333333333|
Danielle  |Greene   |  9500|Sales          |8955.882352941176470588235294117647058824|
David     |Bernstein|  9500|Sales          |8955.882352941176470588235294117647058824|
Den       |Raphaely | 11000|Purchasing     |                                     4150|
Eleni     |Zlotkey  | 10500|Sales          |8955.882352941176470588235294117647058824|
Ellen     |Abel     | 11000|Sales          |8955.882352941176470588235294117647058824|
Gerald    |Cambrault| 11000|Sales          |8955.882352941176470588235294117647058824|
Harrison  |Bloom    | 10000|Sales          |8955.882352941176470588235294117647058824|
Janette   |King     | 10000|Sales          |8955.882352941176470588235294117647058824|
Jennifer  |Dilly    |  3600|Shipping       |3475.555555555555555555555555555555555556|
John      |Russell  | 14000|Sales          |8955.882352941176470588235294117647058824|
Karen     |Partners | 13500|Sales          |8955.882352941176470588235294117647058824|
Kelly     |Chung    |  3800|Shipping       |3475.555555555555555555555555555555555556|
Kevin     |Mourgos  |  5800|Shipping       |3475.555555555555555555555555555555555556|
Lisa      |Ozer     | 11500|Sales          |8955.882352941176470588235294117647058824|
Matthew   |Weiss    |  8000|Shipping       |3475.555555555555555555555555555555555556|
Michael   |Hartstein| 13000|Marketing      |                                     9500|
Nancy     |Greenberg| 12008|Finance        |8601.333333333333333333333333333333333333|
Nandita   |Sarchand |  4200|Shipping       |3475.555555555555555555555555555555555556|
Patrick   |Sully    |  9500|Sales          |8955.882352941176470588235294117647058824|
Payam     |Kaufling |  7900|Shipping       |3475.555555555555555555555555555555555556|
Peter     |Hall     |  9000|Sales          |8955.882352941176470588235294117647058824|
Peter     |Tucker   | 10000|Sales          |8955.882352941176470588235294117647058824|
Renske    |Ladwig   |  3600|Shipping       |3475.555555555555555555555555555555555556|
Sarah     |Bell     |  4000|Shipping       |3475.555555555555555555555555555555555556|
Shanta    |Vollman  |  6500|Shipping       |3475.555555555555555555555555555555555556|
Shelley   |Higgins  | 12008|Accounting     |                                    10154|
Steven    |King     | 24000|Executive      | 19333.3333333333333333333333333333333333|
Tayler    |Fox      |  9600|Sales          |8955.882352941176470588235294117647058824|
Trenna    |Rajs     |  3500|Shipping       |3475.555555555555555555555555555555555556|

note04.gif

bthome.gifTopo


Usar CTE para evitar subselect

Vamos procurar os funcionários que recebem o menor salário em cada departamento. Propomos duas consultas possíveis:

select first_name,salary,job_id
from employees
where (job_id, salary) in (select job_id, min(salary) 
                           from employees
                           group by job_id)
order by job_id, salary;
-- ---------------------------------------------------------------------

WITH MyCTE (job, min_sal)
AS (  select job_id, min(salary) 
      from employees
      group by job_id)
select e.first_name, e.salary, e.job_id
from employees e
inner join MyCTE on MyCTE.job=e.job_id and MyCTE.min_sal=e.salary;
FIRST_NAME|SALARY|JOB_ID    |
----------|------|----------|
William   |  8300|AC_ACCOUNT|
Shelley   | 12008|AC_MGR    |
Jennifer  |  4400|AD_ASST   |
Steven    | 24000|AD_PRES   |
Lex       | 17000|AD_VP     |
Neena     | 17000|AD_VP     |
Luis      |  6900|FI_ACCOUNT|
Nancy     | 12008|FI_MGR    |
Susan     |  6500|HR_REP    |
Diana     |  4200|IT_PROG   |
Michael   | 13000|MK_MAN    |
Pat       |  6000|MK_REP    |
Hermann   | 10000|PR_REP    |
Karen     |  2500|PU_CLERK  |
Den       | 11000|PU_MAN    |
Eleni     | 10500|SA_MAN    |
Sundita   |  6100|SA_REP    |
Martha    |  2500|SH_CLERK  |
Randall   |  2500|SH_CLERK  |
TJ        |  2100|ST_CLERK  |
Kevin     |  5800|ST_MAN    |

note04.gif

O exemplo a seguir deve ser executado no schema SQLTuning e mostra duas consultas possíveis para encontrar clientes que fizeram pedidos com o maior número de itens. Novamente, usamos uma solução com subselect e outra com CTE:

select *
from customer c
inner join sales_order so on c.customer_id = so.customer_id
where so.order_id in (	select i1.order_id
				      	from item i1
				      	group by i1.order_id
				      	having count(*) = (select max(count(*))
				                           from item i2
				                           group by i2.order_id
				                          )
                     );
-- --------------------------------------------------      
with MyCTE1 (order_id, item_count) as (
      select so2.order_id, count(*) 
      from sales_order so2
      inner join item i1 on i1.order_id=so2.order_id
      group by so2.order_id 
), MyCTE2 (max_count) as ( 
      select max(a.item_count)
      from MyCTE1 a
)
select *
from customer c
inner join sales_order so on c.customer_id = so.customer_id
inner join MyCTE1 on MyCTE1.order_id=so.order_id
inner join MyCTE2 on MyCTE1.item_count = MyCTE2.max_count;
CUSTOMER_ID|NAME        |ADDRESS      |CITY       |STATE|ZIP_CODE|AREA_CODE|PHONE_NUMBER|SALESPERSON_ID|CREDIT_LIMIT|ORDER_ID|ORDER_DATE         |CUSTOMER_ID|SHIP_DATE          |TOTAL|OBS                                               |ORDER_ID|ITEM_COUNT|MAX_COUNT|
-----------|------------|-------------|-----------|-----|--------|---------|------------|--------------|------------|--------|-------------------|-----------|-------------------|-----|--------------------------------------------------|--------|----------|---------|
        105|K + T SPORTS|3476 EL PASEO|SANTA CLARA|CA   |91003   |      408|     3769966|          7844|        5000|     617|2009-02-05 00:00:00|        105|2009-03-03 00:00:00|46370|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|     617|        10|       10|

note04.gif

Este query mostra os salários de cada empregado comparando-os com a média do departamento e da função. Como se trata de um subquery correlacionado vamos tentar arranjar uma solução mais eficiente com subselect simples:

select
   e1.ename,
   e1.sal,
   e1.job,
   (select round(avg(e2.sal)) from emp e2 where e2.job=e1.job) "Avg_Sal_Job",
   e1.deptno,
   (select round(avg(e3.sal)) from emp e3 where e3.deptno=e1.deptno) "Avg_Sal_Deptno"
from emp e1;
WITH w_avg_sal_job AS (
	SELECT job, avg(sal) AS Avg_Sal_Job
	FROM emp
	GROUP BY job
),
w_avg_sal_deptno AS (
	SELECT deptno, avg(sal) AS Avg_Sal_Deptno
	FROM emp
	GROUP BY deptno
)
SELECT
   e1.ename,
   e1.sal,
   e1.job,
   j.Avg_Sal_Job,
   e1.deptno,
   d.Avg_Sal_Deptno
FROM emp e1
INNER JOIN w_avg_sal_job j ON j.job=e1.job
INNER JOIN w_avg_sal_deptno d ON d.deptno=e1.deptno;

bthome.gifTopo


Usar CTE para query hierarquico

Alguns SGBDR não disponibilizam o query hierarquico do Oracle, não sendo possível resolver problemas deste tipo. O CTE permite recursividade, que com o auxilio das funções analíticas permitem resolver este problema:

WITH MyCTE (empno, ename, mgr, hierachy_level, myorder) AS (
	SELECT  boss.empno,
		boss.ename,
		boss.mgr,
		1 as hierachy_level,
		CAST(ROW_NUMBER() OVER (ORDER BY boss.empno) as VARCHAR2(4000)) as myorder
	FROM emp boss
	WHERE boss.mgr IS NULL 
	UNION ALL
	SELECT  emp.empno,
		emp.ename,
		emp.mgr,
		MyCTE.hierachy_level+1,
		MyCTE.myorder || '.' || CAST(ROW_NUMBER() OVER (PARTITION BY emp.mgr ORDER BY emp.empno) as VARCHAR2(4000)) as myorder
	FROM emp
	INNER JOIN MyCTE ON emp.mgr = MyCTE.empno
	WHERE emp.mgr IS NOT NULL
)
SELECT myc.*,
	  lpad(' ', (myc.hierachy_level-1)*6) || '* ' || ename as hierarchy
FROM MyCTE myc 
order by myc.myorder;
EMPNO|ENAME |MGR |HIERACHY_LEVEL|MYORDER|HIERARCHY                |
-----|------|----|--------------|-------|-------------------------|
 7839|KING  |    |             1|1      |* KING                   |
 7566|JONES |7839|             2|1.1    |      * JONES            |
 7788|SCOTT |7566|             3|1.1.1  |            * SCOTT      |
 7876|ADAMS |7788|             4|1.1.1.1|                  * ADAMS|
 7902|FORD  |7566|             3|1.1.2  |            * FORD       |
 7369|SMITH |7902|             4|1.1.2.1|                  * SMITH|
 7698|BLAKE |7839|             2|1.2    |      * BLAKE            |
 7499|ALLEN |7698|             3|1.2.1  |            * ALLEN      |
 7521|WARD  |7698|             3|1.2.2  |            * WARD       |
 7654|MARTIN|7698|             3|1.2.3  |            * MARTIN     |
 7844|TURNER|7698|             3|1.2.4  |            * TURNER     |
 7900|JAMES |7698|             3|1.2.5  |            * JAMES      |
 7782|CLARK |7839|             2|1.3    |      * CLARK            |
 7934|MILLER|7782|             3|1.3.1  |            * MILLER     |

note04.gif

Podemos obter a hierarquia da empresa usando uma consulta CTE recursiva. Esta consulta mostra os níveis hierárquicos de forma semelhante ao query hierarquico. Um CTE recursivo requer quatro elementos para funcionar corretamente:

Observe a coluna hierachy_level que tira partido da propriedade recursiva e aumenta em 1 unidade cada vez que a consulta vai um nível mais abaixo. Usamos esse valor para produzir, na última coluna, um output visual que mostra a hierarquia entre os funcionários.

Também precisamos da função analítica para atribuir um número a cada linha e usar esse número para classificar o output. Sem essa coluna, a ordem de escrita estaria incorreta.

bthome.gifTopo


Realizado por Turtle Learning ®. Última alteração em 2019-12-08