Funções ou procedures
Professor Esp. Diego André Sant’Ana
E-mail: [email protected]
Disciplina: Banco de Dados II
professordiegosantana.wordpress.com
Exemplo de funções

As funções que utilizamos que já existe e o AVG(),MAX(),MIN(),
NOW() e etc.
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;

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)
Função divisão

CREATE OR REPLACE FUNCTION get_divisao ( integer
,integer) RETURNS integer AS
$$
DECLARE
resultado INTEGER;
BEGIN
return $1/$2;
END;
$$ LANGUAGE 'plpgsql';

select get_divisao(2,3)







Função IMC

CREATE OR REPLACE FUNCTION get_imc( peso integer
,altura numeric) RETURNS numeric AS
$$
DECLARE
imc INTEGER;
BEGIN
return $1/($2*$2);
END;
$$ LANGUAGE 'plpgsql';

select get_imc (2,3)







Função para trazer os menores de
idade
create function menor_idade() returns
setof prova.aluno as
 'select * from prova.aluno where idade <
18'
 language 'SQL';


select menor_idade()
Função para trazer o aluno
passando ID_ALUNO
create function get_aluno (int)
 returns varchar as
 'select nome_aluno from prova.aluno
where id_aluno = $1'
 language 'SQL';


select get_aluno(1);
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';
select get_multiplicacao(9,7)
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
Função Média

Crie uma função que recebe 3 valores e
retorne a media aritmetica.
Função Média
Crie uma função que recebe 3 valores
N1 X PESO 2,
 N2 X PESO 2,
 N3 X PESO 3
 e retorne a media ponderada.

Função retornar o mês

Crie uma função que entre com o mês e
retorne o nome do mês;
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);
IF NO POSTGRESQL
IF $1=1 THEN
 RETURN 'JANEIRO';
 ELSIF $1=2 THEN
 RETURN 'FEVEREIRO';
 ELSE
 RETURN ‘NAO TEM’;
 END IF;

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';











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);
For na função
FOR registro IN SELECT * FROM
prova.aluno LOOP
/*programação*/
END LOOP;
For na função

















CREATE OR REPLACE FUNCTION get_nome_concatenano() RETURNS varchar AS $$
DECLARE
registro RECORD;
cont integer;
nome varchar;
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()
Função converte data para somente
mês e dia
create function get_numdate (date)
returns integer as E'select
to_char($1,\'mmdd\')::integer' language
'SQL';
select get_numdate('1987-11-30'::date)
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))
Crie a tabela
CREATE TABLE usuario (
 id integer NOT NULL,
 nm_login character varying,
 ds_senha character varying,
 fg_bloqueado boolean,
 nu_tentativa_login integer,
 CONSTRAINT pk_usuario PRIMARY
KEY (id)
 );

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';

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.
Para executar

SELECT get_id( 'joao' );
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 where nm_login = $1;

ELSE

UPDATE usuario SET nu_tentativa_login = tentativas where nm_login = $1;

END IF;

ELSE

UPDATE usuario SET nu_tentativa_login = 0 where nm_login = $1;

END IF;


END;

$$

LANGUAGE 'plpgsql';
Exemplo de chamada login
SELECT set_tentativa_login( 'hallan', 'senha_errada' );
SELECT set_tentativa_login( 'maria', 'senha_errada' );
SELECT set_tentativa_login( 'hallan', 'hallan2011' );
REFERÊNCIAS
http://www.linuxnewmedia.com.br/images/u
ploads/pdf_aberto/LM07_postgresql.pdf
http://postgresqlbr.blogspot.com.br/2012/08
/tratamento-de-parametros-de-funcoescom.html
http://pt.wikibooks.org/wiki/PostgreSQL_Pr
%C3%A1tico/Fun%C3%A7%C3%B5es_Defi
nidas_pelo_Usu%C3%A1rio_e_Triggers/SQ
L