Oracle PL/SQL Programming, 5th Edition

Working with Objects and Collections

One of the most important advantages of NDS over DBMS_SQL is its support for 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 nested table type (preexisting_conditions), as follows:

/* File on web: health$.pkg */
CREATE OR REPLACE TYPE person AS OBJECT (
   name VARCHAR2(50), dob DATE, income NUMBER);
/
CREATE OR REPLACE 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:

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 ...