SQL procedural – parte 2

1. SQL procedural – parte 2

2. SQL Procedural • Cursores • Combinação com transações • Tratamento de exceções

3. 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

4. Plpgsql : cursor • Literatura do Oracle : • cursor implícito : dispensa tratamentos • Explícito: apenas pelo SELECT

5. 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) ;

6. Uso do cursor • Declarar ( declare ) • Abrir ( open ) • Navegar/ Atribuir os valores ( fetch ) • Fechar ( close ou fim de transação ) * Postgres: invocação de função com cursor deve ocorrer dentro de transação

7. 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 ;

8. Abrir cursor para SELECT • Cursor sem limite ( cursor refcursor ) • Consulta sem apóstrofo

9. 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';

10. 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 ;

11. 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';

12. 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 ;

13. FETCH cursor FETCH direction [ count ] { IN | FROM } cursor • direction ::= { FORWARD | BACKWARD | RELATIVE } • count ::= { numrows | ALL | NEXT | PRIOR } • Navegar no cursor

14. 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;

15. Chamada de função dentro de função BEGIN ... PERFORM suaFuncao([parametros]); ... END;

16. Tratamento de exceções • RAISE NOTICE ‘%’, var ; ( mensagem) • RAISE NOTICE ‘ texto qquer’; • RAISE EXCEPTION ‘%’, var ; ( falha )

17. 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

18. SETOF • Retorno de conjunto de qualquer tipo • Create function foo () returns SETOF integer ...

19. 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';

20. 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';