4.1 Defining Object Types
In this section, you will learn how to define object types. The examples here create object types that will be used to store the details of a customer and an address. The address type will be embedded within the customer type, enabling a customer's address to be stored as an attribute of the customer. These types form the basis for all the other examples in this chapter.
When defining an object type, you must define a specification. The specification defines the attributes for the object type. If your object type will contain methods, then the signatures for those methods must also be included in the specification. A method signature is made up of the name of the method and any parameters that will be passed to the method. In addition, if your object type contains methods, you must define a body for the type. The body contains the actual code for the methods.
You use the SQL DDL statements CREATE TYPE and CREATE TYPE BODY to create an object type specification and body. The following CREATE TYPE statement defines the specification for an object type named t_address:
CREATE TYPE t_address AS OBJECT ( street VARCHAR2(15), city VARCHAR2(15), state CHAR(2), zip VARCHAR2(9) ); /
Because the t_address type will not contain any methods, there's no need to create an object body for the type. A body is necessary only when you need to specify code for a method.
In this chapter's examples, the t_customer object type will be used to store customer details. Unlike the t_address type, t_customer implements a method and consequently requires a body. The following CREATE TYPE statement creates the t_customer type. Notice that the t_address type is used to define the address attribute for the customer. The result is an address object nested within each customer object. Also notice that a method signature for the function get_age( ) is included using the MEMBER FUNCTION clause:
CREATE TYPE t_customer AS OBJECT ( id NUMBER, first_name VARCHAR2(10), last_name VARCHAR2(10), dob DATE, phone VARCHAR2(15), address t_address, -- the function get_age( ) returns the age of the customer in years MEMBER FUNCTION get_age RETURN INTEGER ); /
In this example, the get_age( ) function calculates the age, in years, of a customer based on the date of birth stored in the customer's dob attribute. Now, because the specification for t_customer contains a method signature, you must create an object type body using the CREATE TYPE BODY statement. This body must contain the actual code for the get_age( ) function. For example:
CREATE TYPE BODY t_customer AS -- the function get_age( ) returns the age of the customer in years MEMBER FUNCTION get_age RETURN INTEGER IS age INTEGER; BEGIN -- calculate the age in years SELECT ROUND(((sysdate - dob) / 365), 0) INTO age FROM dual; RETURN age; END; END; /
The get_age( ) function shown here calculates the age of the customer in years using the following steps:
-
Subtracts the value stored in the dob attribute from the current date stored in the Oracle SYSDATE variable. The result of this subtraction is the customer's age in days.
-
Divides the result of the subtraction by 365; this gives the customer's age in years. This result is returned as the result of the get_age( ) function.
In this example, the t_customer object type only included a function as a member method. The function was identified using the keywords MEMBER FUNCTION. If you want to write an object type that contains a procedure, you can do so using the MEMBER PROCEDURE clause. The difference is that a MEMBER FUNCTION must return a value, while a MEMBER PROCEDURE does not.
