Leveraging Oracle Collections to Build Array-typed Solutions
The answer to your question is YES, dimensioned variables such as ARRAYS and COLLECTIONS are viable data types in solving problems where there are multiple values in either or both the input and output values.
Additional good news is that the discussion for a simple example (such as the one in the OP) is pretty much the same as for a complex one. Solutions built with arrays are nicely scalable and dynamic if designed with a little advanced planning.
Some Up Front Design Decisions
There are actual collection types called ARRAYS and ASSOCIATIVE ARRAYS. I chose to use NESTED TABLE TYPES because of their accessibility to direct SQL queries. In some ways, they exhibit "array-like" behavior. There are other trade-offs which can be researched through Oracle references.
The query applied to search the COURSE TABLE would apply a JOIN condition instead of an IN-LIST approach.
The use of a STORED PROCEDURE typed object improves database response. Queries within the procedure call can leverage and reuse already compiled code plus their cached execution plans.
Choosing the Right Collection or Array Type
There are a lot of choices of collection types in Oracle for storing variables into memory. Each has an advantage and some sort of limitation. AskTom from Oracle has a good example and break-down of what a developer can expect by choosing one variable collection type over another.
Using NESTED TABLE Types for Managing Multiple Valued Variables
For this solution, I chose to work with NESTED TABLES because of their ability to be accessed directly through SQL commands. After trying several different approaches, I noticed that the plain-SQL accessibility leads to more clarity in the resulting code.
The down-side is that you will notice that there is a little overhead here and there with respect to declaring an instance of a nested table type, initializing each instance, and managing its size with the addition of new values.
In any case, if you anticipate a unknown number of input variables or values (our output), an array-typed data type (collection) of any sort is a more flexible structure for your code. It is likely to require less maintenance in the end.
The Example: A Stored Procedure Search Query
Custom TYPE Definitions
CREATE OR REPLACE TYPE "COURSE_REC_TYPE" IS OBJECT (DEPID NUMBER(10,0), COURSE VARCHAR2(10));
CREATE OR REPLACE TYPE "COURSE_TBL_TYPE" IS TABLE of course_rec_type;
PROCEDURE Source Code
create or replace PROCEDURE ZZ_PROC_COURSE_SEARCH IS
my_input course_tbl_type:= course_tbl_type();
my_output course_tbl_type:= course_tbl_type();
cur_loop_counter pls_integer;
c_output_template constant varchar2(100):=
'DEPID: <<DEPID>>, COURSE: <<COURSE>>';
v_output VARCHAR2(200);
CURSOR find_course_cur IS
SELECT crs.depid, crs.course
FROM zz_course crs,
(SELECT depid, course
FROM TABLE (CAST (my_input AS course_tbl_type))
) search_values
WHERE crs.depid = search_values.depid
AND crs.course = search_values.course;
BEGIN
my_input.extend(2);
my_input(1):= course_rec_type(1, 'A');
my_input(2):= course_rec_type(4, 'D');
cur_loop_counter:= 0;
for i in find_course_cur
loop
cur_loop_counter:= cur_loop_counter + 1;
my_output.extend;
my_output(cur_loop_counter):= course_rec_type(i.depid, i.course);
end loop;
for j in my_output.first .. my_output.last
loop
v_output:= replace(c_output_template, '<<DEPID>>', to_char(my_output(j).depid));
v_output:= replace(v_output, '<<COURSE>>', my_output(j).course);
dbms_output.put_line(v_output);
end loop;
end ZZ_PROC_COURSE_SEARCH;
Procedure OUTPUT:
DEPID: 1, COURSE: A
DEPID: 4, COURSE: D
Statement processed.
0.03 seconds
MY COMMENTS: I wasn't particularly satisfied with the way the input variables were stored. There was a clumsy kind of problem with "loading" values into the nested table structure... If you can consider using a single search key instead of a composite pair (i.e., depid and course), the problem condenses to a simpler form.
Revised Cursor Using a Single Search Value
This is the proposed modification to the table design of the OP. Add a single unique key id column (RecId) to represent each unique combination of DepId and Course.
Note that the RecId column represents a SURROGATE KEY which should have no internal meaning aside from its property as a uniquely assigned value.
Custom TYPE Definitions
CREATE OR REPLACE TYPE "NUM_TBL_TYPE" IS TABLE of INTEGER;
Remove Array Variable
This will be passed directly through an input parameter from the procedure call.
-- REMOVE
my_input course_tbl_type:= course_tbl_type();
Loading and Presenting INPUT Parameter Array (Nested Table)
The following can be removed from the main procedure and presented as part of the call to the procedure.
BEGIN
my_input.extend(2);
my_input(1):= course_rec_type(1, 'A');
my_input(2):= course_rec_type(4, 'D');
Becomes:
create or replace PROCEDURE ZZ_PROC_COURSE_SEARCH (p_search_ids IN num_tbl_type) IS...
and
my_external_input.extend(2);
my_external_input:= num_tbl_type(1, 4);
Changing the Internal Cursor Definition
The cursor looks about the same. You can just as easily use an IN-LIST now that there is only one search parameter.
CURSOR find_course_cur IS
SELECT crs.depid, crs.course
FROM zz_course_new crs,
(SELECT column_value as recid
FROM TABLE (CAST (p_search_ids AS num_tbl_type))
) search_values
WHERE crs.recid = search_values.recid;
The Actual SEARCH Call and Output
The searching portion of this operation is now isolated and dynamic. It does not need to be changed. All the Changes happen in the calling PL/SQL block where the search ID values are a lot easier to read and change.
DECLARE
my_input_external num_tbl_type:= num_tbl_type();
BEGIN
my_input_external.extend(3);
my_input_external:= num_tbl_type(1,3,22);
ZZ_PROC_COURSE_SEARCH (p_search_ids => my_input_external);
END;
-- The OUTPUT (Currently set to DBMS_OUT)
DEPID: 1, COURSE: A
DEPID: 4, COURSE: D
DEPID: 7, COURSE: G
Statement processed.
0.01 seconds
