Can a function return an array (procedures have OUT parameters, functions return things)? Sure
CREATE OR REPLACE PACKAGE some_pkg
AS
TYPE my_assoc_array_typ IS TABLE OF emp%rowtype INDEX BY PLS_INTEGER;
FUNCTION return_array( p_deptno IN emp.deptno%type )
RETURN my_assoc_array_typ;
END;
CREATE OR REPLACE PACKAGE BODY some_pkg
AS
FUNCTION return_array( p_deptno IN emp.deptno%type )
RETURN my_assoc_array_typ
IS
l_emps my_assoc_array_typ;
BEGIN
SELECT *
BULK COLLECT INTO l_emps
FROM emp
WHERE deptno = l_deptno;
RETURN l_emps;
END;
END;
Now, is that the most efficient approach? It might be, it might not be depending on your environment. If there was a single approach that was always more efficient than the alternatives, Oracle wouldn't bother implementing the alternatives.
Using associative arrays (or any PL/SQL collection type) means that your data is stored in the relatively expensive PGA memory on the server. If you have one session reading a few hundred rows into a collection, that's probably not a major concern. If, on the other hand, you have thousands of sessions simultaneously trying to work with collections that have multiple millions of rows, PL/SQL collections will almost certainly create a bottleneck on the database server where most of the server's RAM is now dedicated to supporting these collections. Of course, you can often work around these problems by fetching smaller numbers of rows into each collection and, presumably, allowing for subsequent calls to fetch subsequent sets of rows, but that means architecture changes.
In contrast, data stored in a temporary table, just like data stored in a permanent table, may be in the buffer cache or may be on disk at any point in time. That makes a temporary table more appropriate when you want to manage larger data volumes. But it comes at the cost of performance that you'll likely be doing at least some reads from and writes to physical disk and that your temporary table data will be competing with other data for space in the buffer cache.
A cursor does not store data, it's just a pointer to a program (a compiled SQL statement) that can be executed to fetch the next row of data you want.
