Aula 06 SQL PLpgSQL

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

*
PL/pgSQL
Introdução à Linguagem
*
PL/pgSQL
Procedural Language / PostgreSQL
Linguagem que combina o SQL com características de uma linguagem de programação
Pode estar associada a gatilhos (TRIGGERs) ou serem chamadas diretamente
*
Instalação do PL/pgSQL
Caso não esteja disponível, é necessário instalar a linguagem no BD:
No Prompt de Comando
No PgSQL
createlang plpgsql –U usuário –h host nomeBD
create language plpgsql;
*
Estrutura da Linguagem
A sintaxe básica para se criar uma função é:
CREATE [OR REPLACE] FUNCTION nome ([[nome_param] tipo_param [,...]])
RETURNS tipo_retorno
AS
$$ 
DECLARE
 variável tipo_variável;
 ...
BEGIN
 instrução;
 ...
 RETURN valor_retorno;
END;
$$
LANGUAGE 'plpgsql';
*
Estrutura da Linguagem
Ex1:
CREATE OR REPLACE FUNCTION media (a NUMERIC, b NUMERIC)
RETURNS NUMERIC
AS
$$
DECLARE
 result NUMERIC;
BEGIN
 result := (a + b) / 2;
 RETURN result;
END;
$$
LANGUAGE 'plpgsql';
*
Estrutura da Linguagem
Ex1:
CREATE OR REPLACE FUNCTION media (NUMERIC, NUMERIC)
RETURNS NUMERIC
AS
$$
DECLARE
 result NUMERIC;
BEGIN
 result := ($1 + $2) / 2;
 RETURN result;
END;
$$
LANGUAGE 'plpgsql';
*
Estrutura da Linguagem
Executando a função
Excluindo uma função
É necessário informar o nome e os parâmtros da função para excluí-la
SELECT nome_função (parâmetros);
DROP FUNCTION nome_função ([tipo_param [, tipo_param, ...]])
*
Estrutura da Linguagem
Comentários
Parâmetros
Definidos no cabeçalho da função
Nomeados de acordo com a posição na lista: $1, $2, ...
São constantes (não podem ter o valor alterado dentro a função
-- Comentário de linha
/*
 Comentário de várias
 linhas
*/
*
Variáveis
Devem ser declaradas na seção DECLARE
Os tipos de dados são os mesmos do PostgreSQL
Podem ser inicializadas na declaração
Podem ser declaradas como constantes
DECLARE
 media REAL;
 salario REAL := 15000;
 pi CONSTANT REAL := 3.14159;
*
Variáveis
Apelidos
Quando não é definido o nome de um parâmetro, podem ser utilizados para dar nomes aos parâmetros para serem utilizados no corpo da função
DECLARE
 nota1 ALIAS FOR $1;
 nota2 ALIAS FOR $2;
*
Variáveis
Atribuições
Se o valor ou expressão da direita for deferente do tipo de dados da variável, este é convertido para o mesmo tipo da variável
nota1 := 5.9;
nota2 := 6.0;
*
Variáveis
Tipos Especiais
Existem 3 tipos (pseudo-tipos) que se adaptam aos tipos originais de um atributo ou linha de uma tabela ou ainda ao conjunto de atributos de um conjunto resultado
%TYPE
%ROWTYPE
RECORD
*
Variáveis – Tipos Especiais
%TYPE
Declara uma variável com o mesmo tipo de outra variável ou mesmo tipo de um atributo de uma tabela
DECLARE
 end_residencial varchar(80);
 end_comercial end_residencial%TYPE;
 nome Clientes.nome%TYPE
*
Variáveis – Tipos Especiais
%ROWTYPE
Declara uma variável do tipo registro com os mesmos campos de uma tabela
Para acessar cada campo do registro:
DECLARE
 dados_cliente Clientes%ROWTYPE
dados_cliente.nome
dados_cliente.cidade
*
Variáveis – Tipos Especiais
RECORD
Declara uma variável do tipo registro, cuja estrutura é determinada em tempo de execução, adaptando-se aos dados que se deseja armazenar
DECLARE
 resultado RECORD;
*
Variáveis
Armazenando em variáveis o resultado de consultas
Deve-se utilizar o comando SELECT INTO
O resultado da consulta deve ser um único valor ou uma tupla
DECLARE
 data Clientes.DataCadastro%TYPE;
 dados_cliente Clientes%ROWTYPE;
 cidade Clientes.Cidade%TYPE;
 dados RECORD;
BEGIN
 SELECT INTO data DataCadastro FROM Cliente
 WHERE CodCliente = 5;
 SELECT INTO dados_cliente * FROM Cliente
 WHERE CodCliente = 10;
 cidade := dados_cliente.Cidade;
 SELECT INTO dados nome, datacompra 
 FROM Cliente c JOIN Compras co 
 ON c.CodCliente = co.CodCliente
 WHERE c.CodCliente = 1 AND co.CodCompra = 3
*
Mensagens
RAISE NOTICE
Imprime uma mensagem na tela
RAISE EXCEPTION
Imprime uma mensagem na tela e interrompe a execução do bloco de comandos
RAISE NOTICE ‘Estoque abaixo do limite’;
RAISE NOTICE ‘Média do aluno %’, media;
RAISE EXCEPTION ‘Valor Inválido!!!’;
*
Estruturas Condicionais
IF
IF (condição) THEN
 comandos;
ELSE
 comandos;
END IF;
IF (salario < 2000) THEN
 salario := salario * 1.15;
ELSE
 salario := salario * 1.1;
END IF;
*
Estruturas de Repetição
LOOP
Repete um bloco de comandos até que uma condição se torne verdadeira
LOOP
 ...
 EXIT WHEN (condição);
 ...
END LOOP;
LOOP
 ...
 IF (condição) THEN EXIT;
 END IF;
 ...
END LOOP;
*
Estruturas de Repetição
WHILE
FOR-IN
WHILE (condição) LOOP
 ...
END LOOP;
FOR variável IN v_inicial .. v_final LOOP
 ...
END LOOP;
*
Estruturas de Repetição
FOR-IN-SELECT
Executa o LOOP para cada tupla retornada no comando select
FOR registro IN SELECT ... LOOP
 ...
END LOOP;
*
Funções sem retorno
Uma função que não retorna valores deve:
Possuir tipo de retorno VOID
Não possui o comando RETURN
CREATE OR REPLACE FUNCTION media (A NUMERIC, B NUMERIC)
RETURNS VOID
AS
$$
DECLARE
 result NUMERIC;
BEGIN
 result := (A + B) / 2;
 raise notice ‘A média é %’, result;
END;
$$
LANGUAGE 'plpgsql';
*
Funções com parâmetros de saída
É possível definir, além dos parâmetros de entrada, conjuntos de parâmetros de saída para as funções
Estes parâmetros não precisam ser especificados quando a função for executada
Para indicar que um parâmetro é de saída, deve-se inserir o operador OUT antes da declaração do parâmetro
Também não é necessário utilizar o RETURNS tipo
CREATE FUNCTION função (p_ent, p_ent, OUT p_saida, OUT p_saida)
AS
$$
... 
*
Funções com parâmetros de saída
CREATE OR REPLACE FUNCTION soma(a INT, b INT, OUT res INT) AS $$
BEGIN
 res := a + b;
END;
$$
LANGUAGE PLPGSQL;
SELECT soma(10, 20);
*
Funções com retorno de vários registros
Uma função pode retornar vários registros como resultado
Para isso acrescenta-se a cláusula SETOF ao tipo de retorno da função
tipo: pode ser um tipo de dados ou o nome de uma tabela, caso a função retorne registros contendo todos os campos desta tabela
CREATE FUNCTION função (par1, par2) RETURNS SETOF tipo
AS
$$
... 
*
Funções com retorno de vários registros
Retorno com o comando RETURN QUERY
CREATE OR REPLACE FUNCTION cli_est(est char(2)) 
RETURNS SETOF clientes AS 
$$
BEGIN
 RETURN QUERY select * from clientes where estado = est;
END;
$$
LANGUAGE PLPGSQL;
SELECT * from cli_est(‘MG’);
*
Funções com retorno de vários registros
Retorno utilizando loops e o comando RETURN NEXT
CREATE OR REPLACE FUNCTION cli_est2(est char(2)) 
RETURNS SETOF clientes AS
$$
DECLARE 
 cli clientes%ROWTYPE
BEGIN
 FOR cli IN select * from clientes where estado = est
 LOOP
 RETURN NEXT cli;
 END LOOP;
END;
$$
LANGUAGE PLPGSQL;
SELECT * from cli_est(‘SP’);
*
Funções com retorno de vários registros
OBSERVAÇÃO 1:
Quando o tipo de retorno for do tipo RECORD, deve-se indicar o tipo de cada campo retornado durante a chamada da função:
CREATE OR REPLACE FUNCTION compras_cli() 
RETURNS SETOF RECORD AS
$$
BEGIN
 RETURN QUERY select c.nome, co.datacompra 
 from clientes c join compras co 
 on c.codcliente = co.codcliente;
END;
$$
LANGUAGE PLPGSQL;
SELECT * from compras_cli() as (nome varchar(100), datacompra date);
*
Funções com retorno de vários registros
OBSERVAÇÃO 2:
Quando o tipo de retorno for do tipo RECORD, pode-se combinar o resultado com parâmetros da saída, evitando a necessidade de se definir os parâmetros de retorno na chamada da função
*
Funções com parâmetros de saída
CREATE OR REPLACE FUNCTION cli_est(est
CHAR(2), OUT nome TEXT, 
 OUT codigo INTEGER, OUT data DATE)
RETURNS SETOF RECORD AS $$
 
DECLARE
 rec RECORD;
 
BEGIN
FOR rec IN select c.nome, p.codcompra, p.datacompra
	from clientes join compras p on (c.codcliente = p.codcliente)
	where c.estado like est
 LOOP
	nome := rec.nome;
	codigo := rec.codcompra;
	data := rec.datacompra;
	RETURN NEXT rec;
	END LOOP;
END;
$$
LANGUAGE 'PLPGSQL';