You can use the CREATE TYPE statement to specify the name, method, and other attributes of a type.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
You can use the CREATE TYPE statement to create or replace the specifications of the following types:
Abstract data type (ADT)
Independent variable array (VARRAY) type
Independent nested table type
Incomplete object type
An incomplete object type is a type created by using a forward type definition. An incomplete object type has a name but no attributes or methods. It can be referenced by other types and can be used to define types that reference each other. However, you must first create a complete type before you use it to create table columns, object columns, or nested table columns.
The CREATE TYPE BODY statement is built in with the code for implementing the type.
Note
- If you create a type whose specification declares only attributes but no methods, you do not need to create the type body.
- You can use the
CREATE TYPEstatement to create nested tables and the VARRAY type, but not associative arrays. You can define three collection types in a PL block or package.
Prerequisites
To create a type in your own schema, you must have the CREATE TYPE system privilege. To create a type in the schema of other users, you must have the CREATE ANY TYPE system privilege. You can obtain these privileges directly or through roles.
The owner of a type must be explicitly granted the EXECUTE object privilege or the EXECUTE ANY TYPE system privilege, to access all other types referenced in the definition of this type. The owner of a type cannot obtain these privileges through roles.
If the owner of a type wants to grant other users the access privilege on this type, the type owner must first be granted the EXECUTE object privilege by using GRANT OPTION, or the EXECUTE ANY TYPE system privilege by using ADMIN OPTION. Otherwise, the type owner does not have sufficient privileges to grant other users the access privilege on this type.
In the current OceanBase Database version, you can define object types, also called user-defined types (UDTs). However, when you use the CREATE TABLE statement to create a table, you cannot use the UDTs to define columns in the table. In other words, you cannot directly use these UDTs as a part of a table schema. For example, you cannot create a column and specify a UDT as the data type of the column.
Though you cannot use UDTs when you create a table, you can still use UDTs to define internal variables, parameters, and return types of functions when you write a PL/SQL program.
Syntax
Note
This topic describes the syntax of some important syntax nodes only.
The syntax of
create_type_stmtis as follows:
The syntax of
plsql_type_spec_sourceis as follows:
The syntax of
object_type_defis as follows:
The syntax of
attr_and_element_specis as follows:
The syntax of
element_specis as follows:
The syntax of
el_element_spec_list_ccis as follows:
The syntax of
element_spec_longis as follows:
The syntax of
inheritance_final_instantiable_clauseis as follows:
The syntax of
inheritance_overriding_instantiable_clauseis as follows:
The syntax of
subprogram_specis as follows:
The syntax of
proc_or_func_specis as follows:
Semantics
| Syntax | Keyword or syntax node | Description |
|---|---|---|
| create_type_stmt | OR REPLACE | Re-creates this type (if any) and recompiles it. Before the type is redefined, users granted the access privilege can still access this type without the need to obtain the access privilege again. If any function-based index depends on this type, the database marks this index as DISABLED. |
| plsql_type_spec_source | pl_schema_name | The name of the schema containing the type. The default value is your own schema. |
| plsql_type_spec_source | TYPE | The ADT name, nested table type, or VARRAY type. |
| plsql_type_spec_source | FORCE | If the type specified by type_name exists and has type dependents, but not table dependents, FORCE will force this statement to replace the type. If the type specified by type_name has table dependents, the statement will fail with or without FORCE.
NoticeIf type |
| plsql_type_spec_source | varray_type_def | Creates a type as an orderly set of elements of the same data type. |
| plsql_type_spec_source | nested_table_type_def | Creates a nested table of the type specified by datatype. |
| object_type_def | object_type_def | Creates an ADT. The variables that form the data structure are referred to as attributes. Member subprograms that define ADT behaviors are referred to as methods. You need to use the AS OBJECT keyword when you create an ADT. |
| object_type_def | AS OBJECT | Creates a schema-level ADT. A schema-level ADT is sometimes referred to as a root ADT. |
| attr_and_element_spec | element_spec | Specifies each attribute of the ADT. |
| subprogram_spec | subprogram_spec | Declares a subprocedure. |
| subprogram_spec | member_or_static |
|
| subprogram_spec | proc_or_func_spec | Specifies the parameter and data type of the stored procedure or function. If this subprocedure does not contain the definition of the stored procedure or function, you must use the CREATE TYPE BODY statement to make the definition. If you want to create a subtype, the name of the stored procedure or function cannot be the same as the name (regardless of whether it is inherited) of any attribute declared in the supertype link. |
Examples
Create types
Example 1: Create an object type
Create an object type named person_typ that has two attributes name and age, and a member function named get_age that is used to return the age of an object.
CREATE TYPE person_typ AS OBJECT (
name VARCHAR2(50),
age NUMBER,
MEMBER FUNCTION get_age RETURN NUMBER);
Example 2: Create a VARRAY type
Create a VARRAY type named number_list, which can store at most 10 numbers.
CREATE TYPE number_list AS VARRAY(10) OF NUMBER;
Complete usage example
The following example shows how to create and use an object type.
Step 1: Create an object type
Define the object type and its member function. Assume that the member function get_age is used to return only the age of an object.
CREATE OR REPLACE TYPE person_typ AS OBJECT (
name VARCHAR2(50),
age NUMBER,
MEMBER FUNCTION get_age RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY person_typ AS
MEMBER FUNCTION get_age RETURN NUMBER IS
BEGIN
RETURN self.age;
END get_age;
END;
/
Step 2: Use the object type in PL/SQL
Create a table named
people.CREATE TABLE people (person_id NUMBER, person_name VARCHAR2(50), person_age NUMBER);In PL/SQL, you can declare variables for
person_typto operate the data.DECLARE v_person person_typ := person_typ('Alice', 30); BEGIN -- Insert data into the people table. INSERT INTO people (person_id, person_name, person_age) VALUES (1, v_person.name, v_person.age); COMMIT; END; /