Oracle PL/SQL Programming, Third Edition

Working with Objects and Collections

One of the most important advantages of NDS over DBMS_SQL is its support for post-Oracle7 datatypes, such as objects and collections. You don’t need to change the structure of the code you write in NDS to use it with these datatypes.

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:

/* File on web: 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 minimize_risk  (
      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:

• Set up a new hospital, which means create a new table to hold information ...