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;