Arquivo da categoria: dinamico

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.

Oracle Execute Immediate SQL Dinâmico

Fala pessoAll,

Vamos nós novamente.
A pedido do amigo Marcos Castro dessa vez estaremos falando sobre SQL Dinâmicos, um recurso muito interessante no PL/SQL que em diversas vezes não é utilizado por nossos excelentíssimos desenvolvedores, fazendo com que nossos códigos sejam bem maiores, bem mais complexos e bem mais chatos de dar manutenção. Mas… o lado B dessa história é: Se você complicar muito no uso de SQL Dinâmico, não tenha dúvida de que seu código vai ficar muito complexo e todo mundo que for dar manutenção vai te xingar um bocado! Rsrs.

Vamos lá, ao exemplo prático e simples, que é o que interessa.

Suponha que você tem uma função para ser feita, que deve retornar o nome de uma determinada pessoa… essa pesquisa pode ser pelo seu RG, CPF ou Habilitação. Como podemos fazer isso?

Exemplo 1 (sem SQL Dinâmico):

create or replace function pesquisa_pessoa(pcodigo number, pfiltro varchar2) return varchar2 is
v_nome_pessoa pessoas.nome%type;
begin
if(pfiltro = ‘RG’) then
select nome
into v_nome_pessoa
from pessoas
where rg = pcodigo;
elsif(pfiltro = ‘CPF’) then
select nome
into v_nome_pessoa
from pessoas
where cpf = pcodigo;
elsif(pfiltro = ‘CNH’) then
select nome
into v_nome_pessoa
from pessoas
where cnh = pcodigo;
end if;

return v_nome_pessoa;
end;

Notemos que o SQL é o mesmo sempre (select nome into v_nome_pessoa from pessoas where … = pcodigo ), o que muda é apenas o nome do campo que será utilizado para filtrar a pessoa, portanto, podemos utilizar o recurso de SQL Dinâmico para otimizar este código, e como ele ficaria? Assim:

create or replace function pesquisa_pessoa(pcodigo number, pfiltro varchar2) return varchar2 is
v_nome_pessoa pessoas.nome%type;
v_where varchar2(50);
begin
if(pfiltro = ‘RG’) then
v_where := ‘rg’;
elsif(pfiltro = ‘CPF’) then
v_where := ‘cpf’;
elsif(pfiltro = ‘CNH’) then
v_where := ‘cnh’;
end if;

dbms_output.put_line(‘select nome into v_nome_pessoa from pessoas where ‘||v_where||’ = ‘||pcodigo||’;’);

execute immediate ‘select nome from pessoas where ‘||v_where||’ = ‘||pcodigo
into v_nome_pessoa;

return v_nome_pessoa;
end;

Bem mais simples, e não precisamos ficar repetindo o mesmo comando várias vezes.

Bom, isso é só um exemplo bem simples, mas… o Execute Immediate pode ser usado de diversas outras maneiras.

Achei um site bem interessante, que mostra as diversas usabilidades deste recurso com exemplos que podem ser utilizados para um melhor entendimento. O site é o DBA Support.

É isso aí, espero que este recurso seja útil e bem utilizado nas suas aplicações.

Atc.

Gerson Júnior

(gerson.vasconcelos@gmail.com)