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
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')
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'))
In this section, you ll see some of the methods you can use with collections. Table 13-3 summarizes the 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 :
|
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 :
|
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 :
|
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. |
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.
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
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
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
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
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) =
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) =
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