Banco de dados - SQL

(1)

Banco de dados - SQL

• Views

• Stored Procedures

(2)

Um exemplo prático

(3)

Um exemplo prático

Com os conhecimentos adquiridos até

agora, vamos tentar resolver a seguinte

consulta:

(4)

Um exemplo prático

Essa consulta é bem parecida com a consulta

“

Listar o nome

das disciplinas cuja média das notas seja maior ou igual a

7,0

”

.

Uma possível solução para consulta acima seria:

select nome

from Disciplina

where codigo in

(select cod_disciplina

from Nota

(5)

Um exemplo prático

Contudo, a consulta em questão solicita o nome da disciplina

com a maior média de nota, similar a última questão da nossa

lista do 3 bimestre:

“

Para o aluno João, obter sua matrícula,

sua

data

de

nascimento

(no

formato

DD,

MÊS_POR_EXTENSO de AAAA), o nome da disciplina e a

nota, da disciplina na qual ele tirou a nota mais

alta”

.

Que teria como possível solução:

Select aluno.matricula, date_format(aluno.data_de_nascimento,"%d, %M de %Y") as Nascimento, disciplina.nome ,nota.nota

from Aluno, Disciplina, Nota

where aluno.matricula = nota.matricula_aluno and disciplina.codigo = nota.cod_disciplina and aluno.nome = 'João‘

(6)

Um exemplo prático

Analisando as duas consultas anteriores, podemos

adaptá-las e assim chegar a seguinte solução

possível para a nossa consulta em questão:

select nome

from Disciplina

where codigo in

(select cod_disciplina

from Nota

(7)

Um exemplo prático

Ops, mensagem de erro!!!

Error code 1111.

(8)

Como resolver então?

Utilizando uma linguagem de programação,

podemos filtrar ou tratar o valor retornado por

uma consulta simples, para obtermos o

resultado esperado!

Questões de performance podem ser

levantadas por esse método, principalmente

em aplicações cliente servidor!!!

(9)

Views

Uma View (Visão) é uma tabela virtual baseada no conjunto

de resultados de uma consulta SQL.

Contém linhas e colunas como uma tabela real, e pode

receber comandos como declarações JOIN, WHERE e funções

como uma tabela normal.

Sintaxe:

CREATE VIEW Nome_visão

AS SELECT colunas

(10)

Views - exemplo

Para resolvermos a nossa consulta diretamente no banco de dados,

podemos criar uma

visão

contendo as médias das notas das

disciplinas e após, selecionarmos dessa visão o código da disciplina

com maior média e assim obtermos o nome da disciplina com maior

média de notas.

Exemplo:

CREATE VIEW media_notas_disciplina

AS SELECT cod_disciplina, avg(nota) as media from Nota group by

cod_disciplina;

SELECT nome from disciplina where codigo in

(11)

Views

–

outros exemplos

Listando o conteúdo de uma View:

SELECT * from nome_view;

Alterando uma View:

ALTER VIEW nome_view

AS SELECT colunas FROM tabela WHERE condições;

Eliminando uma View:

(12)

Stored Procedures

Outra forma de solucionarmos a consulta proposta no

segundo slide, seria usarmos os procedimentos armazenados,

mais conhecidos como

Stored Procedures

.

Antes disso,

vamos a definição:

• Um procedimento armazenado é uma sub-rotina disponível

para aplicações que acessam sistemas de banco de dados

relacionais.

• Podem ser usadas para validação de dados, controle de

acesso, execução de declarações SQL complexas e outras

situações.

(13)

Stored Procedures - Sintaxe

Sintaxe básica de criação de

Stored Procedures

:

CREATE PROCEDURE nome_procedimento

(parâmetros) declarações;

Chamando um procedimento criado:

(14)

Stored Procedures - Exemplo

Criando um procedimento para obter o nome da disciplina dado o seu código:

CREATE PROCEDURE ver_disciplina (varCodigo_disc varchar (5)) SELECT concat (“O nome da disciplina é ”, nome) as nome

from disciplina where codigo = varCodigo_disc);

Chamando o procedimento:

CALL ver_disciplina (“PROGI”);

Resultado:

O nome da disciplina é Programação

Excluindo o procedimento:

(15)

Stored Procedures

–

Blocos Begin...End;

• São

“contêineres”

usados para delimitar blocos de comandos a

serem executados por funções ou

Stored Procedures

.

• Cada declaração aninhada possui um delimitador (;).

• Um bloco Begin pode ser aninhado dentro de outros blocos.

• Uma observação importante diz respeito ao delimitador (;), pois ao

ser encontrado em um procedimento ou função, a finaliza

imediatamente.

(16)

Stored Procedures

–

Exemplo

Criando um procedimento para retornar o nome da disciplina com maior média de nota:

DELIMITER //

CREATE PROCEDURE Disc_maior_media () BEGIN

CREATE OR REPLACE VIEW media_nota_disciplina

AS SELECT cod_disciplina, avg(nota) as media from Nota group by cod_disciplina;

SELECT nome from disciplina where codigo in

(select cod_disciplina from media_nota_disciplina where media = (select max(media) from media_nota_disciplina)); END //

(17)

Triggers

Os Triggers, ou gatilhos, são procedimentos invocados quando um comando DML é executado. Algumas características dos triggers são:

• É associado a uma tabela;

• São armazenados no banco de dados como um arquivo separado;

• Não são chamados diretamente, são invocados automaticamente. Alguns usos comuns para os triggers:

• Verificação de integridade dos dados;

• Validação dos dados;

• Rastreamento e registro de logs de atividades nas tabelas;

(18)

Triggers - Sintaxe

Abaixo um exemplo de sintaxe básica de um

trigger

:

CREATE TRIGGER nome_trigger

timing operação

ON tabela

FOR EACH ROW

Declarações;

Onde:

Timing =

BEFORE | AFTER

(19)

Triggers - Exemplo

Criando uma nova tabela para ser usada no exemplo:

CREATE TABLE produto (

idProduto INT NOT NULL AUTO_INCREMENT, Nome_produto VARCHAR(45) NULL, Preco_Normal DECIMAL (10,2) NULL, Preco_Desconto DECIMAL (10,2) NULL,

PRIMARY KEY (idProduto));

Criando o Trigger:

CREATE TRIGGER tr_desconto BEFORE INSERT ON produto

FOR EACH ROW

SET NEW.Preco_Desconto = (NEW.Preco_Normal * 0.90);

Testando o Trigger:

(20)

Triggers - Funcionamento

• Em meio aos TRIGGERS temos dois operadores importantíssimos que nos possibilitam acessar as colunas da tabela alvo do comando DML, ou seja, podemos acessar os valores que serão enviados para uma tabela antes (BEFORE) ou depois (AFTER) de um UPDATE, por exemplo.

• Tais operadores nos permitirão então, ter dois momentos, o antes e o depois e também examinar os valores para que sejam ou não inseridos, atualizados ou excluídos da tabela. Vejamos as seguintes diretrizes:

• INSERT: o operador NEW.nome_coluna, nos permite verificar o valor enviado para ser

inserido em uma coluna de uma tabela.

OBS: OLD.nome_coluna não está disponível.

• DELETE: o operador OLD.nome_coluna nos permite verificar o valor excluído ou a ser

excluído.

OBS: NEW.nome_coluna não está disponível.

• UPDATE: tanto OLD.nome_coluna quanto NEW.nome_coluna estão disponíveis, antes

(21)

Triggers - Funcionamento

• Percebemos então que, ao inserir uma nova

linha em uma tabela, temos os valores das

colunas

disponível

através

do

operador

NEW.nome_coluna.

• Quando excluímos uma linha, temos ainda os

valores das colunas da linha excluída através do

operador

OLD.nome_coluna.

• E temos os dois operadores disponíveis no

(22)

Triggers

–

INSERT - Exemplo

Criaremos um TRIGGER que validará se os dados que foram

passados em uma declaração INSERT BEFORE, podem ou não ser

cadastrados na tabela disciplina. Validaremos o código com

quantidade mínima de caracteres igual a 4.

Delimiter //

Create trigger teste1 before insert on disciplina

For each row

Begin

Set @codigo = new.codigo;

If (char_lenght(@codigo) < 4) then

Set new.codigo = null;

End if;

End//

(23)

Triggers

–

Integridade referencial - Exemplo

Um INSERT que seja endereçado à tabela nota (que tem as colunas matricula_aluno e cod_disciplina definidas como NOT NULL), disparará um TRIGGER, tendo a responsabilidade de conferir se o aluno cuja nota a ser inserida existe na tabela de alunos e se a disciplina que receberá a nota está na tabela disciplina.

Delimiter //

Create trigger teste2 before insert on nota For each row

Begin

Select count(matricula) into @aluno from aluno where matricula = new.matricula_aluno;

Select count(codigo) into @disciplina from disciplina where codigo = new.cod_disciplina;

If (aluno = 0 ) or (disciplina = 0) then Set new.matricula_aluno = null;

Set new.cod_disciplina = null; End if;

End//

(24)

Triggers

–

Resolvendo a consulta do 2° slide

Uma terceira opção de solução para a consulta proposta no segundo slide seria criar uma tabela onde ficaria armazenado somente a informação da disciplina com a maior média. Essa tabela seria automaticamente atualizada com o valor da maior média, a cada nova inserção na tabela nota.

Delimiter //

Create trigger teste3 after insert on nota For each row

Begin

Select max(media) into @maior_media from media_notas_disciplina;

select cod_disciplina into @disciplina from media_notas_disciplina where media = (select max(media) from media_notas_disciplina);

Insert into maior_media Set cod_disciplina = @disciplina, media = @maior_media;

end//

(25)

Triggers

–

Resolvendo a consulta do 2° slide

Melhorando o trigger:

Delimiter //

Create trigger teste3 after insert on nota For each row

Begin

select count(cod_disciplina) into @nulo from maior_media; if (@nulo = 1) then

select cod_disciplina into @deletar from maior_media; delete from maior_media where cod_disciplina = @deletar;

Insert into maior_media Set cod_disciplina = @disciplina, media = @maior_media; Else

Insert into maior_media Set cod_disciplina = @disciplina, media = @maior_media; end if;

(26)