JL Computer Consultancy

There is a question that appears on the internet newsgroup comp.databases.oracle.server fairly regularly about dealing with the problem of creating a list (of strings usually) in one piece of code, and then passing the list to another piece of code to be used in a SQL statement.

The first attempt to do this usually results in code fragments like:

        var1 := "'A','B','C'"
复制

               ...
复制

               ...
复制

        select * from tableX where colX in (:var1);
复制

The idea being that by the time the SQL runs, that the database engine will receive the query:

        select * from tableX where colX in ('A','B','C');
复制

and return rows which satisfy the three different conditions.

Unfortunately, if you pass one bind variable, the thing that arrives is a single bind variable, and cannot be broken up into an arbitrary number of separate values. In this the database responds by searching for all rows where colX is an exact match to the string '''A''',''B'',''C'''. (where the doubled up single-quotes would be used to pass the value in a somple SQL*Plus session)

Oracle 8.0 with the object option, however, introduces the possibility of passing a single variable to a procedure, or cursor, but having the code break the variable into a number of distinct values. My earlier article on PL/SQL tables as Cursors is the starting point for this process, and you should read that article before you continue with this one.

The sample code uses my package jpl_table_3, and the jpl_array described in that article, and it is a parameter of type jpl_array that can become the multiple set of elements in our IN-list.

A Sample procedure

rem
复制

rem     Script:        in_list.sql
复制

rem     Author:        Jonathan Lewis
复制

rem     Dated:         June 99
复制

rem     Oracle:        8.0.4 (NT)
复制

rem     Purpose:       Demo passing a list of values as a parameter
复制

rem                    Uses package jpl_table_3 for table of values
复制

rem
复制

create or replace procedure in_list_demo (v_list in jpl_array) as
复制

--      A procedure taking one IN parameter
复制

--      and a cursor taking one IN parameter
复制

 
复制

--      Note how the 'the(select cast() from dual) converts one parameter into
复制

--      a list of values (actually a subquery, but that's often close enough)
复制

 
复制

        cursor c1 (t_list in jpl_array) is
复制

        select owner, object_type, object_name
复制

        from all_objects t1
复制

        where object_name in (
复制

               select description 
复制

               from 
复制

                       the (   select 
复制

                       cast(jpl_table_3.return_table(t_list) as jpl_array)
复制

                       from dual
复制

                       ) t2
复制

        )
复制

        ;
复制

begin
复制

        for r1 in c1(v_list) loop
复制

               dbms_output.put_line(
复制

                       r1.owner || ' - ' || 
复制

                       r1.object_type || ' - ' || 
复制

                       r1.object_name
复制

               );
复制

        end loop;
复制

end;
复制

.
复制

/
复制

A demonstration of using the procedure

set serveroutput in size 10000
复制

declare
复制

        --      Create a variable of the array type
复制

        my_table       jpl_array := jpl_array();
复制

begin
复制

        --      Insert some 'rows' into the type
复制

        my_table := jpl_array(
复制

               jpl_row(null,'PLAN_TABLE'),
复制

               jpl_row(null,'ALL_TABLES'),
复制

               jpl_row(null,'DUAL')
复制

        );      
复制

        --      call the procedure, passing the one variable
复制

        in_list_demo(my_table);
复制

end;
复制

.
复制

/
复制

The results

PUBLIC - SYNONYM - ALL_TABLES                                                                       
复制

SYS - VIEW - ALL_TABLES                                                                             
复制

SYS - TABLE - DUAL                                                                                  
复制

PUBLIC - SYNONYM - DUAL                                                                             
复制

PUBLIC - SYNONYM - PLAN_TABLE                                                                       
复制

SYSTEM - TABLE - PLAN_TABLE                                                                         
复制

PL/SQL procedure successfully completed.
复制

Interestingly tkprof80 was unable to handle parsing the statement executed by the procedure, but the execution path dumped into the trace file itself showed an example of the new 'collection iterator' operation.

        STAT #5 id=9 cnt=3 pid=8 pos=1 obj=0 op='VIEW '
复制

        STAT #5 id=10 cnt=3 pid=9 pos=1 obj=0 op='SORT UNIQUE '
复制

        STAT #5 id=11 cnt=3 pid=10 pos=1 obj=0 op='COLLECTION ITERATOR '
复制

Apart from this, the main difference in execution paths between the procedure's path and the path taken by a simple SQL staement using 3 bind variables was that the simple SQL used a simple filter operation with the three values, whereas the procedural version with the collection type iterator used a sort/merge join between the 'view' created from the collection and the results generated by the rest of the query. This could make a big difference to your performance in real life - so test thoroughly with real data volumes before using this technique.