View
0
Download
0
Category
Preview:
Citation preview
Prof.: Leonardo Rodrigues Ribeiro
Disciplina: Banco de Dados
IFBA – Campus Brumado
*
**Os sistemas de gerenciamento de banco de dados mais
modernos utilizam recursos baseados em sub-rotinas,
normalmente referenciadas como triggers (gatilhos) e
stored procedures (procedimentos armazenados).
*Sub-Rotina definição:
*É a parte de uma rotina (um conjunto de instruções
relativas a um cálculo ou função em um programa)
que realiza uma tarefa específica em um programa
de computador.
*Essas sub-rotinas são baseadas numa linguagem
estruturada de script.
*Possuem sequência, decisão e laços de repetição.
*Seguem o padrão compatível com a sintaxe SQL:2003.
**Stored Procedures:
*É similar a uma sub-rotina de programação, mas em
linguagem SQL, onde uma coleção de comandos
armazenados são definidos e serão utilizados para gerenciar
um banco de dados, toda vez que uma determinada tarefa
necessite ser realizada.
**Estrutura sintática da stored procedure:
*DELIMITER: delimita o escopo da sub-rotina.
* Ex.: DELIMITER |
//sub-rotina
|
*CREATE PROCEDURE <nome> ([parâmetro])
corpo da rotina
* <nome> - nome de identificação da sub-rotina.
* [Parametro] - é uma das opções IN e OUT colocadas antes do
nome de identificação de uma variável e do tipo de dado válido
da variável informada.
* Corpo da rotina – é um código SQL válido. Pode ter uma ou mais
linhas de código. Começa com BEGIN e finaliza com END.
*
*Estrutura sintática da stored procedure:
*Corpo da sub-rotina.
*Ex.: BEGIN
//instruções
END
*Declaração de variáveis locais, fica entre begin e
end:
*Ex.: DECLARE <variável> <tipo> [DEFAULT valor];
*Use o comando CALL seguido do nome da stored
procedure para realizar o seu processamento.
*Ex.: CALL <nome> ;
*
* Instruções que podem ser colocadas no corpo da sub-rotina.
* Para atribuir um valor a uma variável use o comando SET
SET <variável> = <valor / expressão lógica ou matemática>
* Estruturas de controle:
IF <(condição)> THEN
instruções executadas se a condição for verdadeira
[ELSEIF <(condição)> THEN
instruções executadas senão se a condição for verdadeira
[ELSE
instruções executadas se a condição for falsa ]
END IF;
* Estrutura de controle CASE:
CASE <variável>
WHEN <valor/ condição> THEN instruções a serem executadas
[WHEN = <valor <valor/ condição> THEN instruções a serem executadas]
[ELSE instruções a serem executadas]
END CASE;
*
*Instruções que podem ser colocadas no corpo da sub-rotina.
*Estruturas de repetição:
[rótulo:] REPEAT
instruções
UNTIL (condição) END REPEAT [rótulo];
*Estrutura de repetição:
[rótulo:] WHILE (condição) DO
instruções
END WHILE [rótulo];
*
*Crie e execute uma procedure que imprima uma
saudação na tela.
DELIMITER [caracter de finalização]
CREATE PROCEDURE <nome> ([parâmetro])
BEGIN
[DECLARE <variável> <tipo> [DEFAULT valor];]
instruções
END [caracter de finalização]
DELIMITER ;
CALL <nome> ([parametro]);
*Crie e execute uma procedure que imprima uma
saudação na tela.
DELIMITER |
CREATE PROCEDURE <nome> ([parâmetro])
BEGIN
[DECLARE <variável> <tipo> [DEFAULT valor];]
instruções
END |
DELIMITER ;
CALL <nome> ([parametro]);
*Crie e execute uma procedure que imprima uma
saudação na tela.
DELIMITER |
CREATE PROCEDURE sp_saudacão ()
SELECT ‘Aló, Mundo!’;
| DELIMITER ;
CALL sp_saudacao();
*Crie e execute uma procedure que imprima uma
saudação na tela.
DELIMITER |
CREATE PROCEDURE sp_saudacão ()
BEGIN
[DECLARE <variável> <tipo> [DEFAULT valor];]
instruções
END |
DELIMITER ;
CALL <nome> ([parametro]);
*Crie e execute uma procedure que imprima uma
saudação na tela.
DELIMITER |
CREATE PROCEDURE sp_saudacao ()
SELECT ‘Aló, Mundo!’;
| DELIMITER ;
CALL ([parametro]);
*
*Crie e execute um stored procedure que receba como parametro um nome e exiba a mensagem ‘alo mundo’ acompanhada do nome recebido como parametro:
delimiter #
CREATE PROCEDURE sp_saudacao2 (IN nome VARCHAR(50))
begin
declare mensagem varchar(11);
set mensagem = 'alo mundo';
select mensagem;
select nome;
end #delimiter
CALL sp_saudacao2(“IFBA”);
*
*Crie e execute um stored procedure que exiba o nome e
o salário dos registros da tabela CADFUN:
delimiter #
CREATE PROCEDURE sp_dados()
SELECT NOME, SALARIO FROM CADFUN;
#delimiter
CALL sp_dados();
*
*Crie e execute um stored procedure que exiba o nome e
o salário dos registros da tabela CADFUN, de um
departamento qualquer, usando parametros:
delimiter #
CREATE PROCEDURE sp_dados2 (IN dep Varchar(2))
BEGIN
SELECT NOME, SALARIO FROM CADFUN WHERE DEPTO =
dep;
END
#delimiter
CALL sp_dados2 (1);
*
*Crie e execute um stored procedure que receba dois números inteiros como entrada e retorne como resultado a soma dos dois valores:
delimiter #
CREATE PROCEDURE sp_calcula (IN val1 integer, IN val2 integer, OUT resultado integer)
BEGIN
set resultado = val1 + val2;
END
#delimiter
*
*Crie e execute um stored procedure que receba dois números reais como entrada e retorne como resultado a soma dos dois valores caso essa soma seja maior que 10, caso seja menor não exibe nada:
delimiter #
CREATE PROCEDURE sp_calcula2 (IN val1 float, IN val2 float)
BEGIN
declare resultado float;
set resultado = val1 + val2;
if (resultado > 10) then
select resultado;
end if;
END
#delimiter
*
* Crie e execute um stored procedure que retorne o mês do ano, em português,
de acordo com a data atual, utilizando o comando CASE ao invés do IF:
delimiter #
CREATE PROCEDURE sp_nome_mes ()
BEGIN
declare valor integer;
set valor = month(curdate());
case valor
when 1 then select 'JANEIRO'; when 2 then select 'FEVEREIRO';
when 3 then select 'MARÇO'; when 4 then select 'ABRIL';
when 5 then select 'MAIO'; when 6 then select 'JUNHO';
when 7 then select 'JULHO'; when 8 then select 'AGOSTO';
when 9 then select 'SETEMBRO'; when 10 then select 'OUTUBRO';
when 11 then select 'NOVEMBRO'; when 12 then select 'DEZEMBRO';
end case;
END
#delimiter
*
* Melhore a stored procedure anterior para que ela receba uma data e retorne o
mês do ano:
delimiter #
CREATE PROCEDURE sp_nome_mes2 (IN dt Date)
BEGIN
declare valor integer;
set valor = month(dt);
case valor
when 1 then select 'JANEIRO'; when 2 then select 'FEVEREIRO';
when 3 then select 'MARÇO'; when 4 then select 'ABRIL';
when 5 then select 'MAIO'; when 6 then select 'JUNHO';
when 7 then select 'JULHO'; when 8 then select 'AGOSTO';
when 9 then select 'SETEMBRO'; when 10 then select 'OUTUBRO';
when 11 then select 'NOVEMBRO'; when 12 then select 'DEZEMBRO';
end case;
END
#delimiter
*
*Trigger:
*É similar a descrição de um procedimento, sendo um recurso associado a um banco de dados, que é executado automaticamente quando uma condição preestabelecida é satisfeita. É utilizado para associar um procedimento armazenado a um evento de banco de dados.
*Usos:
*Verificação de integridade dos dados
*Validação dos dados
*Rastreamento e registro dos logs de atividades nas tabelas
*Arquivamento de registros excluídos
*
*Sintaxe para criação de uma Trigger:
DELIMITER [caracter de finalização]
CREATE TRIGGER <nome> <timing> <operação> ON <tabela>
FOR EACH ROW [BEGIN]
<sentença>
[END]
[caracter de finalização]
DELIMITER ;
Timing: BEFORE | AFTER
Operação: INSERT | UPDATE | DELETE
*
* Exemplo:
CREATE TABLE produto(
idproduto INT NOT NULL AUTO_INCREMENT,
nome_produto VARCHAR(30),
preco_normal DECIMAL(10,2),
preco_desconto DECIMAL(10,2),
PRIMARY KEY (idproduto));
CREATE TRIGGER tr_desconto BEFORE INSERT ON produto FOR EACH ROW
SET new.preco_desconto = (NEW.preco_normal * 0.90);
INSERT INTO produto (nome_produto, preco_normal) VALUES (‘FEIJAO’, 1.00);
SELECT * FROM produto;
*
*Ajustando o banco ...
*Crie a tabela auditoria.
CREATE TABLE auditoria (
USUARIO VARCHAR(40),
ACAO VARCHAR(9),
DATA DATE,
HORA TIME,
CODIGO INTEGER
);
*
* Crie a trigger tr_auditor1, que irá registrar na tabela AUDITORIA o
usuário que cadastrou o novo funcionário. Essa trigger será executada
automaticamente toda vez que um novo funcionário for cadastrado
em CADFUN:
delimiter #
CREATE TRIGGER tr_auditor1 AFTER INSERT ON cadfun
FOR EACH ROW
BEGIN
INSERT INTO auditoria SET
codigo = NEW.CODFUN,
usuario = USER(),
acao = 'cadastrou',
data = CURDATE(),
hora = CURTIME() ;
END
# delimiter;
*
* Crie a trigger tr_auditor2, que irá registrar na tabela AUDITORIA o
usuário que alterou dados do funcionário da tabela CADFUN:
delimiter #
CREATE TRIGGER tr_auditor2 BEFORE UPDATE ON cadfun
FOR EACH ROW
BEGIN
INSERT INTO auditoria SET
codigo = NEW.CODFUN,
usuario = USER(),
acao = 'alterou',
data = CURDATE(),
hora = CURTIME()
;
END
# delimiter;
*
* Crie a trigger tr_auditor3, que irá registrar na tabela AUDITORIA o
usuário que deletou um funcionário da tabela CADFUN:
delimiter #
CREATE TRIGGER tr_auditor3 BEFORE DELETE ON cadfun
FOR EACH ROW
BEGIN
INSERT INTO auditoria SET
codigo = OLD.CODFUN,
usuario = USER(),
acao = 'Demitiu',
data = CURDATE(),
hora = CURTIME()
;
END
# delimiter;
*
* Desafio ...
* Antes de demitir (excluir) o funcionário ele deve ser registrado na
tabela MORTO.
delimiter #
CREATE TRIGGER tr_demite BEFORE DELETE ON cadfun
FOR EACH ROW
BEGIN
INSERT INTO morto
SELECT CODFUN, NOME, DEPTO, FUNCAO, SALARIO, ADMISSAO,
FILHOS
FROM cadfun
WHERE CODFUN = old.CODFUN;
END
# delimiter;
*O que aconteceu???
Recommended