Oracle8i SQLJ Developer's Guide and Reference Release 3 (8.1.7) Part Number A83723-01 |
Library | Service | Contents | Index

## 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 remainder 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 user-defined types (UDTs) in SQLJ ***/
      /
      /*** 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')

  Note: Use of a table alias, such as `p` above, is a recommended general practice in Oracle SQL, especially in accessing tables with user-defined types. It is required syntax in some cases where object attributes are accessed. Even when not required, it helps in avoiding ambiguities. See the Oracle8i SQL Reference for more information about table aliases.

### Creating Collection Types

There are two categories of collections: 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, as with any database table, can have any number of rows. For example:

    CREATE TYPE person_array AS TABLE OF person;

This command 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)`

- `PERSON` and `ADDRESS` objects (repeating the same definitions used earlier in "Creating Object Types")

- 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

      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 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 TABLE  employees
      ( empnumber            INTEGER PRIMARY KEY,
        person_data     REF  person,
        manager         REF  person,
        office_addr          address,
        salary               NUMBER,
        phone_nums           phone_array
      )
      /

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index