Recursividade com CTE (commom table expression)

08 May, 2023

Em banco de dados relacionais SQL, common table expression (CTE) funciona como a tabela temporária que armazena um conjunto de dados derivado de uma consulta simples e definido no escopo de execução de uma instrução SELECT, INSERT, UPDATE ou DELETE.

CTEs podem ser uma boa alternativa à subqueries, views, functions e mesmo a tabela temporárias, e está disponível na maioria dos bancos relacionais que implementam o padrão SQL:ANSI99 como SQL Server, Oracle, PostgreSQL, MySQL.

Nesse artigo mostarei um exemplo do uso de CTE usando consultas recursivas.

Dada a tabela employees do esquema de exemplo Human Resources da Oracle mas nesse caso transcrito para SQL Server, vamos mostrar os empregados que reportam, diretamente ou indiretamente, para o empregado 101 e seu respectivo nível de subordinação.

Estrutura da tabela employees (chave primária e demais restrições são irrelevantes para esse exemplo):

CREATE TABLE [dbo].[EMPLOYEES](
	[EMPLOYEE_ID] [numeric](6, 0) NOT NULL,
	[FIRST_NAME] [varchar](20) NULL,
	[LAST_NAME] [varchar](25) NOT NULL,
	[EMAIL] [varchar](25) NOT NULL,
	[PHONE_NUMBER] [varchar](20) NULL,
	[HIRE_DATE] [datetime2](0) NOT NULL,
	[JOB_ID] [varchar](10) NOT NULL,
	[SALARY] [numeric](8, 2) NULL,
	[COMMISSION_PCT] [numeric](2, 2) NULL,
	[MANAGER_ID] [numeric](6, 0) NULL,
	[DEPARTMENT_ID] [numeric](4, 0) NULL,
	[ROWID] [uniqueidentifier] NOT NULL)

Utilizamos a CTE a seguir para retornar os empregados que reportam, diretamente ou indiretamente, para o empregado 101 e o seu nível de subordinação:

WITH reports_to_101 (eid, emp_last, mgr_id, reportLevel) AS
      (
        SELECT employee_id, last_name, manager_id, 0 reportLevel
        FROM employees
        WHERE employee_id = 101
        UNION ALL
        SELECT e.employee_id, e.last_name, e.manager_id, reportLevel + 1
        FROM reports_to_101 r 
		JOIN employees e ON r.eid = e.manager_id
      )
    SELECT eid, emp_last, mgr_id, reportLevel
    FROM reports_to_101
    ORDER BY reportLevel, eid;

No CTE acima é dado o alias reports_to_101 com quatro colunas para retornar os seguintes dados do empregado: id, último_nome, id_do_gerente e nível_de_subordinação. A CTE reports_to_101 possui duas definições de consultas:

1. A primeira definição de 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); e,

2. A segunda definição de consulta é a consulta recursiva que faz a junção com o conteúdo da própria reports_to_101 e da âncora; e o resultado é adicionado em reports_to_101. A operação irá terminar quando não mais linhas foram encontradas pela consulta recursiva.

Resultado final da CTE reports_to_101:

I know it's only SQL but I like it.

O que achou deste post? 😊😐☹️

#cte #data #oracle #sql #sqlserver