Set Up DB Objects Used in Package
CREATE TABLE in_clause_tab (
ID INTEGER,
title VARCHAR2(100),
description VARCHAR2(2000))
Table created.
Set Up DB Objects Used in Package
BEGIN
INSERT INTO in_clause_tab
VALUES (1, 'How do I write a loop?'
, 'What are the different ways I can write a loop in PL/SQL');
INSERT INTO in_clause_tab
VALUES (2, 'Use cursor FOR loop?'
, 'Is there a reason any longer to use a cursor FOR loop, since I can now use BULK COLLECT?');
INSERT INTO in_clause_tab
VALUES (3, 'Which version of PL/SQL is best?'
, 'What is the best version of PL/SQL?');
FOR indx IN 1 .. 10000
LOOP
INSERT INTO in_clause_tab
VALUES (indx + 3, 'Title ' || indx, 'Description ' || indx);
END LOOP;
COMMIT;
END;
1 row(s) inserted.
Set Up DB Objects Used in Package
CREATE TYPE in_clause_ot AS OBJECT (
ID INTEGER
, title VARCHAR2 (100)
, description VARCHAR2 (2000)
)
Type created.
Set Up DB Objects Used in Package
CREATE OR REPLACE TYPE in_clause_tab_nt IS TABLE OF in_clause_ot;
Type created.
Set Up DB Objects Used in Package
CREATE OR REPLACE TYPE pky_nt IS TABLE OF INTEGER;
Type created.
Package Specification of IN Clause "Driver"
CREATE OR REPLACE PACKAGE in_clause
IS
-- Returns a cursor variable based on the weak ref cursor type.
FUNCTION nds_list ( list_in IN VARCHAR2 )
RETURN sys_refcursor;
FUNCTION nds_bulk_list ( list_in IN VARCHAR2 )
RETURN in_clause_tab_nt;
-- Use nds_bulk_list as a table function and return a cursor variable.
FUNCTION nds_bulk_list2 ( list_in IN VARCHAR2 )
RETURN sys_refcursor;
FUNCTION dbms_sql_list ( list_in IN VARCHAR2 )
RETURN in_clause_tab_nt;
FUNCTION nested_table_list ( list_in IN pky_nt )
RETURN sys_refcursor;
FUNCTION nested_table_list2 ( list_in IN pky_nt )
RETURN sys_refcursor;
FUNCTION member_of_list ( list_in IN pky_nt )
RETURN sys_refcursor;
/* Disabled until time when DBMS_UTILITY is executable in LiveSQL
PROCEDURE test_varieties (
iterations_in IN PLS_INTEGER DEFAULT 1
, list_in IN VARCHAR2 DEFAULT '1,3'
, show_timing_in IN BOOLEAN DEFAULT FALSE
, show_data_in IN BOOLEAN DEFAULT FALSE
); */
END in_clause;
Package created.
Implementing the Different Approaches
CREATE OR REPLACE PACKAGE BODY in_clause
IS
FUNCTION nds_list ( list_in IN VARCHAR2 )
RETURN sys_refcursor
IS
retval sys_refcursor;
BEGIN
OPEN retval FOR 'SELECT * FROM in_clause_tab WHERE id IN ('
|| list_in
|| ')';
RETURN retval;
END nds_list;
FUNCTION nds_bulk_list ( list_in IN VARCHAR2 )
RETURN in_clause_tab_nt
IS
retval in_clause_tab_nt;
BEGIN
EXECUTE IMMEDIATE
'SELECT in_clause_ot (id, title, description)
FROM in_clause_tab WHERE id IN (' || list_in || ')'
BULK COLLECT INTO retval;
RETURN retval;
END nds_bulk_list;
FUNCTION nds_bulk_list2 ( list_in IN VARCHAR2 )
RETURN sys_refcursor
IS
retval sys_refcursor;
BEGIN
OPEN retval FOR
SELECT ic.ID, ic.title, ic.description
FROM TABLE ( nds_bulk_list ( list_in )) ic;
RETURN retval;
END nds_bulk_list2;
FUNCTION dbms_sql_list ( list_in IN VARCHAR2 )
RETURN in_clause_tab_nt
IS
l_query VARCHAR2 ( 32767 )
:= 'SELECT * FROM in_clause_tab WHERE id IN (';
l_cur PLS_INTEGER := DBMS_SQL.open_cursor;
l_feedback PLS_INTEGER;
l_ids pky_nt := pky_nt ( );
l_row PLS_INTEGER;
l_onerow in_clause_tab%ROWTYPE;
retval in_clause_tab_nt := in_clause_tab_nt ( );
PROCEDURE string_to_list (
str IN VARCHAR2
, list_inout IN OUT pky_nt
, delim IN VARCHAR2 DEFAULT ','
, append_to_list_in IN BOOLEAN DEFAULT TRUE
)
IS
l_loc PLS_INTEGER;
l_row PLS_INTEGER;
l_startloc PLS_INTEGER := 1;
l_item VARCHAR2 ( 32767 );
BEGIN
IF append_to_list_in
THEN
l_row := NVL ( list_inout.LAST, 0 ) + 1;
ELSE
list_inout.DELETE;
l_row := 1;
END IF;
IF str IS NOT NULL
THEN
LOOP
l_loc := INSTR ( str, delim, l_startloc );
IF l_loc = l_startloc
THEN
l_item := NULL;
ELSIF l_loc = 0
THEN
l_item := SUBSTR ( str, l_startloc );
ELSE
l_item := SUBSTR ( str, l_startloc, l_loc - l_startloc );
END IF;
list_inout.EXTEND;
list_inout ( l_row ) := l_item;
IF l_loc = 0
THEN
EXIT;
ELSE
l_startloc := l_loc + 1;
l_row := l_row + 1;
END IF;
END LOOP;
END IF;
END string_to_list;
BEGIN
-- Parse the delimited list to the collection.
string_to_list ( list_in, l_ids );
l_row := l_ids.FIRST;
-- Build the list of bind variables.
WHILE ( l_row IS NOT NULL )
LOOP
l_query := l_query || ':bv' || l_row || ',';
l_row := l_ids.NEXT ( l_row );
END LOOP;
l_query := RTRIM ( l_query, ',' ) || ')';
-- Define the columns to be queried.
DBMS_SQL.parse ( l_cur, l_query, DBMS_SQL.native );
DBMS_SQL.define_column ( l_cur, 1, 1 );
DBMS_SQL.define_column ( l_cur, 2, 'a', 100 );
DBMS_SQL.define_column ( l_cur, 3, 'a', 2000 );
-- Bind each variable in the provided list.
l_row := l_ids.FIRST;
WHILE ( l_row IS NOT NULL )
LOOP
DBMS_SQL.bind_variable ( l_cur, ':bv' || l_row, l_ids ( l_row ));
l_row := l_ids.NEXT ( l_row );
END LOOP;
-- Execute and then fetch each row.
l_feedback := DBMS_SQL.EXECUTE ( l_cur );
LOOP
l_feedback := DBMS_SQL.fetch_rows ( l_cur );
EXIT WHEN l_feedback = 0;
-- Retrieve individual column values and move them to the nested table.
DBMS_SQL.column_value ( l_cur, 1, l_onerow.ID );
DBMS_SQL.column_value ( l_cur, 2, l_onerow.title );
DBMS_SQL.column_value ( l_cur, 3, l_onerow.description );
retval.EXTEND;
retval ( retval.LAST ) :=
in_clause_ot ( l_onerow.ID, l_onerow.title, l_onerow.description );
END LOOP;
DBMS_SQL.close_cursor ( l_cur );
RETURN retval;
END dbms_sql_list;
FUNCTION nested_table_list ( list_in IN pky_nt )
RETURN sys_refcursor
IS
retval sys_refcursor;
BEGIN
OPEN retval FOR
SELECT *
FROM in_clause_tab
WHERE ID IN ( SELECT column_value
FROM TABLE ( list_in ));
RETURN retval;
END nested_table_list;
FUNCTION nested_table_list2 ( list_in IN pky_nt )
RETURN sys_refcursor
IS
retval sys_refcursor;
BEGIN
OPEN retval FOR
SELECT in_clause_tab.*
FROM in_clause_tab, ( SELECT column_value
FROM TABLE ( list_in ))
WHERE in_clause_tab.ID = column_value;
RETURN retval;
END nested_table_list2;
FUNCTION member_of_list ( list_in IN pky_nt )
RETURN sys_refcursor
IS
retval sys_refcursor;
BEGIN
OPEN retval FOR
SELECT *
FROM in_clause_tab
WHERE ID MEMBER OF list_in;
RETURN retval;
END member_of_list;
/* Disabled until DBMS_UTILITY available in LiveSQL
PROCEDURE test_varieties (
iterations_in IN PLS_INTEGER DEFAULT 1
, list_in IN VARCHAR2 DEFAULT '1,3'
, show_timing_in IN BOOLEAN DEFAULT FALSE
, show_data_in IN BOOLEAN DEFAULT FALSE
)
IS
l_start_time PLS_INTEGER;
PROCEDURE start_timing
IS
BEGIN
l_start_time := DBMS_UTILITY.get_cpu_time;
END start_timing;
PROCEDURE show_elapsed
IS
BEGIN
DBMS_OUTPUT.put_line ( ' Elapsed CPU time: '
|| TO_CHAR ( DBMS_UTILITY.get_cpu_time
- l_start_time
)
);
END show_elapsed;
PROCEDURE init_test ( str_in IN VARCHAR2 )
IS
BEGIN
IF show_timing_in
THEN
start_timing;
END IF;
DBMS_OUTPUT.put_line ( str_in );
END init_test;
PROCEDURE finish_test
IS
BEGIN
IF show_timing_in
THEN
show_elapsed;
END IF;
END finish_test;
PROCEDURE test_nds_list
IS
l_cv sys_refcursor;
l_one in_clause_tab%ROWTYPE;
BEGIN
init_test ( 'Output from NDS_LIST' );
FOR indx IN 1 .. iterations_in
LOOP
l_cv := nds_list ( list_in );
LOOP
FETCH l_cv
INTO l_one;
EXIT WHEN l_cv%NOTFOUND;
IF indx = 1 AND show_data_in
THEN
DBMS_OUTPUT.put_line ( ' ' || l_one.title );
END IF;
END LOOP;
CLOSE l_cv;
END LOOP;
finish_test;
END test_nds_list;
PROCEDURE test_nds_bulk_list
IS
l_array in_clause_tab_nt;
l_row PLS_INTEGER;
BEGIN
init_test ( 'Output from NDS_BULK_LIST' );
FOR indx IN 1 .. iterations_in
LOOP
l_array := nds_bulk_list ( list_in );
l_row := l_array.FIRST;
WHILE ( l_row IS NOT NULL )
LOOP
IF indx = 1 AND show_data_in
THEN
DBMS_OUTPUT.put_line ( ' ' || l_array ( l_row ).title );
END IF;
l_row := l_array.NEXT ( l_row );
END LOOP;
END LOOP;
finish_test;
END test_nds_bulk_list;
PROCEDURE test_nds_bulk_list2
IS
l_cv sys_refcursor;
l_one in_clause_tab%ROWTYPE;
BEGIN
init_test ( 'Output from NDS_BULK_LIST2' );
FOR indx IN 1 .. iterations_in
LOOP
l_cv := nds_bulk_list2 ( list_in );
LOOP
FETCH l_cv
INTO l_one;
EXIT WHEN l_cv%NOTFOUND;
IF indx = 1 AND show_data_in
THEN
DBMS_OUTPUT.put_line ( ' ' || l_one.title );
END IF;
END LOOP;
CLOSE l_cv;
END LOOP;
finish_test;
END test_nds_bulk_list2;
PROCEDURE test_dbms_sql_list
IS
l_array in_clause_tab_nt;
l_row PLS_INTEGER;
BEGIN
init_test ( 'Output from DBMS_SQL_LIST' );
FOR indx IN 1 .. iterations_in
LOOP
l_array := dbms_sql_list ( list_in );
l_row := l_array.FIRST;
WHILE ( l_row IS NOT NULL )
LOOP
IF indx = 1 AND show_data_in
THEN
DBMS_OUTPUT.put_line ( ' ' || l_array ( l_row ).title );
END IF;
l_row := l_array.NEXT ( l_row );
END LOOP;
END LOOP;
finish_test;
END test_dbms_sql_list;
PROCEDURE test_nested_table_list
IS
l_list pky_nt := pky_nt ( 1, 3 );
l_cv sys_refcursor;
l_one in_clause_tab%ROWTYPE;
BEGIN
init_test ( 'Output from NESTED_TABLE_LIST' );
FOR indx IN 1 .. iterations_in
LOOP
l_cv := nested_table_list ( l_list );
LOOP
FETCH l_cv
INTO l_one;
EXIT WHEN l_cv%NOTFOUND;
IF indx = 1 AND show_data_in
THEN
DBMS_OUTPUT.put_line ( ' ' || l_one.title );
END IF;
END LOOP;
CLOSE l_cv;
END LOOP;
finish_test;
END test_nested_table_list;
PROCEDURE test_nested_table_list2
IS
l_list pky_nt := pky_nt ( 1, 3 );
l_cv sys_refcursor;
l_one in_clause_tab%ROWTYPE;
BEGIN
init_test ( 'Output from NESTED_TABLE_LIST - Join' );
FOR indx IN 1 .. iterations_in
LOOP
l_cv := nested_table_list2 ( l_list );
LOOP
FETCH l_cv
INTO l_one;
EXIT WHEN l_cv%NOTFOUND;
IF indx = 1 AND show_data_in
THEN
DBMS_OUTPUT.put_line ( ' ' || l_one.title );
END IF;
END LOOP;
CLOSE l_cv;
END LOOP;
finish_test;
END test_nested_table_list2;
PROCEDURE test_member_of_list
IS
l_list pky_nt := pky_nt ( 1, 3 );
l_cv sys_refcursor;
l_one in_clause_tab%ROWTYPE;
BEGIN
init_test ( 'Output from MEMBER_OF_LIST' );
FOR indx IN 1 .. iterations_in
LOOP
l_cv := member_of_list ( l_list );
LOOP
FETCH l_cv
INTO l_one;
EXIT WHEN l_cv%NOTFOUND;
IF indx = 1 AND show_data_in
THEN
DBMS_OUTPUT.put_line ( ' ' || l_one.title );
END IF;
END LOOP;
CLOSE l_cv;
END LOOP;
finish_test;
END test_member_of_list;
PROCEDURE test_static_in_list
IS
l_list pky_nt := pky_nt ( );
l_cv sys_refcursor;
l_one in_clause_tab%ROWTYPE;
BEGIN
init_test ( 'Output from STATIC_IN_LIST' );
l_list.EXTEND ( 1000 );
l_list ( 1 ) := 1;
l_list ( 2 ) := 3;
FOR indx IN 1 .. iterations_in
LOOP
l_cv := static_in_list ( l_list );
LOOP
FETCH l_cv
INTO l_one;
EXIT WHEN l_cv%NOTFOUND;
IF indx = 1 AND show_data_in
THEN
DBMS_OUTPUT.put_line ( ' ' || l_one.title );
END IF;
END LOOP;
CLOSE l_cv;
END LOOP;
finish_test;
--
init_test ( 'Output from STATIC_IN_LIST_SMALL(50)' );
l_list.EXTEND ( 50 );
l_list ( 1 ) := 1;
l_list ( 2 ) := 3;
FOR indx IN 1 .. iterations_in
LOOP
l_cv := static_in_list ( l_list );
LOOP
FETCH l_cv
INTO l_one;
EXIT WHEN l_cv%NOTFOUND;
IF indx = 1 AND show_data_in
THEN
DBMS_OUTPUT.put_line ( ' ' || l_one.title );
END IF;
END LOOP;
CLOSE l_cv;
END LOOP;
finish_test;
END test_static_in_list;
BEGIN
test_nds_list;
test_nds_bulk_list;
test_nds_bulk_list2;
test_dbms_sql_list;
test_nested_table_list;
test_nested_table_list2;
test_member_of_list;
test_static_in_list;
END test_varieties;
*/
/* Example of results of testing:
11.2
Output from NDS_LIST
Elapsed CPU time: 200
Output from NDS_BULK_LIST
Elapsed CPU time: 217
Output from NDS_BULK_LIST2
Elapsed CPU time: 233
Output from DBMS_SQL_LIST
Elapsed CPU time: 223
Output from NESTED_TABLE_LIST
Elapsed CPU time: 186
Output from NESTED_TABLE_LIST - Join
Elapsed CPU time: 181
Output from MEMBER_OF_LIST
Elapsed CPU time: 879
Output from STATIC_IN_LIST
Elapsed CPU time: 284
Output from STATIC_IN_LIST_SMALL(50)
Elapsed CPU time: 250
10gR1
Output from NDS_LIST
Elapsed CPU time: 254
Output from NDS_BULK_LIST
Elapsed CPU time: 220
Output from NDS_BULK_LIST2
Elapsed CPU time: 269
Output from DBMS_SQL_LIST
Elapsed CPU time: 269
Output from NESTED_TABLE_LIST
Elapsed CPU time: 315
Output from MEMBER_OF_LIST
Elapsed CPU time: 1066
Output from STATIC_IN_LIST
Elapsed CPU time: 421
10gR2 - 1000 iterations, short list
Output from NDS_LIST
Elapsed CPU time: 259
Output from NDS_BULK_LIST
Elapsed CPU time: 217
Output from NDS_BULK_LIST2
Elapsed CPU time: 277
Output from DBMS_SQL_LIST
Elapsed CPU time: 251
Output from NESTED_TABLE_LIST
Elapsed CPU time: 337
Output from NESTED_TABLE_LIST - Join
Elapsed CPU time: 331
Output from NESTED_TABLE_LIST w/o CAST
Elapsed CPU time: 323
Output from MEMBER_OF_LIST
Elapsed CPU time: 1062
Output from STATIC_IN_LIST
Elapsed CPU time: 367
Output from STATIC_IN_LIST_SMALL(50)
Elapsed CPU time: 367
10gR2 - 10000 iterations, longer list
Output from NDS_LIST
Elapsed CPU time: 314
Output from NDS_BULK_LIST
Elapsed CPU time: 272
Output from NDS_BULK_LIST2
Elapsed CPU time: 357
Output from DBMS_SQL_LIST
Elapsed CPU time: 485
Output from NESTED_TABLE_LIST
Elapsed CPU time: 369
Output from NESTED_TABLE_LIST - Join
Elapsed CPU time: 363
Output from NESTED_TABLE_LIST w/o CAST
Elapsed CPU time: 331
Output from MEMBER_OF_LIST
Elapsed CPU time: 1054
Output from STATIC_IN_LIST
Elapsed CPU time: 374
Output from STATIC_IN_LIST_SMALL(50)
Elapsed CPU time: 381
10gR2 - 10000 iterations
Output from NDS_LIST
Elapsed CPU time: 2700
Output from NDS_BULK_LIST
Elapsed CPU time: 2203
Output from NDS_BULK_LIST2
Elapsed CPU time: 2902
Output from DBMS_SQL_LIST
Elapsed CPU time: 2523
Output from NESTED_TABLE_LIST
Elapsed CPU time: 3387
Output from NESTED_TABLE_LIST - Join
Elapsed CPU time: 3333
Output from NESTED_TABLE_LIST w/o CAST
Elapsed CPU time: 3482
Output from MEMBER_OF_LIST
Elapsed CPU time: 11141
Output from STATIC_IN_LIST
Elapsed CPU time: 4012
Output from STATIC_IN_LIST_SMALL(50)
Elapsed CPU time: 4413
*/
END in_clause;
Package Body created.