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.
Esse artigo assume que você já tenha domínio do conteúdo passado anteriormente, caso não tenha, recomendo que leia o artigo SQL - Structured Query Language #1 e seus antecessores.
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.
Exemplo:
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.
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
Codigo_Cia | Nome | Sede | Presidente |
---|---|---|---|
1 | Varig | Vitória | Raul Sidnei |
2 | Air France | Paris | Etoile Arns |
3 | Tam | São Paulo | Cida Faria |
4 | Gol | Rio de Janeiro | Bia Falcão |
VOO
Num_Voo | Tarifa | Partida | Destino | Codigo_Cia* |
---|---|---|---|---|
AF234 | 726.20 | Brasília | Paris | 2 |
VG893 | 254.30 | BH | RS | 1 |
AF875 | 541.00 | São Paulo | Paris | 2 |
VG487 | 189.40 | Brasília | Curitiba | 1 |
GO142 | 349.00 | RJ | Recife | 4 |
TA961 | 99.00 | BH | Brasília | 3 |
Exemplo 1 - Recuperar o nome da companhia aérea, e os voos que cada um oferece.
Isso vai nos dar o seguinte retorno:
resultado da consulta
Nome | Num_Voo |
---|---|
Varig | VG487 |
Varig | VG893 |
Air France | AF234 |
Air France | AF875 |
Tam | TA961 |
Gol | GO142 |
Podemos facilitar essa consulta criando uma "tabela virtual" apartir desse retorno, ou seja, uma visão!
Agora é só consultar a visão vw_voos_por_cia
que vamos obter o mesmo resultado da nossa primeira consulta:
vw_voos_por_cia
Nome | Num_Voo |
---|---|
Varig | VG487 |
Varig | VG893 |
Air France | AF234 |
Air France | AF875 |
Tam | TA961 |
Gol | GO142 |
Podemos também criar visões renomeandos as colunas da tabela base na view, por exemplo:
Sintaxe
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:
cia_aerea | numero_voos |
---|---|
Air France | 2 |
Gol | 1 |
Tam | 1 |
Varig | 2 |
cia_aerea | numero_voos |
---|---|
Air France | 2 |
Varig | 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.
Exemplo: a empresa poderia não querer que o público tivesse acesso às tarifas dos voos (devido a uma promoção), na qual vários voos teriam o mesmo preço anunciado separadamente.
Solução: para "esconder" a coluna Tarifa
, eu poderia criar uma visão da tabela VOO
que contém todos os dados, esceto Tarifa
.
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.
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:
num_voo | partida | destino |
---|---|---|
AF234 | Brasília | Paris |
AF875 | São Paulo | Paris |
GO142 | RJ | Recife |
GO542 | Brasília | Belém |
TA961 | BH | Brasília |
VG487 | Brasília | Curitiba |
VG893 | BH | RS |
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.
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
Alterar o nome da VIEW
Comando para renomear uma visão:
Exemplo
Mudar o código (consulta) da VIEW
Comando para alterar o código de uma visão:
Exemplo
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:
UPDATE
Atualizar o status do pedido (fechado, pendente)UPDATE
Atualizar o status dos itens do pedido (vendido, pendente)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"):
Vantagens:
- Simplificação da execução de instruções SQL pela aplicação.
- Transferência de parte da responsabilidade de processamento para o servidor.
- Facilidade na manutenção, reduzindo a quantidade de alterações na aplicação.
Desvantagens:
- Necessidade de maior conhecimento da sintaxe do banco de dados para escrita de rotinas em SQL (nível avançado).
- As rotinas ficam mais facilmente acessíveis. Alguém que tenha acesso ao banco de dados poderá visualizar e alterar o código.
Criando uma PROCEDURE
Sintaxe
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
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:
Exemplo
Atualizando PROCEDURES
Para atualiza o código de uma procedure, basta usar o comando CREATE OR REPLACE PROCEDURE
.
Excluindo PROCEDURES
Assim como outras estruturas no banco de dados, para exclusão de procedures basta fazer:
Sintaxe
Exemplo
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
Exemplo
plpgsql
PL/pgSQL é a abreviatura de Procedural Language/PostgreSQL Structured Query Language. É uma linguagem procedural suportada pelo PostgreSQL, usado para escrever lógicas complexas e controle de fluxo, como loops, condicionais, etc. Adicionando poderes extras às funcionalidades do SQL.
Mais referências:
- Chapter 43. PL/pgSQL — SQL Procedural Language: [EN] https://www.postgresql.org/docs/current/plpgsql.html
- PLPGSQL: [PT] https://pt.wikipedia.org/wiki/PLPGSQL
- PostgreSQL PL/pgSQL: [EN] https://www.postgresqltutorial.com/postgresql-plpgsql/
Capítulo 43. PL/pgSQL - Linguagem procedural SQL: [PT] https://halleyoliv.gitlab.io/pgdocptbr/plpgsql.html - PostgreSQL Prático/Funções Definidas pelo Usuário e Triggers/PlpgSQL: [PT] https://pt.wikibooks.org/wiki/PostgreSQL_Pr%C3%A1tico/Fun%C3%A7%C3%B5es_Definidas_pelo_Usu%C3%A1rio_e_Triggers/PlpgSQL
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
Atualizando FUNÇÕES
Para atualizar o código de uma função, basta usar o comando CREATE OR REPLACE FUNCTION
.
Excluindo FUNÇÕES
Para exclusão de funções, basta fazer:
Sintaxe
Exemplo