4.5 Working with Objects and Collections

One of the most important advantages of NDS over DBMS_SQL is its support for new Oracle8 datatypes: objects and collections. You don't need to change the structure of the code you write in NDS to use it with objects and collections.

Suppose that I am building an internal administrative system for the national health management corporation Health$.Com. To reduce costs, the system will work in a distributed manner, creating and maintaining separate tables of customer information for each for-profit hospital owned by Health$.Com.

I'll start by defining an object type (person) and VARRAY type (preexisting_conditions), as follows:

CREATE TYPE person AS OBJECT (
   name VARCHAR2(50), dob DATE, income NUMBER);
/

CREATE TYPE preexisting_conditions IS TABLE OF VARCHAR2(25);
/

Once these types are defined, I can build a package to manage my most critical health-related information -- data needed to maximize profits at Health$.Com. Here is the specification:

/* Filename on companion disk: health$.pkg */
CREATE OR REPLACE PACKAGE health$
AS
   PROCEDURE setup_new_hospital (hosp_name IN VARCHAR2);

   PROCEDURE add_profit_source (
      hosp_name IN VARCHAR2, 
      pers IN Person, 
      cond IN preexisting_conditions);

   PROCEDURE weed_out_poor_and_sick (
      hosp_name VARCHAR2,
      min_income IN NUMBER := 100000,
      max_preexist_cond IN INTEGER := 0);

   PROCEDURE show_profit_centers (hosp_name VARCHAR2);
 END health$;
/

With this package, I can do the following:

1. Set up a new hospital, which means create a new table to hold information about that hospital. Here's the implementation from the body:

   FUNCTION tabname (hosp_name IN VARCHAR2) IS
   BEGIN
      RETURN hosp_name || '_profit_center';
   END;
   
   PROCEDURE setup_new_hospital (hosp_name IN VARCHAR2) IS
   BEGIN
      EXECUTE IMMEDIATE 
         'CREATE TABLE ' || tabname (hosp_name) || ' (
            pers Person, 
            cond preexisting_conditions)
            NESTED TABLE cond STORE AS cond_st';
   END;

   TIP: Since preexisting_conditions is a nested table, I must specify the "store table" that will hold it.

2. Add a "profit source" (formerly known as a "patient") to the hospital, including his or her preexisting conditions. Here's the implementation from the body:

   PROCEDURE add_profit_source (
      hosp_name IN VARCHAR2, 
      pers IN Person, 
      cond IN preexisting_conditions)
   IS
   BEGIN
      EXECUTE IMMEDIATE 
         'INSERT INTO ' || tabname (hosp_name) || 
             ' VALUES (:revenue_generator, :revenue_inhibitors)' 
         USING pers, cond;
   END;

   The use of objects and collections is transparent. I could be inserting scalars like numbers and dates, and the syntax and code would be the same.

3. Remove from the hospital all the really poor and sick people, those individuals who are not contributing to the profit margins of my corporation (hey, maybe we shouldn't mix profit margins and health care!). This is the most complex of the programs; here is the implementation:

PROCEDURE weed_out_poor_and_sick (
   hosp_name VARCHAR2,
   min_income IN NUMBER := 100000,
   max_preexist_cond IN INTEGER := 1)
IS
   cv RefCurTyp;
   human Person;
   known_bugs preexisting_conditions;
   
   v_table VARCHAR2(30) := tabname (hosp_name);
   v_rowid ROWID;
BEGIN
   /* Find all rows with more than the specified number
      of preconditions and deny them coverage. */  
   OPEN cv FOR
      'SELECT ROWID, pers, cond
         FROM ' || v_table || ' alias
        WHERE (SELECT COUNT(*) FROM TABLE (alias.cond))
               > ' ||
               max_preexist_cond || 
         ' OR
               alias.pers.income < ' || min_income;        
   LOOP
      FETCH cv INTO v_rowid, human, known_bugs;
      EXIT WHEN cv%NOTFOUND; 
      EXECUTE IMMEDIATE 
         'DELETE FROM ' || v_table ||
         ' WHERE ROWID = :rid'
         USING v_rowid;
   END LOOP;
   CLOSE cv;
END;

TIP: I decided to retrieve the ROWID of each profit source so that when I do the DELETE it would be easy to identify the row. It would be awfully convenient to make the query FOR UPDATE, and then use WHERE CURRENT OF cv in the DELETE statement, but that is not possible, for two reasons: (1) The cursor variable would have to be globally accessible to be referenced inside a dynamic SQL statement, and (2) You cannot declare cursor variable in packages, because they don't have persistent state. See Section 4.6.3, "Dynamic PL/SQL for more details.