CREATE TYPE

CREATE TYPE specifies the name and attributes of a type, as well as its methods and other properties.

CREATE TYPE is used to create or replace the following type specifications:

• Abstract data types (ADTs)

• Independent variable arrays (VARRAYs)

• Independent nested tables

• Incomplete object types

  An incomplete type is a type created by a forward type definition. It is called incomplete because it has a name but no attributes or methods. It can be referenced by other types, allowing for custom mutually referenced types. However, you must create the complete type before you can use it to define columns in tables, object columns, or nested tables.

CREATE TYPE BODY specifies the code that implements the type.

Prerequisites

To create a type in a schema, you must have the CREATE TYPE system privilege. To create a type in another user's schema, you must have the CREATE ANY TYPE system privilege. You can obtain these privileges directly or through roles.

The owner of the type must be explicitly granted the EXECUTE object privilege on all other types referenced in the type definition, or the owner must be granted the EXECUTE ANY TYPE system privilege. Owners cannot obtain these privileges through roles.

If the type owner intends to grant access to other users, the owner must be granted the EXECUTE object privilege on the type with the GRANT OPTION, or the owner must be granted the EXECUTE ANY TYPE system privilege with the ADMIN OPTION. Otherwise, the type owner does not have sufficient privileges to grant access to other users.

In the current version of OceanBase Database, when you create a new table using the CREATE TABLE statement, you can define your own object types (also known as user-defined types - UDTs). However, these custom types cannot be used as the data types for columns in database tables. In other words, you cannot directly use these UDTs as part of the table structure, such as creating a column with a type specified as your custom object type.

Nevertheless, even if you cannot use user-defined object types (UDTs) when creating tables, you can still use custom object types to define variables, parameters, and return types for functions in PL/SQL programs.

Syntax

• The syntax of create_type_stmt is as follows:

  

• The syntax of plsql_type_spec_source is as follows:

  

• The syntax of object_type_def is as follows:

  

• The syntax of attr_and_element_spec is as follows:

  

• The syntax of element_spec is as follows:

  

• The syntax of el_element_spec_list_cc is as follows:

  

• The syntax of element_spec_long is as follows:

  

• The syntax of inheritance_final_instantiable_clause is as follows:

  

• The syntax of inheritance_overriding_instantiable_clause is as follows:

  

• The syntax of subprogram_spec is as follows:

  

• The syntax of proc_or_func_spec is as follows:

  

Semantics

Examples

Creating object types

Example 1: Creating an object type

Create an object type named person_typ with two attributes: name and age, and a member function get_age to return the age of the object.

CREATE TYPE person_typ AS OBJECT (
  name VARCHAR2(50),
  age NUMBER,
  MEMBER FUNCTION get_age RETURN NUMBER);

Example 2: Creating a VARRAY type

Create a VARRAY type named number_list that can store up to 10 numbers.

CREATE TYPE number_list AS VARRAY(10) OF NUMBER;

Full usage example

We will use the creation of an object type to demonstrate a complete usage example.

Step 1: Create an object type

First, define the object type and its member functions. Here, we assume the get_age member function simply returns the age of the 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

1. Create a table named people.

   CREATE TABLE people (person_id NUMBER, person_name VARCHAR2(50), person_age NUMBER);
   

2. In a PL/SQL program, you can declare variables of the person_typ type to manipulate these 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;
   /