OceanBase Developer Center (ODC) provides a visual interface for creating types. This topic describes how to create a type in ODC.
Overview
A database type object defines the name, attributes, methods, and other properties of a type.
ODC supports the creation of three types: object types, array types, and table types.
Object type: An abstract data type that encapsulates database objects, the relationships between them, and their basic operations.
Array type: A varray is a collection of objects that have the same data type, similar to a nested table. The size of a varray is specified when it is created. This allows a table column to have a data type of a variable-length multidimensional array. The array can contain elements of any primitive data type, enumeration type, composite type, or user-defined type.
Table type: A table type is an independent nested table type. You can use a created table type to define a table column.
As shown in the preceding figure, creating a type involves the following four steps:
This topic provides an example of creating an object type named ob_var in ODC. This type contains two varchar parameters: var and var1.
The data used in this topic is for demonstration only. Replace it with your actual data.
Procedure
Step 1: Specify the type name
After you log on to a database connection in OceanBase Developer Center, click Type in the navigation pane on the left to view the type list. In the upper-right corner of the type list, click + to create a type. Alternatively, you can click Create in the top navigation bar to create the required object.
Step 2: Select the type object to create
Type |
Description |
Example |
Object type |
Abstract data type (ADT).
Note
Only object types can contain subprograms. |
|
Array type |
Independent varray type. |
|
Table type |
Independent nested table type. |
Use |
Step 3: Edit the statement
After you specify the preceding information, click Next. On the statement editing page, a type definition statement is automatically generated based on the information you provided in the Create Type dialog box. You can then edit the statement as needed. After you finish editing, click Create in the upper-right corner of the page to create the type.
The toolbar on the editing page provides the following features:
Feature |
Description |
Format |
Click this button to format the selected SQL statements or all SQL statements in the current SQL window. Formatting operations include indenting, adding line breaks, and highlighting keywords. |
Find/Replace |
Enter content in the search box to search the script. After the search, you can enter content in the replace box to replace the found content. |
Undo |
Revert the script to the state before the last operation. |
Redo |
After performing an Undo operation, re-applies the undone operation. |
Case |
Provides three effects: ALL UPPERCASE, all lowercase, and Initial Caps. Converts the selected statements in the script to the corresponding format. |
Indent |
Provides two effects: Add Indent and Remove Indent. Adds or removes indents for the selected statements in the script. |
Comment |
Provides two effects: Add Comment and Remove Comment. Converts the selected statements to comments or converts comments back to SQL statements. |
IN Value Conversion |
Converts a format such as A B to ('A','B'). |
Step 4: Finish creating the type
Click Create. After the type is created, it is typically used in PL statements. You can use the INSERT keyword to call the user-defined type.
In the navigation pane on the left, right-click a type name in the type list. From the shortcut menu that appears, you can perform management operations such as View, Create, Download, Delete, and Refresh to quickly manage the target object. For more information, see Manage types and Common features.
Example:
-- Create a table
create table data_type (id number(10),name varchar2(50),age int,address varchar2(50),salary float);
-- Insert data into the table
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;
-- User-defined 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 items: variable, record, table column name: ' || v_name || ' * ' || v_age || ' * ' || v_salary);
v_arr('James') := 78000;
dbms_output.put_line('Referenced item: name of the collection variable: ' || v_arr.FIRST);
open c2;
fetch c2 into v_rec;
dbms_output.put_line('Referenced item: cursor variable name: ' || v_rec.name || ' * ' || v_rec.age);
close c2;
v_obj:=ob_var('test','object');
dbms_output.put_line('Referenced item: name of the object instance: ' || v_obj.var || ' * ' || v_obj.var1);
end;
end;
/
begin
p_datatype;
end;
/