SQL procedural – parte 2
SQL Procedural
• Cursores
• Combinação com transações
• Tratamento de exceções
Plpgsql : cursor
• Tabela temporária armazenada em memória
• Criada por resultado de COMMIT, ROLLBACK,
INSERT, SELECT , UPDATE, DELETE
• Contém registros afetados pelo comando q o criou
• Leitura de uma linha por vez
• Evitar sobrecarga de memória (N linhas de consulta)
• Postgres: Tipo refcursor
Plpgsql : cursor
• Literatura do Oracle :
– cursor implícito : dispensa tratamentos
– Explícito: apenas pelo SELECT
Declaração de cursor
DECLARE
• curs1 refcursor; -- curs1 é um cursor não iniciado
• curs2 CURSOR FOR SELECT * FROM tab1 ;
• curs3 CURSOR (key integer) IS SELECT * FROM
tab1 WHERE unique1 = key;
• curs1 refcursor (30) ;
Uso do cursor
1. Declarar ( declare )
2. Abrir ( open )
3. Navegar/ Atribuir os valores ( fetch )
4. Fechar ( close ou fim de transação )
* Postgres: invocação de função com cursor
deve ocorrer dentro de transação
Abrir cursor
• OPEN FOR SELECT
OPEN cursor FOR SELECT query ;
• OPEN FOR EXECUTE
OPEN cursor FOR EXECUTE query ;
• OPEN cursor com limite
OPEN cursor FOR EXECUTE query ;
Abrir cursor para SELECT
•
•
Cursor sem limite ( cursor refcursor )
Consulta sem apóstrofo
create function cursorLerAluno(varchar) returns
text as $$
declare
c refcursor;
nm aluno.nome%type;
cur aluno.curriculo%type;
begin
OPEN c FOR SELECT nome, curriculo from
aluno where num_matricula = $1;
fetch c into nm, cur;
close c;
return nm || ','|| cur;
end;
$$ language 'plpgsql';
Abrir cursor para EXECUTE
•
•
•
•
Cursor sem limite
Palavra reservada EXECUTE
EXECUTE pede string ( usar apóstrofo )
Situações de SQL dinâmico ( passagem dos
parâmetros como tabela , comando )
• Sintaxe
OPEN cursor FOR EXECUTE query ;
create or replace function cursorLerAluno(varchar)
returns text as $$
declare
c refcursor;
nm aluno.nome%type;
cur aluno.curriculo%type;
begin
OPEN c FOR EXECUTE 'SELECT nome, curriculo
from aluno where num_matricula ='|| $1;
fetch c into nm, cur;
close c;
return nm || ','|| cur;
end;
$$
language 'plpgsql';
Abrir cursor
• OPEN FOR SELECT
OPEN cursor FOR SELECT query ;
• OPEN FOR EXECUTE
OPEN cursor FOR EXECUTE query ;
• OPEN cursor com limite
OPEN cursor FOR EXECUTE query ;
FETCH cursor
FETCH direction [ count ] { IN | FROM } cursor
• direction ::= { FORWARD | BACKWARD |
RELATIVE }
• count ::= { numrows | ALL | NEXT | PRIOR }
• Navegar no cursor
create or replace function teste() returns boolean as
$$
declare
c refcursor;
reg aluno%rowtype;
i integer;
tot integer;
begin
OPEN c FOR SELECT * from aluno ;
SELECT count(*) INTO tot from aluno;
i:=1;
FOR i IN 1..tot LOOP
FETCH c INTO reg;
raise notice '%', reg.nome ;
raise notice '%', reg.num_matricula ;
END LOOP;
close c;
RETURN FOUND;
end;
$$ language 'plpgsql';
BEGIN;
SELECT teste();
COMMIT;
Chamada de função dentro de função
BEGIN
...
PERFORM suaFuncao([parametros]);
...
END;
Tratamento de exceções
• RAISE NOTICE ‘%’, var ; ( mensagem)
• RAISE NOTICE ‘ texto qquer’;
• RAISE EXCEPTION ‘%’, var ; ( falha )
Bloco de exceção
BEGIN comandos
EXCEPTION WHEN condição THEN
comandos de tratamento
WHEN condição THEN
comandos de tratamento
END;
• Custo é alto, usar sse necessário
SETOF
• Retorno de conjunto de qualquer tipo
• Create function foo () returns SETOF
integer ...
Exemplos da última aula
• Calcular a média de vl_total_mês pelo numero de
clientes ( tabela cliente)
create or replace function calculaMediaGanhoMes() returns float as $$
declare
tot integer;
media , vl_total real;
begin
select COUNT(*), SUM(vl_total_mes) INTO tot, vl_total from cliente;
media := vl_total/ tot;
RETURN media;
end;
$$ language 'plpgsql';
Exemplo
com
atributo
TYPE
create or replace function exibeDadosAluno ( varchar ) returns text as S$
declare
nm aluno.nome%type ;
mae dados_aluno.nome_pai%type;
pai dados_aluno.nome_mae%type;
mat ALIAS FOR $1; -- apelido para o parâmetro
begin
select aluno.nome, nome_pai, nome_mae into nm, pai, mae from aluno
natural join dados_aluno where aluno.num_matricula like mat ;
if FOUND Then
return 'Aluno: ' || nm || ',' || 'Pai: ' || pai || ',' || 'Mãe: ' || mae ;
else
return 'nao achou ninguem com essa matricula';
end if;
end;
$$ LANGUAGE 'plpgsql';