Using Collections in PLSQL | Oracle Database 10g SQL (Osborne ORACLE Press Series)

Using Collections in PL/SQL

You can use collections in PL/SQL. In this section, you ll see how to perform the following tasks in PL/SQL:

Manipulate varrays
Manipulate nested tables
Use collection methods

Manipulating Varrays

In this section, you ll see a package named varray_package that contains the following items:

A function named get_customers() that returns the rows in the customers_with_varray table.
A procedure named insert_customer() that adds a row to the customers_with_varray table.

The collection_user.sql script contains the following package specification and body:

 CREATE OR REPLACE PACKAGE varray_package AS  TYPE ref_cursor_typ IS REF CURSOR;  FUNCTION get_customers RETURN ref_cursor_typ;  PROCEDURE insert_customer (p_id IN customers_with_varray.id%TYPE,  p_first_name IN customers_with_varray.first_name%TYPE,  p_last_name IN customers_with_varray.last_name%TYPE,  p_addresses IN customers_with_varray.addresses%TYPE); END varray_package; / CREATE OR REPLACE PACKAGE BODY varray_package AS  FUNCTION get_customers  RETURN ref_cursor_typ IS  customers_ref_cursor ref_cursor_typ;  BEGIN  -- get the REF CURSOR  OPEN customers_ref_cursor FOR  SELECT *  FROM customers_with_varray;  -- return the REF CURSOR  RETURN customers_ref_cursor;  END get_customers;  PROCEDURE insert_customer (p_id IN customers_with_varray.id%TYPE,  p_first_name IN customers_with_varray.first_name%TYPE,  p_last_name IN customers_with_varray.last_name%TYPE,  p_addresses IN customers_with_varray.addresses%TYPE) IS  BEGIN  INSERT INTO customers_with_varray  VALUES (p_id, p_first_name, p_last_name, p_addresses);  COMMIT;  EXCEPTION  WHEN OTHERS THEN  ROLLBACK;  END insert_customer; END varray_package; /

Note

varray_package (and the other packages you ll see in this chapter) is created by collection_schema.sql , so to follow along with the examples you must have already run this script and then you connect to the database as collection_user with a password of collection_password . The package is not created by collection_schema_9i.sql , so don t try and run the package while connected as collection_user2 as it won t work!

The following example calls varray_package.insert_customer() to add a new row to the customers_with_varray table:

 CALL varray_package.insert_customer(2, 'James', 'Red',  varray_address_typ('10 Main Street, Green Town, CA, 22212',  '20 State Street, Blue Town, FL, 22213'));

The next example calls varray_package.get_products() to retrieve the rows from customers_with_varray :

  SELECT varray_package.get_customers   FROM dual;  GET_CUSTOMERS -------------------- CURSOR STATEMENT : 1 CURSOR STATEMENT : 1  ID FIRST_NAME LAST_NAME ---------- ---------- ---------- ADDRESSES ------------------------------------------  1 Steve Brown VARRAY_ADDRESS_TYP('2 State Street, Beantown, MA, 12345',  '4 Hill Street, Lost Town, CA, 54321')  2 James Red VARRAY_ADDRESS_TYP('10 Main Street, Green Town, CA, 22212',  '20 State Street, Blue Town, FL, 22213')

Manipulating Nested Tables

In this section, you ll see a package named nested_table_package that contains the following items:

A function named get_customers() that returns the rows in the table customers_with_nested_table
A procedure named insert_customer() that adds a row to the table customers_with_nested_table

The collection_user.sql script contains the following package specification and body:

 CREATE OR REPLACE PACKAGE nested_table_package AS  TYPE ref_cursor_typ IS REF CURSOR;  FUNCTION get_customers RETURN ref_cursor_typ;  PROCEDURE insert_customer (p_id IN customers_with_nested_table.id%TYPE,  p_first_name IN customers_with_nested_table.first_name%TYPE,  p_last_name IN customers_with_nested_table.last_name%TYPE,  p_addresses IN customers_with_nested_table.addresses%TYPE); END nested_table_package; / CREATE OR REPLACE PACKAGE BODY nested_table_package AS  FUNCTION get_customers  RETURN ref_cursor_typ IS  customers_ref_cursor ref_cursor_typ;  BEGIN  -- get the REF CURSOR  OPEN customers_ref_cursor FOR  SELECT *  FROM customers_with_nested_table;  -- return the REF CURSOR  RETURN customers_ref_cursor;  END get_customers;  PROCEDURE insert_customer (p_id IN customers_with_nested_table.id%TYPE,  p_first_name IN customers_with_nested_table.first_name%TYPE,  p_last_name IN customers_with_nested_table.last_name%TYPE,  p_addresses IN customers_with_nested_table.addresses%TYPE) IS  BEGIN  INSERT INTO customers_with_nested_table  VALUES (p_id, p_first_name, p_last_name, p_addresses);  COMMIT;  EXCEPTION  WHEN OTHERS THEN  ROLLBACK;  END insert_customer; END nested_table_package; /

The following example calls nested_table_package.insert_customer() to add a new row to customers_with_nested_table :

 CALL nested_table_package.insert_customer(2, 'James', 'Red',  nested_table_address_typ(address_typ('10 Main Street', 'Green Town', 'CA', '22212'),  address_typ('20 State Street', 'Blue Town', 'FL', '22213')));

The next example calls nested_table_package.get_products() to retrieve the rows from customers_with_nested_table :

  SELECT nested_table_package.get_customers   FROM dual;  GET_CUSTOMERS -------------------- CURSOR STATEMENT : 1 CURSOR STATEMENT : 1  ID FIRST_NAME LAST_NAME ---------- ---------- ---------- ADDRESSES(STREET, CITY, STATE, ZIP) ------------------------------------------------------------  1 Steve Brown NESTED_TABLE_ADDRESS_TYP(ADDRESS_TYP('2 State Street', 'Beantown', 'MA', '12345'),  ADDRESS_TYP('4 Hill Street', 'Lost Town', 'CA', '54321'))  2 James Red NESTED_TABLE_ADDRESS_TYP(ADDRESS_TYP('10 Main Street', 'Green Town', 'CA', '22212'),  ADDRESS_TYP('20 State Street', 'Blue Town', 'FL', '22213'))

Collection Methods

In this section, you ll see some of the methods you can use with collections. Table 13-3 summarizes the collection methods.

Table 13-3: Collection Methods
Method	Description
COUNT	Returns the number of elements in the collection.
DELETE DELETE( n ) DELETE( n , m )	Removes elements from a collection. There are three forms of DELETE : DELETE removes all elements. DELETE( n ) removes the n th element. DELETE( n , m ) removes elements n through m .
EXISTS( n )	Returns true if the n th element in a collection exists.
EXTEND EXTEND( n ) EXTEND( n , m )	Increases the size of a collection. There are three forms of EXTEND : EXTEND adds one element, which is set to null. EXTEND( n ) adds n elements, which are set to null. EXTEND( n , m ) adds n elements, which are set to m .
FIRST	Returns the first (smallest) index number in a collection. If the collection is empty, FIRST returns null.
LAST	Returns the last (greatest) index number in a collection. If the collection is empty, LAST returns null.
NEXT( n )	Returns the index number of the element after n . If there are no elements after n , NEXT returns null.
PRIOR( n )	Returns the index number of the element before n . If there are no elements before n , PRIOR returns null.
TRIM TRIM( n )	Removes elements from the end of a collection. There are two forms of TRIM : TRIM removes one element from the end. TRIM( n ) removes n elements from the end.

In the following sections, you ll see the use of the methods shown in Table 13-3.

Note	The following sections will use a package named collection_method_examples that is created by the collection_schema.sql script. You ll see the individual methods defined in this package in the following sections.

Using COUNT

You use COUNT to get the number of elements in the collection. The following initialize_addresses() function is defined in the collection_method_examples package and performs the following tasks:

Accepts a parameter named id_par that specifies the ID of a row in the table customers_with_nested_table to retrieve
Declares an object named addresses_var of type nested_table_address_typ
Retrieves the addresses column from customers_with_nested_table into addresses_var

Displays the number of elements in addresses_var using COUNT

 FUNCTION initialize_addresses(id_par customers_with_nested_table.id%TYPE) RETURN nested_table_address_typ IS  addresses_var nested_table_address_typ; BEGIN  DBMS_OUTPUT.PUT_LINE('Initializing addresses');  SELECT addresses  INTO addresses_var  FROM customers_with_nested_table  WHERE id = id_par;  DBMS_OUTPUT.PUT_LINE('Number of addresses = ' addresses_var.COUNT);  RETURN addresses_var; END initialize_addresses;

The following example connects as collection_user , turns the server output on, and calls collection_method_examples.initialize_addresses() :

  CONNECT collection_user/collection_password   SET SERVEROUTPUT ON   SELECT collection_method_examples.initialize_addresses(1) addresses   FROM dual;  ADDRESSES(STREET, CITY, STATE, ZIP) --------------------------------------------------------- NESTED_TABLE_ADDRESS_TYP(ADDRESS_TYP('2 State Street', 'Beantown', 'MA', '12345'),  ADDRESS_TYP('4 Hill Street', 'Lost Town', 'CA', '54321'))

The following display_addresses() procedure performs the following tasks:

Accepts a parameter named addresses_par of type nested_table_address_typ that contains a list of addresses
Declares an object named addresses_var of type nested_table_address_typ
Displays the number of addresses in address_var using COUNT

Uses a FOR loop to display the addresses in address_var

 PROCEDURE display_addresses(addresses_par nested_table_address_typ) IS  count_var INTEGER; BEGIN  DBMS_OUTPUT.PUT_LINE('Current number of addresses = ' addresses_par.COUNT);  FOR count_var IN 1..addresses_par.COUNT LOOP  DBMS_OUTPUT.PUT_LINE('Address #'  count_var  ':');  DBMS_OUTPUT.PUT(addresses_par(count_var).street  ', ');  DBMS_OUTPUT.PUT(addresses_par(count_var).city  ', ');  DBMS_OUTPUT.PUT(addresses_par(count_var).state  ', ');  DBMS_OUTPUT.PUT_LINE(addresses_par(count_var).zip);  END LOOP; END display_addresses;

You ll see the use of collection_method_examples.display_addresses() shortly.

Using DELETE

You use DELETE to remove elements from a collection. The following delete_address() procedure performs the following tasks:

Accepts a parameter named address_num_par that specifies the position of the address to remove
Declares an object named addresses_var of type nested_table_address_typ
Calls initialize_addresses() to populate addresses_var with the addresses of customer #1
Displays the addresses in addresses_var using display_addresses()
Removes the address specified by address_num_par from addresses_var using DELETE

Displays the addresses in addresses_var again using display_addresses()

 PROCEDURE delete_address(address_num_par INTEGER) IS  addresses_var nested_table_address_typ; BEGIN  addresses_var := initialize_addresses(1);  display_addresses(addresses_var);  DBMS_OUTPUT.PUT_LINE('Deleting address #'  address_num_par);  addresses_var.DELETE(address_num_par);  display_addresses(addresses_var); END delete_address;

The following example calls collection_method_examples.delete_address(2) to remove address #2:

  CALL collection_method_examples.delete_address(2);  Initializing addresses Number of addresses = 2 Current number of addresses = 2 Address #1: 2 State Street, Beantown, MA, 12345 Address #2: 4 Hill Street, Lost Town, CA, 54321 Deleting address #2 Current number of addresses = 1 Address #1: 2 State Street, Beantown, MA, 12345

Using EXTEND

You use EXTEND to add elements to the end of a collection. The following extend_addresses() procedure performs the following tasks:

Declares an object named addresses_var of type nested_table_address_typ
Calls initialize_addresses() to populate addresses_var with the addresses of customer #1
Displays the addresses in addresses_var using display_addresses()
Copies address #1 twice to the end of addresses_var using EXTEND

Displays the addresses in addresses_var again using display_addresses()

 PROCEDURE extend_addresses IS  addresses_var nested_table_address_typ; BEGIN  addresses_var := initialize_addresses(1);  display_addresses(addresses_var);  DBMS_OUTPUT.PUT_LINE('Extending addresses');  addresses_var.EXTEND(2, 1);  display_addresses(addresses_var); END extend_addresses;

The following example calls collection_method_examples.extend_addresses() :

  CALL collection_method_examples.extend_addresses();  Initializing addresses Number of addresses = 2 Current number of addresses = 2 Address #1: 2 State Street, Beantown, MA, 12345 Address #2: 4 Hill Street, Lost Town, CA, 54321 Extending addresses Current number of addresses = 4 Address #1: 2 State Street, Beantown, MA, 12345 Address #2: 4 Hill Street, Lost Town, CA, 54321 Address #3: 2 State Street, Beantown, MA, 12345 Address #4: 2 State Street, Beantown, MA, 12345

Using FIRST

You use FIRST to get the first (smallest) index number in a collection. If the collection is empty, FIRST returns null. The following first_address() procedure performs the following tasks:

Declares an object named addresses_var of type nested_table_address_typ
Calls initialize_addresses() to populate addresses_var with the addresses of customer #1
Displays the index of the first address in addresses_var using FIRST
Removes address #1 from addresses_var using DELETE

Displays the index of the first address in addresses_var again using FIRST

 PROCEDURE first_address IS  addresses_var nested_table_address_typ; BEGIN  addresses_var := initialize_addresses(1);  DBMS_OUTPUT.PUT_LINE('First address = '  addresses_var.FIRST);  DBMS_OUTPUT.PUT_LINE('Deleting address #1');  addresses_var.DELETE(1);  DBMS_OUTPUT.PUT_LINE('First address = '  addresses_var.FIRST); END first_address;

The following example calls collection_method_examples.first_address() :

  CALL collection_method_examples.first_address();  Initializing addresses Number of addresses = 2 First address = 1 Deleting address #1 First address = 2

Using LAST

You use LAST to get the last (greatest) index number in a collection. If the collection is empty, LAST returns null. The following last_address() procedure performs the following tasks:

Declares an object named addresses_var of type nested_table_address_typ
Calls initialize_addresses() to populate addresses_var with the addresses of customer #1
Displays the index of the last address in addresses_var using LAST
Removes address #2 from addresses_var using DELETE

Displays the index of the last address in addresses_var again using LAST

 PROCEDURE last_address IS  addresses_var nested_table_address_typ; BEGIN  addresses_var := initialize_addresses(1);  DBMS_OUTPUT.PUT_LINE('Last address = '  addresses_var.LAST);  DBMS_OUTPUT.PUT_LINE('Deleting address #2');  addresses_var.DELETE(2);  DBMS_OUTPUT.PUT_LINE('Last address = '  addresses_var.LAST); END last_address;

The following example calls collection_method_examples.last_address() :

  CALL collection_method_examples.last_address();  Initializing addresses Number of addresses = 2 Last address = 2 Deleting address #2 Last address = 1

Using NEXT

You use NEXT( n ) to get the index number of the element after n . If there are no elements after n , NEXT returns null.

The following next_address() procedure performs the following tasks:

Declares an object named addresses_var of type nested_table_address_typ
Calls initialize_addresses() to populate addresses_var with the addresses of customer #1
Displays the index of the next address after #1 in addresses_var using NEXT

Displays the index of the next address after #2 in addresses_var using NEXT , which is null since there is no address after #2

 PROCEDURE next_address IS  addresses_var nested_table_address_typ; BEGIN  addresses_var := initialize_addresses(1);  DBMS_OUTPUT.PUT_LINE('addresses_var.NEXT(1) = '  addresses_var.NEXT(1));  DBMS_OUTPUT.PUT_LINE('addresses_var.NEXT(2) = '  addresses_var.NEXT(2)); END next_address;

The following example calls collection_method_examples.next_address(); notice addresses_var.NEXT(2) is null:

  CALL collection_method_examples.next_address();  Initializing addresses Number of addresses = 2 addresses_var.NEXT(1) = 2 addresses_var.NEXT(2) =

Using PRIOR

You use PRIOR( n ) to get the index number of the element before n . If there are no elements before n , PRIOR returns null.

The following prior_address() procedure performs the following tasks:

Declares an object named addresses_var of type nested_table_address_typ
Calls initialize_addresses() to populate addresses_var with the addresses of customer #1
Displays the index of the address before #2 in addresses_var using PRIOR

Displays the index of the address before #1 in addresses_var using NEXT , which is null since there is no address before #1

 PROCEDURE prior_address IS  addresses_var nested_table_address_typ; BEGIN  addresses_var := initialize_addresses(1);  DBMS_OUTPUT.PUT_LINE('addresses_var.PRIOR(2) = '  addresses_var.PRIOR(2));  DBMS_OUTPUT.PUT_LINE('addresses_var.PRIOR(1) = '  addresses_var.PRIOR(1)); END prior_address;

The following example calls collection_method_examples.prior_address(); notice addresses_var.PRIOR(1) is null:

  CALL collection_method_examples.prior_address();  Initializing addresses Number of addresses = 2 addresses_var.PRIOR(2) = 1 addresses_var.PRIOR(1) =

Using TRIM

You use TRIM to remove elements from the end of a collection. The following trim_addresses() procedure performs the following tasks:

Declares an object named addresses_var of type nested_table_address_typ
Calls initialize_addresses() to populate addresses_var with the addresses of customer #1
Displays the addresses in addresses_var using display_addresses()
Copies address #1 three times to the end of addresses_var using EXTEND
Displays the addresses in addresses_var again using display_addresses()
Removes two addresses from the end of addresses_var using TRIM

Displays the addresses in addresses_var again using display_addresses()

 PROCEDURE trim_addresses IS  addresses_var nested_table_address_typ; BEGIN  addresses_var := initialize_addresses(1);  display_addresses(addresses_var);  DBMS_OUTPUT.PUT_LINE('Extending addresses');  addresses_var.EXTEND(3, 1);  display_addresses(addresses_var);  DBMS_OUTPUT.PUT_LINE('Trimming 2 addresses from end');  addresses_var.TRIM(2);  display_addresses(addresses_var); END trim_addresses;

The following example calls collection_method_examples.trim_addresses() :

  CALL collection_method_examples.trim_addresses();  Initializing addresses Number of addresses = 2 Current number of addresses = 2 Address #1: 2 State Street, Beantown, MA, 12345 Address #2: 4 Hill Street, Lost Town, CA, 54321 Extending addresses Current number of addresses = 5 Address #1: 2 State Street, Beantown, MA, 12345 Address #2: 4 Hill Street, Lost Town, CA, 54321 Address #3: 2 State Street, Beantown, MA, 12345 Address #4: 2 State Street, Beantown, MA, 12345 Address #5: 2 State Street, Beantown, MA, 12345 Trimming 2 addresses from end Current number of addresses = 3 Address #1: 2 State Street, Beantown, MA, 12345 Address #2: 4 Hill Street, Lost Town, CA, 54321 Address #3: 2 State Street, Beantown, MA, 12345