How to create an object type and object type table in Oracle Database

OracleDatabase creationObject TypeThe operation is actually similar to the operation for creating a table, but the object type does not actually allocate space for the stored data. In addition, the object type also has attributes and methods. This article mainly introduces the Oracle object types andObject Type tableNext we will introduce some of the knowledge.

Simple Object Types without methods:

 
 

  
  
CREATE TYPE type_name as OBJECT (  
  
  
 
  
  
column_1 type1,  
  
  
 
  
  
column_2 type2, ); 
 
 

Note: AS OBJECT

After the object type is created, you can use this type when creating a table, for example:

 
 

  
  
CREATE TYPE HUMAN AS OBJECT(  
  
  
 
  
  
NAME VARCHAR2(20),  
  
  
 
  
  
SEX VARCHAR2(1),-- F : FEMALE M:MALE   
  
  
 
  
  
BIRTHDAY DATE,  
  
  
 
  
  
NOTE VARCHAR2(300)  
  
  
 
  
  
) 
 
 

Later, you can view it using the following statement:

 
 

  
  
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TYPE' CREATE TABLE STUDENTS(  
  
  
 
  
  
GUID NUMBER NOT NULL,  
  
  
 
  
  
STUDENTS HUMAN  
  
  
 
  
  
) 
 
 

This saves two triggers.

When inserting data, you can:

 
 

  
  
Insert into students (STUDENT) VALUES (HUMAN ('xling', 'M', TO_DATE ('20170101', 'yyyymmdd'), 'test '))
 
 

Note: HUMAN ('xling', 'M', TO_DATE ('201312', 'yyyymmdd'), 'test') is the default constructor.

If you want to select a female F) record, you can:

 
 

  
  
SELECT * FROM STUDENTS S WHERE S.STUDENT.SEX = 'F' 
 
 

Note: cannot be written as: SELECT * from students where student. SEX = 'F' the following error is reported: ORA-00904: "STUDENT". "SEX": the identifier is invalid

Object Type table: Each record is an object table, called an object type table. it has two usage methods: 1, used as a table with only one object type field. 2. Used as a standard relational table with object type fields.

Syntax: create table table_name OF object_type;

Example: create table TMP_STUDENTS of human;

With DESC TMP_STUDENTS, we can see that its field structure is the same as that of HUMAN.

An object type table has two advantages:

1. simplified the use of objects to some extent, because the field types in the object table are the same as those in the object type, so object names are not required to modify object attributes, you can insert data into an object-type table, just like inserting a common relational table:

 
 

  
  
Insert into TMP_STUDENTS VALUES ('xling', 'M', TO_DATE ('20170101', 'yyyymmdd'), 'object type table ');
  
  
 
  
  
Insert into TMP_STUDENTS VALUES (HUMAN ('snow', 'F', TO_DATE ('20170101', 'yyyymmdd'), 'Type constructor '));
 
 

The second feature is that an object table is a convenient way to create a table using the object type as a template. It ensures that multiple tables have the same structure.

The object type TABLE cannot be found in the USER_TABLES TABLE, but can be found in the USER_OBJECTS TABLE, and OBJECT_TYPE = 'table'

When the type is defined, a method is automatically included, that is, the default constructor. The constructor name is the same as the object name, and it has a variable corresponding to each property of the object type.

Method of object type:

 
 

  
  
CREATE TYPE type_name AS OBJECT (  
  
  
 
  
  
column1 column_type1,  
  
  
 
  
  
column2 column_type2,  
  
  
 
  
  
... ,  
  
  
 
  
  
MEMBER FUNCTION method_name(args_list) RETURN return_type, ) 
 
 

Note: it is a member function (of course, it is also a member procedure and there is no returned value)

Like PACKAGE), if the object type has a method, you must declare a BODY:

 
 

  
  
CREATE TYPE BODY type_name AS MEMBER FUNCTION method_name RETURN return_type {AS | IS}  
  
  
 
  
  
variable declareations..  
  
  
 
  
  
BEGIN  
  
  
 
  
  
CODE..  
  
  
 
  
  
RETURN return_value;  
  
  
 
  
  
END;//END MEMBER FUNCTION END;//END TYPE BODY 
 
 

As follows:

 
 

  
  
CREATE TYPE HUMAN AS OBJECT(  
  
  
 
  
  
NAME VARCHAR2(20),  
  
  
 
  
  
SEX VARCHAR2(1),-- F : FEMALE M:MALE   
  
  
 
  
  
BIRTHDAY DATE,  
  
  
 
  
  
NOTE VARCHAR2(300),   
  
  
 
  
  
MEMBER FUNCTION GET_AGE RETURN NUMBER  
  
  
 
  
  
)  
  
  
 
  
  
--BODY   
  
  
 
  
  
CREATE TYPE BODY HUMAN AS  
  
  
 
  
  
MEMBER FUNCTION GET_AGE RETURN NUMBER AS  
  
  
 
  
  
V_MONTHS NUMBER;  
  
  
 
  
  
BEGIN  
  
  
 
  
  
SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,BIRTHDAY)/12) INTO V_MONTHS FROM DUAL;  
  
  
 
  
  
RETURN V_MONTHS;  
  
  
 
  
  
END;   
  
  
 
  
  
END; 
 
 

Note: The BODY format IS not as object, nor IS it enclosed in parentheses. The AS or IS after the member function cannot be omitted.

Here is an introduction to Oracle object types and Object Type tables. I hope this introduction will bring you some benefits. Thank you!