query. 3. QUANDO USAR STORED PROCEDURES software permitir.

BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
- Aula 10 PROCEDIMENTOS ARMAZENADOS - STORED PROCEDURES
1. INTRODUÇÃO
Em muitas situações será necessário armazenar procedimentos escritos com a finalidade de
se utilizar recursos como loop, estruturas de decisão, etc. Para isso, utiliza-se os procedimentos
armazenados o qual definimos como um programa escrito em uma linguagem própria que é
armazenado como parte do banco de dados.
Em outras palavras Procedimento armazenado ou Stored Procedure é uma coleção
de comandos em SQL disponíveis em Banco de dados. Encapsula tarefas repetitivas, aceita
parâmetros de entrada e retorna um valor de status (para indicar aceitação ou falha na execução).
O procedimento armazenado pode reduzir o tráfego na rede, melhorar a performance, criar
mecanismos de segurança, etc. (Wikipédia, a enciclopédia livre, 2009).
2. VANTAGENS DO USO DE STORED PROCEDURES?
-
-
Redução de tráfico de rede.
Aumenta a performance da aplicação, particularmente em uma WAN ou em uma conexão
de baixa velocidade.
Utilizando stored procedure, elimina-se o processo de “parsed”, ou seja da query ter que
ser analisada gramaticalmente e submetido ao optimizador para formulação de um plano
de execução.
São analisadas, optimizadas e armazenadas em uma forma executável no momento em
que são adicionadas ao banco de dados.
É possível executar operações muito mais complexas que uma simples query.
Pode ser usada por aplicações distintas.
Facilita a manutenção, pois é possível alterá-la sem ter que recompilar a aplicação.
Proporciona mais segurança ao banco de dados, acessando tabelas que o usuário não tem
acesso
3. QUANDO USAR STORED PROCEDURES
-
Sempre que a metodologia de desenvolvimento do software permitir.
Não existem desvantagens em usar procedimentos armazenados, entretanto existem duas
limitações:
- Deve ser possível passar qualquer informação variável para a stored procedure
(parâmetros).
- Operações complexas podem ser limitadas.
1
BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
4. STORED PROCEDURES NO POSTGRESQL
Como visto, Stored Procedures são programas desenvolvidos em determinada linguagem
de script e armazenados no servidor, local onde serão processados. Também são conhecidos como
funções, este o motivo pelo qual as stored procedures no PostgreSQL são referenciadas como
Function.
O PostgreSQL conta com três formas diferentes de criar funções:
- Funções em Linguagem SQL: São funções que utilizam a sintaxe SQL e se
caracterizam por não possuírem estruturas de condição (if, else, case), estruturas de repetição
(while, do while, for), não permitirem a criação de variáveis e utilizam sempre algum dos seguintes
comandos SQL: SELECT, INSERT, DELETE ou UPDATE.
- Funções de Linguagens Procedurais: ao contrário das funções SQL, aqui é permitido
o uso de estruturas de condição e repetição e o uso de variáveis. As funções em linguagens
procedurais caracterizam-se também por não possuírem apenas uma possibilidade de linguagem,
mas várias. Normalmente a mais utilizada é conhecida como PL/PgSQL, linguagem semelhante ao
conhecido PL/SQL da Oracle. Existem outras linguagens como, por exemplo, o PL/Perl, PL/Python
e PL/Tcl, que possuem sintaxe igual ou semelhante às linguagens que lhes deram origem.
- Funções em Linguagens Externas ou de Rotinas Complexas: São funções
normalmente escritas em C++ que trazem consigo a vantagem de utilizarem uma linguagem com
diversos recursos, na qual pode-se implementar algoritmos com grande complexidade. Tais
funções são empacotadas e registradas no SGBD para seu uso futuro.
Existem ainda outras linguagens como PL/Ruby, PL/sh e PL/Java, no entanto, estas são
definidas por projetos independentes. Dentre elas destacam-se as funções criadas com PL/Java
devido a sua crescente demanda entre usuários do PostgreSQL.
Para criar uma função utilizando SQL no PostgreSQL utiliza-se o comando CREATE
FUNCTION:
SINTAXE:
CREATE [ OR REPLACE ] FUNCTION nome ( [ tipo_do_parametro1 [,
RETURNS tipo_retornado AS '
Implementação_da_função;
'
LANGUAGE 'SQL';
...] ] )
- CREATE FUNCTION é o comando que define a criação de uma função, [OR REPLACE]
informa que se acaso existir uma função com este nome, a atual função deverá sobrescrever a
antiga.
2
BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
- RETURNS tipo_retornado informa o tipo de dado que será retornado ao término da
função. Tais tipos de retornos são os convencionais como o INTEGER, FLOAT, VARCHAR, etc. As
funções em SQL também permitem o retorno de múltiplos valores e para isso informa-se como
retorno SETOF. Na implementação haverá as linhas de programação para a implementação da
stored procedure.
- LANGUAGE indica para a função em qual linguagem ela está sendo implementada.
Na passagem de parâmetros à função não se utiliza o nome nas variáveis declaradas nos
parênteses da assinatura da função. Utiliza-se o tipo da variável de parâmetro separado por
vírgula. Para acessar o valor dos parâmetros, utiliza-se o $ mais o número da posição que ocupa
nos parâmetros, seguindo a ordem da esquerda para a direita.
EXEMPLO:
CREATE FUNCTION soma(INTEGER, INTEGER)
RETURNS INTEGER AS '
SELECT $1 + $2;
'
LANGUAGE 'SQL';
O fato de que as funções utilizando SQL sempre retornam valor faz com que seja sempre
necessário que a última linha de comando da função utilize o comando SELECT.
EXEMPLO:
CREATE FUNCTION cubo(INTEGER)
RETURNS FLOAT AS '
SELECT $1 ^ 3;
'
LANGUAGE 'SQL';
Também é possível criar funções que fazem interação entre uma determinada consulta e
parâmetros utilizados na função. Na função abaixo obtém-se o total de CD‟s gravados por
determinada gravadora, passando como parâmetro o id da gravadora (grav_id):
EXEMPLOS:
CREATE FUNCTION cdGravados(INTEGER)
RETURNS INTEGER AS '
SELECT COUNT(cd_id) FROM cd WHERE grav_id = $1; '
LANGUAGE 'SQL';
CREATE OR REPLACE FUNCTION cdGravados2(in INT, out f1 INT, out f2 TEXT, out f3 NUMERIC)
AS $$ SELECT CAST(cd_id as integer), CAST(cd_nome AS text), CAST(cd_preco AS numeric)
FROM cd WHERE GRAV_id = $1 $$
LANGUAGE „SQL‟;
3
BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
OBS.:
Dependendo da configuração escolhida na instalação do PostgreSQL será necessário
ajustar o tipo do retorno fazendo uso da função CAST. Exemplo: CAST(COUNT(cd_id) as
INTEGER)
Para usar a função utiliza-se a cláusula SELECT.
EXEMPLO:
SELECT cdGravados(4);
Como mencionado, também é possível retornar várias linhas de uma consulta em uma
função. Para isso, utilizamos o tipo de retorno SETOF. No próximo exemplo é criada uma função
em que retorna todos os CD‟s mais caros que determinado valor passado por parâmetro.
EXEMPLO:
CREATE OR REPLACE FUNCTION precoMaior(NUMERIC(10,2))
RETURNS SETOF cd AS'
SELECT * FROM cd WHERE cd_preco > $1;
'
LANGUAGE 'SQL';
Quando as funções possuem o seu retorno referenciado em uma tabela ou uma View, ou
seja, quando a função retorna um resultset, devemos utilizar a função da seguinte maneira:
EXEMPLO:
SELECT * FROM precoMaior(5.2);
A exclusão de uma função se faz através do comando DROP, como visto na DDL.
SINTAXE:
DROP FUNCTION nome_da_funcao();
Quando a função, na sua assinatura contiver parâmetros não será possível sua exclusão
através do comando DROP FUNCTION nome_da_funcao(), ou seja, para excluir uma função é
necessário passar toda a sua assinatura.
EXEMPLO:
DROP FUNCTION precoMaior(NUMERIC(10,2));
Ainda existe o fato que no momento da exclusão poder excluir a função passando mais um
parâmetro, como no exemplo a seguir:
EXEMPLO:
DROP FUNCTION cd_grav (psql INTEGER) RESTRICT;
4
BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
DROP FUNCTION cd_grav (psql INTEGER) CASCADE;
Passando o RESTRICT como parâmetro, a exclusão da função será recusada caso existam
dependências de objetos em torno da função (como por exemplo, Triggers e operadores). Com o
CASCADE esses objetos serão excluídos juntamente com a função.
OBS.:
Se a função for removida e recriada, a nova função não é mais a mesma entidade que era
antes. Ficarão inválidas as regras, visões, gatilhos, etc. existentes que fazem referência à antiga
função. Use o comando CREATE OR REPLACE FUNCTION para mudar a definição de uma
função, sem invalidar os objetos que fazem referência à função.
4.1. PL/pgSQL
A PL/pgSQL é uma linguagem estrutural estendida da SQL que tem por objetivo auxiliar as
tarefas de programação no PostgreSQL. Ela incorpora à SQL características procedurais, como os
benefícios e facilidades de controle de fluxo de programas que as melhores linguagens possuem
como loops estruturados (for, while) e controle de decisão (if, then, else).
Dessa forma, programar em PL/pgSQL significa ter a disposição um ambiente procedural
totalmente desenvolvido para aplicações de bancos de dados, beneficiando-se do controle
transacional inerente das aplicações deste tipo.
4.1.1. ELEMENTOS DA LINGUAGEM
A criação de Stored Procedures inclui todas as construções de uma linguagem de programação
estruturada, como:
- Cometários: -- Este é um comentário
- Bloco de comandos (BEGIN/END): É possível usar construções como IF-THEN-ELSE e loops
WHILE que somente podem conter um comando.
- Comandos de atribuição: var1 := var2 * var3
- Operador de concatenação: || (duas barras verticais)
- Além desses elementos é possível usar as expressões condicionais:
- BETWEEN
- LIKE IN
- EXISTS
- ANY
- ALL
- IS NULL
- IS NOT NULL
4.1.2. ESTRUTURA DA LINGUAGEM
A PL/pgSQL é estruturada em blocos, assim, todo o texto de definição de uma função
precisa estar em um bloco.
5
BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
SINTAXE:
[<<label>>]
[DECLARE
declarações]
BEGIN
conteúdo 1;
conteúdo 2;
conteúdo n;
END;
- DECLARE é a área para declaração de variáveis. As variáveis declaradas nessa sessão
são inicializadas recebendo seus valores default sempre que existe uma entrada nesse bloco.
Como na maior parte das linguagens existentes, na PL/pgSQL, variáveis com o mesmo nome
possuem o seu escopo definido para o bloco imediatamente posterior à sua declaração.
EXEMPLO
CREATE OR REPLACE FUNCTION mostra_valor(valor INT)
RETURNS INTEGER AS
$$ DECLARE
valor INTEGER := $1;
BEGIN
RAISE NOTICE 'O valor da variável aqui é %', Valor;
RETURN valor;
END; $$
LANGUAGE 'PLPGSQL';
Executando a função:
SELECT mostra_valor(12);
Como visto é possível usar as mesmas estruturas de controle disponíveis nas linguagens de
programação:
EXEMPLO
CREATE OR REPLACE FUNCTION formatarCPF(cpf VARCHAR(11))
RETURNS VARCHAR(14) AS $$
BEGIN
IF char_length(cpf) != 11 THEN
RAISE NOTICE 'Formato inválido: %', $1;
RETURN 'ERRO';
END IF;
RETURN SUBSTRING(cpf FROM 1 FOR 3) || '.' || SUBSTRING(cpf FROM 4 FOR 3) || '.' ||
SUBSTRING(cpf FROM 7 FOR 3) || '-' || SUBSTRING(cpf FROM 10 FOR 2);
END;
$$
6
BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
LANGUAGE PLPGSQL;
SELECT formatarCPF('11111111111');
Além de todos os recursos advindos da estrutura de programação é permitido fazer
interação com a estrutura do banco de dados nas operações da DML como no exemplo abaixo:
EXEMPLO
CREATE OR REPLACE FUNCTION alteraPreco(id INTEGER, preco NUMERIC(10,4))
RETURNS VARCHAR(50) AS $$
DECLARE
valorMax NUMERIC(10,4) := 100;
valorMin NUMERIC(10,4) := 1;
BEGIN
IF $2 >= valorMin and $2 <= valorMax THEN
UPDATE CD SET cd_preco = $2 WHERE cd_id = $1;
RETURN 'Preço alterado com sucesso';
ELSE
RETURN 'O valor inserido está fora da faixa delimitada para preços de CDs';
END IF;
END;
$$
LANGUAGE PLPGSQL;
SELECT alteraPreco(1, 16.5);
SELECT alteraPreco(2, 120);
As versões 8.3 e posteriores do PostgreSQL permitem o retorno de conjuntos de linhas e
colunas de uma função através da funcionalidade RETURN QUERY. O RETURN QUERY aceita o
tipo RECORD como valor de retorno tornando fácil retornar resultados de consultas genéricas.
EXEMPLO:
CREATE OR REPLACE FUNCTION cd_grav (psql integer)
RETURNS SETOF RECORD AS $$
BEGIN
RETURN QUERY SELECT cd_nome, grav_nome FROM cd, gravadora WHERE cd.grav_id =
gravadora.grav_id AND gravadora.grav_id = $1; -- Consulta
RETURN ; -- Retorna as linhas
END;
$$ LANGUAGE plpgsql;
A diferença desta forma de programar está na chamada da função. Ao utilizar valor de
retorno do tipo RECORD, deve ser indicada a lista de campos a serem retornados e seus
7
BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
tipos. Esta pode ser vista como uma limitação, embora na prática também seja uma forma de se
garantir a confiabilidade dos resultados retornados na execução de comandos SQL dentro de
funções pela validação dos valores retornados.
EXEMPLO:
SELECT * FROM cd_grav(2) AS ( c1 VARCHAR(50), c2 VARCHAR(50) );
Esses são alguns recursos possíveis com Stored Procedures/Functions no PostgreSQL.
Contudo, é importante saber que existe um gama de outros recursos possíveis fazendo uso do
PgSQL, assim como acontece com outros bancos de dados como o Oracle.
5. EXERCÍCIOS
a) Crie uma Stored Procedure no PostgreSQL que faça inserções na tabela de música.
b) Crie uma Stored Procedure no PostgreSQL que retorne quais música foram gravadas em
determinado CD passando como parâmetro o id do CD (cd_id).
6. REFERÊNCIAS
ELMASRI, Ramez; Sistema de Banco de Dados - Fundamentos e Aplicações - 4ª Edição,
Pearson Education, 2005
OLIVEIRA, Celso Henrique Poderoso; SQL – Curso Prático; Novatec, 2002.
Momjian, Bruce; PostgreSQL: Introduction and Concepts. E-Book; Addison-Wesley, 2001.
PostgreSQL – Manual (With user comments) do PostgreSQL 8.3. Disponível em
<http://www.postgresql.org/files/documentation/pdf/8.3/postgresql-8.3-A4.pdf>
8