5 - Procedures e Triggers

Prévia do material em texto

Banco de Dados II
Aula 1 - Stored Procedures, 
Functions, Cursores, Triggers e Views.
Reginaldo Gotardo – Uniseb Ciência da Computação – 5o semestre
2
Nós veremos 
1. Stored Procedures, Functions e Cursores
2. Triggers
3. Views
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
Aula 2 – Stored Procedures, Functions, 
Cursores, Triggers e Views.
Parte I
Stored Procedures, Functions, 
Cursores
4
Stored Procedures
Nome dado ao recurso que possibilita
armazenar no servidor um conjunto de
instruções que processem determinados
valores e ações com o objetivo de atingir um
resultado
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
5
Stored Procedures
Nome dado ao recurso que possibilita
armazenar no servidor um conjunto de
instruções que processem determinados
valores e ações com o objetivo de atingir um
resultado
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
Em outras palavras, são blocos de 
código SQL armazenados no 
servidor, normalmente com o 
objetivo de realizar transações 
completas.
6
Stored Procedures
Benefícios
– Centralização
– Segurança
– Velocidade
– Suporte a transações
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
7
Stored Procedures
Benefícios
– Centralização
– Segurança
– Velocidade
– Suporte a transações
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
Em casos em que o banco será 
acessado por diversas aplicações, 
escritas em diversas linguagens, e 
cada sistema terá uma 
responsabilidade diferente.
8
Stored Procedures
Benefícios
– Centralização
– Segurança
– Velocidade
– Suporte a transações
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
Em casos em que o banco será 
acessado por diversas aplicações, 
escritas em diversas linguagens, e 
cada sistema terá uma 
responsabilidade diferente.
Os códigos de interação com o banco 
são escritos uma única vez!
Quando for necessário realizar 
manutenção no código escrito, essa 
tarefa será realizada uma única vez, 
diretamente na stored procedure.
9
Stored Procedures
Benefícios
– Centralização
– Segurança
– Velocidade
– Suporte a transações
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
É possível restringir a interação dos 
desenvolvedores com o banco de 
dados por meio de stored 
procedures (pensando de novo em 
diferentes equipes de projeto).
10
Stored Procedures
Benefícios
– Centralização
– Segurança
– Velocidade
– Suporte a transações
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
É possível restringir a interação dos 
desenvolvedores com o banco de 
dados por meio de stored 
procedures (pensando de novo em 
diferentes equipes de projeto).
Toda e qualquer informação requerida ao 
banco de dados deve ser feita por meio de 
stored procedures. Como a gerência desse 
recurso fica a cargo de um único 
responsável qualificado, evita-se a 
execução de códigos mal desenvolvidos ou 
mal intencionados.
11
Stored Procedures
Benefícios
– Centralização
– Segurança
– Velocidade
– Suporte a transações
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
Em se tratando de um bloco de códigos em vez de 
uma única expressão SQL, a execução desses blocos 
escritos diretamente no servidor, onde são nativos, 
pode resultar num aumento de performance do que 
se fosse executado em diversas solicitações à parte 
vindas de uma aplicação externa.
12
Stored Procedures
Benefícios
– Centralização
– Segurança
– Velocidade
– Suporte a transações
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
Em se tratando de um bloco de códigos em vez de 
uma única expressão SQL, a execução desses blocos 
escritos diretamente no servidor, onde são nativos, 
pode resultar num aumento de performance do que 
se fosse executado em diversas solicitações à parte 
vindas de uma aplicação externa.
Contudo, dependendo do modo como são 
utilizadas, a centralização da execução 
deste comando no servidor pode 
comprometer a performance, ao invés de 
melhorá-la.
13
Stored Procedures
Benefícios
– Centralização
– Segurança
– Velocidade
– Suporte a transações
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
Pode-se garantir que toda a 
operação seja executada, ou 
caso algum erro ocorra, que 
nenhuma ação seja salva no 
banco de dados de maneira a 
deixá-lo inconsistente.
14
Stored Procedures – um exemplo (MySQL)
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
DELIMITER $$
DROP PROCEDURE IF EXISTS `empresa`.`primeira_SP_teste` $$
CREATE PROCEDURE `empresa`.`primeira_SP_teste` ()
BEGIN
SELECT ('ola');
END $$
DELIMITER ;
15
Stored Procedures – Sintaxe (MySQL)
 Definer determina qual conta de usuário será consultada para
determinar se privilégios apropriados estão disponíveis as
queries definidas pela stored procedure
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE 
[ DEFINER = { user | CURRENT_USER } ]
PROCEDURE proc_name([parameters, ...])
[characteristics]
[BEGIN]
corpo_da_rotina;
[END]
16
Stored Procedures – Sintaxe (MySQL)
 Parâmetros de Entrada:
– IN: passar informações para um procedimento
– OUT: passar informações de volta para fora do procedimento
– INOUT: passar informações ao procedimento, ter seu valor alterado e
depois passar informações de volta para fora do procedimento.
 Para qualquer parâmetro OUT ou INOUT você precisa prefixar
seus nomes com o símbolo @ quando chamar a stored
procedure de modo que o parâmetro poderá ser chamado de
fora do procedimento.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
17
Stored Procedures – Sintaxe (MySQL)
 Veja o exemplo
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE PROCEDURE get_inventory(IN product CHAR(8), OUT count 
INT)
...corpo do procedimento
Você o chama da seguinte maneira:
CALL get_inventory(“ZCCUUII”, @count);
O parâmetro count pode ser acessado:
SELECT @count;
18
Stored Procedures – Sintaxe (MySQL)
 Declarando e configurando variáveis
– Declarar sempre dentro de um bloco BEGIN / END
– O escopo da variável pertencerá a este bloco
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
DECLARE variavel_nome tipo [valor_default];
DECLARE salario DECIMAL(8,2);
DECLARE bonus DECIMAL(4,2);
19
Stored Procedures – Sintaxe (MySQL)
 Configurando variáveis
– Use a instrução SET para modificar o valor de uma variável
– Você pode usar SELECT ... INTO também
– Lembre-se que, se quiser usar o valor dela fora da procedure, esta
precisa ser uma variável OUT ou INOUT
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
SET variavel_nome = valor [ , variavel_nome=valor]
DECLARE i INT;
SET i = 10;
DECLARE a INT
SELECT valor INTO a FROM tabela1 WHERE id=“89009”;
20
Stored Procedures – Sintaxe (MySQL)
 Configurando variáveis
– Use a instrução SET para modificar o valor de uma variável
– Você pode usar SELECT ... INTO também
– Lembre-se que, se quiser usar o valor dela fora da procedure, esta
precisa ser uma variável OUT ou INOUT
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE PROCEDURE teste(OUT a INT)
SELECT 45 INTO a;
CALL teste(@inv);
SELECT @inv;
21
Stored Procedures – Sintaxe (MySQL)
 Executando uma stored procedure
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CALL teste(@inv);
CALL procedure(parametro1, parametro2);
22
Stored Function – Sintaxe (MySQL)
 Criando um exemplo com múltiplas instruções
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
DELIMITER $$
DROP FUNCTION IF EXISTS `empresa`.`bonus` $$
CREATE FUNCTION `empresa`.`bonus` (SSN_E INT(8)) RETURNS DECIMAL(10,2)
BEGIN
DECLARE total decimal(10,2);
DECLARE bonus decimal(10,2);SELECT salario INTO total from empregado where SSN=SSN_E;
SET bonus = total*.10;
RETURN bonus;
END $$
DELIMITER ;
23
Stored Procedure – Sintaxe (MySQL)
 Quando criar uma stored procedure com múltiplas instruções
você precisa delimitar um bloco BEGIN / END.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
BEGIN
Código;
Código;
Código;
END
24
Stored Procedure – Sintaxe (MySQL)
Condicionais
– IF-ELSEIF-ELSE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
IF condicao THEN instrucao [ou lista de instruções]
[ELSEIF condicao THEN lista_instrucao]
[ELSE lista instrucao]
END IF
25
Stored Procedure – Sintaxe (MySQL)
Condicionais
– IF-ELSEIF-ELSE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
IF years_employed < 5 THEN
SET bonus = total * 0.05;
ELSEIF years_employed >= 5 and years_employed < 10 THEN
SET bonus = total * .06;
ELSE 
SET bonus = total * 0.8;
END IF
26
Stored Procedure – Sintaxe (MySQL)
Condicionais
– CASE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CASE
WHEN condicao THEN lista_instrucao
[ WHEN condicao THEN lista_instrucao]
[ELSE lista_instrucao]
END CASE
27
Stored Procedure – Sintaxe (MySQL)
Condicionais
– CASE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CASE
WHEN estado=“SP” THEN
SET ipva = .04;
WHEN estado=“PR” THEN
SET ipva= .02;
ELSE
SET ipva=.03;
END CASE
CASE estado
WHEN “SP” THEN
SET ipva = .04;
WHEN “PR” THEN
SET ipva= .02;
ELSE
SET ipva=.03;
END CASE
28
Stored Procedure – Sintaxe (MySQL)
Iterações
– Antes vamos falar de cursores!
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
29
Cursores
Iteração num conjunto de resultados
Permite obter cada linha do conjunto separadamente
e executar múltiplas operações nesta linha, sem
afetar as outras linhas do conjunto
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
30
Cursores
Ciclo de Vida
– Declare o cursor com uma instrução DECLARE
– Abra o cursor com a instrução OPEN
– Faça fetch dos dados a partir do cursor com a instrução
FETCH
– Feche o cursor com a instrução CLOSE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
31
Cursores
Ciclo de Vida
– Declare o cursor com uma instrução DECLARE
– Abra o cursor com a instrução OPEN
– Faça fetch dos dados a partir do cursor com a instrução
FETCH
– Feche o cursor com a instrução CLOSE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
DECLARE cursor_nome CURSOR FOR select_statement;
DECLARE calc_bonus CURSOR 
FOR SELECT id, salario, comissao FROM empregados;
32
Cursores
Ciclo de Vida
– Declare o cursor com uma instrução DECLARE
– Abra o cursor com a instrução OPEN
– Faça fetch dos dados a partir do cursor com a instrução
FETCH
– Feche o cursor com a instrução CLOSE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
OPEN cursor_nome;
OPEN calc_bonus;
33
Cursores
Ciclo de Vida
– Declare o cursor com uma instrução DECLARE
– Abra o cursor com a instrução OPEN
– Faça fetch dos dados a partir do cursor com a instrução
FETCH
– Feche o cursor com a instrução CLOSE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
FETCH cursor_nome INTO var1 [ , var2 ... ]
34
Cursores
Ciclo de Vida
– Declare o cursor com uma instrução DECLARE
– Abra o cursor com a instrução OPEN
– Faça fetch dos dados a partir do cursor com a instrução
FETCH
– Feche o cursor com a instrução CLOSE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
DELIMITER $$
CREATE PROCEDURE calculate_bonus()
BEGIN
DECLARE emp_id INT;
DECLARE sal DECIMAL(8,2);
DECLARE comm DECIMAL(3,2);
DECLARE done INT;
DECLARE calc_bonus CURSOR FOR SELECT id, salario, comissao FROM empregado;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN calc_bonus;
begin_calc: LOOP
FETCH calc_bonus INTO emp_id, sal, comm;
IF done THEN
LEAVE begin_calc;
END IF;
IF sal > 60000.00 THEN
IF comm > 0.05 THEN
UPDATE empregado SET bonus = sal * comm WHERE id=emp_id;
ELSEIF comm <=0.05 THEN
UPDATE empreagado SET bonus = sal * 0.03 WHERE id=emp_id;
END IF;
ELSE
UPDATE empregado SET bonus = sal * 0.07 WHERE id=emp_id;
END IF;
END LOOP begin_calc;
CLOSE calc_bonus;
END $$
DELIMITER ;
35
Cursores
Ciclo de Vida
– Declare o cursor com uma instrução DECLARE
– Abra o cursor com a instrução OPEN
– Faça fetch dos dados a partir do cursor com a instrução
FETCH
– Feche o cursor com a instrução CLOSE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CLOSE calc_bonus;
36
Cursores – Lidando com Condições
DECLARE pode especificar HANDLERS que podem
executar caso uma situação em particular aconteça
ou condição ocorra.
– Ex. Determinar que um cursor chegou ao final numa
iteração
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
DECLARE fim INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fim=1;
IF fim=1 THEN
LEAVE loop;
END IF
37
Cursores – Lidando com Condições
DECLARE pode especificar HANDLERS que podem
executar caso uma situação em particular aconteça
ou condição ocorra.
– Ex. Determinar que um cursor chegou ao final numa
iteração
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
DECLARE fim INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fim=1;
IF fim=1 THEN
LEAVE loop;
END IF
O MySQL suporta várias condições em 
relação a reação (handlers) para eventos. 
Para mais detalhes veja páginas 595 e 768 
do manual do MySQL (versão pdf).
38
Stored Procedure – Sintaxe (MySQL)
Iterações (ITERATE)
– LEAVE
– LOOP
– REPEAT
– WHILE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
Dependendo do valor de uma variável ou 
resultado de uma tarefa você pode sair 
imediatamente de um loop ou de um 
bloco BEGIN / END usando LEAVE.
LEAVE label;
39
Stored Procedure – Sintaxe (MySQL)
Iterações (ITERATE)
– LEAVE
– LOOP
– REPEAT
– WHILE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
[begin_label] LOOP
statement list
END LOOP [end_label]
Continua a Iteração por um conjunto de 
instruções definidas em seu bloco até que 
LEAVE seja encontrada.
40
Stored Procedure – Sintaxe (MySQL)
Iterações (ITERATE)
– LEAVE
– LOOP
– REPEAT
– WHILE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
[begin_label] LOOP
statement list
END LOOP [end_label]
Continua a Iteração por um conjunto de 
instruções definidas em seu bloco até que 
LEAVE seja encontrada.
DELIMITER $$
CREATE PROCEDURE calculate_bonus()
BEGIN
DECLARE emp_id INT;
DECLARE sal DECIMAL(8,2);
DECLARE comm DECIMAL(3,2);
DECLARE done INT;
DECLARE calc_bonus CURSOR FOR SELECT id, salario, comissao FROM empregado;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN calc_bonus;
begin_calc: LOOP
FETCH calc_bonus INTO emp_id, sal, comm;
IF done THEN
LEAVE begin_calc;
END IF;
IF sal > 60000.00 THEN
IF comm > 0.05 THEN
UPDATE empregado SET bonus = sal * comm WHERE id=emp_id;
ELSEIF comm <=0.05 THEN
UPDATE empreagado SET bonus = sal * 0.03 WHERE id=emp_id;
END IF;
ELSE
UPDATE empregado SET bonus = sal * 0.07 WHERE id=emp_id;
END IF;
END LOOP begin_calc;
CLOSE calc_bonus;
END //
DELIMITER ;
41
Stored Procedure – Sintaxe (MySQL)
Iterações (ITERATE)
– LEAVE
– LOOP
– REPEAT
– WHILE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
[begin_label] REPEAT
statement list
UNTIL condition
END REPEAT [end_label]
Continua a Iteração por um conjunto de 
instruções definidas em seu bloco até que 
LEAVE seja encontrada.
42
Stored Procedure – Sintaxe (MySQL)
Iterações (ITERATE)
– LEAVE
– LOOP
– REPEAT
– WHILE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
Continua a Iteração por um conjunto de 
instruções definidas em seu bloco até que 
LEAVEseja encontrada.
DELIMITER //
CREATE PROCEDURE test_data (IN rows INT)
BEGIN
DECLARE val1 FLOAT;
DECLARE val2 FLOAT;
REPEAT
SELECT RAND() INTO val1;
SELECT RAND() INTO val2;
INSERT INTO analise VALUES (NULL, val1, val2);
SET rows = rows – 1;
UNTIL rows = 0
END REPEAT;
END //
DELIMITER ;
43
Stored Procedure – Sintaxe (MySQL)
Iterações (ITERATE)
– LEAVE
– LOOP
– REPEAT
– WHILE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
[begin_label] WHILE condition DO
statement list
END WHILE [end_label]
Continua a Iteração por um conjunto de 
instruções definidas em seu bloco até que 
LEAVE seja encontrada.
44
Stored Procedure – Sintaxe (MySQL)
Iterações (ITERATE)
– LEAVE
– LOOP
– REPEAT
– WHILE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
Continua a Iteração por um conjunto de 
instruções definidas em seu bloco até que 
LEAVE seja encontrada.
DELIMITER //
CREATE PROCEDURE test_data (rows INT)
BEGIN
DECLARE val1 FLOAT;
DECLARE val2 FLOAT;
WHILE rows > 0 DO
SELECT RAND() INTO val1;
SELECT RAND() INTO val2;
INSERT INTO analise VALUES (NULL, val1, val2);
SET rows = rows – 1;
END WHILE;
END //
DELIMITER ;
45
Stored Procedure – Sintaxe (MySQL)
Chamando um Rotina dentro de outra
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
DELIMITER //
CREATE PROCEDURE process_logs()
BEGIN
SELECT “Processando Logs”;
END //
CREATE PROCEDURE process_users()
BEGIN
SELECT “Processando Usuarios”;
END//
CREATE PROCEDURE maintenance()
BEGIN
CALL process_logs();
CALL process_users();
END//
DELIMITER;
46
Stored Procedure – Sintaxe (MySQL)
Chamando uma Função dentro de uma Rotina
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
# Criamos uma tabela para testarmos a função e o procedimento
CREATE TABLE notas(aluno VARCHAR(20), nota1 INT, nota2 INT, nota3 INT, nota4 INT); 
# Inserimos valores de exemplo
INSERT INTO notas VALUES(‘Juju', 10, 9, 10, 10); 
INSERT INTO notas VALUES(‘Marquinhos', 5, 2, 3, 4); 
47
Stored Procedure – Sintaxe (MySQL)
Chamando uma Função dentro de uma Rotina
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
DELIMITER //
CREATE FUNCTION mediaaluno (nome VARCHAR(20)) RETURNS FLOAT
BEGIN
DECLARE n1,n2,n3,n4 INT;
DECLARE med FLOAT;
SELECT nota1,nota2,nota3,nota4 INTO n1,n2,n3,n4 FROM notas WHERE aluno = nome;
SET med = (n1+n2+n3+n4)/4;
RETURN med;
END
//
CREATE PROCEDURE resultadoaluno(IN nome VARCHAR(20))
BEGIN
SELECT mediaaluno(nome);
END //
DELIMITER ;
48
Stored Procedure – Sintaxe (MySQL)
Chamando uma Função dentro de uma Rotina
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
# Chamamos pela linha de comando
mysql > CALL resultadoaluno(‘Juju’);
49
Stored Procedure – Sintaxe (MySQL)
Características de uma Stored Procedure
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE 
[ DEFINER = { user | CURRENT_USER } ]
PROCEDURE proc_name([parameters, ...])
[characteristics]
[BEGIN]
corpo_da_rotina;
[END]
50
Stored Procedure – Sintaxe (MySQL)
Características de uma Stored Procedure
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE 
[ DEFINER = { user | CURRENT_USER } ]
PROCEDURE proc_name([parameters, ...])
[characteristics]
[BEGIN]
corpo_da_rotina;
[END]
LANGUAGE SQL
| [ NOT ] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’
51
Stored Procedure – Sintaxe (MySQL)
Características de uma Stored Procedure
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE 
[ DEFINER = { user | CURRENT_USER } ]
PROCEDURE proc_name([parameters, ...])
[characteristics]
[BEGIN]
corpo_da_rotina;
[END]
LANGUAGE SQL
| [ NOT ] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’
Atualmente, o SQL é a única linguagem 
suportada para Store Procedures, mas 
existem planos para introduzir um 
framework que suporte outras linguagens.
52
Stored Procedure – Sintaxe (MySQL)
Características de uma Stored Procedure
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE 
[ DEFINER = { user | CURRENT_USER } ]
PROCEDURE proc_name([parameters, ...])
[characteristics]
[BEGIN]
corpo_da_rotina;
[END]
LANGUAGE SQL
| [ NOT ] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’
Usado somente com Stored Functions.
Qualquer função declarada como 
DETERMINISTIC irá retornar sempre o 
mesmo valor desde que o mesmo 
conjunto de parâmetros seja passado (isto 
é útil em cenários de replicação).
53
Stored Procedure – Sintaxe (MySQL)
Características de uma Stored Procedure
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE 
[ DEFINER = { user | CURRENT_USER } ]
PROCEDURE proc_name([parameters, ...])
[characteristics]
[BEGIN]
corpo_da_rotina;
[END]
LANGUAGE SQL
| [ NOT ] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’
Indica o tipo de tarefa da Stored Procedure:
CONTAINS SQL – especifica que o SQL está presente, mas não vai ler nem 
escrever dados;
NO SQL – indica que nenhum SQL está presente neste procedimento;
READ SQL DATA – indica que o SQL irá somente ler dados;
MODIFIES SQL DATA – indica que o SQL irá modificar dados.
(Até a versão 5.0 estes elementos não influenciavam no resultado da 
procedure)
54
Stored Procedure – Sintaxe (MySQL)
Características de uma Stored Procedure
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE 
[ DEFINER = { user | CURRENT_USER } ]
PROCEDURE proc_name([parameters, ...])
[characteristics]
[BEGIN]
corpo_da_rotina;
[END]
LANGUAGE SQL
| [ NOT ] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’
Se a característica for configurada como DEFINER
então o procedimento será executado de acordo
com os privilégios do usuário que definiu os
procedimentos. Se for INVOKER, será executado
de acordo com os privilégios do usuário
executando o procedimento.
55
Stored Procedure – Sintaxe (MySQL)
Características de uma Stored Procedure
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE 
[ DEFINER = { user | CURRENT_USER } ]
PROCEDURE proc_name([parameters, ...])
[characteristics]
[BEGIN]
corpo_da_rotina;
[END]
LANGUAGE SQL
| [ NOT ] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’
Se a característica for configurada como DEFINER
então o procedimento será executado de acordo
com os privilégios do usuário que definiu os
procedimentos. Se for INVOKER, será executado
de acordo com os privilégios do usuário
executando o procedimento.
Executar um procedimento com privilégios de
outros usuário é interessante para garantir a
segurança do sistema. Você pode ter usuários
que não tem absolutamente nenhum direito
sobre o banco de dados além de executar alguns
procedimentos.
56
Stored Procedure – Sintaxe (MySQL)
Características de uma Stored Procedure
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE 
[ DEFINER = { user | CURRENT_USER } ]
PROCEDURE proc_name([parameters, ...])
[characteristics]
[BEGIN]
corpo_da_rotina;
[END]
LANGUAGE SQL
| [ NOT ] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’
Adicionar comentários
sobre o procedimento.
57
Stored Procedure – Sintaxe (MySQL)
Apagando uma Stored Procedure
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
DROP ( PROCEDURE | FUNCTION ) [ IF EXISTS ] sp_nome;58
Stored Procedure – Sintaxe (MySQL)
Alterando uma Stored Procedure
– Até o momento, apenas alterações de características;
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
ALTER ( PROCEDURE | FUNCTION ) sp_nome [ características ];
59
Stored Procedure – Sintaxe (MySQL)
Status de uma Stored Procedure
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
SHOW ( PROCEDURE | FUNCTION ) STATUS [ LIKE ‘pattern’ ] ;
SHOW PROCEDURE STATUS LIKE ‘testeproc’\G;
60
Stored Procedure – Sintaxe (MySQL)
Sintaxe de uma Stored Procedure criada
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
SHOW CREATE ( PROCEDURE | FUNCTION ) sp_nome;
SHOW CREATE PROCEDURE testeproc\G;
61
Exercício 4.1
1 - Para o BD empresa que vimos em aula, escreva
um stored procedure que atualize o salário dos
funcionários em 10% para aqueles que ganham
menos de 30.000 e em 8% para aqueles que ganham
mais de 30.000.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
62
Exercício 4.2
2 – Para o exemplo de notas de alunos visto, insira
notas para 20 alunos (incluindo o nome destes),
depois crie uma função que calcule a média da
turma. Após isto, faça uma stored procedure que
passe como parâmetro o nome de um aluno e
verifica se este aluno foi aprovado ou reprovado. A
condição para que ele seja aprovado é que sua nota
seja maior ou igual à média da turma (use a função
criada).
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
63
Bibliografia
The MySQL Manual version 5.1
– http://dev.mysql.com/doc/refman/5.1/en/
__ Version 4.1
– http://dev.mysql.com/doc/refman/4.1/pt/index.html
ZIVIANI, R. & NAVATHE, S. B. Sistemas de Banco de
Dados. 4. ed. São Paulo: Pearson Adison-Wesley,
2005.
GILMORE, J. Dominando PHP e MySQL. Rio de
Janeiro: Altabooks, 2008.
MILANI, A. MySQL Guia do Programador. São Paulo:
Novatec, 2006.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
Aula 2 – Stored Procedures, Functions, 
Cursores, Triggers e Views.
Parte II
Triggers
65
O que é um Trigger
Uma tarefa executada em resposta a algum evento
pré-determinado
– Inserir, modificar ou apagar dados de tabelas
– Pode ocorrer antes ou imediatamente após um evento
como esse
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
66
O que é um Trigger
Gatilho ou trigger é um recurso de programação
executado sempre que o evento associado ocorrer.
É muito utilizada para ajudar a manter a consistência
dos dados ou para propagar alterações em um
determinado dado de uma tabela para outras.
Um bom exemplo é um gatilho criado para controle
de quem alterou a tabela, nesse caso, quando a
alteração for efetuada, o gatilho é "disparado" e
grava em uma tabela de histórico de alteração, o
usuário e data/hora da alteração.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
67
O que é um Trigger
Outro exemplo é usar gatilhos para garantir que
regras de negócio sejam automatizadas e cumpridas.
– Garantir que uma tentativa de inserir informações sobre
um produto na tabela product inclua o identificador de um
fabricante cujas informações já estão na tabela
manufacturer
– Validação de dados antes de inserir
Garantia de integridade referencial
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
68
Ação antes de um evento
Um distribuidor de alimentos gourmet exige que
pelo menos $10 de café seja comprado antes que a
transação seja processada. Se um usuário tentar
adicionar menos que essa quantidade no carrinho de
compras esse valor valor deve ser automaticamente
arredondado para $10.
– Um trigger antes, o qual avalia qualquer tentativa de
inserir um produto no carrinho de compras e aumenta o
valor de qualquer compra inaceitavelmente baixa para
$10.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
69
Ação antes de um evento
Um trigger antes, o qual avalia qualquer tentativa de
inserir um produto no carrinho de compras e
aumenta o valor de qualquer compra
inaceitavelmente baixa para $10.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
Shopping cart insertion request submitted:
If product identifier set to “coffee”:
If dollar amoun < $10:
Set dollar amount = $10;
End If
End If
Process insertion request
70
Sintaxe Trigger – MySQL
A cláusula DEFINER determina qual conta de usuário
será consultada para determinar os privilégios
adequados disponíveis para as queries dentro do
trigger.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE
[ DEFINER = { USER | CURRENT_USER } ]
TRIGGER <nome>
{ BEFORE | AFTER }
{INSERT | UPDATE | DELETE }
ON <table name>
FOR EACH ROW
<codigo SQL da Trigger>
71
Sintaxe Trigger – MySQL
Definir dados de antes (OLD) e depois (NEW)
– 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 a tabela alvo 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.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
72
Sintaxe Trigger – MySQL
Antes mesmo de analisarmos os operadores, temos
que analisar as seguintes diretrizes:
– INSERT: o operador NEW.nome_coluna, nos permite
verificar o valor enviado para ser inserido em uma coluna
de uma tabela. 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.
NEW.nome_coluna não está disponível.
– UPDATE: tanto OLD.nome_coluna quanto
NEW.nome_coluna estão disponíveis, antes (BEFORE) ou
depois (AFTER) da atualização de uma linha.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
73
Sintaxe Trigger – MySQL
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 UPDATE pois consiste em um DELETE
seguido por um INSERT.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
74
Sintaxe Trigger – MySQL
Exemplo
– Vamos criar uma tabela tbl_cliente
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE TABLE tbl_cliente (
cliente_id int unsigned auto_increment primary key,
cliente_nome char(80) not null,
cliente_email char(80) not null,
dt_cadastro timestamp default current_timestamp
) Engine = INNODB;
75
Sintaxe Trigger – MySQL
Exemplo
– Vamos criar um trigger bem simples que validará o
tamanho do nome.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
delimiter //
CREATE TRIGGER trg_1 BEFORE INSERT ON tbl_cliente
FOR EACH ROW
BEGIN
DECLARE nome VARCHAR(80);
SET nome = NEW.cliente_nome;
IF (( CHAR_LENGTH(nome) <= 4) OR (nome = ‘’) )
THEN SET NEW.cliente_nome = NULL;
END IF;
END //
delimiter ;
76
Sintaxe Trigger – MySQL
Exemplo
– Ao tentarmos inserir um valor cujo número de caracteres é
menor ou igual a 0 ou nada, o TRIGGER será disparado e
setará o valor enviado para NULL através do operador
NEW.nome_coluna.
– Como na tabela de exemplo a coluna cliente_nome foi
configurada com a restrição NOT NULL, ou seja, não
aceitará valores nulos, uma mensagem de erro será
enviada e o INSERT falhará.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
INSERT INTO tbl_cliente SET cliente_nome = ‘test’, cliente_email = 
‘teste@teste.com’;
77
Sintaxe Trigger – MySQL
Podemos criar uma restrição de integridade
referencial com TRIGGERS.
– Por exemplo, usaremos tabelas MyISAM (que não dão
suporte a criaçãode relacionamento com chaves
estrangeiras) e, aproveitando nosso exemplo, como
clientes compram produtos, podemos criar uma tabela de
produtos e restringir que somente os produtos
cadastrados podem ser comprados, inseridos na tabela de
compra que somente clientes cadastrados podem efetuar
compras. Criamos então a tabela de produtos e compras.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
78
Sintaxe Trigger – MySQL
Podemos criar uma restrição de integridade
referencial com TRIGGERS.
– Por exemplo, usaremos tabelas MyISAM (que não dão
suporte a criação de relacionamento com chaves
estrangeiras) e, aproveitando nosso exemplo, como
clientes compram produtos, podemos criar uma tabela de
produtos e restringir que somente os produtos
cadastrados podem ser comprados, inseridos na tabela de
compra que somente clientes cadastrados podem efetuar
compras. Criamos então a tabela de produtos e compras.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
79
Sintaxe Trigger – MySQL
Podemos criar uma restrição de integridade
referencial com TRIGGERS.
– Por exemplo, usaremos tabelas MyISAM (que não dão
suporte a criação de relacionamento com chaves
estrangeiras) e, aproveitando nosso exemplo, como
clientes compram produtos, podemos criar uma tabela de
produtos e restringir que somente os produtos
cadastrados podem ser comprados, inseridos na tabela de
compra que somente clientes cadastrados podem efetuar
compras. Criamos então a tabela de produtos e compras.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE TABLE tbl_cliente (
cliente_id int unsigned auto_increment primary key,
cliente_nome char(80) not null,
cliente_email char(80) not null,
dt_cadastro timestamp default current_timestamp
) Engine = MyISAM;
80
Sintaxe Trigger – MySQL
Podemos criar uma restrição de integridade
referencial com TRIGGERS.
– Por exemplo, usaremos tabelas MyISAM (que não dão
suporte a criação de relacionamento com chaves
estrangeiras) e, aproveitando nosso exemplo, como
clientes compram produtos, podemos criar uma tabela de
produtos e restringir que somente os produtos
cadastrados podem ser comprados, inseridos na tabela de
compra que somente clientes cadastrados podem efetuar
compras. Criamos então a tabela de produtos e compras.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE TABLE tbl_compra (
dt_cadastro timestamp default current_timestamp,
cliente_id int unsigned not null,
produto_id int unsigned not null
) Engine = MyISAM;
81
Sintaxe Trigger – MySQL
Podemos criar uma restrição de integridade
referencial com TRIGGERS.
– Por exemplo, usaremos tabelas MyISAM (que não dão
suporte a criação de relacionamento com chaves
estrangeiras) e, aproveitando nosso exemplo, como
clientes compram produtos, podemos criar uma tabela de
produtos e restringir que somente os produtos
cadastrados podem ser comprados, inseridos na tabela de
compra que somente clientes cadastrados podem efetuar
compras. Criamos então a tabela de produtos e compras.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE TABLE tbl_produto (
produto_id int unsigned auto_increment primary key,
produto_nome CHAR(80) not null
) Engine = MyISAM;
82
Sintaxe Trigger – MySQL
Podemos criar uma restrição de integridade
referencial com TRIGGERS.
– Por exemplo, usaremos tabelas MyISAM (que não dão
suporte a criação de relacionamento com chaves
estrangeiras) e, aproveitando nosso exemplo, como
clientes compram produtos, podemos criar uma tabela de
produtos e restringir que somente os produtos
cadastrados podem ser comprados, inseridos na tabela de
compra que somente clientes cadastrados podem efetuar
compras. Criamos então a tabela de produtos e compras.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
DELIMITER //
CREATE TRIGGER trg_2 BEFORE INSERT ON tbl_compra
FOR EACH ROW
BEGIN
#verifica se id de cliente existe
SELECT COUNT(cliente_id) INTO @cliente_id FROM tbl_cliente
WHERE cliente_id = NEW.cliente_id;
#verifica se id de produto existe
SELECT COUNT(produto_id) INTO @produto_id FROM tbl_produto
WHERE produto_id = NEW.produto_id;
IF ((@cliente_id = 0) OR (@produto_id = 0)) THEN
#força o erro
SET NEW.cliente_id=NULL;
SET NEW.produto_id=NULL
END IF;
END//
DELIMITER ;
83
Excluindo um Trigger
DROP TRIGGER <nome>;
Não há suporte a alteração!
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
84
Limitações
 Não se pode chamar diretamente um TRIGGER com
CALL, como se faz com um Stored Procedures;
 Não é permitido iniciar ou finalizar transações em
meio à TRIGGERS;
 Não se pode criar um TRIGGERS para uma tabela
temporária - TEMPORARY TABLE;
 TRIGGERS ainda não podem ser implementadas com
a intenção de devolver para o usuário ou para uma
aplicação mensagens de erros.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
85
Exercício 4.3
Para a tabela empregado crie Trigger(s) que faça(m) o
log da tabela:
– Registre se houve inserção, atualização ou exclusão na
tabela.
– Use uma nova tabela para guardar estas informações
– Tabela LOG
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
ID TIPO TIME USUÁRIO
1 INSERCAO 05/12/2012 – 13:08:08 ROOT
2 DELECAO 06/12/2012 – 14:00:01 ROOT
86
Exercício 4.4
Crie uma tabela que receba cadastro de clientes com
nome, endereço, telefone e email.
– Crie uma tabela que contenha as preferências da pessoa
em relação a produtos
Toda vez que um cliente for cadastrado deverá ser
disparado um gatilho que insere o email do cliente
numa newsletter, o endereço numa mala direta e o
telefone numa tabela de ordem de serviço para
ligações da área de marketing (oferta de produtos).
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
87
Bibliografia
The MySQL Manual version 5.1
– http://dev.mysql.com/doc/refman/5.1/en/
__ Version 4.1
– http://dev.mysql.com/doc/refman/4.1/pt/index.html
ZIVIANI, R. & NAVATHE, S. B. Sistemas de Banco de
Dados. 4. ed. São Paulo: Pearson Adison-Wesley,
2005.
GILMORE, J. Dominando PHP e MySQL. Rio de
Janeiro: Altabooks, 2008.
MILANI, A. MySQL Guia do Programador. São Paulo:
Novatec, 2006.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
Aula 2 – Stored Procedures, Functions, 
Cursores, Triggers e Views.
Parte III
Views
89
O que é uma View?
Uma View é um objeto que pertence a um banco de
dados, definida baseada em declarações SELECT´s,
retornando uma determinada visualização de dados
de uma ou mais tabelas.
Esses objetos são chamados por vezes de "virtual
tables", formada a partir de outras tabelas que por
sua vez são chamadas de "based tables" ou ainda
outras Views.
E alguns casos, as Views são atualizáveis e podem ser
alvos de declaração INSERT, UPDATE e DELETE, que
na verdade modificam sua "based tables".
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
90
Vantagens
 Uma View pode ser utilizada, por exemplo, para retornar um
valor baseado em um identificador de registro;
 Pode ser utilizada para promover restrições em dados para
aumentar a segurança dos mesmos e definir políticas de
acesso em nível de tabela e coluna.
– Podem ser configurados para mostrar colunas diferentes para
diferentes usuários do banco de dados;
 Pode ser utilizada com um conjunto de tabelas que podem ser
unidas a outros conjuntos de tabelas com a utilização de
JOIN´s ou UNION.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
91
Vantagens
Para definir Views em um banco de dados,
utilize a declaração CREATE VIEW, a qual tem a
seguinte sintaxe:
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE [OR REPLACE] 
[ALGORITHM = algorithm_type] VIEW
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
92
Vantagens
Para definir Views em um banco de dados,
utilizea declaração CREATE VIEW, a qual tem a
seguinte sintaxe:
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE [OR REPLACE] 
[ALGORITHM = algorithm_type] VIEW
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
OR REPLACE: pode ser utilizada para substituir
uma View de mesmo nome existente no banco
de dados ao qual ela pertence. Pode-se utilizar
ALTER TABLE para o mesmo efeito;
93
Vantagens
Para definir Views em um banco de dados,
utilize a declaração CREATE VIEW, a qual tem a
seguinte sintaxe:
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE [OR REPLACE] 
[ALGORITHM = algorithm_type] VIEW
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
ALGORITHM: essa cláusula define qual algoritmo
interno utilizar para processar a View quando a
mesma for invocada. Estes podem ser
UNDEFINED (cláusula em branco), MERGE ou
TEMPTABLE.
94
Vantagens
Para definir Views em um banco de dados,
utilize a declaração CREATE VIEW, a qual tem a
seguinte sintaxe:
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE [OR REPLACE] 
[ALGORITHM = algorithm_type] VIEW
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
É o nome que damos ao objeto View que
criarmos. Esse nome poderá ser não qualificado,
quando criado no banco de dados corrente ou
totalmente qualificado quando criarmos em um
banco de dados que não está definido no
contexto atual (db_name.view_name).
95
Vantagens
Para definir Views em um banco de dados,
utilize a declaração CREATE VIEW, a qual tem a
seguinte sintaxe:
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE [OR REPLACE] 
[ALGORITHM = algorithm_type] VIEW
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
column_list: recurso para que possamos
sobrescrever os nomes das colunas que serão
recuperadas pela declaração SELECT;
96
Vantagens
Para definir Views em um banco de dados,
utilize a declaração CREATE VIEW, a qual tem a
seguinte sintaxe:
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE [OR REPLACE] 
[ALGORITHM = algorithm_type] VIEW
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
select_statement: é a sua declaração SELECT e indica a
forma na qual você deseja que os dados sejam retornados.
Tal declaração deverá indicar a forma a qual você deseja
retornar os dados, podendo ser utilizado funções, JOIN´s e
UNION. Podem ser utilizadas quaisquer tabelas ou views
contidas no servidor de bancos de dados MySQL,
observando a questão de nomes totalmente qualificados
ou não.
97
Vantagens
Para definir Views em um banco de dados,
utilize a declaração CREATE VIEW, a qual tem a
seguinte sintaxe:
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
CREATE [OR REPLACE] 
[ALGORITHM = algorithm_type] VIEW
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
WITH CHECK OPTION: todas as declarações que
tentarem modificar dados de uma view definida
com essa cláusula serão revisadas para checar se
as modificações respeitarão a condição WHERE,
definida no SELECT da View.
98
Definindo Views
Para iniciarmos com a mão na massa,
definiremos uma View simples para listar
cidades da tabela City, do banco de dados
World.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
99
Definindo Views
A partir desse momento, se dermos um SHOW
TABLES no banco de dados World, veremos
que uma tabela adicional foi criada, que é a
View que criamos.
Para uma conceituação mais ampla, uma View
é um mapeamento lógico de várias tabelas
contidas em um ou mais bancos de dados que
por sua vez estão em um servidor MySQL.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
100
Definindo Views
No caso da View criada, temos uma tabela
virtual (vw_viewCity) baseada em uma tabela
chamada de base (City).
Um bom exemplo para utilizarmos a tal lista
de colunas é criar a mesma View, mas agora
sobrescrevendo o nome da coluna "Name"
para "Cidade“.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
101
Definindo Views
No caso da View criada, temos uma tabela
virtual (vw_viewCity) baseada em uma tabela
chamada de base (City).
Um bom exemplo para utilizarmos a tal lista
de colunas é criar a mesma View, mas agora
sobrescrevendo o nome da coluna "Name"
para "Cidade“.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
102
Definindo Views
Faltou o OR REPLACE
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
103
Definindo Views
Podemos criar Views mais sofisticadas,
com um comando SELECT mais
trabalhado, podendo utilizar das
cláusulas WHERE, GROUP BY, HAVING e
ORDER BY.
Alguns SGBD´s comerciais não permitem
a utilização de ORDER BY em meio do
SELECT na definição de uma View, a
exemplo do SQL Server, da Microsoft.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
104
Definindo Views
Uma consulta mais trabalhada para a criação de uma
View, envolvendo as tabelas Country e
CountryLanguage do banco de dados World.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
105
Atualizando Views
Views podem ser constituídas facilmente, como
vimos anteriormente.
Mas para termos Views que podem receber
declarações de atualização tais como UPDATE e
DELETE, que de fato alteram as tabelas base ("based
tables"), temos que ter alguns cuidados na criação
do objeto de visualização.
Uma View criada com funções agregadas, por
exemplo, não poderá receber atualizações, pois os
dados logicamente estão agregados ou agrupados e
não teremos correspondências diretas para uma
exclusão ou atualização.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
106
Atualizando Views
Análise a seguinte situação: "crie uma View no
banco de dados World para contar quantas
línguas se falam em cada país que aparece
nas tabelas Country e CountryLanguage".
– Sendo esta a tarefa, teremos 1 registro apenas
para cada país da tabela Country, contando
quantas línguas aparecem na tabela para este
único país na tabela CountryLanguage. Isso não
nos possibilita atualizar uma determinada linha,
pois, o que retornou dessa consulta foi um
conjunto agrupado por país.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
107
Atualizando Views
Quando temos uma View com um SELECT
simples, sem agrupamento, podemos atualizá-
la, esta que na verdade, somente receberá a
declaração e quem serão atualizadas serão as
tabelas base que tem seus dados mapeados
para esta View. Para que você não estrague
seu banco de dados World, criaremos uma
tabela chamada "CountryPop", com seguinte
sintaxe:
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
108
Atualizando Views
Quando temos uma View com um SELECT simples,
sem agrupamento, podemos atualizá-la, esta que na
verdade, somente receberá a declaração e quem
serão atualizadas serão as tabelas base que tem seus
dados mapeados para esta View. Para que você não
estrague seu banco de dados World, criaremos uma
tabela chamada "CountryPop", com seguinte sintaxe:
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
109
Atualizando Views
Com a tabela base criada, vamos então definir
a View para que possamos atualizar a mesma.
Faremos um SELECT na View para exibir a
linha que atualizaremos e na seqüência
emitimos um UPDATE para que a mesma seja
atualizada.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
110
Atualizando Views
 Com a tabela
base criada,
vamos então
definir a View
para que
possamos
atualizar a
mesma.
 Faremos um
SELECT na View
para exibir a
linha que
atualizaremos e
na seqüência
emitimos um
UPDATE paraque a mesma
seja atualizada.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
111
Views com WITH CHECK OPTION 
Podemos ainda trabalhar algumas restrições na
criação de algumas Views, como utilizar a opção
WITH CHECK OPTION.
Atribuindo esta opção na criação de uma View
significa que atualização que são emitidas terão que
se encaixar às condições definidas na cláusula
WHERE da consulta SELECT.
Peguemos um exemplo de uma View que nos
retorne todos os países da tabela CountryPop que
acabamos de criar, que tenha populações maior ou
igual a 100000000.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
112
Views com WITH CHECK OPTION 
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
113
Views com WITH CHECK OPTION 
 Definimos na cláusula WHERE da View que somente poderia
ser atualizado o número da população do país se este
comando de atualização enviasse um número maior ou igual
a 100000000. Enviamos um UPDATE para atualizar a
população da Nigéria para 9999999, que é ligeiramente
menor que o número definido no WHERE da View.
 WITH CHECK OPTION somente será aceita em meio a uma
View atualizável, caso aquela que você vier a definir não seja
atualizável, um erro será enviado e a mesma não será criada.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
114
Apagando uma View
DROP VIEW
– Para excluir uma View, basta utilizar o comando
DROP VIEW view_name
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
115
Exercício 4.5
Crie uma view que contenha todos os estados do
Brasil, além das informações como população de
cada estado, capital, etc.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.
116
Bibliografia
The MySQL Manual version 5.1
– http://dev.mysql.com/doc/refman/5.1/en/
__ Version 4.1
– http://dev.mysql.com/doc/refman/4.1/pt/index.html
ZIVIANI, R. & NAVATHE, S. B. Sistemas de Banco de
Dados. 4. ed. São Paulo: Pearson Adison-Wesley,
2005.
GILMORE, J. Dominando PHP e MySQL. Rio de
Janeiro: Altabooks, 2008.
MILANI, A. MySQL Guia do Programador. São Paulo:
Novatec, 2006.
Banco de Dados Stored Procedures, Functions, Cursores, Triggers e Views.