Geek Answers Handbook

Use Oracle PL / SQL for loop to iterate through comma delimited string

I am writing a piece of code that will need to iterate over the contents of a string, with each value being divided by ,.

eg. I haveelements

v_list_pak_like varchar2(4000) := 'PEBO,PTGC,PTTL,PTOP,PTA';

How can I get it in Array / Cursor to iterate over it in my loop?

for x in (elements)
loop
   -- do my stuff
end loop;

I am looking for a very simple way if you can avoid declaring associative arrays.

Is it possible to create a function that will return something suitable for use as an element for a loop for(on the contrary to what can be used as in https://stackoverflow.com/a/165013/... )?

Thank you very much in advance.

+4
source share
2

SQL. , . - Oracle

, PL/SQL, :

SQL> set serveroutput on
SQL> DECLARE
  2    str VARCHAR2(100) := 'PEBO,PTGC,PTTL,PTOP,PTA';
  3  BEGIN
  4    FOR i IN
  5    (SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) l
  6    FROM dual
  7      CONNECT BY LEVEL <= regexp_count(str, ',')+1
  8    )
  9    LOOP
 10      dbms_output.put_line(i.l);
 11    END LOOP;
 12  END;
 13  /
PEBO
PTGC
PTTL
PTOP
PTA

PL/SQL procedure successfully completed.

SQL>
+10

Lalit , for:

CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE
       FUNCTION comma_to_table(p_list IN VARCHAR2)
         RETURN t_my_list
       AS
         l_string VARCHAR2(32767) := p_list || ',';
         l_comma_index PLS_INTEGER;
         l_index PLS_INTEGER := 1;
         l_tab t_my_list     := t_my_list();
       BEGIN
         LOOP
           l_comma_index := INSTR(l_string, ',', l_index);
           EXIT
         WHEN l_comma_index = 0;
           l_tab.EXTEND;
           l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_comma_index - l_index));
           l_index            := l_comma_index + 1;
         END LOOP;
         RETURN l_tab;
       END comma_to_table;
/

, for:

declare
  v_list_pak_like varchar2(4000) := 'PEBO,PTGC,PTTL,PTOP,PTA';
begin
    FOR x IN (select * from (table(comma_to_table(v_list_pak_like)) ) )
    loop
        dbms_output.put_line(x.COLUMN_VALUE);
    end loop;
end;
/

COLUMN_VALUE, Oracle, , .

, :

PEBO
PTGC
PTTL
PTOP
PTA
+1

More articles:


All Articles