Arquivo da categoria: functions

Alternativa InitCap. Upper e Lower no lugar certo.

Fala PessoALL,

Hoje falaremos de manipulação/formatação de strings, com o uso da boa e velha função InitCap do Oracle.
Acho que muitos de nós já nos deparamos com este tipo de problema, temos no nosso banco, uma determinada descrição gravada da forma que o usuário achar mais bonito! Ou seja, sem ter um padrão para gravação. Para gravar a frase: “Este blog é uma beleza”, por exemplo, podemos ter de várias formas:

– Este blog é uma beleza
– ESTE BLOG É UMA BELEZA
– EsTe BlOg É uMa BeLeZa (vai que o usuário é internetês)

Entre muitas outras formas. Porém na hora de fazermos um relatório, gostaríamos muitas vezes que nosso relatório mantenha um padrão de exibição da descrição, fica no mínimo estranho que tenhamos uma frase maíuscula, outra minúscula, outra mesclada, etc… para isso o Oracle nos dá uma função chamada InitCap.

Esta função nos dá a possibilidade de padronizar a nossa descrição, ela coloca maiúscula todas as primeiras letras das palavras da frase, por exemplo:

– Este Blog É Uma Beleza

Só que este ainda não é o padrão que utilizamos para escrever, apenas as primeiras letras da primeira palavra da frase é que são maiúsculas. Todas as demais são minúsculas, ficando a frase assim:

-Este blog é uma beleza

Bem mais bonito não??

Pensando nesse problema, vamos deixar de blábláblá e vamos a prática!

Eu desenvolvi uma função, a pedido do meu amigo Marcos Castro, que faz exatamente isso que queremos, ela só coloca maíuscula a primeira letra da primeira palavra de uma nova frase, ou seja, o que vem depois do ponto, como costumamos fazer no nosso dia-a-dia, como mostrado no exemplo acima.

Segue a função:


create or replace function initcapmc(p_str varchar2) return varchar2 is
v_carac_anterior varchar2(1);
v_carac_atual varchar2(1);
v_retorno varchar2(32767);
begin
--Atribui o primeiro caractere como maiúsculo.
v_retorno := upper(substr(p_str,1,1));

--Varre a string a partir da segunda posição
for i in 2..length(p_str) loop
--Recupera o caractere que está sendo analisado
v_carac_atual := substr(p_str,i,1);

--Se o caractere analisado anteriormente foi um ponto, vai colocar maiúsculo
if(v_carac_anterior = '.') then
v_retorno := v_retorno || upper(v_carac_atual);
else
v_retorno := v_retorno || lower(v_carac_atual);
end if;

--Atribui o caractere já processado como sendo o anterior, para a proxima execucao. Ignora espaco.
if(trim(v_carac_atual) is not null) then
v_carac_anterior := v_carac_atual;
end if;
end loop;

return v_retorno;

end;

Espero que gostem… em caso de bugs, favor informar.

Atc.
Gerson Júnior
(gerson.vasconcelos@gmail.com)

Oracle – Sinônimos públicos (public synonyms). Quando devo usar?

Fala PessoALL,

Depois de muito tempo sem escrever, vamos nós de novo!

Tenho me deparado sempre com dúvidas de desenvolvedores quanto ao uso de sinônimos públicos. Devemos usar? Não devemos? Cria pra todos os objetos? Não cria? Vamos tentar de uma vez por todas desvendar esse mistério de public synonym no Oracle.

Bom, sinônimos, como bem sabemos são palavras que tem o mesmo significado… isso no português! No Oracle, sinônimo é um pouco diferente, porém sendo um pouco igual! Confuso não? Rs. Vamos simplificar!

No Oracle o sinônimo público (public synonym) é um objeto de banco, cujo dono é PUBLIC (ou seja, todo mundo) que “aponta” para um outro objeto de um determinado schema. Como se fosse uma espécie de link. Por exemplo:

Imagine que você tem uma tabela no schema DONOSIS chamada MINHATABELA. Caso algum usuário que não seja DONOSIS deseje executar um select nesta tabela, você faz o seguinte comando: select * from donosis.minhatabela, mas e se eu não quiser colocar o dono do objeto na frente? Seja para que meus usuários não saibam quem é o dono dos objetos (questões de segurança), seja para que eu simplifique a codificação? Aí eu uso o sinônimo público! Eu crio um sinônimo púbico chamado MINHATABELA que aponta para o objeto de banco: DONOSIS.MINHATABELA. O código para criação é:

create or replace public synonym MINHATABELA for DONOSIS.MINHATABELA;

Após a criação deste sinônimo público, qualquer usuário do banco que executar o select select * from MINHATABELA; conseguirá de forma transparente acessar a tabela MINHATABELA do schema DONOSIS sem nem saber que ela se encontra neste schema! Simples não?

Isso pode ser utilizado para qualquer objeto de banco, como: Views, Procedures, Functions, Packages, Tables, etc.

Claro que para que o acesso ao objeto seja concluído, o usuário que está acessando tem que possuir privilégio no objeto de destino. Ou seja, mesmo com sinônimo público, os privilégios que foram concedidos no objeto de destino continuam funcionando normalmente.

Vantagens?
Simplicidade de codificação (não precisa colocar o nome do dono do objeto na frente).
Transparência de propriedade (não se sabe quem é o dono do objeto).
Simples modificação de objetos (você pode mudar o dono dos objetos, sem impacto algum).

Quando não usar?
Quando o objeto só será utilizado pelo próprio dono. Por exemplo: Se nossa tabela MINHATABELA fosse utilizada apenas por objetos do schema DONOSIS, não tinhamos a menor necessidade de ter um sinônimo, o objeto sendo do próprio schema, não precisamos colocar o schema na frente!

Erros mais comuns:
ORA-01775 loop chain of synonyms – Este erro ocorre geralmente quando ocorre algum problema com o objeto destino que o sinônimo aponta, por exemplo: Se for uma procedure que está inválida; Se for uma tabela que não existe; algo do tipo!

É isso pessoal, espero que tenha ficado claro como funciona e para que serve os sinônimos públicos (public synonym). Por enquanto é só!

Qualquer coisa, basta entrar em contato.

Atc.
Gerson Júnior
gerson.vasconcelos@gmail.com

Função de grupo para multiplicar, SUM -> MULT

Fala pessoal,

Recebi um email de um leitor solicitando uma solução para que fosse desenvolvida uma função semelhante ao SUM, já nativo do banco de dados Oracle, só que fizesse multiplicação e não soma, como o SUM faz atualmente!

Por exemplo:

Tabela: FATORIAL
Campo: VALOR

Valor
5
6
3
2
3

Portanto, se usarmos o SUM, teriamos um resultado assim: 5 + 6 + 3 + 2 + 3 = 19
O pretendido é: 5 * 6 * 3 * 2 * 3 = 540

A sugestão que dei foi: Criar uma função própria que faça a multiplicação dos valores. A implementação/testes ficou assim:


SQL> --Cria tabela
SQL> create table fatorial(campo1 number, valor number);
Tabela criada.
SQL> --Insere valores
SQL> insert into fatorial values (1,2);
1 linha criada.
SQL> insert into fatorial values (1,3);
1 linha criada.
SQL> insert into fatorial values (2,4);
1 linha criada.
SQL> insert into fatorial values (2,4);
1 linha criada.
SQL>

Em seguida criamos a seguinte function:


SQL> create or replace function mult_vals(p_filtro number) return number is
2 v_retorno number;
3 begin
4 v_retorno := 0;
5 for i in (select valor from fatorial where campo1 = p_filtro)
6 loop
7 if (v_retorno = 0) then
8 v_retorno := 1;
9 end if;
10 v_retorno := v_retorno*i.valor;
11 end loop;
12 return v_retorno;
13 end;
14 /
FunþÒo criada.
SQL>

E depois é só testar:


SQL> select campo1, mult_vals(campo1)
2 from fatorial
3 group by campo1;
CAMPO1 MULT_VALS(CAMPO1)
---------- -----------------
1 6
2 16
SQL>

Funcionou como uma luva não?

É isso, o que não tem nativo a gente faz!
Neste caso fiz algo bem específico, só pra atender a necessidade que nosso leitor precisava, mas podemos pensar em algo mais genérico, se necessário! Usando SQL Dinâmico, passando nome do campo a ser multiplicado e nome da tabela! Fica a sugestão!

Agradecendo ao nosso leitor Rodrigo Vieira pela solicitação!!

Grande abraço, espero que gostem.

Potência / Expoentes em SQL e PL/SQL – Oracle

Fala Pessoal,

Hoje vai mais uma dica de SQL e PL/SQL.

Até o dia de hoje, eu nunca tinha necessitado usar expoente no SQL / PL/SQL, nunca tinha pego nenhum problema em desenvolvimento que precisasse ser usado potência, expoente e tal.

Porém, hoje uma amiga desenvolvedora, Leilah, precisou dessa funcionalidade e aí discutimos um pouco sobre o assunto. Descobri portanto, que no Oracle, não precisamos fazer muita conta e nem usar sintaxes esquisitas para usar potência e elevar um número a uma determinada potência, basta usarmos a função POWER, isso mesmo, funciona assim:

Exemplo SQL:

SQL> select power(2,3) from dual;
POWER(2,3)
----------
8

Neste exemplo, temos 2 elevado a 3, que nos dá 8 como resultado…

Exemplo PL/SQL:

SQL> declare
2 v_num number;
3 begin
4 v_num := power(2,3);
5 dbms_output.put_line('O numero 2 elevado a 3 é: '||to_char(v_num));
6 end;
7 /
O numero 2 elevado a 3 é: 8
Procedimento PL/SQL concluÝdo com sucesso.

Pronto pessoal, fica aí a dica de utilização de potência em PL/SQL.

Espero que gostem.

Grande abraço.

Atc.
Gerson Júnior
gerson.vasconcelos@gmail.com

Validando Digito Verificador Inscrição Estadual Bahia (BA)

Fala PessoAll,

Bom, como toda e boa pessoa normal, de vez em quando bate a saudade das épocas de desenvolvimento, de quebrar cabeça para fazer alguns códigos “ninja” pra resolver alguns problemas do nosso Dia a Dia (Oracle, rsrs).

Otem me deparei com a demanda de um amigo, precisando fazer uma validação do dígito da Inscrição estadual da Bahia, em PL/SQL. Vamos lá!

Primeiro passo: Pesquisar como se faz a validação do dígito verificador da inscrição estadual da BAHIA!! BAHIA!! Ninguém melhor que a Sefaz/BA pra me dizer isso. Então, fui no site da SEFAZ/BA e achei como fazer a validação, depois foi só cair no PL/SQL de cabeça e colocar pra funcionar. A página que diz como deve ser feita a validação é: http://www.sefaz.ba.gov.br/contribuinte/informacoes_fiscais/doc_fiscal/calculodv.htm.

Segue abaixo a function utilizada para fazer esta validação. Essa function retorna ‘S’ caso esteja OK e ‘N’ caso não seja validado o dígito verificador.


create or replace function valida_dig_inscest_BA(pInscEstadual varchar2) return varchar2 is
v_IE varchar2(10);

idig1 number;
idig2 number;

iRes1 number;
iRes2 number;

cBase1 number;
cBase2 number;
begin
v_IE := rtrim(replace(replace(replace(pInscEstadual, '.', ''), '-', ''), '/', ''));

if length(v_IE) <> 8 then
return('N');
end if;

iRes1 := 0;
iRes2 := 0;

cBase1 := substr(v_IE, 1, 6);
iDig1 := substr(v_IE, 7, 1);
iDig2 := substr(v_IE, 8, 1);

--Se o primeiro dígito for um dos testados, modulo 10, senao, modulo 11
if(substr(cBase1, 1, 1) in (0, 1, 2, 3, 4, 5, 8)) then
--Faz um loop de 7 até 2...
for i in reverse 2 .. 7 loop
--Verifica se o numero encontrado é válido.
if instr('0123456789', SubStr(cBase1, (7-i)+1, 1)) > 0 Then
--Sendo válido, já soma com a multiplicação pelo indice.
ires2 := ires2 + (SubStr(cBase1, (7-i)+1, 1) * i);
else
return('N');
end if;
end loop;

--Recupera o resto do calculo realizado por 10
ires2 := mod(ires2, 10);

--Se for 0, o digito é zero.
if(ires2 = 0) then
ires2 := 0;
--Se não for zero, subtrai o valor de 10
else
ires2 := 10 - ires2;
end if;

--O numero para o calculo do primeiro digito, é os 6 primeiros + o digito 2
cBase2 := cBase1||ires2;

--Faz um loop de 8 até 2...
for i in reverse 2 .. 8 loop
--Verifica se o numero encontrado é válido.
if instr('0123456789', SubStr(cBase2, (8-i)+1, 1)) > 0 Then
--Sendo válido, já soma com a multiplicação pelo indice.
ires1 := ires1 + (SubStr(cBase2, (8-i)+1, 1) * i);
else
return('N');
end if;
end loop;

--Recupera o resto do calculo realizado por 10
ires1 := mod(ires1, 10);

--Se for 0, o digito é zero.
if(ires1 = 0) then
ires1 := 0;
--Se não for zero, subtrai o valor de 10
else
ires1 := 10 - ires1;
end if;

--Se resultado 1 e 2 iguais a digitos 1 e 2, é válido
if(ires1 = idig1 and ires2 = idig2) then
return('S');
else
return('N');
end if;
--Se começa com 6, 7 ou 9
else
--Faz um loop de 7 até 2...
for i in reverse 2 .. 7 loop
--Verifica se o numero encontrado é válido.
if instr('0123456789', SubStr(cBase1, (7-i)+1, 1)) > 0 Then
--Sendo válido, já soma com a multiplicação pelo indice.
ires2 := ires2 + (SubStr(cBase1, (7-i)+1, 1) * i);
else
return('N');
end if;
end loop;

--Recupera o resto do calculo realizado por 11
ires2 := mod(ires2, 11);

--Se for 0, o digito é zero.
if(ires2 = 0) then
ires2 := 0;
--Se não for zero, subtrai o valor de 11
else
ires2 := 11 - ires2;
end if;

--O numero para o calculo do primeiro digito, é os 6 primeiros + o digito 2
cBase2 := cBase1||ires2;

--Faz um loop de 8 até 2...
for i in reverse 2 .. 8 loop
--Verifica se o numero encontrado é válido.
if instr('0123456789', SubStr(cBase2, (8-i)+1, 1)) > 0 Then
--Sendo válido, já soma com a multiplicação pelo indice.
ires1 := ires1 + (SubStr(cBase2, (8-i)+1, 1) * i);
else
return('N');
end if;
end loop;

--Recupera o resto do calculo realizado por 10
ires1 := mod(ires1, 11);

--Se for 0, o digito é zero.
if(ires1 = 0) then
ires1 := 0;
--Se não for zero, subtrai o valor de 10
else
ires1 := 11 - ires1;
end if;

if(ires1 = idig1 and ires2 = idig2) then
return('S');
else
return('N');
end if;
end if;
end;

É isso aí gente, espero que seja útil.

Atc.
Gerson Júnior
gerson.vasconcelos@gmail.com

Função DECODE no Oracle SQL

Fala PessoAll,

Hoje vamos falar de Construção de SQL.

As vezes precisamos fazer umas coisas mais avançadas num comando SQL e aí precisamos utilizar alguns recursos mais interessantes que o Oracle nos proporciona.

Quem nunca pensou: “Ai como seria bom se tivesse um IF no select”. Tem sim!

O primeiro recurso que podemos utilizar é o DECODE, essa função é bem interessante e quebra um bocado de galhos, vamos ver um exemplo pra “clarear” as idéias.

Suponha que você tem uma tabela que contém o sexo das pessoas, mas na tabela só armazena F ou M e você quer que seja mostrado “Masculino” e “Feminino” no retorno do seu select, como fazemos isso no select? Com DECODE!

Assim:


select nome,
dt_nascimento,
decode(sexo,
'M', 'Masculino',
'F', 'Feminino',
'Indefinido') sexo
from pessoas;

Como funciona isso… o DECODE testa o valor que você passa no primeiro parâmetro e vai comparando com o que você especifica e retorna o que você deseja. Complexo não? NÃO!! Vamos explicar o nosso exemplo:

No exemplo acima é passado o campo sexo, em seguida passamos o primeiro valor de teste e o que vai retornar caso encontre este valor, que neste caso é: Se encontrar 'M' retorne 'Masculino', sempre aos pares. Ou seja, valor encontrado e logo depois o valor que vai retornar. Como podemos ver, depois vem mais um par… 'F' e 'Feminino' o que nos diz que se encontrar um 'F', traga 'Feminino' na coluna.

Tá, mas e esse último valor que tem 'Indefinido'? Ele não tem par!! Vai dar erro? Não, esse é uma espécie de ELSE do DECODE… se ele não encontrar nenhuma das alternativas passadas para retornar um valor especificado, ele retorna esse último valor. No nosso exemplo, podemos ver que caso não encontre 'M' nem 'F' no campo, ele irá retornar o valor 'Indefinido' para a coluna… agora ficou claro!

O DECODE não tem um limite mínimo nem máximo de “pares de teste e retorno”, você pode ir especificando os valores e os retornos de acordo com sua necessidade.

Bom, esse é um recurso do Oracle que pode ser bem útil para pessoas que fazem SQL, que elaboram relatórios, que vivem fazendo query entre outros casos.

Como podemos verificar, para usar o DECODE nós temos que saber os valores que estão na coluna para que possamos informar qual o retorno caso determinado valor seja encontrado, mas… e se eu quiser utilizar uma faixa de valores por exemplo? Tipo… se coluna nota estiver entre 0 e 3 retorno 'Péssimo', entre 4 e 5 retorne 'Ruim', entre 6 e 8 retorne 'Bom' e acima de 8 retorne 'Ótimo'? Não dá pra fazer isso com DECODE né? Mas, tenha calma… no próximo post falaremos sobre uma outra função do Oracle que é uma espécie de DECODE avançado, que permite esse tipo de teste e muito mais.

Espero que gostem e que seja útil. Até a próxima.

Não exitem em comentar e/ou mandar e-mails.

Atc.
Gerson Júnior
gerson.vasconcelos@gmail.com

Oralce PL/SQL – Funções (Funtions) e Procedures

Fala PessoAll,

Bom… hoje em mais um dia a dia de trabalho, rolou mais uma dúvida sobre o uso de procedures e funções no PL/SQL. Aí fiz a seguinte pergunta: Qual a diferença entre Procedure e Function no PL/SQL? Aí surgiram aquelas velhas respostas decoradas da faculdade: “Procedure não retorna valor!”, “Função retorna valor e procedure não retorna.”, entre outras. Vamos lá então:

No Oracle a diferença básica entre uma e outra é que a Function OBRIGATÓRIAMENTE tem que retornar um valor, você pode até criar a função e compilar ela sem um Return, mas na hora que você rodar esta função você vai obter um erro oracle dizendo que: “Function Retorned withou value”, ou seja, função não retorna nenhum valor (algo parecido), e não funciona. Porque a diferença básica? Porque procedures no Oracle também podem retornar valores, isso mesmo, basta você criar um parametro do tipo OUT, assim:

create or replace procedure pr_teste(p_t number, p_ret out varchar2) is
begin
if(p_t = 1) then
p_ret := 'É 1';
else
p_ret := 'Não é 1';
end if;
end;

Neste exemplo de código, note que estamos atribuindo ao parâmetro p_ret o valor que será retornado para o local que chamou essa procedure (veremos exemplo desta chamada nos exemplos a seguir).

Ah… então, se as duas retorna valor, porque eu tenho procedure e function? E porque eu uso uma e não outra ou a outra e não uma?

Vamos aos pontos de cada uma delas:

Nas Funtions:
– Pode ser usada em comandos select, insert etc para ser retornada como uma coluna da query:
select codigo, nome, fn_calculaIdade(codigo) Idade from pessoas; Neste exemplo, fn_calculaIdade recebe o codigo da pessoa como parametro e retorna a idade dela, isso será exibido como uma coluna na query com nome Idade

– Pode ser atribuida diretamente a uma variável:

declare
v_idade number;
begin
.
.
v_idade := fn_calculaIdade(codigo);
.
.
end;

Neste exemplo, estamos no meio de um bloco PL/SQL e atribuímos diretamente a uma variável o valor que retornará da função.

E mais algumas coisas sobre função.

Nas Procedures:
– O grande “plus” das procedures é: Podem retornar mais de um resultado! Ah… isso mesmo, essa é a grande vantagem das procedures, existe a possibilidade de ser retornado mais de um retorno (retornar retorno, coisa feia não? você entenderá jájá), coisa que é completamente impossível usando função. Funções só retornam um único resultado.

Complicou? Vamos ao exemplo:

Primeiro vamos criar uma procedure com 3 (isso mesmo 3, três, III, rsrs) parâmetros de retorno:


create or replace procedure pr_buscaEndereco(p_codigo_pessoa number, p_rua out varchar2, p_bairro out varchar2, p_cidade out varchar2) is
begin
begin
select rua,
estado,
cidade
into p_rua,
p_bairro,
p_cidade
from pessoas
where codigo = p_codigo_pessoa;
exception
when no_data_found then
p_rua := 'Rua não encontrada.';
p_bairro := 'Bairro não encontrada.';
p_cidade := 'Cidade não encontrada.';
end;
end;

Note que os parâmetros que serão usados para retorno, tem uma cláusula OUT na frente do tipo, isso que diferencia ele de um parâmetro comum, IN.

Ah… bom, mas e como é que eu vou usar isso? Assim:

declare
v_pessoa_rua varchar2(100);
v_pessoa_bairro varchar2(100);
v_pessoa_cidade varchar2(100);
begin
pr_teste(212, v_pessoa_rua, v_pessoa_bairro, v_pessoa_cidade);
dbms_output.put_line('Endereço da pessoa 212: Rua: '||v_pessoa_rua||' Bairro: '||v_pessoa_bairro||' Cidade: '||v_pessoa_cidade);
end;

Neste exemplo, temos três variáveis criadas, estas três variáveis são passadas na chamada da procedure e como estes parâmetros no qual elas são passadas são OUT, irão retornar algum valor, que será o valor da variável após execução da procedure.

É isso aí, portanto, caso você precise de uma função que retorne mais de um valor, não tente criar uma função genérica cheia de IF’s e chamar ela mais de uma vez fazendo o mesmo select em colunas diferentes, use uma procedure com mais de um parâmetro OUT que isso provavelmente resolverá seus problemas.

Momento DBA: Lembrem que quanto menos funções são chamadas, mais agradável para o banco. A cada função que chamamos o banco vai ter que ver, executar, retornar, isso usa memória, processador e etc. Se no lugar de 10 chamadas para uma função voce usar uma procedure com 10 parametros OUT, é bem menos “doloroso” para o banco. Outra coisa que é muito importante e que ocorre muito é usar função para retornar como uma determinada coluna de um comando select… lembre-se que se está função está na clausula select ela será chamada exatamente a quantidade de vezes de quantos registros existirem. No exemplo da função fn_calculaIdade que citei lá em cima, se tivermos 1.000.000 de registros, esta função será executada 1.000.000 de vezes, se é uma função mais robusta e complexa, imagine pra onde vai a performance da sua query. Além da perda de performance na query, de quebra você ainda perde uma credibilidade com o DBA! Que quando você degradar o banco todo, ele vai ficar bravo com você!!

Grande abraço a todos.

Fiquem a vontade para comentários e/ou e-mails.

Atc.
Gerson Júnior
gerson.vasconcelos@gmail.com