How to find last iteration in below for loop-CodePudding

I have following Oracle query. I am trying to find last index in the iteration, In other words, I want to print the result only in last step. But I have no success

set serveroutput on
DECLARE
  str VARCHAR2(100) := 'a,c,v,b';
  V_CMP_MUMBER VARCHAR2(20);
   V_CMP_MUMBERS VARCHAR2(200);
  BEGIN
  FOR i IN
  (SELECT trim(regexp_substr(str, '[^,] ', 1, LEVEL)) l
   FROM dual
      CONNECT BY LEVEL <= regexp_count(str, ',') 1
   )
   LOOP
   select cn INTO V_CMP_MUMBER from VP where NAME=i.l;
   V_CMP_MUMBERS := V_CMP_MUMBERS || ',' || v_cmp_mumber;
     dbms_output.put_line(REGEXP_REPLACE(V_CMP_MUMBERS,'^,', '' ));
    
  END LOOP;
 END;
  /

CodePudding user response：

Don't use a loop and do it all in a single SQL query:

DECLARE
  str VARCHAR2(100) := 'a,c,v,b';
  V_CMP_MUMBERS VARCHAR2(200);
  v_count       PLS_INTEGER;
BEGIN
  SELECT COUNT(*),
         LISTAGG(cn, ',')
           WITHIN GROUP (ORDER BY INSTR(','||str||',', ','||name||','))
  INTO   v_count,
         V_CMP_MUMBERS
  FROM   VP
  WHERE  INSTR(','||str||',', ','||name||',') > 0;

  dbms_output.put_line('Number of rows matched: ' || v_count);
  dbms_output.put_line('Matches: ' || V_CMP_MUMBERS);
END;
/

Which, for the sample data:

CREATE TABLE vp (name, cn) AS
SELECT 'a', 'aaa' FROM DUAL UNION ALL
SELECT 'b', 'bbb' FROM DUAL UNION ALL
SELECT 'c', 'ccc' FROM DUAL UNION ALL
SELECT 'v', 'vvv' FROM DUAL;

Outputs:

Number of rows matched: 4
Matches: aaa,ccc,vvv,bbb

db<>fiddle here

CodePudding user response：

Move dbms_output.put_line call out of the loop.

For my sample table:

SQL> select * from vp;

        CN N
---------- -
       100 a
       200 b
       300 c
       400 v

SQL>

result is then

SQL> DECLARE
  2    str            VARCHAR2(100) := 'a,c,v,b';
  3    V_CMP_MUMBER   VARCHAR2(20);
  4    V_CMP_MUMBERS  VARCHAR2(200);
  5    l_last_index   number := 0;
  6  BEGIN
  7    FOR i IN
  8      (SELECT trim(regexp_substr(str, '[^,] ', 1, LEVEL)) l
  9       FROM dual
 10       CONNECT BY LEVEL <= regexp_count(str, ',') 1
 11      )
 12    LOOP
 13      l_last_index := l_last_index   1;
 14      select cn INTO V_CMP_MUMBER from VP where NAME=i.l;
 15      V_CMP_MUMBERS := V_CMP_MUMBERS || ',' || v_cmp_mumber;
 16    END LOOP;
 17    dbms_output.put_line('Last index = ' || l_last_index);
 18    dbms_output.put_line(REGEXP_REPLACE(V_CMP_MUMBERS,'^,', '' ));
 19  END;
 20  /
Last index = 4
100,300,400,200

PL/SQL procedure successfully completed.

SQL>