Arrays in Oracle PL/SQL to store rows

I'm trying to perform something in Oracle/PLSQL where I want to select some stuff from the db via a cursor and store it into an array of rows, which I can later process. Searched on google but to no avail. Will appreciate any help here.

What I have in mind

1. Use cursor to read through a db
2. store rows which fits a particular cr开发者_StackOverflowiterial into an array of rows
3. sort the array of rows
4. print the items in the array of rows using dbms_output.put_line

---

Take a look at PL/SQL Collections. You can create a collection of a PL/SQL record, where the record is defined as the %ROWTYPE of your table. Then you load that table & perform operations on it.

For example:

DECLARE
 CURSOR emp_cursor IS
 SELECT employee_id, employee_name from employees;

 TYPE employeeArrayType IS TABLE OF a_cur%ROWTYPE;
 employeeArray employeeArrayType;

BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor BULK COLLECT INTO employeeArray LIMIT 100;
    EXIT WHEN emp_cursor%NOTFOUND;
  END LOOP;
  CLOSE emp_cursor;

  -- Now you can do work with employeeArray
END;

The code declares my cursor and collection, then loads the collection using a FETCH..BULK COLLECT process in a loop (which is advisable with large amounts of data).

However, Codo's comment applies here - it's much easier if you filter and sort your data as part of the WHERE clause & ORDER BY clause of your SQL query. When you have a database engine behind you there's not very many cases where it would be better to do set operations in a procedural language. A few ETL processes may require it, but for your question, I really would recommend doing your work in SQL then working with the final resultset.

That being said, sorting PL/SQL collections of records is not as easy as one would like. For an overview of ways to sort a collection, take a look at the AMIS technology blog (and here for part 2).