oracle sql simple array from table data

Question

I'm very new to SQL and would like to know how to get some data from a table and place it into an array. If I have this:

SELECT SeatNo FROM SEATING_PLAN
WHERE Block = 1;

SeatNo and Block are INT.

What sort of array should I use to place all of the seat numbers that match into the array, and what sort of loop is best for this?

Thanks in advance

Why do you need to place it in an array? An array within the database or in another language? There should be no need to loop... — Ben
– Ben, Commented Oct 27, 2013 at 11:09
There is a need to loop if you are using say JDBC to access the database from an external Java application. Which programming language, and are you writing an external application, or embedded code in a stored procedure? — Robin Green
– Robin Green, Commented Oct 27, 2013 at 11:11
It is for an assignment. I have already handed it in, but I would like to improve it. The procedure checks how many seats in a certain block, then counts how many have been booked. if there are seats left then it continues with a booking. I would like to improve it by checking what seat numbers are taken and then assigning spare seats. I just wanted to put all of the seats into an array and then check against the book ones, as soon as a seat that wasn't booked was compared the booking would continue. — user1667474
– user1667474, Commented Oct 27, 2013 at 12:04

Jon Heller · Accepted Answer · 2013-10-27 17:55:23Z

1

In PL/SQL you have 2 types of arrays, associative arrays and variant arrays. There is another type of collection in PL/SQL called Nested Tables. This profussion makes PL/SQL obscure as the three are treated similarly for some operations and completely different for other.

http://docs.oracle.com/cd/B12037_01/appdev.101/b10807/05_colls.htm

If you want to store scalar type collections, I recommend you to use associative arrays:

DECLARE
   TYPE t_SeatNo is TABLE OF SEATING_PLAN.SeatNo%TYPE INDEX BY BINARY_INTEGER;
   seats t_SeatNo;
BEGIN
   SELECT SeatNo
   BULK COLLECT INTO seats
   FROM SEATING_PLAN
   WHERE Block = 1;

   -- Do what you need with the data

END;

edited Oct 27, 2013 at 17:55

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

answered Oct 27, 2013 at 12:00

Yván Ecarri

1,76019 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

oracle sql simple array from table data

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