Common Table Expressions (CTE) usando SQL 2005
Problema do aluno: Preciso desenvolver um sistema no estilo network-marketing, onde os participantes recebam um percentual sobre as vendas até o 5º nível de hierarquia. Tudo bem com as telas, mas o banco de dados “está pegando”. Como faço as pesquisas recursivas?
Solução: Usar CTE, que é um recurso presente no Microsoft SQL Server 2005.
Inicialmente, criei um banco de dados, uma tabela de funcionários com a hierarquia definida:
USE MASTER
IF EXISTS(SELECT * FROM SYSDATABASES WHERE NAME=’EXEMPLO_CTE’)
DROP DATABASE EXEMPLO_CTE
–
CREATE DATABASE EXEMPLO_CTE
GO
USE EXEMPLO_CTE
–
CREATE TABLE FUNCIONARIO
(
COD INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
NOME VARCHAR(50) NULL,
COD_SUPERIOR INT NULL FOREIGN KEY REFERENCES FUNCIONARIO
)
–
INSERT INTO FUNCIONARIO VALUES (NULL, NULL)
INSERT INTO FUNCIONARIO VALUES (NULL, 1)
INSERT INTO FUNCIONARIO VALUES (NULL, 1)
INSERT INTO FUNCIONARIO VALUES (NULL, 2)
INSERT INTO FUNCIONARIO VALUES (NULL, 3)
INSERT INTO FUNCIONARIO VALUES (NULL, 4)
INSERT INTO FUNCIONARIO VALUES (NULL, 5)
INSERT INTO FUNCIONARIO VALUES (NULL, NULL)
INSERT INTO FUNCIONARIO VALUES (NULL, 7)
INSERT INTO FUNCIONARIO VALUES (NULL, 8)
INSERT INTO FUNCIONARIO VALUES (NULL, 5)
INSERT INTO FUNCIONARIO VALUES (NULL, 6)
INSERT INTO FUNCIONARIO VALUES (NULL, 5)
INSERT INTO FUNCIONARIO VALUES (NULL, 5)
INSERT INTO FUNCIONARIO VALUES (NULL, 12)
INSERT INTO FUNCIONARIO VALUES (NULL, 12)
INSERT INTO FUNCIONARIO VALUES (NULL, 14)
INSERT INTO FUNCIONARIO VALUES (NULL, 15)
–
UPDATE FUNCIONARIO
SET NOME = ‘FUNCIONARIO ‘ + CAST(COD AS VARCHAR)
WHERE COD_SUPERIOR IS NULL
–
UPDATE FUNCIONARIO
SET NOME = ‘FUNCIONARIO ‘ + CAST(COD AS VARCHAR)
+ ‘ SUBORDINADO AO ‘ + CAST(COD_SUPERIOR AS VARCHAR)
WHERE COD_SUPERIOR IS NOT NULL
Após isso, fiz o select:
WITH FUNCS(COD, NOME, SUP, HIERARQUIA, NIVEL)
AS
(
SELECT *,
CONVERT(VARCHAR(MAX), COD),
0
FROM FUNCIONARIO
WHERE COD_SUPERIOR IS NULL
UNION ALL
SELECT F.*, FUNCS.HIERARQUIA + ‘ - ‘ + CONVERT(VARCHAR, F.COD), FUNCS.NIVEL+1
FROM FUNCIONARIO F
INNER JOIN FUNCS ON F.COD_SUPERIOR = FUNCS.COD
)
SELECT * FROM FUNCS
ORDER BY HIERARQUIA
