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
- Use cursor to read through a db
- store rows which fits a particular cr开发者_StackOverflowiterial into an array of rows
- sort the array of rows
- 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).
精彩评论