Programando em SQL Objetivo Stored procedures, Triggers e

15/8/2008
Objetivo
• Descrever as funcionalidades de triggers,
store procedures e funções;
• Apresentar vantagens e benefícios;
• Criação e Gerenciamento de Store
Program;
Programando em SQL
Triggers, Stored Procedures e
funções
Profa. Késsia Marchi
Profa. Késsia Marchi
Stored procedures, Triggers e
Funções
• Procedimentos internos que tem a capacidade de
disparar ações baseadas em eventos ocorridos em
uma tabela ou em outros procedimentos;
• Utilizados em tarefas rotineiras e repetitivas;
• Diversos SGBD apresentam essas funcionalidades;
• Podem ser criados por meio de várias linguagens de
programação, como exemplo:
–
–
–
–
–
Linguagens SQL
Perl
Phthon
C
E outras...
Profa. Késsia Marchi
• Usados para eliminar erros obtidos
através do uso da aspas.
• Com o uso dos delimitadores ($$) o
interpretador entende que esses
caracteres definem o início e o fim da
definição.
Profa. Késsia Marchi
• Funções, Trigger e Stored procedures
apresentam sintaxe muito semelhantes
– Distinguem-se pela forma de execução
• Funções – Por chamadas a seus nomes, através
de comandos como o select ou, em linguagens de
programação, através de seus próprios métodos.
• Trigger – Disparadas por eventos
• Procedures – Declarações em programas
aplicativos, funções ou triggers
Profa. Késsia Marchi
Caracteres delimitadores de
string
– CRIAR FUNÇÃO TESTE “FUNCAO=“TESTE”” -– CRAR FUNÇÃO TESTE $$FUNCAO= “TESTE”$$
Criação de Stored procedures,
Triggers e Funções
ERRO
Relacionamento entre funções
• O relacionamento entre funções se dá
através do uso de ponteiros, utilizando os
OID das funções que são invocadas.
• Sempre que é necessário excluir e
reconstruir uma função, o seu OID é
alterado, logo, é necessário re-compilar
todas as funções que invocam a função
reconstruída.
Profa. Késsia Marchi
1
15/8/2008
Funções
• São blocos de códigos SQL armazenados
no servidor de banco de dados que
podem ser invocados a qualquer momento
com o objetivo de realizar algum
processamento em informações.
• O objetivo da função não é o de realizar
transação de negócios completas, mas
sim, realizar pequenas operações,
normalmente auxiliares, que possam ser
solicitadas em um processo de transação.
Profa. Késsia Marchi
Funções
• O uso das funções devem ser mesclados
com as Stored Procedures e Triggers.
• Cabe a Stored Procedures o
gerenciamento das transações.
• Cabe as funções a disponibilização de
recursos e bibliotecas de códigos
freqüentemente utilizadas, a fim de evitar
a redundância da escrita e manutenção de
códigos SQL no corpo da Stored
Procedure.
Profa. Késsia Marchi
Funções já existentes em
PostgreSQL
•
•
•
•
•
•
•
•
USER
CURRENT_USER
current_setting('datestyle');
current_database()
current_schema()
inet_server_addr()
generate_series(start, stop)
generate_series(start, stop,
step)
• Funções de Agregação
• Para conhecer todas as
funções disponíveis no
PostgreSQL 8.0 acesse
http://www.postgresql.org/docs
/8.3/static/functions.html
UDF’s
• Funções definidas pelo Usuário
– Podem ser criadas por SQL ou em
Linguagens de alto-nível (bibliotecas);
– Aplica o conceito de re-utilização;
– Útil para automatizar funções repetitivas;
Profa. Késsia Marchi
Profa. Késsia Marchi
Criando funções
CREATE FUNCTION nome(argumentos)
RETURNS tipo_de_retorno AS $$
DECLARE
-- bloco de declarações das variáveis
BEGIN
-- corpo da função
END;
$$ LANGUAGE plpgsql;
Profa. Késsia Marchi
Criando funções
• Parâmetros
– A lista de parâmetros devem estar sempre
presentes.
• Se não houver parâmetros indica uma lista vazia
de ( ).
• Cláusula RETURNS
– Indica o tipo de retorno da função.
– Caso não tenha retorno deve ser utilizada a
opção VOID como tipo de retorno.
Profa. Késsia Marchi
2
15/8/2008
Exemplo de Função
• Exemplo de função sem o uso de nenhum objeto
do BD.
CREATE FUNCTION soma(valorA integer, valorB integer) RETURNS integer AS $$
DECLARE
valor_total integer := 0;
BEGIN
valor_total = valorA + valorB;
RETURN valor_total;
END;
$$ LANGUAGE plpgsql;
Profa. Késsia Marchi
Exemplo de Função
• Alterar Função
– Nome
ALTER FUNCTION nome_antigo([argumentos])
RENAME TO novo_nome;
Profa. Késsia Marchi
Exclusão de Função
Parêmetro
Descrição
Nome
Informe o nome da função a ser removida;
Argumentos
Informe os tipos de dados dos argumentos da função que
será removida.
Opções
É possível utilizar dois valores como opções de exclusão,
sendo:
CASCADE
Ao utilizar a opção CASCADE, todos os objetos
diretamente dependentes da função em questão são
excluídos junto com a mesma.
RESTRICT
Se a opção RESTRICT for utilizada, somente a função
será removida, mantendo todos os demais objetos
independentes de suas dependencias. Ao utilizar esta
opção, a CASCADE não poderá ser utilizada e vice-versa.
Profa. Késsia Marchi
Exemplo de Função
• Exemplo de função utilizando tabelas no BD.
CREATE FUNCTION capturaLinha() RETURNS RECORD AS $$
DECLARE
-- Declara uma variável do tipo da linha da tabela em questão
minhaLinha exemplo%ROWTYPE;
BEGIN
-- Captura uma linha qualquer na tabela específica
SELECT * INTO minhaLinha from exemplo LIMIT 1;
-- Exibe na tela um dos campos da linha retornada
RAISE NOTICE 'O campo extexto desta linha: %', minhaLinha.extexto;
--Retorna a linha completa
RETURN minhaLinha;
END;
$$ LANGUAGE plpgsql;
Profa. Késsia Marchi
Exclusão de Função
• DROP FUNCTION
nome_função([argumentos]) opções;
Profa. Késsia Marchi
Referências Bibliográficas
• Korth, Henry F. e Silbershcatz, Abraham;
Sistemas de Banco de Dados; Elsevier;
5ª Ed., 2006.
• Elmasri, Ramez; Sistemas de Banco de
Dados; Addison Wesley, 2005.
• MILANI, André; PostgreSQL Guia do
Programador. Novatec, 2008
Profa. Késsia Marchi
3