You may have worked with collections in SQL and PL/SQL already. Collections are basically arrays holding columns or records of a user-defined data type. They come in various flavors. They can be defined as PL/SQL variables, but also as data types within the database. For example like this:
create type test_coll_type is table of number; /
Imagine you have a procedure receiving such an array filled with id’s as an IN parameter. Within the procedure we need to check whether any of the values in this array exist within a table in the database. One’s first idea to solve this would probably be something like this:
create table x(id number, name varchar2(30)); ID NAME ---------- ------------------------------ 1 James 2 Mark 3 John create or replace procedure who_walks_the_dog1(p_coll test_coll_type) is l_name varchar2(30); begin for i in p_coll.first..p_coll.last loop if p_coll(i) is not null then begin select name into l_name from x where id = p_coll(i); exception when no_data_found then null; end; dbms_output.put_line(l_name||' walks the dog'); end if; end loop; end who_walks_the_dog1; /
Of course that is not very pretty code and it took me longer to write than I initially thought it would take. It also means a lot of calls to the database. That creates a lot of switches between the SQL and PL/SQL engine causing performance degradation.
It would be ideal if we could just take the list of ID’s and use it in a WHERE clause saying “WHERE id in (list of id’s)”.
Here is how you do it. You use the TABLE operator that basically converts a collection to a normal table so you can use the collection in SQL statements. That looks like this:
create or replace procedure who_walks_the_dog2(p_collÂ Â test_coll_type) is type name_tabtyp is table of varchar2(30) index by binary_integer; name_tab name_tabtyp; begin select name bulk collect into name_tab from x where id in (select column_value from table(p_coll)); if name_tab.count > 0 then for i in name_tab.first..name_tab.last loop dbms_output.put_line(name_tab(i)||' walks the dog'); end loop; end if; end who_walks_the_dog2; /
The TABLE operator allows you to write shorter, simpler code that’s more intuitive to read and it should be faster especially when you process a lot of rows. Test it like this.
declare test_coll test_coll_type := test_coll_type(); begin test_coll.extend(5); test_coll(1) := 1; test_coll(2) := 2; who_walks_the_dog2(test_coll); end; /
Collections and the TABLE operator have lots more interesting applications. Perhaps more in the next post.