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:
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;
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.