User-Defined Types in the Database

This section contains examples of creating and using user-defined object types and collection types in Oracle8i. A full SQL script for all the user-defined types employed in the object and collection sample applications is in "Definition of Object and Collection Types".

For more information about any of the SQL commands used here, refer to the Oracle8i SQL Reference.

Creating Object Types

Oracle SQL commands to create object types are of the following form:

CREATE TYPE typename AS OBJECT
( 
  attrname1    datatype1,
  attrname2    datatype2,
  ...         ...
  attrnameN    datatypeN
);

Where typename is the desired name of your object type, attrname1 through attrnameN are the desired attribute names, and datatype1 through datatypeN are the attribute datatypes.

The rest of this section provides an example of creating user-defined object types in Oracle8i.

The following items are created using the SQL script below:

• two object types, PERSON and ADDRESS

• a typed table for PERSON objects

• an employees table that includes an ADDRESS column and two columns of PERSON references

  /*** Using UDTs in SQLJ ***/
  SET ECHO ON;
  /**
  /*** Clean up in preparation ***/
  DROP TABLE EMPLOYEES
  /
  DROP TABLE PERSONS
  /
  DROP TYPE PERSON FORCE
  /
  DROP TYPE ADDRESS FORCE
  /
  /*** Create ADDRESS UDT ***/
  CREATE TYPE ADDRESS AS OBJECT
  ( 
    street        VARCHAR(60),
    city          VARCHAR(30),
    state         CHAR(2),
    zip_code      CHAR(5)
  )
  /
  /*** Create PERSON UDT containing an embedded ADDRESS UDT ***/
  CREATE TYPE PERSON AS OBJECT
  ( 
    name    VARCHAR(30),
    ssn     NUMBER,
    addr    ADDRESS
  )
  /
  /*** Create a typed table for PERSON objects ***/
  CREATE TABLE persons OF PERSON
  /
  /*** Create a relational table with two columns that are REFs 
       to PERSON objects, as well as a column which is an Address ADT. ***/
  CREATE TABLE  employees
  ( 
    empnumber            INTEGER PRIMARY KEY,
    person_data     REF  PERSON,
    manager         REF  PERSON,
    office_addr          ADDRESS,
    salary               NUMBER
  )
  /*** Insert some data--2 objects into the persons typed table ***/
  INSERT INTO persons VALUES (
              PERSON('Wolfgang Amadeus Mozart', 123456,
                 ADDRESS('Am Berg 100', 'Salzburg', 'AT','10424')))
  /
  INSERT INTO persons VALUES (
              PERSON('Ludwig van Beethoven', 234567,
                 ADDRESS('Rheinallee', 'Bonn', 'DE', '69234')))
  /
  /** Put a row in the employees table **/
  INSERT INTO employees (empnumber, office_addr, salary) VALUES (
              1001,
              ADDRESS('500 Oracle Parkway', 'Redwood Shores', 'CA', '94065'),
              50000)
  /
  /** Set the manager and PERSON REFs for the employee **/
  UPDATE employees 
     SET manager =  
         (SELECT REF(p) FROM persons p WHERE p.name = 'Wolfgang Amadeus Mozart')
  /
  UPDATE employees 
     SET person_data =  
         (SELECT REF(p) FROM persons p WHERE p.name = 'Ludwig van Beethoven')
  /
  COMMIT
  /
  QUIT
  
  

Creating Collection Types

There are two categories of collections you can define: variable-length arrays (VARRAYs) and nested tables.

Oracle SQL commands to create VARRAY types are of the following form:

CREATE TYPE typename IS VARRAY(n) OF datatype;

Where typename is the desired name of your VARRAY type, n is the desired maximum number of elements in the array, and datatype is the datatype of the array elements. You must specify the maximum number of elements in the array. For example:

CREATE TYPE myvarr IS VARRAY(10) OF INTEGER;

Oracle SQL commands to create nested table types are of the following form:

CREATE TYPE typename AS TABLE OF datatype;

Where typename is the desired name of your nested table type and datatype is the datatype of the table elements (this can be a user-defined type as well as a standard datatype). A nested table is limited to one column, although that one column type can be a complex object with multiple attributes. The nested table, like any database table, can have any number of rows. For example:

CREATE TYPE person_array AS TABLE OF person;

This creates a nested table where each row consists of a person object.

The rest of this section provides an example of creating a user-defined collection type (as well as object types) in Oracle8i.

The following items are created and populated using the SQL script below:

• two object types, PARTICIPANT_T and MODULE_T

• a collection type, MODULETBL_T, which is a nested table of MODULE_T objects

• a projects table that includes a column of PARTICIPANT_T references and a column of MODULETBL_T nested tables

• a collection type PHONE_ARRAY, which is a VARRAY of VARCHAR2(30)

• an employees table, which includes a PHONE_ARRAY column

  Rem This is a SQL*Plus script used to create schema to demonstrate collection 
  Rem manipulation in SQLJ 
  
  DROP TABLE projects
  /
  DROP TABLE employee
  /
  DROP TYPE MODULETBL_T 
  /
  DROP TYPE MODULE_T 
  /
  DROP TYPE PARTICIPANT_T 
  /
  DROP TYPE PHONE_ARRAY
  /
  CREATE TYPE PARTICIPANT_T AS OBJECT (
    empno   NUMBER(4),
    ename   VARCHAR2(20),
    job     VARCHAR2(12),
    mgr     NUMBER(4),
    hiredate DATE,
    sal      NUMBER(7,2),
    deptno   NUMBER(2)) 
  /
  show errors 
  CREATE TYPE MODULE_T  AS OBJECT (
    module_id  NUMBER(4),
    module_name VARCHAR2(20), 
    module_owner REF PARTICIPANT_T, 
    module_start_date DATE, 
    module_duration NUMBER )
  /
  show errors 
  create TYPE MODULETBL_T AS TABLE OF MODULE_T;
  /
  show errors 
  CREATE TABLE projects (
    id NUMBER(4),
    name VARCHAR(30),
    owner REF PARTICIPANT_T,
    start_date DATE,
    duration NUMBER(3),
    modules  MODULETBL_T  ) NESTED TABLE modules STORE AS modules_tab ;
  
  show errors 
  CREATE TYPE PHONE_ARRAY IS VARRAY (10) OF varchar2(30)
  /
  
  CREATE TABLE  employees
  ( empnumber            INTEGER PRIMARY KEY,
    person_data     REF  person,
    manager         REF  person,
    office_addr          address,
    salary               NUMBER,
    phone_nums           phone_array
  )
  /
  commit;
  exit;
  
  

---