|
Many programming techniques use collection types such as arrays, bags, lists, nested tables,
sets, and trees. To support these techniques in database applications, PL/SQL provides the
datatypes TABLE and VARRAY, which allow you to declare index-by tables, nested tables,
and variable-size arrays.
A collection is an ordered group of elements, all of the same type. Each element has a
unique subscript that determines its position in the collection. Collections work like the
arrays found in most third-generation programming languages. Also, collections can be
passed as parameters. So, you can use them to move columns of data into and out of database
tables or between client-side applications and stored subprograms.
Collections are effectively arrays. Similar to LOBs, collections are manipulated by
methods on a collection resource, which is allocated with oci_new_collection() .
In a simple email address book demonstration (created by Charles Poulsen from Oracle),
two VARRAYs are created, one for an array of people’s names, and one for an array of email
addresses. VARRAYs (short for variable-size arrays) use sequential numbers as subscripts to
access a fixed number of elements.
SQL> drop table emails;
SQL> create table emails (
2 user_id varchar2(10),
3 friend_name varchar2(20),
4 email_address varchar2(20));
SQL> create or replace type email_array as
2 varray(100) of varchar2(20);
3 /
SQL> create or replace type friend_array as
2 varray(100) of varchar2(20);
3 /
SQL> create or replace procedure update_address_book(
2 p_user_id in varchar2,
3 p_friend_name friend_array,
4 p_email_addresses email_array)
5 is
6 begin
7 delete from emails where user_id = p_user_id;
8 for i in 1 .. p_email_addresses.count loop
9 insert into emails
10 (user_id, friend_name, email_address)
11 values (p_user_id, p_friend_name(i),
12 p_email_addresses(i));
13 end loop;
14 end update_address_book;
15 /
The update_address_book() procedure loops over all elements of the address collection
and inserts each one.
In PHP we create collection variables and use the append() method to add elements to
each array. By binding as OCI_B_NTY (“Named Type”) we can pass collections to the
PL/SQL procedure arguments. The following PHP code creates a collection of names and a
collection of email addresses. These collections are bound to the arguments of the PL/SQL
address_book() call. When this executes, the names and email addresses are inserted into
the database.
myupdateaddresses.php
<?php
$c = oci_connect('hr', 'hrpwd', '//localhost/XE');
$user_name = 'cjones';
$friends_names = array('alison', 'aslam');
$friends_emails = array('alison@example.com', 'aslam@example.com');
$friend_coll = oci_new_collection($c, 'FRIEND_ARRAY');
$email_coll = oci_new_collection($c, 'EMAIL_ARRAY');
for ($i=0; $i < count($friends_names); $i++) {
$friend_coll->append($friends_names[$i]);
$email_coll->append($friends_emails[$i]);
}
$s = oci_parse($c,
"begin update_address_book(:name, :friends, :emails); end;");
oci_bind_by_name($s, ':name', $user_name);
oci_bind_by_name($s, ':friends', $friend_coll, -1, OCI_B_NTY);
oci_bind_by_name($s, ':emails', $email_coll, -1, OCI_B_NTY);
oci_execute($s);
?>
The emails table now has the inserted data:
SQL> select * from emails;
USER_ID FRIEND_NAME EMAIL_ADDRESS
---------- -------------------- --------------------
cjones alison alison@example.com
cjones aslam aslam@example.com
Other OCI8 collection methods allow accessing or copying data in a collection.
|
