|Oracle9i SQL Reference
Release 1 (9.0.1)
Part Number A90125-01
CREATE TYPE to
DROP ROLLBACK SEGMENT, 3 of 26
BODY to define or implement the member methods defined in the object type specification. You create object types with the
TYPE and the
BODY statements. The
TYPE statement specifies the name of the object type, its attributes, methods, and other properties. The
BODY statement contains the code for the methods in the type.
For each method specified in an object type specification for which you did not specify the call_spec, you must specify a corresponding method body in the object type body.
Every member declaration in the
TYPE specification for object types must have a corresponding construct in the
To create or replace a type body in your own schema, you must have the
TYPE or the
TYPE system privilege. To create an object type in another user's schema, you must have the
TYPE system privileges. To replace an object type in another user's schema, you must have the
TYPE system privileges.
REPLACE to re-create the type body if it already exists. Use this clause to change the definition of an existing type body without first dropping it.
Users previously granted privileges on the re-created object type body can use and reference the object type body without being granted privileges again.
You can use this clause to add new member subprogram definitions to specifications added with the
Specify the schema to contain the type body. If you omit schema, Oracle creates the type body in your current schema.
Specify the name of an object type.
Specify the type of method function or procedure subprogram associated with the object type specification.
You must define a corresponding method name, optional parameter list, and (for functions) a return type in the object type specification for each procedure or function declaration.
Declare a procedure or function subprogram.
Declare the procedure or function.
Specify the call specification ("call spec") that maps a Java or C method name, parameter types, and return type to their SQL counterparts.
The Java_declaration, 'string' identifies the Java implementation of the method.
You can declare either a
MAP method or an
ORDER method, but not both. If you declare either method, you can compare object instances in SQL.
If you do not declare either method, you can compare object instances only for equality or inequality. Instances of the same type definition are equal only if each pair of their corresponding attributes is equal.
MEMBER to declare or implement a member function (
MAP method) that returns the relative position of a given instance in the ordering of all instances of the object. A
MAP method is called implicitly and specifies an ordering of object instances by mapping them to values of a predefined scalar type. PL/SQL uses the ordering to evaluate Boolean expressions and to perform comparisons.
If the argument to the
MAP method is null, the
MAP method returns null and the method is not invoked.
An object type body can contain only one
MAP method, which must be a function. The
MAP function can have no arguments other than the implicit
MEMBER to specify a member function (
ORDER method) that takes an instance of an object as an explicit argument and the implicit
SELF argument and returns either a negative, zero, or positive integer. The negative, positive, or zero indicates that the implicit
SELF argument is less than, equal to, or greater than the explicit argument.
If either argument to the
ORDER method is null, the
ORDER method returns null and the method is not invoked.
When instances of the same object type definition are compared in an
BY clause, Oracle invokes the
An object specification can contain only one
ORDER method, which must be a function having the return type
Declare a function subprogram.
EXTERNAL is an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle Corporation recommends that you use the call_spec syntax with the C_declaration.
Several examples of creating type bodies appear in the "Examples" section of
The following example shows how the type body of the
data_typ object type (see "Object Type Examples") must be modified when an attribute is added to the type:
ALTER TYPE data_typ ADD MEMBER FUNCTION qtr(der_qtr DATE) RETURN CHAR CASCADE; CREATE OR REPLACE TYPE BODY data_typ IS MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS BEGIN RETURN (year + invent); END; MEMBER FUNCTION qtr(der_qtr DATE) RETURN CHAR IS BEGIN IF (der_qtr < TO_DATE('01-APR', 'DD-MON')) THEN RETURN 'FIRST'; ELSIF (der_qtr < TO_DATE('01-JUL', 'DD-MON')) THEN RETURN 'SECOND'; ELSIF (der_qtr < TO_DATE('01-OCT', 'DD-MON')) THEN RETURN 'THIRD'; ELSE RETURN 'FOURTH'; END IF; END; END; / END;