Arquivo da tag: select

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.

Dica: Como descobrir quem referencia uma coluna?

Fala PessoAll,

A dica de hoje é a respeito do seguinte…

Muitas vezes temos a necessidade de fazer algumas alterações nas nossas tabelas, nosso modelo, etc. E aí precisamos levantar quais tabelas recerenciam uma determinada tabela ou coluna, quais as filhas dessas colunas, quais as foreign key que fazem referência a elas e etc.

Aí, podemos usar o seguinte select:


select distinct c.table_name
from dba_constraints c,
dba_cons_columns cc
where c.constraint_type = 'R'
and cc.owner = c.owner
and cc.constraint_name = c.r_constraint_name
and cc.owner = &SCHEMA
and cc.table_name = &TABELA
and cc.column_name = &COLUNA

Claro que temos que mudar os parâmetros &SCHEMA, &TABELA e &COLUNA, para recuperar as tabelas que são filhas da tabela que você deseja.

Exemplo fica melhor não? Vamos lá:

Eu preciso descobrir quais as tabelas que fazem referência a alguma coluna da tabela SOURCE, para tal fazemos o seguinte select:


SQL> select DISTINCT C.TABLE_NAME
2 from dba_constraints c,
3 dba_cons_columns cc
4 where c.constraint_type = 'R'
5 and cc.owner = c.owner
6 and cc.constraint_name = c.r_constraint_name
7 and cc.owner = 'ORABUGIT'
8 and cc.table_name = 'SOURCES'
9 /
TABLE_NAME
------------------------------
PEOPLE_EQUIPS

Como podemos ver, obtemos como resposta a tabela PEOPLE_EQUIPS, portanto podemos ver que nesta tabela tem alguma coluna que referencia através de foreign key uma coluna da tabela SOURCE.

Espero que a dica seja útil!!

Abraços.

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