CODEWITHSUNDEEP
oracle-databaseloopsplsqldynamic
Christopher DeAngelis
Christopher DeAngelis

Reputation: 1

How can I create an array in PL/SQL that takes values from a SELECT statement?

I'm trying to create a script that can select a list of values, put them into an array, then iterate the array so that the selected values can be deleted. I think I'm ultimately just confusing myself, but my biggest issue seems to be figuring out how to select values into an array. With every other example I've seen on here, people define the array statically, not with a select statement.

I've tried to create a FOR loop that looks like this:

DECLARE 
  type PERMIDS IS VARRAY(10) OF VARCHAR2(10);
  ID PERMIDS;
  total integer;
  
BEGIN
  ID := PERMIDS('1','2','3','4','5');
  total := ID.count;
  
  FOR i in 1 .. total LOOP
      SYS.DBMS_OUTPUT.PUT_LINE('ID: ' || ID(i));
  END LOOP;
END;

This runs, but it doesn't allow for dynamic definition of the array.

What I'd like to do is something like this, with the select statement potentially yielding any number of results:

DECLARE 
  type PERMIDS IS VARRAY(10) OF VARCHAR2(10);
  ID PERMIDS;
  total integer;
  
BEGIN
  SELECT rg.ID INTO ID
  FROM table1 rg
  LEFT JOIN table2 a
  ON rg.ID = a.ID
  WHERE a.ID = '1234';
  total := ID.count;
  
  FOR i in 1 .. total LOOP
      SYS.DBMS_OUTPUT.PUT_LINE('ID: ' || ID(i));
  END LOOP;
END;

It's important that this loop can handle any number of results from the select statement, 0 included, as a user could have any number of records.

Upvotes: 0

Views: 537

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

One option is to use built-in sys.odcivarchar2list type and bulk collect into it.

Sample table (department names will be put into a collection):

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

PL/SQL procedure:

SQL> set serveroutput on
SQL>
SQL> declare
  2    l_id sys.odcivarchar2list;
  3  begin
  4    select dname
  5      bulk collect into l_id
  6      from dept;
  7
  8    for i in 1 .. l_id.count loop
  9      dbms_output.put_line('ID = ' || l_id(i));
 10    end loop;
 11  end;
 12  /
ID = ACCOUNTING
ID = RESEARCH
ID = SALES
ID = OPERATIONS

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions