SQL - Structured Query Language #2

Introdução

Esse artigo é um resumo feito no intuito de servir como fixação dos conteúdos da matéria de Banco de Dados, no curso de Gestão da T.I - FAPAM 2º Período.

Aulas ministradas pelo professor Gabriel Ribeiro Diniz.

Os comandos SQL serão em sua grande maioria voltados ao SGBD PostgreSQL, já que é o SGBD estudado no curso.

Visão (VIEW)

A "Visão" é uma tabela virtual, na qual os dados não estão fisicamente armazenados. É uma forma de visualizar os dados de uma ou mais tabelas, sem a necessidade de armazenar esses dados em uma tabela física, ela usa dados de tabelas físicas para criar uma tabela virtual, como se fosse um "atalho" para uma consulta complexa nas tabelas físicas.

TABELAS BASE X TABELAS VIRTUAL

• Tabela Base: Tuplas (linhas) estão fisicamente armazenadas no banco de dados.
• Tabela Virtual: Somente a estrutura (esquema) da tabela é armazenada no banco de dados, e não as tuplas.

Para que usar visão?

Deve-se usar visões quando for frequente consultas com junção de várias relações (tabelas), bastando definir estas junções como uma visão e posteriormente consulta-la, na claáusula FROM.

Desse modo, o objetivo principal de uma visão é simplificar as consultas frequentes de dados, ocultando a complexidade dessas consultas SQL.

Criando uma visão

Podemos usar o comando CREATE VIEW antes de uma consulta convenvional no banco.

CREATE VIEW nome_da_visao AS <consulta>;

Exemplo:

CREATE VIEW vw_contato_cliente AS SELECT id, nome, email, telefone FROM Cliente;

Fisicamente, os dados em vw_contato_cliente são os mesmos que em Cliente, mas a visão vw_contato_cliente é uma forma mais simples de acessar esses dados.

Após ser criada, uma visão pode ser consultada normalmente, com a cláusula FROM, como se fosse uma tabela base.

SELECT nome, email FROM vw_contato_cliente;

Sendo assim, se você atualizar os dados da tabela Cliente, a visão vw_contato_cliente também será atualizada, pois ela é uma "referência" para a tabela Cliente.

Outras visões podem ser criadas a partir de visões existentes, e assim por diante (visões sobre visões).

Podemos criar também visões apartir do resultado de múltiplas tabelas. Considere as seguintes tabelas:

CIA_AEREA

VOO

Exemplo 1 - Recuperar o nome da companhia aérea, e os voos que cada um oferece.

SELECT CA.NOME, V.NUM_VOO FROM VOO V INNER JOIN CIA_AEREA CA
ON V.CODIGO_CIA = CA.CODIGO_CIA;

Isso vai nos dar o seguinte retorno:

resultado da consulta

Podemos facilitar essa consulta criando uma "tabela virtual" apartir desse retorno, ou seja, uma visão!

CREATE VIEW vw_voos_por_cia AS
SELECT CA.NOME, V.NUM_VOO FROM VOO V INNER JOIN CIA_AEREA CA
ON V.CODIGO_CIA = CA.CODIGO_CIA;

Agora é só consultar a visão vw_voos_por_cia que vamos obter o mesmo resultado da nossa primeira consulta:

SELECT * FROM vw_voos_por_cia;

vw_voos_por_cia

Podemos também criar visões renomeandos as colunas da tabela base na view, por exemplo:

Sintaxe

CREATE VIEW nome_da_visao (coluna1, coluna2, coluna3, ...) AS <consulta>

Exemplo - Recuperar o nome da companhia aérea e o número de voos que cada uma oferece. Seguindo o exemplo dos voos, considere as seguintes tabelas:

CREATE VIEW total_voos (cia_aerea, numero_voos) AS
SELECT nome, COUNT(num_voo)
  FROM VOO V INNER JOIN CIA_AEREA CA
  ON V.CODIGO_CIA = CA.CODIGO_CIA
GROUP BY nome;

SELECT * FROM total_voos;

SELECT * FROM total_voos WHERE numero_voos >= 2;

Vantagens

As views sãi uma simplificação das consultas que são frequente na aplicaçao, e auxilia no processo de autorização/segurança.

Assim, o acesso não será mais à tabela base VOO e sim, à visão criada a partir da tabela VOO.

Atualização de uma VIEW

O SGBD não garante que uma visão sempre atualizará. Uma view com uma única tabela de definição é atualizável.

UPDATE <nome da visão> SET <valores> WHERE <condição>;

As views definidas sobre múltiplas tabelas usando junções, geralemnte não são atualizáveis.

As views definidas com funções de agrupamente (GROUP BY) e agregação (SUM, AVG, COUNT, MAX, MIN) não são atualizáveis.

Considerações:

A visão não é montada no instante de sua definição, mas sim no momento em que alguma consulta for realizada sobre ela (pois é uma tarefa custosa, em termos de desempenho, manter todas as tuplas se elas não estão sendo usadas em nenhuma consulta).

Em outras palavras, apenas o esquema da visão é armazenado no banco de dados. Todas as vezes que a visão é consultada, o SGBD calcula quais tuplas seriam retornadas por aquela consulta e exibe na tela.

Exemplo - Considerando a seguinte visão:

CREATE VIEW vw_voos_sem_tarifa AS
SELECT NUM_VOO, PARTIDA, DESTINO FROM VOO;

Se tentarmos atualizar essa view diretamente, a tabela base VOO será atualizada, porém o campo Tarifa da tabela VOO terá o valor NULL pois ela não esta sendo referenciada na view.

Exclusão de uma VIEW

Podemos usar o comando DROP VIEW para excluir uma visão.

DROP VIEW <nome_da_visao>;

Ela ficará disponível até que seja executado o comando para deletá-la. É possível excluir visões quando elas não forem mais necessárias, desta forma elas não farão parte do SGBD.

Exemplo

DROP VIEW vw_voos_por_cia;
DROP VIEW vw_voos_sem_tarifa

Alterar o nome da VIEW

Comando para renomear uma visão:

ALTER VIEW <nome_da_visao> RENAME TO <novo_nome_da_visao>;

Exemplo

ALTER VIEW vw_voos_por_cia RENAME TO vw_voos_por_companhia;

Mudar o código (consulta) da VIEW

Comando para alterar o código de uma visão:

CREATE OR REPLACE VIEW <nome_da_visao> AS <nova_consulta>;

Exemplo

CREATE OR REPLACE VIEW vw_teste AS
SELECT * FROM VOO
WHERE tarifa >= 500.00;

Procedimentos (PROCEDURES)

As Stored Procedures são rotinas definidas no banco de dados, indentificadas pelo por um nome pelo qual podem ser invocadas. Um procedimento pode executar uma série de de instruções e receber parâmetros.

Para que usar stored procedures?

Muitas vezes é requerido várias consultas e atualizações no DB, o que acarreta um maior consumo de recursos pela aplicação (desempenho, memória, etc.). No caso de aplicações web, isso se torna mais visível, devido a maior quantidade de informações que precisam trafegar pela rede e de requisições ao servidor.

Uma boa forma de contornar e atenuar esses consumo de recurso diretamente pela aplicação, é transferir parte do processamento para o DB. Assim, considerando que as máquinas servidoras geralmente têm configurações de hardware mais robustas (e nada se pode garantir com relação às máquinas clientes), essa pode ser uma "saída" a se considerar.

Usar ou não usar procedures?

Como exemplo para o funcionamento dos Stored Procedures, iremos comparar a execução de uma rotina utilizando e outra não utilizando essa técnica.

Considere o seguinte contexto de uma aplicação comercial:

• O cliente fez um pedido no qual são inseridos itens
• O pedido (bem como os itens) permanecem com status "PENDENTE" até ser confirmado.
• O operador confirma o pedido e faz o registro no livro caixa.

Até o pedido ser confirmado, nenhum lançamento é feito no livro caixa, então é preciso ter uma rotina de confirmação de pedido, que deve executar as seguintes ações:

1. UPDATE Atualizar o status do pedido (fechado, pendente)
2. UPDATE Atualizar o status dos itens do pedido (vendido, pendente)
3. INSERT Lançar o valor do pedido no caixa (preço)

Temos então pelo menos 3 instruções de atualiação e/ou inserção. Poderíamos representar essa sitação graficamente pela figura:

Por outro lado, poderíamos agrupar essas três instrulções no corpo de um procedimento e chamá-lo a partir da aplicação uma única vez.

As ações de UPDATE/INSERT/DELETE, apartir daí, ficariam por conta do servidor. A representação gráfica desse modelo é mostrada a seguir (Através do procedimento chamado "CONFIRMAR PEDIDO"):

Criando uma PROCEDURE

Sintaxe

CREATE PROCEDURE nome_da_procedure(parametro1 tipo, parametro2 tipo, ...)
LANGUAGE SQL AS
$$
  <corpo da procedure>
$$;

Onde, parametro1, parametro2, ... são os parâmetros que a procedure pode receber (opcionais, caso não ouver, deve se passar parênteses vazios nome_da_procedure()), LANGUAGE SQL indica que a procedure será escrita em SQL, $$ é um delimitador de bloco de código e <corpo da procedure> é o código SQL que será executado.

Exemplo

CREATE PROCEDURE insert_cliente(nome VARCHAR(39), cpf VARCHAR(11), celular VARCHAR(11))
LANGUAGE SQL AS
$$
  INSERT INTO CLIENTE (nome, cpf, celular)
  VALUES ((SELECT MAX(codigo_cliente) + 1 FROM cliente), nome, cpf, celular);
$$;

Tendo criado a procedure, chamá-lo é bastante simples. Para fazer isso fazemos o uso da palavra reservada CALL, como mostra o código a seguir:

CALL <nome procedimento>(<parâmetros>);

Exemplo

CALL insert_cliente('Mateus Felipe', '00000000000', '37999999999');

Atualizando PROCEDURES

Para atualiza o código de uma procedure, basta usar o comando CREATE OR REPLACE PROCEDURE.

CREATE OR REPLACE PROCEDURE update_preco(novo_preco NUMERIC, codigo_produto INTEGER)
LANGUAGE SQL AS
$$
  UPDATE PRODUTO SET preco = novo_preco WHERE codigo_produto = codigo_produto;
$$;

Excluindo PROCEDURES

Assim como outras estruturas no banco de dados, para exclusão de procedures basta fazer:

Sintaxe

DROP PROCEDURE nome_da_procedure;

Exemplo

DROP PROCEDURE insert_cliente;
DROP PROCEDURE update_preco;

Funções (FUNCTIONS)

Funções (Function) são rotinas definidas no banco de dados, identificadas por um nome, pelo qual podem ser invocadas, parecidos com uma procedure, recebendo parâmetros, executando instruções, mas com o diferencial de que uma função sempre retorna um valor.

Outro diferencial das funções em relação aos procedimentos, é que ela não precisa da palavra resrvada CALL para ser invocada, basta chamar a função no meio de uma consulta SQL.

Sintaxe

CREATE FUNCTION nome_da_funcao(parametro1 tipo, parametro2 tipo, ...)
RETURNS tipo_retorno
LANGUAGE plpgsql AS
$$
  DECLARE
    -- declaração de variáveis
  BEGIN
    -- lógica
  END;
$$;

Exemplo

CREATE FUNCTION getNumeroFilmes(dataInicial DATE, dataFinal DATE)
RETURNS INTEGER
LANGUAGE plpgsql AS
$$
  DECLARE
    contadorFilmes INTEGER;
  BEGIN
    SELECT COUNT(*) INTO contadorFilmes FROM FILME
    WHERE data_lancamento BETWEEN dataInicial AND dataFinal;
 
    RETURN contadorFilmes;
  END;
$$;

Observações

A declaração de novas variáveis pode ser necessária no corpo da função!

Tendo criado a funcion, como executa-la? Como na maioria das vezes haverá um tipo de retorno, a chamada da mesma poderá ser feita após o SELECT, ou após a cláusula WHERE, sendo, para este último caso, o valor retornado deve ser comparado a alguma condição.

Exemplo

SELECT contar_filmes('AÇÃO') FROM Filmes;

SELECT * FROM Filmes
WHERE tipo = pegar_tipo_com_mais_filmes();

Atualizando FUNÇÕES

Para atualizar o código de uma função, basta usar o comando CREATE OR REPLACE FUNCTION.

CREATE OR REPLACE FUNCTION getNumeroFilmes(dataInicial DATE, dataFinal DATE)
RETURNS INTEGER
LANGUAGE plpgsql AS...

Excluindo FUNÇÕES

Para exclusão de funções, basta fazer:

Sintaxe

DROP FUNCTION nome_da_funcao;

Exemplo

DROP FUNCTION getNumeroFilmes;

Gatilhos (TRIGGER)

Triggers são objetos do banco de dados que, relacionados a certa tabela, permitem a realização de processamentos em consequência de uma dererminada ação sobre a tabela.

As triggers são uma forma de automatizar certas ações com base em eventos ocorridos. Podem ser executadas antes BEFORE ou depois AFTER de uma operação de inserção INSERT, atualização UPDATE ou exclusão DELETE de registros.

Em aplicações que utilizam banco de dados, que ações sejam disparadas em resposta como consequência de outras, realizando operações de cálculo, validação e, em geral, surtindo alterações na base de dados automaticamente.

VANTAGENS:

• Parte do processo que seria executado pela aplicação (sistema/software) passa para o banco de dados, poupando recursos da máquina cliente.
• Facilita a manutenção, sem que seja necessário alterar o código da aplicação.

SINTAXE:

CREATE TRIGGER <Nome do gatilho>
<Momento> <Evento> ON nome_da_tabela
FOR EACH ROW EXECUTE FUNCTION <Funcao>;

Momento aqui se refere a quando a trigger será executada, podendo ser BEFORE (antes) ou AFTER (depois) de um evento. O evento é a operação que dispara a trigger, podendo ser INSERT (inserção), UPDATE (atualização) ou DELETE (deleção).

Mas antes de criarmos a trigger de fato, precisamos criar a função que será executada pela trigger. Aqui, reaproveitaremos o conteúdo passado no tópico anterior sobre Funções, introduzindo alguns conceitos novos.

Primeiro vamos pegar uma função a ser executada pela trigger em um UPDATE, e explicar os elementos que a compõem.

Exemplo de uma function que possui os elementos OLD e NEW:

CREATE OR REPLACE FUNCTION atualizar_preco()
RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
BEGIN
  IF NEW.preco <> OLD.preco THEN
    INSERT INTO HISTORICO_PRECO (codigo_produto, preco_antigo, preco_novo)
    VALUES (NEW.codigo_produto, OLD.preco, NEW.preco);
  END IF;
  RETURN NEW;
END;
$$;
 
CREATE TRIGGER trigger_atualizar_preco
BEFORE UPDATE ON PRODUTO
FOR EACH ROW EXECUTE FUNCTION atualizar_preco();

Essa é uma função que será executada antes de uma atualização na tabela PRODUTO. O que ela faz é verificar se o preço novo é diferente do preço antigo, e se for, insere um registro no histórico de preços.

Elementos:

• OLD: refere-se ao valor antigo da linha que está sendo atualizada.
• NEW: refere-se ao valor novo da linha que está sendo atualizada.
• RETURN NEW: é necessário para que a atualização seja efetivada.
• IF NEW.preco <> OLD.preco THEN: é uma condição que verifica se o preço novo é diferente do preço antigo. Se for, é feito um registro no histórico de preços.
• INSERT INTO HISTORICO_PRECO: é a instrução que insere o registro no histórico de preços.

Excluindo TRIGGERS

Para exclusão de triggers, basta fazer:

DROP TRIGGER nome_da_trigger;

Fontes

• Slides das aulas do professor Gabriel Ribeiro Diniz.
• Trigger.md por Guilherme Guimarães Oliveira