I've two large tables and I have to process a little resultset from all of these tables. However, processing is completed in several functions each and performance should do some joining to be able to format the information in most convenient way.
I'd certainly have to cache the first resultset in some way so it may be used again through the functions. What I must do is defined the very first result occur one collection, the 2nd resultset in another collection, after which manipulate these collections through SQL queries as though these were real SQL tables.
Are you able to suggest how you can do this?
Seems like employment for temp tables:
CREATE GLOBAL TEMPORARY TABLE table_name (...) ON ...
ON has two options, with various impacts:
ON COMMIT DELETE ROWSidentifies temporary table could be transaction specific. Data persist within table as much as transaction ending time. Should you finish the transaction the database truncates the table (remove all rows). Suppose should you problem commit or run ddl then data within the temporary table is going to be lost. It's automatically option.
ON COMMIT PRESERVE ROWSidentifies temporary table could be session specific. Data persist within table as much as session ending time. Should you finish the session the database truncates the table (remove all rows). Suppose you type exit in SQL*Plus then data within the temporary table is going to be lost.
...but it's entirely possible that you don't have to use temporary tables. Derived tables/inline sights/subqueries (maybe pipelining) may possibly do what you would like, however the info is vague and so i can't recommend a specific approach.
In case your collections are declared having a SQL type they are utilized in SQL claims having a TABLE() function. In Oracle 10g we are able to merge collections while using MULTISET UNION operator. The next code shows good examples of both techniques...
SQL> declare 2 v1 sys.dbms_debug_vc2coll; 3 v2 sys.dbms_debug_vc2coll; 4 v3 sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll(); 5 begin 6 select ename 7 bulk collect into v1 8 from emp; 9 select dname 10 bulk collect into v2 11 from dept; 12 13 -- manipulate connects using SQL 14 15 for r in ( select * from table(v1) 16 intersect 17 select * from table(v2) 18 ) 19 loop 20 dbms_output.put_line('Employee '|| r.column_value ||' has same name as a department'); 21 end loop; 22 23 -- combine two collections into one 24 25 dbms_output.put_line('V3 has '|| v3.count() ||' elements'); 26 v3 := v1 multiset union v2; 27 dbms_output.put_line('V3 now has '|| v3.count() ||' elements'); 28 end; 29 / Employee SALES has same name as a department V3 has 0 elements V3 now has 23 elements PL/SQL procedure successfully completed. SQL>
You will find many other approaches which you'll employ. Usually it is best to make use of SQL instead of PL/SQL, so OMG Ponies suggestion of temporary tables may be appropriate. It truly is dependent around the precise particulars of the processing needs.
You have to create an schema-level type(not in the package) like a nested table. You are able to populate them, and you could use them in your soul queries normally tables while using "table()" statement.
This link describes the thing you need. A fast example
create type foo as table of number;-- or a record type, data%rowtype, whatever ... myfoo1 foo := foo (1,2,3); myfoo2 foo := foo(3,4,5) select column_value into bar from table(foo1) join table(foo2) using (column_value)