Transact-SQL - Básico: DML e DQL

 1.    Definição

 

Data Manipulation Language (DML) significa Linguagem de Manipulação de Dados. Data Query Language (DQL) significa Linguagem de Consulta a Dados. São outros segmentos da linguagem SQL e apresenta meios para os dados serem armazenados ou visualizados. Antigamente se falava somente em DML. Alguns especialistas decidiram separar as duas classes. Alguns ainda reconhecem somente a DML. Irei referir genericamente a DML/DQL, mas quando falar das instruções em particular separarei os termos.

O importante é reconhecer a diferença entre DDL e DML/DQL: a primeira cria tabelas e a segunda manipula e consulta dados nas tabelas. Esta associação, apesar de incompleta, nos dá o exemplo claro da divisão entre dois segmentos de linguagem do SQL.

A manipulação e consulta dos dados pode ser visto em quatro aspectos: inserção, alteração, exclusão e visualização. Observe o quadro a seguir e compare a cláusula utilizada de acordo função:

 

Função	Cláusula para sintaxe
Inserir (DML)	INSERT
Alterar (DML)	UPDATE
Excluir (DML)	DELETE
Visualizar (DQL)	SELECT

 

No trabalho com os dados deve existir o cuidado do desenvolvedor em reconhecer o quanto se afeta uma ou mais tabelas à medida que se executam os comandos acima.

Antes de qualquer procedimento, pergunte-se: estarei inserindo um registro inteiro ou alterando um já existente? Devo alterar um só registro da tabela, ou vários, ou todos? Quais os cuidados que devo ter para apagar um registro? E sobre a visualização: qual informação necessito demonstrar ao usuário?

O trabalho de manipulação de dados, portanto, não depende simplesmente dos comandos acima, mas também do conhecimento dos predicados. Entenda-se por predicado toda a informação condicional que seleciona um conjunto específico dentro do seu banco de dados. A cláusula para a determinação de um predicado em DML/DQL é o WHERE.

Para exemplificar o uso do predicado, utilizaremos o comando de visualização de dados. Contudo, não podemos usar a visualização se não existir dados na tabela. Veja como inserir registros na tabela Cad_Curso criada acima:

 

INSERT INTO Cad_Curso (Codigo, Descricao, Nivel, Media)

VALUES (‘000001’, ’Curso de SQL Iniciante’, 1, 5.0)

 

INSERT INTO Cad_Curso (Codigo, Descricao, Nivel, Media)

VALUES (‘000002’, ’Curso de SQL Intermediario’, 2, 7.5)

 

INSERT INTO Cad_Curso (Codigo, Descricao, Nivel, Media)

VALUES (‘000003’, ’Curso de SQL Avancado’, 3, 8.0)

 

Os comandos acima, inserem três registros na tabela Cad_Curso. São três cursos de SQL em diferentes níveis e com diferentes médias para conclusão dos cursos. Cada um recebeu um código, de modo que se tentássemos inserir outro curso com um desses valores, ocorreria um “Key Violation” pois foi definido que o campo código faz parte da chave primária.

Podemos executar a visualização agora:

 

SELECT * FROM Cad_Curso

 

Seleciona todos os campos e todos os registros disponíveis na tabela Cad_Curso.

Outra visualização dos dados:

 

SELECT Codigo, Descricao FROM Cad_Curso

WHERE Media > 6

 

Seleciona os campos código e descrição para serem apresentados, mas demonstra somente os registro que possuem o campo média maior que 6, ou seja, os dois últimos registros do exemplo de inserção.

O uso da seleção através do predicado pode ser comparado à teoria dos conjuntos: imagine um conjunto de dados que representa toda a tabela em questão. A condição fornecida através do WHERE,seleciona um subconjunto de dados que está contido no principal.

As condições por serem booleanas, podem ser incrementadas com comandos de conexão:

 

Operador	Cláusula para sintaxe
E	AND
OU	OR
NÃO	NOT

 

SELECT Código, Descricao FROM Cad_Curso

WHERE Media > 6 and Nivel = 3

 

Seleciona os campos código e descrição, mas demonstra somente registros que tenham a média maior que 6 e que o nível do curso seja igual a 3.

A linha condicional mencionada acima pode ser usada inclusive para comandos de atualização (UPDATE) e exclusão (DELETE) de registro. Suponha uma alteração em que os cursos de níveis 2 e 3 passaram a ter média 6.5 para aprovação.

 

UPDATE Cad_Curso

SET Media=6.5

WHERE Nivel >= 2

 

O problema da atualização acima é que ele fará a alteração como prevista, porém, se o comando for rodado novamente, a atualização dos dois registros será feita novamente. Para prevenir que a alteração seja feita apenas uma vez, acrescente a condição que indique se a média for diferente do valor atribuído:

 

UPDATE Cad_Curso

SET Media=6.5

WHERE Nivel >= 2 and Media <> 6.5

 

Imagine agora que o curso avançado sairá de programação por não ter alunos que preencham o número mínimo de vagas. Vamos excluir este curso do cadastro:

 

DELETE FROM Cad_Curso

WHERE Nivel = 3

 

Perceba que estamos excluindo todos os cursos nível 3, não somente o curso de SQL. Se quiséssemos excluir um curso em especial, especifique na condição sempre a chave primária.

O próximo passo na jornada do aprendizado em SQL é efetuar o relacionamento entre tabelas. A ideia de relacionamento está vinculada à ideia de normalização de armazenamento de dados. Normalização é a distribuição eficiente de dados dentro de um banco de dados.

No exemplo que viemos desenvolvendo, considere um curso de SQL nível 1 e seus alunos. Ao invés de gravarmos uma tabela única com todos os dados da Cad_Curso acrescentando os dados do aluno, separamos em outras duas tabelas para não termos que ficar relembrando como foram cadastrados os dados do curso.

 

Uma tabela de cursos (Cad_Curso), uma de alunos (Cad_Aluno) e uma de matrícula (Mov_Matricula) que relaciona quais alunos estão matriculados em quais cursos.

Relembremos agora os comandos DDL para criarmos as duas tabelas ainda inexistentes no SGBD:

 

CREATE TABLE dbo.Cad_Aluno (

 Codigo      char(6)        NOT NULL,

 Nome        char(30)       NULL,

 

    CONSTRAINT PK_Cad_Aluno PRIMARY KEY (Codigo)

)

 

CREATE TABLE dbo.Mov_Matricula (

 Curso       char(6)        NOT NULL,

 Aluno       char(6)        NOT NULL,

 Data_Inicio SmallDateTime  NULL,

 Data_Final  SmallDateTime  NULL,

 

    CONSTRAINT PK_Mov_Matricula PRIMARY KEY (Curso, Aluno)

)

 

Detalhe importante: a chave primária da Mov_Matricula é composta. Isto significa que um mesmo aluno não poderá se cadastrar no mesmo curso duas vezes, pois esta é a função da chave primária, identificar um único registro dentro de uma tabela. Tentar cadastrar um aluno duas vezes no mesmo curso é causar um “Key violation”.

Vamos inserir dois alunos na Cad_Aluno:

 

INSERT INTO Cad_Aluno (Codigo, Nome)

VALUES (‘000001’, ’Primeiro aluno’)

 

INSERT INTO Cad_Aluno (Codigo, Nome)

VALUES (‘000002’, ’Segundo aluno’)

 

Agora vamos matriculá-los:

 

INSERT INTO Mov_Matricula (Curso, Aluno, Data_Inicio, Data_Final)

VALUES (‘000001’, ’000001’, ‘2023-07-03’, ‘2023-07-31’)

 

INSERT INTO Mov_Matricula (Curso, Aluno)

VALUES (‘000002’, ’000001’)

 

INSERT INTO Mov_Matricula (Curso, Aluno, Data_Inicio, Data_Final)

VALUES (‘000001’, ’000002’, ‘2023-07-03’, ‘2023-07-31’)

 

Veja que os alunos foram matriculados, mas ao inserirmos o ‘Primeiro aluno’ no curso SQL nível dois, não especificamos a Data_Inicial e Data_Final. Ele ficou matriculado no segundo curso para garantir a vaga, mas este curso ainda não tem dia pra começar.

Esta inserção de registro é possível, pois declaramos Data_Inicial e Data_Final como NULL, que significa que podemos deixá-los sem valores, ao contrário do código do curso e do aluno, que são NOT NULL.

Agora temos dados suficientes para demonstrar o que é relacionamento. Existem vários tipos de relacionamentos, mas por praticidade, vamos resumi-los em três tipos no quadro a seguir:

 

Cláusula	Descritivo
INNER	É um relacionamento bilateral restrito de modo que um comando de visualização irá retornar registros desde que existam nas duas tabelas.
LEFT / RIGHT	É um relacionamento unilateral de modo que um comando de visualização irá retornar registros da tabela da esquerda (LEFT) ou direita (RIGHT) independente se existe ou não na outra tabela.
FULL	É um relacionamento bilateral irrestrito de modo que um comando de visualização irá retornar registros, independente se exista uma tabela e não exista na outra.

 

Uma cláusula de relacionamento mencionada acima deve vir acompanhada por outra cláusula fixa: JOIN.

No exemplo das tabelas de cursos, alunos e matrículas imagine que queremos visualizar todas as matrículas que existem, demonstrando o nome do aluno e o curso que ele foi inscrito. Note que a tabela de matrícula possui o código do curso e o código do aluno. Para se encontrar a descrição e o nome, devemos recorrer às técnicas de relacionamento:

 

SELECT C.Descricao, A.Nome, M.Data_Inicio, M.Data_Final

FROM Mov_Matricula M

  INNER JOIN Cad_Curso C ON (M.Curso = C.Codigo)

  INNER JOIN Cad_Aluno A ON (M.Aluno = A.Codigo)

 

A sentença acima foi dividida em linhas propositalmente para identificarmos todos os elementos do código:

·        Os campos selecionados foram: descrição do curso, nome do aluno, data de início e data final do curso;

·         Estas informações estão separadas em diversas tabelas: Cad_Curso, Cad_Aluno e Mov_Matricula;

·         Iniciamos uma pesquisa em Mov_Matricula e demos um apelido para ela “M”;

·    Fizemos um relacionamento bilateral restrito (INNER JOIN) com o cadastro de cursos (Cad_Curso), o que significa que queremos retornar registros de ambas as tabelas (Cad_Curso e Mov_Matricula) somente se existir um relacionamento válido;

·         Adotamos o apelido “C” para a Cad_Curso;

·   Através da especificação ON, repassamos a condição de relacionamento das tabelas: pela chave primária da tabela primária, no caso, o código do curso. Entenda por tabela primária aquela que possui dados básicos de cadastros. As tabelas secundárias geralmente são de movimentos, possibilitando a integração entre duas tabelas primárias;

·    Entre parênteses as chaves são equivalentes sempre especificando o apelido (alias) da tabela e o campo de relacionamento;

·         Um segundo relacionamento, agora com a Cad_Aluno foi preparado;

·         Adotamos o apelido “A” para a Cad_Aluno;

·         Novamente especifica-se a cláusula INNER JOIN e através do ON, equivale-se os campos de relacionamento, sendo validado pelo código do aluno;

·        Cada campo retornado pelo SELECT também deve ser precedido pelo apelido da tabela de origem.

 

Por fim, se quisermos inserir um predicado para efetuar a condição:

 

SELECT C.Descricao, A.Nome, M.Data_Inicio, M.Data_Final

FROM Mov_Matricula M

  INNER JOIN Cad_Curso C ON (M.Curso = C.Codigo)

  INNER JOIN Cad_Aluno A ON (M.Aluno = A.Codigo)

WHERE M.Data_Inicio >= ’2023-07-01’

  and M.Data_Inicio <= ‘2023-07-31’

ORDER BY C.Descricao

 

Trará os cursos e alunos matriculados com data de início entre 01 e 31 de julho de 2023. Dois detalhes: os cursos sem data de início (NULL) não serão listados; e a lista virá ordenada pela descrição do curso (ORDER BY).

Neste momento, podemos explicar melhor as aplicações das Store Procedure’s, Trigger’s e View’s. Veja como o comando acima é extenso. Imagine toda vez reescrevê-lo para executar cada período de data de início de curso que quiser listar. Cria-se uma Store Procedure com os parâmetros da seleção:

 

DROP PROCEDURE dbo.SP_Lista_Matricula

GO

CREATE PROCEDURE dbo.SP_Lista_Matricula (

 @DtInicial SmallDateTime,

 @DtFinal SmallDateTime

)

AS BEGIN

  -- observe que as datas fixas

  -- foram substituídas por parâmetros

  SELECT C.Descricao, A.Nome, M.Data_Inicio, M.Data_Final

  FROM Mov_Matricula M

    INNER JOIN Cad_Curso C ON (M.Curso = C.Codigo)

    INNER JOIN Cad_Aluno A ON (M.Aluno = A.Codigo)

  WHERE M.Data_Inicio >= @DtInicial and M.Data_Inicio <= @DtFinal

  ORDER BY C.Descricao

 END

GO

 

-- Comando para executar a Store Procedure

-- enviando os parâmetros

EXEC SP_Lista_Matricula ‘2023-07-01‘, ’2023-07-31’

GO

 

Uma outra maneira de sintetizar a consulta acima seria por meio de view. Uma view é como se transformasse um bloco de tabelas relacionadas em uma tabela só:

 

DROP VIEW dbo.Vw_Lista_Matricula

GO

CREATE VIEW dbo.Vw_Lista_Matricula

AS

  SELECT C.Descricao, A.Nome, M.Data_Inicio, M.Data_Final

  FROM Mov_Matricula M

    INNER JOIN Cad_Curso C ON (M.Curso = C.Codigo)

    INNER JOIN Cad_Aluno A ON (M.Aluno = A.Codigo)

  ORDER BY C.Descricao

GO

 

-- Um comando bastante utilizado em Transact-SQL é o DECLARE.

-- Ele declara variáveis para serem utilizadas dentro de

-- um bloco de execução, inclusive dentro de Store Procedure’s

DECLARE @DtInicial SmallDateTime, @DtFinal SmallDateTime

 

-- A atribuição é feita através de um comando SELECT

SELECT @DtInicial = ’2023-07-01’, @DtFinal = ‘2023-07-31’

 

-- Observe que as datas foram retiradas para serem tratadas

-- na execução da view

SELECT * FROM Vw_Lista_Matricula

WHERE Data_Inicio >= @DtInicial and Data_Inicio <= @DtFinal

 

Este bloco da view não limitei ao que a sintaxe necessita para serem apresentados outros recursos da linguagem, como a declaração de variáveis e atribuição por meio de um SELECT, que não tem apenas a função de trazer registros de tabelas.

Finalizando, vamos fazer a exclusão de todas as matrículas no momento em que apagarmos um determinado curso do cadastro. Para fazer isto, utilizamos uma trigger que, como foi explicado, é executada toda vez que inserimos, alteramos ou excluímos um registro. Assim, vamos agregar esta rotina de exclusão das matrículas ao excluir um registro do Cad_Curso:

 

DROP TRIGGER dbo.Del_Cad_Curso

GO

CREATE TRIGGER dbo.Del_Cad_Curso

ON Cad_Curso FOR DELETE

AS BEGIN

  DELETE M  -- Observe que neste caso, podemos especificar o apelido

  FROM Mov_Matricula M

   INNER JOIN deleted d ON (M.Curso = d.Codigo)

END

GO

 

-- Exemplo de um comando de exclusão que irá “startar” a trigger

DELETE FROM Cad_Curso

WHERE Codigo=’000001’

 

Atente-se à tabela “deleted”. Esta é uma tabela que apenas subexiste durante a execução da trigger contendo os registros que foram apagados na tabela especificada na declaração ON. Se a trigger fosse de inserção (FOR INSERT), a tabela preenchida seria uma chamada “inserted”.

No caso de uma alteração (FOR UPDATE) a situação seria a seguinte: a tabela “deleted” conteria dados antigos do registro, e a tabela “inserted” teria os novos dados do registro. Isto porque alteração é exatamente isto: uma exclusão de dados antigos e uma inserção de dados novos. Este recurso pode ser utilizado para gravar uma tabela de log, por exemplo.

Um último detalhe a ser observado: o comando de exclusão foi utilizado junto com um relacionamento. Isto é possível, pois o SGBD interpretará como sendo uma exclusão de registro previamente existente dentro de uma condição. Esta condição limita-se a existência de uma ocorrência de uma chave em outra tabela.