Creating Object Types

You create an object type using the CREATE [OR REPLACE] TYPE statement. The following example uses the CREATE TYPE statement to create an object type named address_typ . This object type is used to represent an address and contains four attributes named street , city , state , and zip :

 CREATE TYPE address_typ AS OBJECT (street VARCHAR2(15),  city VARCHAR2(15),  state CHAR(2),  zip VARCHAR2(5)); / 

Note  

I ve provided a SQL*Plus script named object_schema.sql in the SQL directory, which creates a user named object_user with a password of object_password . The object_schema.sql script creates the types, and tables, and performs the various INSERT statements shown in this section. You can run the object_schema.sql script if you are using an Oracle8 database or above.

As you can see from the previous example, each attribute is defined using a database type. For example, street is defined as VARCHAR2(15) . As you ll see shortly, the type of an attribute can itself be an object type. You ll notice I add _typ to the end of my object types. You can follow this standard when creating your own object types or you can use your own standard ”just be sure to use your standard consistently.

As I mentioned at the start of this chapter, I m going to represent a person using an object type. The following statement creates an object type named person_typ . Notice that person_typ uses address_typ to define an attribute named address :

 CREATE TYPE person_typ AS OBJECT (id NUMBER,  first_name VARCHAR2(10),  last_name VARCHAR2(10),  dob DATE,  phone VARCHAR2(12),  address address_typ); / 

The next example creates an object type named product_typ that will be used to represent products. Notice that product_typ declares a function named get_sell_by_date() . This function will return the date by which the product must be sold based on the days_valid attribute and the current date:

 CREATE TYPE product_typ AS OBJECT (id NUMBER,  name VARCHAR2(15),  description VARCHAR2(22),  price NUMBER(5, 2),  days_valid NUMBER,  -- declare the get_sell_by_date() member function,  -- get_sell_by_date() returns the date by which the  -- product must be sold  MEMBER FUNCTION get_sell_by_date RETURN DATE); / 

The MEMBER FUNCTION clause is used to declare the get_sell_by_date() function. You can declare a procedure using MEMBER PROCEDURE . A procedure is similar to a function except that a procedure doesn t typically return a value.

Since product_typ contains a method declaration, a body for product_typ must also be created. The body defines the code for the method, and a body is created using the CREATE TYPE BODY statement. The following example creates the body for product_typ . Notice that this body contains the code definition for the get_sell_by_date() method:

 CREATE TYPE BODY product_typ AS  -- define the get_sell_by_date() member function,  -- get_sell_by_date() returns the date by which the  -- product must be sold  MEMBER FUNCTION get_sell_by_date RETURN DATE IS  v_sell_by_date DATE;  BEGIN  -- calculate the sell by date by adding the days_valid attribute  -- to the current date (SYSDATE)  SELECT days_valid + SYSDATE  INTO v_sell_by_date  FROM dual;  -- return the sell by date   RETURN v_sell_by_date;  END; END; / 

As you can see, get_sell_by_date() calculates and returns the date by which the product must be sold by adding the days_valid attribute to the current date. The current date is obtained from the database using SYSDATE .

You can also create a synonym and a public synonym for a type. The following example creates a public synonym named pub_product_typ for product_typ :

 CREATE PUBLIC SYNONYM pub_product_typ FOR product_typ; 

Note  

You must have the CREATE PUBLIC SYNONYM privilege to run this statement.