CREATE TYPE BODY v16
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;