Sistema de Gerenciamento de BD - Aula 03

Prévia do material em texto

Prof. Martin MorãesProf. Martin Morães
AulaAula33
Sistema Gerenciador 
de Banco de Dados1
10
Conversa Inicial
Programas Armazenados
� Procedures, 
triggers e funções
� Programações 
dentro do DB
� Como criar e 
como utilizar
Contextualizando
Programas Armazenados
� Automatizar 
rotinas:
• log
• atualização de 
estoque
• validações
• etc.
Pesquisa
Programas 
Armazenados
Programas Armazenados
� Cada SGDB 
implementa a sua 
própria 
linguagem de 
programação
� Comumente 
denominada de 
PL, acrônimo 
para Procedural 
Language
� As procedures
� As functions
� As triggers
Delimitadores
Programas Armazenados –
Delimitadores
� Toda instrução SQL 
tem que terminar 
com um caractere 
delimitador
� Os programas 
armazenados são 
instruções SQL do 
tipo CREATE 
� Precisam ser 
encerrados com 
um delimitador
seq Código
1 Delimitador *
2
Cria procedure totaliza()
INÍCIO
Instrução SQL 1;
Instrução SQL 2;
Instrução SQL 3;
FIM*
3 Delimitador;
Variáveis
Variáveis
� A sintaxe é:
DECLARE var_name [, var_name] 
... type [DEFAULT value]
• Exemplo:
DECLARE vlr, vlrUm int;
DECLARE vlrDois int
DEFAULT 5;
� Default NULL
• Atribuição
� SET vlr = 10;
� SELECT MIN(id), 
MAX(id) 
INTO num1, num2 
FROM alunos;
Controle de Fluxo
Controle de Fluxo
� If
� Case
� While
� Repeat
Controle de Fluxo – If
� Sintaxe:
IF search_condition THEN 
statement_list
[ELSEIF 
search_condition THEN 
statement_list] ...
[ELSE statement_list]
END IF
• Exemplo:
IF num1 = 1 THEN
SELECT "UM";
ELSEIF num1 > 1 THEN
SELECT “MAIOR”;
END IF;
Controle de Fluxo – Case
� Sintaxe:
CASE case_value
WHEN when_value
THEN statement_list
[WHEN when_value
THEN statement_list] ...
[ELSE statement_list]
END CASE
• Exemplo:
CASE num1
WHEN 0 THEN 
SELECT “ZERO”;
WHEN 1 THEN
SELECT “UM”;
WHEN 2 THEN 
SELECT “DOIS”;
END CASE;
Controle de Fluxo – While
� Sintaxe:
WHILE search_condition DO
statement_list
END WHILE
• Exemplo:
WHILE num2 > 10 DO
SET num2 = 
num2 – 100;
END WHILE;
Controle de Fluxo – Repeat
� Sintaxe:
REPEAT
statement_list
UNTIL search_condition
END REPEAT
• Exemplo:
REPEAT
SET num2 = 
num2 – 10;
UNTIL num2 < 10
END REPEAT;
Procedimentos –
Procedures
Procedure
� Sintaxe:
CREATE PROCEDURE sp_name 
([proc_parameter[,...]])
routine_body
• Exemplo:
CREATE PROCEDURE lista_prof_aluno()
BEGIN
SELECT p.nome, a.nome
from aula
left join alunos a 
on aula.idaluno = a.id
left join professores p 
on aula.idprofessor = p.id;
END
� Executar a procedure
• Sintaxe:
CALL sp_name 
([parameter[,...]])
CALL sp_name[()]
� Exemplo:
CALL lista_prof_aluno();
CALL lista_prof_aluno;
� Especificando parâmetros
• IN
• OUT
• INOUT
Procedure – IN
� Exemplo:
CREATE PROCEDURE `lista_prof_aluno`(in idProf
int)
BEGIN
SELECT p.nome, a.nome
from aula
left join alunos a on 
aula.idaluno = a.id
left join professores p on 
aula.idprofessor = p.id
where
aula.idprofessor = idProf;
END
� Exemplo:
call lista_prof_aluno(101);
Procedure – OUT
� Variáveis de ambiente
SET @NUM = 10, @nome = 
“MARTIN”;
SELECT @NUM, @nome;
CREATE PROCEDURE 
`lista_prof_aluno_TOTAL`(in idProf int, out 
total int)
BEGIN
SELECT count(*) into total 
from aula
left join alunos a on 
aula.idaluno = a.id
left join professores p on 
aula.idprofessor = p.id
where
aula.idprofessor = idProf;
END
� set @total_registros = 0;
� call lista_prof_aluno_TOTAL
(101, @total_registros);
� select @total_registros;
Procedure – INOUT
CREATE PROCEDURE 
`lista_prof_aluno_TOTAL`(inout total int)
BEGIN
SELECT count(*) into total 
from aula
left join alunos a on 
aula.idaluno = a.id
left join professores p on 
aula.idprofessor = p.id
where
aula.idprofessor = total;
END
� set @total_registros = 101;
� call lista_prof_aluno_TOTAL
(@total_registros);
� select @total_registros;
Gatilhos – Triggers
Triggers
� Na criação define-se:
• um nome 
• ocasião
� “BEFORE” ou 
“AFTER”
• eventos
� “insert”
� “update”
� “delete”
• tabela
• ordem
� Sintaxe:
CREATE TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name
FOR EACH ROW
[trigger_order]
trigger_body
• Exemplo:
DELIMITER //
CREATE TRIGGER contador
AFTER INSERT
ON professores 
FOR EACH ROW
BEGIN
SET @Total = @Total+1;
END//
DELIMITER;
� Os operadores
• “OLD”
• “NEW”
CREATE TRIGGER contador
AFTER INSERT ON professores
FOR EACH ROW
BEGIN
SET @Total = 
@Total+1;
SET @NOMES = 
CONCAT_WS(';', 
@NOMES, 
NEW.nome);
END
SET @NOMES = "A";
INSERT INTO 
professores(nome) 
VALUES("Aurélio")
,("David"),("João“
);
SELECT @NOMES;
# @NOMES
'A;Aurélio;David;João'
� INSERT só tem 
(NEW), não tem 
(OLD)
� DELETE só tem 
(OLD), não tem 
(NEW)
� UPDATE tem 
(NEW) e (OLD)
Síntese
Programas Armazenados
� Procedures, 
triggers e funções
� Programações 
dentro do DB
� Como criar e 
como utilizar
� Automatizar rotinas:
• log
• atualização de 
estoque
• validações
• etc.