CREATE TYPE BODY v15

Name

CREATE TYPE BODY — Define a new object type body.

Synopsis

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

Where method_spec is:

subprogram_spec

Where 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

CREATE TYPE BODY defines a new object type body. CREATE OR REPLACE TYPE BODY either creates a new object type body or replaces an existing body.

If you include a schema name, then the object type body is created in the specified schema. Otherwise it's 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 can't match any existing object type body in the same schema unless you want to update the definition of an existing object type body. In that case, use CREATE OR REPLACE TYPE BODY.

Parameters

name

The name (optionally schema-qualified) of the object type for which to create a body.

MEMBER

STATIC

Specify MEMBER if the subprogram operates on an object instance. Specify STATIC if the subprogram operates independently of any particular object instance.

proc_name

The name of the procedure to create.

SELF [ IN | IN OUT ] name

A member method has an implicit, built-in parameter named SELF whose data type is the same as the object type being defined. SELF refers to the object instance that's currently invoking the method. You can explicitly declare SELF as an IN or IN OUT parameter in the parameter list. If you explicitly declare it, SELF must be the first parameter in the parameter list. If you don't explicitly declare SELF, its parameter mode defaults to IN OUT for member procedures and IN for member functions.

argname

The name of an argument. The argument is referenced by this name in the method body.

argtype

The data types of the method’s arguments. The argument types can be a base data type or a user-defined type such as a nested table or an object type. Don't specify a length for any base type. For example, specify VARCHAR2, not VARCHAR2(10).

DEFAULT value

Supplies a default value for an input argument if one isn't supplied in the method call. You can't specify DEFAULT for arguments with modes IN OUT or OUT.

program_body

The pragma, declarations, and SPL statements that make up the body of the function or procedure. Use the pragma PRAGMA AUTONOMOUS_TRANSACTION to set the function or procedure as an autonomous transaction.

func_name

The name of the function to create.

rettype

The return data type, which can be any of the types listed for argtype. Don't specify a length for rettype.

Examples

Create the object type body for object type emp_obj_typ:

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;

Create the object type body for object type dept_obj_typ:

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;

See also

CREATE TYPE, DROP TYPE