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 ...
Get Oracle PL/SQL Programming, Third Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.