Oracle object type

Oracle object type
Oracle object typeObject Type

In PL/SQL, object-oriented programmers are based on object types. The object type is a user-defined Composite data type. It encapsulates the data structure and processes and functions used to manipulate the data structure.

The object type of the database is similar to that of JAVA and C #, and can contain attributes (used to store object states) and methods (used to create object behavior models ). The object type is also user-defined.

The Object Type includes two parts: Object Type Specification and Object Type Body:

Object Type standard interface between objects and applications, used to define the public attributes and methods of objects. Object Type bodies are used to implement the public methods defined by object type standards. Create a simple object type

The syntax for creating a simple data type is as follows:

CREATE [OR REPLACE] TYPE type_name AS OBJECT(    column_name data_type    [,column_name data_type,...])

Object Type attributes are used to describe the features of an object. Each object type has at least one attribute, and contains at most 1000 attributes. The attribute type can be any Oracle data type (including the object type) except the following ): LONG, long raw, NCHAR, NCLOB, NVARCHAR2, ROWID, UROWID, and specific PL/SQL types % TYPE and % ROWTYPE.

Example 1:

First, grant the user the permission to create objects:

GRANT CREATE ANY TYPE TO siege;

Then create an object:

CREATE TYPE stu AS OBJECT( name VARCHAR2(20), sex VARCHAR2(2), birthday DATE, note VARCHAR2(300))

We can also use an object as a normal type:

CREATE TYPE stu2 AS OBJECT( sid NUMBER(4), student stu)

Similarly, we can use a custom type when creating a table:

CREATE TABLE student2( sid NUMBER(4), student stu)

Create an object type with a function

Syntax for creating object types with functions:

CREATE [OR REPLACE] TYPE type_name AS OBJECT(    column_name data_type    [,column_name data_type,...],    member function method_name(args_list) return_type,    ...)

Function indicates a function. You can use member procedure to replace member function when creating an object-type member statement, but no return value exists.

If an object contains a function, you also need to declare an object BODY. The Object BODY defines the actual code of the function. Its creation syntax is as follows:

CREATE [OR REPLACE] TYPE BODY type_name AS MEMBER FUNCTION method_name RETURN return_type {AS|IS} variable declareations...; BEGIN ... RETURN return_value; END;END; column_name data_type [,column_name data_type,...]

Example 2:

First, create an object type:

CREATE TYPE stu3 AS OBJECT( name VARCHAR2(20), sex VARCHAR2(2), birthday DATE, note VARCHAR2(300), MEMBER FUNCTION get_age RETURN NUMBER)

Then create the Object Body:

CREATE TYPE BODY stu3 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; 

Then we create the student3 table:

CREATE TABLE student3( sid NUMBER(4), student stu3) 

Insert a data entry:

INSERT INTO student3 VALUES(1,stu3('siege','M',TO_DATE('19910228','YYYYMMDD'),'my notes'));

Now we can query the inserted statement:

SELECT s.sid,s.student.name,s.student.sex,s.student.birthday,s.student.note,s.student.get_age() FROM student3 s

The result is as follows:

SID STUDENT.NAME STUDENT.SEX STUDENT.BIRTHDAY STUDENT.NOTE S.STUDENT.GET_AGE()
1 siege M 28/02/1991 my notes 24

Get object type information

Before using the object type, you must specify the structure of the object type (mainly including the data type and data range of the attribute). You can use the DESCRIBE command to view the structure of the object.

In the command window of PL/SQL developer, open a window and enter the following statement to set the information depth:

SET DESCRIBE [DEPTH {1|n|ALL}]

The value range of depth is 1-50, and the default value is 1. Depth indicates the object level, similar to the level in JAVA. In this example, the depth of STU2 is 2.

DESCRIBE STU2;

You can query the object type information.

Column object

The column object defines a single COLUMN using the OBJECT class type, and the objects stored in this COLUMN become COLUMN objects.

If you only need to use the object type to define a single column in the table, you can use the column object form. When creating a table, you can define the column data type as the object type.

Syntax:

column_name type_name

When adding data to a table containing column objects, you must use constructors to provide attribute values for column objects.

Object table

An object table defines an entire row in a TABLE using the OBJECT type. This TABLE is called an object table.

If you need to define the entire table using the object type, you can create the entire table as an object table. Use the OF clause to identify a table as an object table. Its syntax is as follows:

CREATE TABLE table_name OF type_name;

You can add a record to an object table in either of the following ways:

Provide attribute values through Constructors

Provide column values in the relational table

Example 3:

CREATE TABLE student4 OF stu;

Now student4 is created.

Add data to the table using the constructor:

INSERT INTO student4 VALUES(stu('siege','M',TO_DATE('19910228','YYYYMMDD'),'NOTHING'));

By adding data to a common table:

In addition to common query statements, the object Table query can also use the value function:

SELECT value(s) FROM student4 s

You can also query one or more columns separately:

SELECT VALUE(s).sex FROM student4 s

Object Identifier and Object Reference

Each OBJECT in the OBJECT table has a unique object identifier (OID), which can be stored in a column named REF. Object references are defined using the REF type. Generally, they can be used as pointers to objects in the object table. You can use object references to create models for relationships between object tables, rather than using foreign keys.

Use the REF () function to retrieve the OID of an object.

Example 4:

SELECT REF(s) FROM student4 s

Result:

REF(S)
000028020928DFC3EBA9874580B244E5CDEBF8F899401875D0C7E041528E5969734EB8E5B5014000F50000
0000280209AFF154978FF84383BAFDD806C52FD6F3401875D0C7E041528E5969734EB8E5B5014000F50001

The combination of long strings of numbers and letters is OID, which identifies the location of objects in the database. You can store the OID in an object reference and use it to access the object it references.

You can use object references to create a model for the relationship between object tables. Use the REF type to define an object reference. Syntax:

CREATE TABLE table_name( ref_name REF type_name SCOPE IS table_type_name);

The scope is Clause limits object references to objects in a specific table.

Example 5:

CREATE TABLE student_object_ref( ID NUMBER(4), stu_ref REF stu SCOPE IS student4)

Insert two pieces of data:

INSERT INTO student_object_ref(id,stu_ref) VALUES(1,(SELECT REF(s) FROM student4 s WHERE s.name='siege'));INSERT INTO student_object_ref(id,stu_ref) VALUES(2,(SELECT REF(s) FROM student4 s WHERE s.name='cage'))

Query results:

 ID STU_REF 1 000022020828DFC3EBA9874580B244E5CDEBF8F899401875D0C7E041528E5969734EB8E5B5 2 0000220208AFF154978FF84383BAFDD806C52FD6F3401875D0C7E041528E5969734EB8E5B5

Now we use the DEREF function to query the results:

SELECT DEREF(stu_ref) FROM student_object_ref

The result is as follows:

DEREF(STU_REF).NAME DEREF(STU_REF).SEX DEREF(STU_REF).BIRTHDAY DEREF(STU_REF).NOTEsiege M 28/02/1991 NOTHINGcage M 28/02/1991 NOTHING

The identifier is not displayed, but the data of the object reference. We can query data through object reference:

SELECT id,DEREF(stu_ref).name,DEREF(stu_ref).sex,DEREF(stu_ref).note FROM student_object_ref

We can also modify the object reference:

UPDATE student_object_ref SET stu_ref=(SELECT REF(s) FROM student4 s WHERE s.name='cage')

INSERT INTO student4 VALUES('cage','M',TO_DATE('19910228','YYYYMMDD'),'NOTHING')