CASE WHEN THEN ELSE END

CASE WHEN THEN ELSE END Professor Esp. Diego André Sant’Ana E-mail: diego.santana@ifms.edu.br Disciplina: Banco de Dados II professordiegosantana.wordpress.com

O que é case • É umaexpressãocondicionalexistente no SQL quefaz a função similar do if/else. • CASE • WHEN (CONDICAO) THEN (VALOR) • ELSE (VALOR) • END

Exemplo SELECT CASE WHEN A.SEXO='F' THEN 'FEMININO' WHEN A.SEXO='M' THEN 'MASCULINO' ELSE 'INDEFINIDO' END , CASE A.SEXO WHEN 'F' THEN 'FEMININO' WHEN 'M' THEN 'MASCULINO' ELSE 'INDEFINIDO' END FROM ( SELECT 'F' AS SEXO UNION ALL SELECT 'M' UNION ALL SELECT 'I' ) A

Exemplo select NOME_ALUNO, case when idade<18 then 'MENOR DE IDADE' else 'MAIOR DE IDADE' end AS STATUS_IDADE from prova.aluno

Exemplo SELECT CASE WHEN NOME_ALUNO ILIKE 'ANA%' THEN NOME_ALUNO||' IFMS' ELSE NOME_ALUNO END FROM PROVA.ALUNO

Exemplo SELECT IDADE, CASE IDADE%2 WHEN 0 THEN 'PAR' WHEN 1 THEN 'IMPAR' END FROM PROVA.ALUNO

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

Exemplo de funções • As funçõesqueutilizamosquejá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 • soma 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 • soma INTEGER; • BEGIN • return $1/($2*2); • END; • $$ LANGUAGE 'plpgsql'; • select get_imc (2,3)

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) • );

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

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://blog.hallanmedeiros.com/docencia/banco-de-dados-tutorial/postgresql-funcoes/

CASE WHEN THEN ELSE END