|Oracle8i SQLJ Developer's Guide and Reference
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.
Oracle SQL commands to create object types are of the following form:
CREATE TYPE typename AS OBJECT ( attrname1 datatype1, attrname2 datatype2, ... ... attrnameN datatypeN );
typename is the desired name of your object type,
attrnameN are the desired attribute names, and
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:
employeestable that includes an
ADDRESScolumn and two columns of
/*** 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
Use of a table alias, such as
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:
typename is the desired name of your
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:
Oracle SQL commands to create nested table types are of the following form:
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:
This creates a nested table where each row consists of a
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:
MODULETBL_T, which is a nested table of
projectstable that includes a column of
PARTICIPANT_Treferences and a column of
PHONE_ARRAY, which is a VARRAY of
employeestable, which includes a
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;