Create a type

This topic describes how to create a type in the SQL window.

Overview

A database type object specifies the name, method, and other attributes of a type.

OceanBase Cloud supports three types: object type, array type, and table type.

• Object type: an abstract data type (ADT), which is an expression that encapsulates database objects, relationships between database objects, and basic operations on database objects.

• Array type: a variable array, which is a collection similar to a nested table. A variable array is a collection of objects, where each object is of the same data type. The size of a variable array is determined when the array is created. The data type of a column in a table can be a variable-length multidimensional array. The type of the array can be any of the primitive types, the enumerated type, the composite type, or a user-defined type.

• Table type: an independent nested table type. The created arrays are directly defined in the columns of the table.

You can create a type in the following 4 steps:

1. Specify the type name.
2. Select a type object.
3. Edit SQL statements.
4. Complete the type creation.

In the following example, the ob_var object type is created in the SQL window. The type contains two VARCHAR type parameters: var and var1.

Prerequisites

You need to have the database account and password for the current tenant to log in to the SQL Console.

Prerequisites

Step 1: Specify the name of the type

After logging in to the SQL console and entering the SQL window, click the Type label in the database list on the left to view the type list. To create a type, click + in the upper-right corner of the type list.

Step 2: Select a type object

Step 3: Edit SQL statements

After you specify the information in Step 2, click Next: Verify SQL Statement to go to the statement editing page. This page displays the corresponding type definition statements generated based on the information you specified in the Create Type dialog box. You must complete the statements based on your needs. After you complete the statements, click Create in the upper-right corner to complete the type creation.

Step 4: Complete the type creation

Click Create in the upper-right corner to create the type. After a user-defined type is created, you can use the INSERT key word to call it in a PL statement.

Example:

-- Create a table.
create table data_type (id number(10),name varchar2(50),age int,address varchar2(50),salary float);

-- Insert table data.
insert into data_type values(1,'baba',20,'hangzhou',3000.00);

-- Create a type.
create or replace type ob_var as object(
  var varchar2(10),
  var1 varchar(10)
);

delimiter /
-- Create a stored procedure.
create or replace procedure p_datatype is
begin
  declare
    rec data_type%rowtype;
    v_age rec.age%type;
    var varchar2(50);
    v_name var%type;
    v_salary data_type.salary%type;

-- Define a type.
    type salary is table of number index by varchar2(20);
    arr salary;
    v_arr arr%type;

    CURSOR c2 IS SELECT name, age FROM data_type;
    c_row c2%rowtype;
    v_rec c_row%type;

    ob ob_var;
    v_obj ob%type;

  begin
    v_name := 'ali ';
    v_age := 30;
    v_salary := 2000;
    dbms_output.put_line('Referenced item: variable, record, and table column name: ' || v_name  || ' * ' || v_age || ' * ' || v_salary);

    v_arr('James') := 78000;
    dbms_output.put_line('Referenced item: name of collection variable ' || v_arr.FIRST);

    open c2;
    fetch c2 into v_rec;
    dbms_output.put_line('Referenced item: name of cursor variable: ' || v_rec.name || ' * ' || v_rec.age);
    close c2;

    v_obj:=ob_var('test','object');
    dbms_output.put_line('Referenced item: name of object instance: ' || v_obj.var || ' * ' || v_obj.var1);
  end;
end;
/

begin
  p_datatype;
end;
/

To manage a type, right-click the type name in the list of databases on the left, and select the required operation from the context menu, which provides the following options: View, Create, Delete, Download, and Refresh. For more information, refer to Functional keys in SQL Console.