SQL Avançada
SQL Avançada
Luídne Mota, 16 de
Luídne Mota, 16 de
março de 2012
março de 2012
Views
Views
Views ou simplesmente, Visões em banco de dados são tabelas virtuais criadas a Views ou simplesmente, Visões em banco de dados são tabelas virtuais criadas a partir de uma ou mais tabelas físicas de um banco de dados.
partir de uma ou mais tabelas físicas de um banco de dados.
Ela permite fazer consultas aos dados de maneira virtual, isto é, fazer consultas Ela permite fazer consultas aos dados de maneira virtual, isto é, fazer consultas sem que sejam realmente nas tabelas físicas, isso ajuda na segurança de
sem que sejam realmente nas tabelas físicas, isso ajuda na segurança de informações. Por exemplo:
informações. Por exemplo:
Podemos criar uma tabela que será usada para as consultas que serão Podemos criar uma tabela que será usada para as consultas que serão feitas pelos alunos
feitas pelos alunos, desta maneira, podemos at, desta maneira, podemos atribuir à essa nova visão ribuir à essa nova visão apenas osapenas os campos que lhe interessam como nome e sua média de uma disciplina. Isto campos que lhe interessam como nome e sua média de uma disciplina. Isto aumenta a segurança, pois não terá como por algum método hacker obter os aumenta a segurança, pois não terá como por algum método hacker obter os outros dados como a média de outros alunos, porque estão na tabela física.
outros dados como a média de outros alunos, porque estão na tabela física. Um uso que também podemos fazer com visões, acontece quando precisamos de Um uso que também podemos fazer com visões, acontece quando precisamos de alguns dados restritos numa tabela separada. Fazemos isto criando uma visão, ou alguns dados restritos numa tabela separada. Fazemos isto criando uma visão, ou mais se necessário, sem precisar duplicar os dados de uma tabela em outra no mais se necessário, sem precisar duplicar os dados de uma tabela em outra no mesmo banco de dados.
mesmo banco de dados.
A sintaxe básica de uma visão é a seguinte: A sintaxe básica de uma visão é a seguinte:
É bom usarmos visões apenas quando se tratar de consultas, pois se pensarmos É bom usarmos visões apenas quando se tratar de consultas, pois se pensarmos em fazer inserções, alterações ou exclusões podem tornar uma visão muito em fazer inserções, alterações ou exclusões podem tornar uma visão muito complexa. Em geral, uma visão suporta os mesmos comandos de uma tabela, complexa. Em geral, uma visão suporta os mesmos comandos de uma tabela, afinal, visões também são tabelas, só que, virtuais.
afinal, visões também são tabelas, só que, virtuais.
CREATE VIEW
CREATE VIEW nome_da_visaonome_da_visao AS SELECT
AS SELECT t1.t1.campo1campo1, t2., t2.campo2campo2 FROM
FROM tabela1 t1, tabela2tabela1 t1, tabela2 WHERE
Stored Procedure
Stored Procedure, ou procedimento armazenado é uma coleção de comandos SQL que encapsula comandos para chamá-los como se fosse um comando só. É como de escrever um programa dentro do próprio servidor para ser executado dentro do próprio servidor.
Serve para resolver tarefa que são executados com alta frequência. Tal caso, sem Stored Procedures, essas tarefas quando executadas demasiadamente no servidor de banco de dados lhe faz estar sobrecarregado ocasionando perda de desempenho. Por isso, o uso de Stored Procedure provê a melhora de performance, além de criar mecanismos de segurança na aplicação, etc.
O objetivo da criação do Stored Procedure foi o equilíbrio da carga entre o servidor de aplicação e o servidor de bando de dados, ou seja, equilibrar a balança a qual os respectivos servidores são os pesos.
Então, vamos iniciar a criação de um Stored Procedure... Sua sintaxe base é:
Por exemplo, podemos fazer o seguinte Stored Procedure:
Para executá-lo basta chamá-lo pelo nome que demos a ele:
Importante: Quando você usa “END;” para fechar o corpo do procedimento antes dele vem algum comando também terminado com “;” reconhecendo como se ali fosse o final do procedimento.
Por isso, antes de criamos nossa Stored Procedure vamos mudar o delimitador SQL padrão para “ // ”.
O comando para mudar é o seguinte:
DELIMITER //
CREATE PROCEDURE nome() BEGIN
comandos... END;
CREATE PROCEDURE todos_professores() BEGIN
SELECT * FROM professor; END;
Se tiver apenas um comando dentro do Stored Procedure não é necessário usar os delimitadores do corpo BEGIN e END.
Como dito no começo desse artigo, Stored Procedure é como se fosse um programa, então, ele deve ter nenhum, um ou mais parâmetros.
Veja como se cria um com parâmetro:
Até aqui fizemos um simples Stored Procedure.
Vamos torná-lo mais próximo a um “programa” criando variáves e estruturas de controle como IF ELSE, WHILE.
Agora com WHILE:
CREATE PROCEDURE nome(variavel int) BEGIN
SELECT * FROM professor WHERE idprofessor=variavel; END//
CREATE PROCEDURE nome(variavel int) BEGIN
DECLARE var INT; SET var = variavel; IF(var > 0) THEN
SELECT * FROM professor WHERE idprofessor=variavel; ELSE
SELECT 0; END IF;
END//
CREATE PROCEDURE incrementa(parametro int) BEGIN
WHILE paramentro >= 0 DO
INSERT INTO outratabela(valor) VALUES(parametro);
SET parametro = parametro + 1; END WHILE;
CREATE TRIGGER nome TIME EVENT ON tabela
FOR EACH ROW instrucoes;
DROP TRIGGER nome;
Agora chamamos a rotina:
Trigger
Trigger, ou Gatilho, é um recurso usado em banco de dados SQL que visa execução de determinados procedimentos que são executados automaticamente quando é disparado algum evento em uma tabela ou uma view.
Um problema: quando temos o conteúdo de uma tabela alterado, nesta alteração outros dados de outras tabelas podem estar relacionados e, consequentemente, alguns dados podem ficar sem nexo por isto. Para resolver este gargalo dentro do banco de dados usamos triggers.
Solução: usamos uma trigger para que quando esta tabela for alterada possamos tomar alguma providência em relação à outra tabela.
Triggers, por exemplo, podem ser usadas para gerar dados em uma tabela de históricos do próprio bando de dados.
Outros exemplos que podemos citar do benefício são os seguinte:
Executar regras de negócio (exemplo: notificar um gerente de estoque quando o estoque de produtos estiver baixo);
Replicar dados (exemplo: replicar dados em outro banco de dados como forma de monitoramento);
Garantir desempenho (exemplo: atualizar o saldo de uma conta-corrente após uma transação, para consultas rápidas ao saldo).
Existem três tipos de triggerque podemos criar: 1. Para inserção de dados: INSERT;
2. Para exclusão de dados: DELETE;
3. Para atualização de dados: UPDATE;
Sua sintaxe básica é:
E para excluí-la:
Agora vamos criar um trigger básico:
O NEW.nome é o campo nome da tabela cliente que será o novo dados inserido em todos registros da tabela auxiliar.
Pode ser NEW ou OLD;
O TIME pode ser: AFTER ou BEFORE.
O EVENT pode ser: INSERT, DELETE ou UPDATE.
Se você for usar mais instruções, por exemplo, INSERT, UPDATE e DELETE, você precisa delimitar o corpo das instruções com BEGIN e AND.
E, ainda, delimitar a instrução SQL com outro delimitador que você mesmo especificar ao contrário do padrão que é “;”.
Por exemplo:
CREATE TRIGGER tg_cliente_cadastrar AFTER INSERT ON cliente
FOR EACH ROW
INSERT INTO auxiliar SET telefone = NEW.contato;
IMPORTANTE:
Não pode haver duas trigger numa dada tabela que tenha a mesma ação e evento. Por exemplo, você não pode ter dois BEFORE UPDATE para uma tabela. Mas você pode ter umBEFORE UPDATE e umBEFORE INSERT, ou um
BEFORE UPDATE e um AFTER UPDATE.
Nota:
Atualmente, ações de chaves estrangeiras não ativam triggers.
DELIMITER//
CREATE TRIGGER teste_trigger AFTER INSERT ON tabela1
FOR EACH ROW BEGIN
INSERT INTO tabela2 SET campo1 = NEW.campo2;
DELETE FROM tabela3 WHERE campo3 = NEW.campo1; UPDATE tabela4 SET campoA = campoB + 1 WHERE campo4 = NEW.campo1;
Importante: Quando você usa “END;” para fechar o corpo do procedimento antes dele vem algum comando também terminado com “;” reconhecendo como se ali fosse o final do procedimento.
Por isso, antes de criamos nossa Stored Procedure vamos mudar o delimitador SQL padrão para “ // ”.
O comando para mudar é o seguinte:
