Clausula in

[aline_plr]

Pessoal, to com um problema.
É o seguinte em uma function recebo como parametro uma string esse parametro é para ser utilizado na clausula in do select. Ex:

O valor da variavel cr = é 'F03120,F03141'

Selecionar tudo

function relat_geral_economico ( cr varchar2) return Bollean  
cr1 varchar2(50);     
is 

begin
for reg in (select o.cod_centrocusto from v_ofn_orcamento o 
where o.cod_centrocusto in ( cr )) loop
cr1:=reg.cod_centrocusto;
end loop;

return true;
end

Como posso fazer pra esse select funcionar sem que eu precisa jogar em tabela temporaria ou em uma table function ?

[Marciel]

Você poderia criar uma função no banco que verifica se o texto está em na string com esse formato. Então usaria dessa forma:

Selecionar tudo

BEGIN
  FOR REG IN (SELECT O.COD_CENTROCUSTO
                FROM V_OFN_ORCAMENTO O
               WHERE F_EXISTE (O.COD_CENTROCUSTO, CR) = 1) LOOP
    CR1 := REG.COD_CENTROCUSTO;
  END LOOP;
END;

Não sei se há como fazer de outra forma.

[aline_plr]

mas náo entendi como seria essa função F_EXISTE

[SergioLBJr]

o seu problema no caso é que você quer fazer algo assim :

Selecionar tudo

in ('F03120','F03141')

mas esta fazendo algo assim :

Selecionar tudo

in (F03120,F03141)

?????

se sim:

Na hora de montar este cr você concatena '''' antes e depois dos valores.

Tive este problema com um report chamado por um form.

Vai mostrando os que o cr ta retornando até tu acertar, mas é só concatenar a quantidade certea de aspas simples que ele funciona.

se não é este problema então explica melhor.

[aline_plr]

É muito boa esta idéia de concater, mas não funcionou. Meu select não retornou nada.
Assim funciona

Selecionar tudo

select sum(v.val_lancamento) val_lancamento
       from v_ofn_orcamento v
       where v.tip_orcamento in ('PL', 'IAC')
       and v.cod_centrocusto in ('F03120', 'F03141')  
       and v.num_conta_pai=24;

Assim não funciona

Selecionar tudo

select sum(v.val_lancamento) val_lancamento
       from v_ofn_orcamento v
       where v.tip_orcamento in ('PL', 'IAC')
       and v.cod_centrocusto in ('''F03120'',''F03141''')  
       and v.num_conta_pai=24;

Será que fiz algo de errado pra não funcionar. To precisando muito disso.

[SergioLBJr]

não vai funcionar mesmo do segundo jeito

da um dbms_output.putline(cr);

concatena aspas até ela ficar assim 'F03120', 'F03141'.

Tu deve ta concatenando mais aspas que o nescessário.

[aline_plr]

Fiz o select avaixo para ver o que retornava

select '''F03120'',''F03141''' cr from dual

Retornou assim: 'F03120','F03141'

E estiver correto o select abaixo continua não funcionado, não retorna nenhuma linha

Selecionar tudo

select sum(v.val_lancamento) val_lancamento 
from v_ofn_orcamento v 
where v.tip_orcamento in ('PL', 'IAC') 
and v.cod_centrocusto in (select '''F03120'',''F03141''' cr from dual) 
and v.num_conta_pai=24; 

O que devo estar fazendo de errado, as aspas parece que não são.
Será que isso funciona mesmo ?

[SergioLBJr]

Posta o código onde tu monta o parâmetro cr pra mim dar uma olhada.

[aline_plr]

eu apenas atribuo o valor a variavel cr.
Ex:

Selecionar tudo

cr:='''F03120'',''F03141''';

[SergioLBJr]

Isso:

Selecionar tudo

cr := '''||F03120||'',''||F03141||''';

[SergioLBJr]

e tem aspas sobrando ou faltando aí

faça assim

Selecionar tudo

cr:= ''||F03120||'',''||F03141||'';

ou assim

Selecionar tudo

cr:= ''''||F03120||'''',''''||F03141||'''';

[aline_plr]

Não funciona, você está com o oracle instalado ai ? Faz o teste fazendo os exemplos que me passou.

Selecionar tudo

cr:= ''||F03120||'',''||F03141||''; 

ou assim

Selecionar tudo

cr:= ''''||F03120||'''',''''||F03141||'''';

Desta forma F03120 e F03141 não ficam entre aspas e assim não são string, portanto dá erro na atribuição.
Desculpe, mas não sei como isso pode dar certo. você já utilizou isso na prática ? Se desse cero iria resolver um problemão pra mim

[fsitja]

Para fazer IN você não pode comparar com uma string. Para o Oracle é tudo uma variável só. O jeito é repartir a string usando uma table function e jogando o resultado dela no IN.

Selecionar tudo

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as fsitja
 
SQL> 
SQL> CREATE TABLE t1 AS
  2  SELECT 1 col1 FROM dual UNION ALL
  3  SELECT 2 col1 FROM dual UNION ALL
  4  SELECT 35 col1 FROM dual UNION ALL
  5  SELECT 50 col1 FROM dual UNION ALL
  6  SELECT 100 col1 FROM dual UNION ALL
  7  SELECT 4000 col1 FROM dual UNION ALL
  8  SELECT 5000 col1 FROM dual
  9  /
 
Table created
SQL> CREATE OR REPLACE TYPE tab_number AS TABLE OF NUMBER
  2  /
 
Type created
SQL> CREATE OR REPLACE FUNCTION in_list(p_in_list IN VARCHAR2) RETURN tab_number AS
  2    v_tab     tab_number := tab_number();
  3    i         NUMBER;
  4  BEGIN
  5    i := 1;
  6    LOOP
  7      v_tab.extend;
  8      v_tab(v_tab.last) := regexp_substr(p_in_list, '[0-9]+', 1, i);
  9      EXIT WHEN v_tab(v_tab.last) IS NULL;
 10      i := i + 1;
 11    END LOOP;
 12    v_tab.trim;
 13    RETURN v_tab;
 14  END;
 15  /
 
Function created
SQL> SELECT *
  2    FROM t1
  3   WHERE col1 IN (SELECT * FROM TABLE(CAST(in_list('2, 35, 4000') AS tab_number)));
 
      COL1
----------
         2
        35
      4000
 
SQL> 

[SergioLBJr]

Eu já usei pra chamar relatório.

Eu montava um parâmetro texto que recebia quantos registros o usuario informasse em um bloco multirow e la dentro do report eu colocava o parâmetro dentro de um in.

Selecionar tudo

cr:= ''''||'F03120'||''''||','||''''||'F03141'||''''

no pl isso funcionou para mim.

[fsitja]

Sim, mas foi usando SQL dinâmico então. Nem sempre é possível ou desejável usar SQL dinâmico.

[SergioLBJr]

Sim foi usado para um sql dinâmico.

O que penso ser o que a aline deseja já que está usando um parâmetro para o in.

Do contrario só jogaria os valores lá.

[aline_plr]

no plsql

Selecionar tudo

cr:= ''''||'F03120'||''''||','||''''||'F03141'||''''  

funcionou em um select pra você ?

[Marciel]

Selecionar tudo

CREATE OR REPLACE FUNCTION F_EXISTE (YSTRING IN VARCHAR2, YCOD IN NUMBER)
  RETURN NUMBER IS
  YAUX    VARCHAR2 (4000) := YSTRING;
  YCONT   NUMBER          := 0;
  YNUM    NUMBER;
BEGIN
  WHILE YCONT < LENGTH (YAUX) LOOP
    YCONT := INSTR (YAUX, ',');
    YNUM := TO_NUMBER (SUBSTR (YAUX,
                               1,
                               YCONT));

    IF YNUM = YCOD THEN
      RETURN 1;
    END IF;

    YAUX := SUBSTR (YAUX, YCONT + 1);
  END LOOP;

  RETURN 0;
END F_EXISTE;

-----------

SELECT O.COD_CENTROCUSTO
  FROM V_OFN_ORCAMENTO O
 WHERE F_EXISTE (O.COD_CENTROCUSTO, CR) = 1

[Marciel]

Acho que essa função que postei vai falhar para checar o último elemento da string. Um jeito de corrigir é mudar a linha abaixo:

Selecionar tudo

  YAUX    VARCHAR2 (4000) := YSTRING || ','; 

[aline_plr]

pra mim não funconou no plsql isso:

Selecionar tudo

cr:= ''''||'F03120'||''''||','||''''||'F03141'||''''

[fsitja]

Marciel,
É uma solução criativa, porém apresenta um problema de escalabilidade. Imagine se a tabela tiver X milhões de linhas, você terá que chamar a user-defined function X milhões de vezes e o desempenho do select vai para o espaço... Isso porque a query é simples, agora coloca joins e outras operações ali e já era.

Dá uma olhada na comparação de performance das soluções. Testei com 1 milhão de linhas, o que é bem pouco comparando com muitas tabelas por aí.

Selecionar tudo

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as fsitja
 
SQL> 
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE tab1 AS
  2  SELECT LEVEL col1 FROM dual
  3  CONNECT BY LEVEL < 1000000
  4  /
 
Table created
SQL> CREATE OR REPLACE FUNCTION F_EXISTE (YSTRING IN VARCHAR2, YCOD IN NUMBER)
  2    RETURN NUMBER IS
  3    YAUX    VARCHAR2 (4000) := YSTRING || ',';
  4    YCONT   NUMBER          := 0;
  5    YNUM    NUMBER;
  6  BEGIN
  7    WHILE YCONT < LENGTH (YAUX) LOOP
  8      YCONT := INSTR (YAUX, ',');
  9      YNUM := TO_NUMBER (SUBSTR (YAUX,
 10                                 1,
 11                                 YCONT));
 12  
 13      IF YNUM = YCOD THEN
 14        RETURN 1;
 15      END IF;
 16  
 17      YAUX := SUBSTR (YAUX, YCONT + 1);
 18    END LOOP;
 19  
 20    RETURN 0;
 21  END F_EXISTE;
 22  /
 
Function created
SQL> CREATE OR REPLACE TYPE tab_number AS TABLE OF NUMBER
  2  /
 
Type created
SQL> CREATE OR REPLACE FUNCTION in_list(p_in_list IN VARCHAR2) RETURN tab_number AS
  2    v_tab     tab_number := tab_number();
  3    i         NUMBER;
  4  BEGIN
  5    i := 1;
  6    LOOP
  7      v_tab.extend;
  8      v_tab(v_tab.last) := regexp_substr(p_in_list, '[0-9]+', 1, i);
  9      EXIT WHEN v_tab(v_tab.last) IS NULL;
 10      i := i + 1;
 11    END LOOP;
 12    v_tab.trim;
 13    RETURN v_tab;
 14  END;
 15  /
 
Function created
SQL> DECLARE
  2    tab1   NUMBER;
  3    t2   NUMBER;
  4    qtty NUMBER;
  5  BEGIN
  6    tab1 := dbms_utility.get_time;
  7    SELECT COUNT(*) INTO qtty FROM tab1 WHERE col1 IN (SELECT * FROM TABLE(in_list('2, 35, 4000')));
  8    t2 := dbms_utility.get_time;
  9    dbms_output.put_line(qtty || ' registros em ' || to_char((t2 - tab1) / 100, '990.000') || ' segundos');
 10    tab1 := dbms_utility.get_time;
 11    SELECT count(*) INTO qtty FROM tab1 WHERE f_existe('2, 35, 4000', col1) = 1;
 12    t2 := dbms_utility.get_time;
 13    dbms_output.put_line(qtty || ' registros em ' || to_char((t2 - tab1) / 100, '990.000') || ' segundos');
 14  END;
 15  /
 
3 registros em    0.280 segundos
3 registros em   10.410 segundos
 
PL/SQL procedure successfully completed
SQL> DROP TABLE TAB1;
 
Table dropped
SQL> DROP FUNCTION F_EXISTE;
 
Function dropped
SQL> DROP FUNCTION in_list;
 
Function dropped
SQL> DROP TYPE tab_number;
 
Type dropped
 
SQL> 

De 0.28 segundos para 10.41.

Aline, para poder usar string como o Sérgio sugeriu, você precisa usar SQL dinâmico. Por isso não está funcionando.

Eu no seu lugar preferiria ficar com o SQL estático para não arrumar corda para me enforcar. Debugar SQL dinâmico é uma dor de cabeça desnecessária nesse caso.

[RodrigoValentim]

Aline, tirou sua duvida?

[Bel-Kyor]

Bom dia,
essa é uma das maneiras possíveis

Selecionar tudo

declare

 vWhere varchar2(500) := 'FORD,MILLER';

begin
  for xemp in ( SELECT *
                  FROM SCOTT.EMP
                 WHERE Instr( vWhere, Ename ) > 0 )
  loop
    dbms_output.put_line( xemp.empno||' '||xemp.ename||' '||xemp.job );
  end loop;
 
end;

Retorno:

Selecionar tudo

7902 FORD ANALYST
7934 MILLER CLERK

Abraço
Belk's