Defines a new object type body.

Syntax

CREATE [ OR REPLACE ] TYPE BODY name
  { IS | AS }
 method_spec [...]
END

Where method_spec is:

subprogram_spec

and subprogram_spec is:

  { MEMBER | STATIC }
  { PROCEDURE proc_name
      [ ( [ SELF [ IN | IN OUT ] name ]
          [, argname [ IN | IN OUT | OUT ] argtype
                     [ DEFAULT value ]
          ] ...)
      ]
  { IS | AS }
 program_body
    END;
  |
    FUNCTION func_name
      [ ( [ SELF [ IN | IN OUT ] name ]
          [, argname [ IN | IN OUT | OUT ] argtype
                     [ DEFAULT value ]
          ] ...)
      ]
    RETURN rettype
  { IS |AS }
 program_body
    END;
  }

Description

Use CREATE TYPE BODY to define a new object type body. Use CREATE OR REPLACE TYPE BODY to either create a new object type body, or replace an existing body.

If a schema name is included, the object type body is created in the specified schema. Otherwise, the object type body is created in the current schema. The name of the new object type body must match an existing object type specification in the same schema. The new object type body name must not match any existing object type body in the same schema unless you want to update the definition of an existing object type body. In which case, you can use CREATE OR REPLACE TYPE BODY.

Parameters

Parameter Description
name The name of the object type for which a body is to be created. The name may be optional and schema-qualified.
MEMBER | STATIC Specify MEMBER if the subprogram runs on an object instance. Specify STATIC if the subprogram runs independently of any particular object instance.
proc_name The name of the procedure to create.
SELF [ IN | IN OUT ] name For a member method, there is an implicit and built-in parameter named SELF. The data type of this parameter is the data type of the object type that is defined. SELF refers to the object instance that is invoking the method. SELF can be explicitly declared as an IN or IN OUT parameter in the parameter list. If explicitly declared, the SELF parameter must be the first in the parameter list. If the SELF parameter is not explicitly declared, the default parameter mode is IN OUT for member procedures and IN for member functions.
argname The name of an argument. The argument is referenced by this name within the method body.
argtype The data type(s) of the arguments. The argument types may be a base data type or a user-defined type such as a nested table or an object type. The basic data type cannot be specified a length. For example, you must specify VARCHAR2 instead of VARCHAR2(10) as the data type.
DEFAULT value This parameter provides a default value for an input argument if no default value is provided in the method call. DEFAULT may not be specified for arguments with the IN OUT or OUT modes.
program_body The pragma, declarations, and SPL statements that comprise the body of the function or procedure. The pragma can be PRAGMA AUTONOMOUS_TRANSACTION to set the function or procedure as an autonomous transaction.
func_name The name of the function to create.
rettype The data type returned. It can be any of the types listed for argtype. For argtype, a length must not be specified for rettype.

Example

The following example shows how to create the object type body for the emp_obj_typ object type that is created by the CREATE TYPE command.

CREATE OR REPLACE TYPE BODY emp_obj_typ AS
    MEMBER PROCEDURE display_emp (SELF IN OUT emp_obj_typ)
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Employee No   : ' || empno);
        DBMS_OUTPUT.PUT_LINE('Name          : ' || ename);
        DBMS_OUTPUT.PUT_LINE('Street        : ' || addr.street);
        DBMS_OUTPUT.PUT_LINE('City/State/Zip: ' || addr.city || ', ' ||
            addr.state || ' ' || LPAD(addr.zip,5,'0'));
    END;
END;

The following example shows how to create the object type body for the dept_obj_typ object type that is created by the CREATE TYPE command.

CREATE OR REPLACE TYPE BODY dept_obj_typ AS
    STATIC FUNCTION get_dname (p_deptno IN NUMBER) RETURN VARCHAR2
    IS
        v_dname     VARCHAR2(14);
    BEGIN
        CASE p_deptno
            WHEN 10 THEN v_dname := 'ACCOUNING';
            WHEN 20 THEN v_dname := 'RESEARCH';
            WHEN 30 THEN v_dname := 'SALES';
            WHEN 40 THEN v_dname := 'OPERATIONS';
            ELSE v_dname := 'UNKNOWN';
        END CASE;
        RETURN v_dname;
    END;
    MEMBER PROCEDURE display_dept
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Dept No    : ' || SELF.deptno);
        DBMS_OUTPUT.PUT_LINE('Dept Name  : ' ||
            dept_obj_typ.get_dname(SELF.deptno));
    END;
END;