Banco de Dados PLPGSQL
Prof. Dr. Joel da Silva
PLPGSQL - Introdução
3
PLPGSQL - Introdução
l A PLPGSQL ou PL/pgSQL é uma linguagem estendida da SQL que tem por objetivo auxiliar as tarefas de programação no PostgreSQL.
l Ela incorpora à SQL características procedurais, como os benefícios e facilidades de controle de fluxo de programas que as melhores linguagens possuem. Por exemplo loops estruturados (for, while) e controle de decisão (if then else).
l PLPGSQL é uma PL/SQL significa "Procedural Language extensions to SQL", que pode ser usado em bancos de dados. O PL/SQL é a linguagem SQL com construções de programação similares a outras liguagens.
l Apesar do QUERY constar no nome, ela não é apenas de consulta (inclusão, alteração,...)
l Documentação e artigos para leitura:
l Versão Atual (Inglês): http://www.postgresql.org/docs/current/static/plpgsql.html
l Livro Practical PostgreSQL: http://www.faqs.org/docs/ppbook/book1.htm
l Tradução para Português (v 8.0) http://pgdocptbr.sourceforge.net/pg80/plpgsql.html
l WIKILIVROS PostgreSQL Prático:
l http://pt.wikibooks.org/wiki/PostgreSQL_Pr%C3%A1tico/Fun%C3%A7%C3%B5es_Definidas_pelo_Usu
%C3%A1rio_e_Triggers/PlpgSQL
l http://www.codeproject.com/Articles/33734/How-to-write-PL-pgSQL-functions-for-PostgreSQL-8-3
l http://postgres.cz/wiki/PL/pgSQL_(en)
PLPGSQL - Introdução
l
Estrutura de uma função
CREATE OR REPLACE FUNCTION OLA_MUNDO() RETURNS VOID AS $$
-- assinatura da função
DECLARE -- declaração de variáveis
nome varchar(50) :=’TSI - IFFarroupilha';
BEGIN -- início de um bloco de instruções RAISE NOTICE 'OLÁ %',nome;
END; --final de um bloco
$$ LANGUAGE plpgsql; -- final da função
l
Executando uma função
select OLA_MUNDO() -- comando para chamar a função
5
PLPGSQL
l Estrutura de uma função com parâmetros
CREATE OR REPLACE FUNCTION OLA_MUNDO(nome varchar) RETURNS VOID AS $$
BEGIN
RAISE NOTICE 'OLÁ %',nome;
END;
$$ LANGUAGE plpgsql;
select OLA_MUNDO(’IFFarroupilha – Frederico Westphalen')
PLPGSQL
l Estrutura de uma função com parâmetros
CREATE OR REPLACE FUNCTION SOMAR(n1 integer, n2 integer) RETURNS integer AS $$
BEGIN
RAISE NOTICE 'SOMA DOS VALORES : ';
RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql;
select somar(2,4)
7
PLPGSQL
Exemplo utilizando a base dvd rental
l
Listar todos os pagamentos realizados juntamente com a soma de total de todos os pagamentos;
CREATE OR REPLACE FUNCTION PAGAMENTOS() RETURNS VOID AS $$
DECLARE
pagamentos RECORD;
pagamento float;
total float;
BEGIN
RAISE NOTICE '#### SELECIONANDO PAGAMENTOS ####';
total = 0;
FOR pagamentos IN SELECT payment_id, payment.amount FROM payment where amount >0 LOOP
pagamento = pagamentos."amount";
total = total + pagamento;
RAISE NOTICE 'Valor ==> % ', pagamento;
END LOOP; RAISE NOTICE 'Total ==> % ', round(total::numeric,2);
RETURN;END;$$LANGUAGE plpgsql;
--chamada para a função SELECT PAGAMENTOS();
PLPGSQL
Exemplo utilizando a base dos correios
l
Exemplo de Função para listar dados de uma tabela /* Função para selecionar somente as localidades do RS*/
CREATE OR REPLACE FUNCTION LOCALIDADES_RS() RETURNS VOID AS $$
DECLARE
localidades RECORD;
nome_localidade varchar;
BEGIN
RAISE NOTICE '#### SELECIONANDO LOCALIDADES ####';
FOR localidades IN SELECT * FROM "LOG_LOCALIDADE" WHERE "UFE_SG" = 'RS' LOOP
nome_localidade = localidades."LOC_NO";
RAISE NOTICE 'Nome da Localidade ==> % ', nome_localidade;
END LOOP;
RETURN;
END;
$$LANGUAGE plpgsql;
9
l
Exemplo de Função para listar dados de uma tabela
/*Função para selecionar somente as localidades de um estado passado como parâmetro */
CREATE OR REPLACE FUNCTION
LOCALIDADES_DO_ESTADO(estado varchar) RETURNS VOID AS $$
DECLARE
localidades RECORD;
nome_localidade varchar;
BEGIN
RAISE NOTICE '#### SELECIONANDO LOCALIDADES ####';
FOR localidades IN SELECT * FROM "LOG_LOCALIDADE" WHERE
"UFE_SG" = estado LOOP
--FOR localidades IN SELECT * FROM "LOG_LOCALIDADE" WHERE
"UFE_SG" = $1 LOOP
nome_localidade = localidades."LOC_NO";
RAISE NOTICE 'Nome da Localidade ==> % ', nome_localidade;
END LOOP;
RETURN;
END;
$$LANGUAGE plpgsql;
--chamada para a função
SELECT LOCALIDADES_DO_ESTADO('SP')
PLPGSQL
Exemplo utilizando a base dos correios
l
Exemplo de Função para listar dados de uma tabela
/*Função para concatenar o nome da localidade com o nome do estado*/
CREATE OR REPLACE FUNCTION LOCALIDADE_ESTADO() RETURNS VOID AS $$
DECLARE
localidades RECORD;
nome_localidade varchar;
nome_estado varchar;
nome_completo varchar;
BEGIN
RAISE NOTICE '#### CONCATENANDO NOMES DE LOCALIDADES E NOMES DE ESTADOS ####';
FOR localidades IN SELECT * FROM "LOG_LOCALIDADE" LOOP nome_localidade = localidades."LOC_NO";
nome_estado = localidades."UFE_SG" ;
nome_completo = nome_localidade || '-' || nome_estado;
RAISE NOTICE '% ', nome_completo;
END LOOP;
RETURN;
END;
PLPGSQL
Exemplo utilizando a base dos correios
11
PLPGSQL - Exercício
l Crie uma função chamada ENDERECO.
–