Inserting data from DB into array variable (PL/SQL)

Question

I'd like to read distinct data from one attribute of a table and then insert it into array variable. Considered data is of varchar2 type. After that I'd like to use this data written in arrays in 'where' condition. So in general I'd like to do sth like this:

TYPE genreArray IS VARRAY(50) OF VARCHAR2(12);
genres genreArray;

BEGIN
    FOR a IN (SELECT DISTINCT genre FROM books) LOOP
    genres(a) := uniqe GENRE*

* of course this line is wrong because I couldn't find an answer to my question on the Internet.

The second question is wether I could use something like dynamic array where I define number of elements during execution of a program?

And my final question is if I could use it another select like:

SELECT sth WHERE sth AND GENRE=genres(i)

Assuming that 'i' is a for loop variable.

Thank you in advance for your help.

Is there a reason that you want to use a varray rather than, say, a nested table? — Justin Cave
– Justin Cave, Commented Oct 25, 2016 at 17:03
I don't want to save this data inside DB. I'd like to compute sth and then display result. — monterinio
– monterinio, Commented Oct 25, 2016 at 17:05
PL/SQL has three different types of collections-- associative arrays, nested tables, and varrays. Nested tables are the most common, varrays are very, very seldom used. None of them require data to be stored in the database, they're all maintained in memory. — Justin Cave
– Justin Cave, Commented Oct 25, 2016 at 17:07

Justin Cave · Accepted Answer · 2016-10-25 17:11:42Z

2

If there is no particular reason to use a varray, using a nested table would be much more natural.

DECLARE
  TYPE genre_nt IS TABLE OF varchar2(12);
  l_genres genre_nt;
BEGIN
  SELECT DISTINCT genre
    BULK COLLECT INTO l_genres
    FROM books;

  FOR i IN 1 .. l_genres.count
  LOOP
    dbms_output.put_line( l_genres(i) );
  END LOOP;
END;

answered Oct 25, 2016 at 17:11

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting data from DB into array variable (PL/SQL)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related