SQL Statements: CREATE SYNONYM to DROP ROLLBACK SEGMENT, 8 of 31

CREATE TYPE BODY

Purpose

Use the CREATE TYPE BODY to define or implement the member methods defined in the object type specification. You create object types with the CREATE TYPE and the CREATE TYPE BODY statements. The CREATE TYPE statement specifies the name of the object type, its attributes, methods, and other properties. The CREATE TYPE 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.

See Also: CREATE TYPE and ALTER TYPE for information on creating and modifying a type specification

Prerequisites

Every member declaration in the CREATE TYPE specification for object types must have a corresponding construct in the CREATE TYPE or CREATE TYPE BODY statement.

To create or replace a type body in your own schema, you must have the CREATE TYPE or the CREATE ANY TYPE system privilege. To create an object type in another user's schema, you must have the CREATE ANY TYPE system privileges. To replace an object type in another user's schema, you must have the DROP ANY TYPE system privileges.

Syntax

procedure_declaration | function_declaration::=

call_spec::=

Java_declaration::=

C_declaration::=

Keywords and Parameters

`OR` `REPLACE`

Specify OR 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 ALTER TYPE ... REPLACE statement.

`schema`

Specify the schema to contain the type body. If you omit schema, Oracle creates the type body in your current schema.

`type_name`

Specify the name of an object type.

`IS` | `AS`

MEMBER | STATIC

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.

procedure_declaration

Declare a procedure subprogram.

function_declaration

Declare a function subprogram.

See Also:

- CREATE TYPE for a list of restrictions on user-defined functions

- PL/SQL User's Guide and Reference for information about overloading subprogram names within a package

- CREATE PROCEDURE , CREATE FUNCTION , and Oracle8i Application Developer's Guide - Fundamentals

`MAP` | `ORDER`

MAP MEMBER

Specify MAP 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 SELF argument.

ORDER MEMBER

Specify ORDER 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 ORDER BY clause, Oracle invokes the order method function_spec.

An object specification can contain only one ORDER method, which must be a function having the return type NUMBER.

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.

procedure_declaration | function_declaration

Declare a procedure or function subprogram. The RETURN clause is valid only for a function. The syntax shown is an abbreviated form.

See Also: CREATE PROCEDURE and CREATE FUNCTION for the full syntax with all possible clauses

pl/sql_block

Declare the procedure or function.

See Also: PL/SQL User's Guide and Reference

call_spec

Specify the call specification ("call spec") that maps a Java or C method name, parameter types, and return type to their SQL counterparts.
In Java_declaration, 'string' identifies the Java implementation of the method.

See Also:

- Oracle8i Java Stored Procedures Developer's Guide

- Oracle8i Application Developer's Guide - Fundamentals for an explanation of the parameters and semantics of the C_declaration

AS EXTERNAL

AS 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.

Examples

Creating a Type Body Example

The following object type body implements member subprograms for rational. (PL/SQL is shown in italics.)

CREATE TYPE BODY rational
  IS 
   MAP MEMBER FUNCTION rat_to_real RETURN REAL IS
      BEGIN 
         RETURN numerator/denominator;  
      END; 

   MEMBER PROCEDURE normalize IS
      gcd NUMBER := integer_operations.greatest_common_divisor 
                     (numerator, denominator);
      BEGIN
         numerator := numerator/gcd;
         denominator := denominator/gcd;
      END;

   MEMBER FUNCTION plus(x rational) RETURN rational IS
      r rational := rational_operations.make_rational
                      (numerator*x.denominator + 
                       x.numerator*denominator,
                       denominator*x.denominator);
      BEGIN
         RETURN r;
      END;

   END;

`MEMBER` \| `STATIC`	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.
	`procedure_declaration`	Declare a procedure subprogram.
	`function_declaration`	Declare a function subprogram.
	See Also: - CREATE TYPE for a list of restrictions on user-defined functions - PL/SQL User's Guide and Reference for information about overloading subprogram names within a package - CREATE PROCEDURE , CREATE FUNCTION , and Oracle8i Application Developer's Guide - Fundamentals

`MAP` `MEMBER`	Specify `MAP` `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 `SELF` argument.
`ORDER` `MEMBER`	Specify `ORDER` `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 `ORDER` `BY` clause, Oracle invokes the order method `function_spec`.
	An object specification can contain only one `ORDER` method, which must be a function having the return type `NUMBER`.
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.
`procedure_declaration` \| `function_declaration`	Declare a procedure or function subprogram. The `RETURN` clause is valid only for a function. The syntax shown is an abbreviated form. See Also: CREATE PROCEDURE and CREATE FUNCTION for the full syntax with all possible clauses
	`pl`/`sql_block`	Declare the procedure or function. See Also: PL/SQL User's Guide and Reference
	`call_spec`	Specify the call specification ("call spec") that maps a Java or C method name, parameter types, and return type to their SQL counterparts. In `Java_declaration,` '`string`' identifies the Java implementation of the method. See Also: - Oracle8i Java Stored Procedures Developer's Guide - Oracle8i Application Developer's Guide - Fundamentals for an explanation of the parameters and semantics of the C_declaration
	`AS` `EXTERNAL`	`AS` `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`.