Funções ou procedures

1. Funções ou procedures Professor Esp. Diego André Sant’Ana E-mail: diego.santana@ifms.edu.br Disciplina: Banco de Dados II professordiegosantana.wordpress.com

2. Exemplo de funções • As funçõesqueutilizamosquejáexiste e o AVG(),MAX(),MIN(), NOW() e etc.

3. Estrutura básica • CREATE OR REPLACE FUNCTION nome_da_funcao ( parametros ) •     RETURNS tipo_retorno AS • $$ • DECLARE •       --declaracao de variaveis • BEGIN •       --conteudo da funcao • END; • $$ LANGUAGE linguagem;

4. Função soma CREATE OR REPLACE FUNCTION get_soma( integer ,integer) RETURNS integer AS $$ DECLARE  soma INTEGER; BEGIN return $1+$2; END; $$ LANGUAGE 'plpgsql'; • select get_soma(2,3)

5. Função divisão • CREATE OR REPLACE FUNCTION get_divisao ( integer ,integer) RETURNS integer AS • $$ • DECLARE • resultadoINTEGER; • BEGIN • return $1/$2; • END; • $$ LANGUAGE 'plpgsql'; • select get_divisao(2,3)

6. Função IMC • CREATE OR REPLACE FUNCTION get_imc( peso integer ,altura numeric) RETURNS numeric AS • $$ • DECLARE • imcINTEGER; • BEGIN • return $1/($2*$2); • END; • $$ LANGUAGE 'plpgsql'; • select get_imc (2,3)

7. Função para trazer os menores de idade • createfunctionmenor_idade() returnssetofprova.aluno as • 'select * fromprova.alunowhere idade < 18' • language 'SQL'; • selectmenor_idade()

8. Função para trazer o aluno passando ID_ALUNO • createfunctionget_aluno (int) • returnsvarchar as • 'selectnome_alunofromprova.alunowhereid_aluno = $1' • language 'SQL'; • selectget_aluno(1);

9. Função de multiplicação • CREATE OR REPLACE FUNCTION get_multiplicacao (p1 integer ,p2 integer) RETURNS integer AS • $$ • DECLARE •     resultado INTEGER; • BEGIN • return p1*p2; • END; • $$ LANGUAGE 'plpgsql'; • selectget_multiplicacao(9,7)

10. Teste com a multiplicação • select p1,p2,get_multiplicacao(p1,p2) from • ( • (select generate_series as p1 from generate_series(1,10,1)) as a • cross join • (select generate_series as p2 from generate_series(1,10,1)) as b • )as a

11. Função Média • Crieumafunçãoquerecebe 3 valores e retorne a media aritmetica.

12. Função Média • Crieumafunçãoquerecebe 3 valores N1 X PESO 2, • N2 X PESO 2, • N3 X PESO 3 • e retorne a media ponderada.

13. Função retornar o mês • Crieumafunçãoque entre com o mês e retorne o nome do mês;

14. Função retornar o mês • CREATE OR REPLACE FUNCTION GET_MES_SQL(INTEGER) RETURNS VARCHAR AS • E'select • case $1 • when 1 then \'JANEIRO\' when 2 then \'FEVEREIRO\' when 3 then \'MARÇO\' when 4 then\'ABRIL\' when 5 then\'MAIO\' • when 6 then \'JUNHO\' when 7 then \'JULHO\' when 8 then \'AGOSTO\' • when 9 then \'SETEMBRO\' when 10 then\'OUTUBRO\' • when 11 then\'NOVEMBRO\' when 12 then \'DEZEMBRO\' END AS MES' • LANGUAGE 'sql'; • SELECT GET_MES_SQL(2);

15. IF NO POSTGRESQL • IF $1=1 THEN • RETURN 'JANEIRO'; • ELSIF $1=2 THEN • RETURN 'FEVEREIRO'; • ELSE • RETURN ‘NAO TEM’; • END IF;

16. Função retornar o mês • CREATE OR REPLACE FUNCTION GET_MES(INTEGER) RETURNS VARCHAR AS • $$ • DECLARE • MES INTEGER:=$1; • BEGIN • IF MES=1 THEN • RETURN 'JANEIRO'; • ELSIF MES=2 THEN • RETURN 'FEVEREIRO'; • END IF; • END; • $$ • LANGUAGE 'plpgsql';

17. COMO IMPRIMIR MENSAGEM • RAISE NOTICE 'existe no banco ID = % ', id; create function teste(id integer) returns void as $$ BEGIN RAISE NOTICE 'existe no banco ID = % ', id; END; $$ LANGUAGE 'plpgsql'; SELECT TESTE(1);

18. For na função FOR registro IN SELECT * FROM prova.alunoLOOP /*programação*/ END LOOP;

19. For na função • CREATE OR REPLACE FUNCTION get_nome_concatenano() RETURNS varchar AS $$ • DECLARE • registro RECORD; • cont integer; • nomevarchar; • BEGIN • RAISE NOTICE 'Inicio'; • nome := 'inicial:'; • FOR registro IN SELECT * FROM prova.aluno limit 10 LOOP • RAISE NOTICE 'to no loop ID = % ', registro.id_aluno; • nome :=nome || registro.nome_aluno; • RAISE NOTICE 'nome = % ', registro.nome_aluno; • END LOOP; • return 1; • END; • $$ LANGUAGE plpgsql; • select get_nome_concatenano()

20. Função converte data para somente mês e dia create function get_numdate (date) returns integer as E'selectto_char($1,\'mmdd\')::integer' language 'SQL'; select get_numdate('1987-11-30'::date)

21. Função para trazer o signo da pessoa create function get_signo (int) returns varchar as E'select case when $1 <=0120 then \'capricornio\' when $1 >=0121 and $1 <=0219 then \'aquario\' when $1 >=0220 and $1 <=0320 then \'peixes\' when $1 >=0321 and $1 <=0420 then \'aries\' when $1 >=0421 and $1 <=0520 then \'touro\' when $1 >=0521 and $1 <=0620 then \'gemeos\' when $1 >=0621 and $1 <=0722 then \'cancer\' when $1 >=0723 and $1 <=0822 then \'leao\' when $1 >=0823 and $1 <=0922 then \'virgem\' when $1 >=0923 and $1 <=1022 then \'libra\' when $1 >=1023 and $1 <=1122 then \'escorpiao\' when $1 >=1123 and $1 <=1222 then \'sagitario\' when $1 >=1223 then \'capricornio\' end as signo' language 'SQL‘; select get_signo (get_numdate('1987-11-30'::date))

22. Crie a tabela • CREATE TABLE usuario ( • id integer NOT NULL, • nm_logincharactervarying, • ds_senhacharactervarying, • fg_bloqueadoboolean, • nu_tentativa_logininteger, • CONSTRAINT pk_usuario PRIMARY KEY (id) • );

23. Criar a função • CREATE OR REPLACE FUNCTION get_id ( varchar ) RETURNS integer AS • $$ • DECLARE • variavel_id INTEGER; • BEGIN •         SELECT INTO variavel_id id FROM usuario WHERE nm_login = $1; •         RETURN variavel_id; • END; • $$ LANGUAGE 'plpgsql';

24. Sobre a função • A função acima (chamada get_id) recebe como parâmetro um VARCHAR e retorna um INTEGER, e funciona da seguinte forma: • - declara uma variável chamada variavel_id, do tipo INTEGER; • - faz um select na tabelas de usuário onde a coluna nm_login é igual ao varchar recebido como parâmetro, e colocar o valor de id encontrado dentro da variável variavel_id (através do comandoSELECT INTO); • - retorna a variavel_id. • O $1 representa o primeiro parâmetro recebido, o $2 o segundo, e assim por diante.

25. Para executar • SELECT get_id( 'joao' );

26. Exemplo de função • CREATE OR REPLACE FUNCTION set_tentativa_login ( VARCHAR, VARCHAR ) •     RETURNS VOID AS • $$ • DECLARE •         registro RECORD; •         tentativas INTEGER; • BEGIN •         SELECT INTO registro id, fg_bloqueado, nu_tentativa_login FROM usuario WHERE nm_login = $1 AND ds_senha = $2; •         IF registro IS NULL •         THEN •             SELECT INTO tentativas nu_tentativa_login FROM usuario WHERE nm_login = $1; •             tentativas := tentativas + 1; •             IF tentativas > 2 •             THEN •                 UPDATE usuario SET nu_tentativa_login = tentativas, fg_bloqueado = TRUE wherenm_login = $1; •             ELSE •                 UPDATE usuario SET nu_tentativa_login = tentativas wherenm_login = $1; •             END IF; •         ELSE •             UPDATE usuario SET nu_tentativa_login = 0 wherenm_login = $1; •         END IF; • END; • $$ • LANGUAGE 'plpgsql';

27. Exemplo de chamada login SELECT set_tentativa_login( 'hallan', 'senha_errada' ); SELECT set_tentativa_login( 'maria', 'senha_errada' ); SELECT set_tentativa_login( 'hallan', 'hallan2011' );

28. REFERÊNCIAS http://www.linuxnewmedia.com.br/images/uploads/pdf_aberto/LM07_postgresql.pdf http://postgresqlbr.blogspot.com.br/2012/08/tratamento-de-parametros-de-funcoes-com.html http://pt.wikibooks.org/wiki/PostgreSQL_Pr%C3%A1tico/Fun%C3%A7%C3%B5es_Definidas_pelo_Usu%C3%A1rio_e_Triggers/SQL