Ajuda para criar procedure

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
kkfranca
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 1
Registrado em: Qui, 14 Jun 2007 9:01 am
Localização: Belo Horizonte - MG
Karina França

Estou criando uma procedure que recebe como parâmetro um campo varchar2 com o conteúdo '7;14;152;186'.
Cada item desta string corresponde a um registro na tabela AGENTE.
Como posso fazer um select usando este parâmetro na cláusula IN, sendo que o campo COD_AGENTE é do tipo NUMBER.

Ex:

Selecionar tudo

declare
v_agentes varchar2(100);
begin
v_agentes := '7;14;152;186';
for r_teste in (select cod_agente, dsc_razao_social
                from novo_comissao.agente a
                where a.cod_agente in replace(v_agentes,';',',')) loop
dbms_output.put_line(r_teste.cod_agente);
end loop;
end;
Agradeço desde já a colaboração.
Karina
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Sex, 30 Mar 2007 7:26 pm
Localização: Londrina - PR
Rafael O. Genaro

Karina,

Não existe um jeito extremamente simples de realizar isso, mas ai vai um exemplo de como fazer:

A idéia é criar um type em sua base, como uma table do tipo de dados que você pretende utilizar no operador in.

Em seguida, você precisa criar uma função que irá processar a string delimitada (no meu exemplo, eu utilizei virgulas para separar os valores), e retornar uma variável do tipo criado.

Por fim, basta utilizar o comando cast, convertendo o type criado em uma tabela, que poderá ser utilizada normamente em comandos SQL.

Segue abaixo um exemplo:

Selecionar tudo

-- Criar um type para o tipo de dados que será utilizado no in.
create or replace type number_t as table of number;
/

-- Criar uma função que realize o parsing da string e
-- alimente a tabela do type criado.
create or replace
function f_lista
( p_valores in varchar2
) return number_t
as
  v_valores varchar2(4000);
  v_tab     number_t := number_t();
  j         number;
begin
  v_valores := p_valores||',';

  while v_valores is not null loop

    j         := to_number(trim(substr(v_valores,1, instr(v_valores, ',')-1)));
    v_valores := substr(v_valores, instr(v_valores, ',')+1);
    v_tab.extend;
    v_tab(v_tab.count) := j;

  end loop;

  return v_tab;

end f_lista;
/

-- Exemplos de como realizar a consulta


-- Buscar apenas os valores separados por ",":
select column_value cod from the (select cast(f_lista('1, 0, 56, 994 , 12') as number_t) from dual);
/

-- Utilizar uma lista separada por "," como parâmetro para o operador in:
select * from all_source where line in
(
  select column_value cod
  from   the (
               select cast(f_lista('1, 0, 56, 994 , 12') as number_t)
               from dual
             )
)
and rownum < 10
;
/
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 14 visitantes