We've already seen an example of how you can use collections as module parameters. Let's take a look at the one subtlety involved if that collection happens to be an associative array.
A collection TYPE is just another type of data to the PL/SQL engine, so it should come as no surprise that you can pass a collection as a parameter in a procedure or a function. With this approach you can, in a single call, pass all the values in a table into the module. In the following package specification I define two modules that pass PL/SQL tables as parameters. The send_promos procedure sends a promotional mailing to all the companies in my table. The companies_overdue function returns a table filled with the names of companies that have overdue bills.
CREATE OR REPLACE PACKAGE company_pkg IS /* Nested table that matches the company table in structure */ TYPE companies_tabtype IS TABLE OF company%ROWTYPE; /* Parameter is a table of company primary keys */ PROCEDURE send_promos (companies_in IN companies_tabtype); /* Function returns a table of company names */ FUNCTION companies_overdue (overdue_date_in IN DATE) RETURN companies_tabtype;END company_pkg;Now that I have a package containing both the table type and the programs referencing those types, I can call these programs. The only tricky part is remembering that you must declare a collection based on the matching type before you can use any of the programs. This step is shown in the following procedure:
CREATE OR REPLACE PROCEDURE send_invoices IS indx PLS_INTEGER; /* Declare a nested table based on the packaged type. */ companies company_pkg. companies_tabtype; BEGIN companies:= company_pkg.companies_overdue (SYSDATE-30); indx:= companies.FIRST; LOOP EXIT WHEN indx IS NULL; send_invoice (companies(indx).company_id); indx:= companies.NEXT (indx); END LOOP;END;11.6.7 PL/SQL-to-Server Integration
To provide another demonstration of how collections can ease the burden of transferring data between the server and the PL/SQL application program, let's look at a new example. The main entity in this example is the apartment complex. We use a nested table of objects to hold the list of apartments for each apartment complex.
Each apartment is described by the following attributes:
CREATE TYPE Apartment_t AS OBJECT (unit_no NUMBER, square_feet NUMBER, bedrooms NUMBER, bathrooms NUMBER, rent_in_dollars NUMBER);We can now define the nested table type that will hold a list of these apartment objects:
CREATE TYPE Apartment_tab_t AS TABLE OF Apartment_t;Using this type as the type of a column, here is the definition of our database table:
CREATE TABLE apartment_complexes (name VARCHAR2(75), landlord_name VARCHAR2(45), apartments Apartment_tab_t)NESTED TABLE apartments STORE AS apartments_store_tab;If you're curious, the INSERT statements to populate such a table look like the following (note the use of nested constructors to create the collection of objects):
INSERT INTO apartment_complexes VALUES ('RIVER OAKS FOUR', 'MR. JOHNSON', Apartment_tab_t(Apartment_t(1, 780, 2, 1, 975), Apartment_t(2, 1200, 3, 2, 1590), Apartment_t(3, 690, 1, 1.5, 800), Apartment_t(4, 690, 1, 2, 450), Apartment_t(5, 870, 2, 2, 990)));INSERT INTO apartment_complexes VALUES ('GALLERIA PLACE', 'MS. DODENHOFF', Apartment_tab_t(Apartment_t(101, 1000, 3, 2, 1295), Apartment_t(102, 800, 2, 1, 995), Apartment_t(103, 800, 2, 1, 995), Apartment_t(201, 920, 3, 1.5, 1195), Apartment_t(202, 920, 3, 1.5, 1195), Apartment_t(205, 1000, 3, 2, 1295)));Now, at last, we can show off some wonderful features of storing collections in the database.
Imagine that we are the new managers of the River Oaks Four apartments and not nearly as committed to affordable housing as the previous manager. We want to demolish any unit that rents for less than $500 and raise the rent on everything else by 15%.
DECLARE /* Declare the cursor that will retrieve the collection of || apartment objects. Since we know we're going to update the || record, we can lock it using FOR UPDATE. */ CURSOR aptcur IS SELECT apartments FROM apartment_complexes WHERE name = 'RIVER OAKS FOUR' FOR UPDATE OF apartments; /* Need a local variable to hold the collection of fetched || apartment objects. */ l_apartments apartment_tab_t; which INTEGER;BEGIN /* A single fetch is all we need! */ OPEN aptcur; FETCH aptcur INTO l_apartments; CLOSE aptcur; /* Iterate over the apartment objects in the collection and || delete any elements of the nested table that meet the || criteria. */ which:= l_apartments.FIRST; LOOP EXIT WHEN which IS NULL; IF l_apartments(which).rent_in_dollars < 500 THEN l_apartments.DELETE(which); END IF; which:= l_apartments.NEXT(which); END LOOP; /* Now iterate over the remaining apartments and raise the || rent. Notice that this code will skip any deleted || elements. */ which:= l_apartments.FIRST; LOOP EXIT WHEN which IS NULL; l_apartments(which).rent_in_dollars:= l_apartments(which).rent_in_dollars * 1.15; which:= l_apartments.NEXT(which); END LOOP; /* Finally, ship the entire apartment collection back to the || server -- in a single statement! */ UPDATE apartment_complexes SET apartments = l_apartments WHERE name = 'RIVER OAKS FOUR'; END;To me, one of the most significant aspects of this example is the single-statement fetch (and store). This PL/SQL fragment emulates the creating of a client-side cache of data, which is an essential concept in many object-oriented and client/server architectures. Using this kind of approach with collections can reduce network traffic and improve the quality of your code.
