OOP concepts in Oracle databases (1)

OOP concepts in Oracle:Object Type, variable array, nested table, object table, and object view.

Object Type:

Advantages:

1) it is easier to interact with object applications written in Java and C ++.

2) easy to obtain. An object type request can obtain information from multiple Relational Tables.

Syntax:

CREATE [or replace] TYPE type_name

{AS | IS} OBJECT | UNDER super_type}

{

Attribute_name datatype [, attribute_name datatype]… --- Member variables

[{MAP | ORDER} MEMBER function_name,] --- sorting Function

[{FINAL | not final} MEMBER function_name,] --- inherited MEMBER functions

[{INSTANTIABLE | not instantiable} MEMBER function_name,] --- whether the MEMBER function can be instantiated

[{MEMBER | STATIC} function_name,] --- STATIC and non-static member functions

} [{FINAL | not final}] --- can an object be inherited?

[{INSTANTIABLE | not instantiable}] --- can an object be instantiated?

/

The entity part of the object type (that is, the implementation part of the function, optional ):

CREATE [or replace]

Type body type_name {AS | IS}

[{MAP | ORDER} MEMBER function_body,] --- sorting Function

[{MEMBER | STATIC} function_name,] --- STATIC and non-static member functions

END;

/

For example:

Create or replace

Type person as object (

First_name varchar2 (100 ),

Last_name varchar2 (100 ))

/

The attribute type can be any oracle data type (including Custom Data) except the following:

LONG and LONG RAW

NCHAR, NCLOB, NVARCHAR2

ROWID and UROWID

Specific PL/SQL types: % TYPE % ROWTYPE

View:

Desc person

Constructor:

Set serveroutput on

Declare

Rochelle person

Begin

Rochelle person: = person ('donny ', 'chen ');

Dbms_output.putline (l_person.first_name );

End;

/

The constructor must take all attributes of the object type as parameters. Because these parameters do not have default values, they must be provided even if they are null.

Example:

Object Type in the table:

The object type can be used as a column in the database, so it is called a column object.

Create table person_table

(

Name person,

Age number)

/

Set desc depth all

Desc person_table

Set desc depth 1

Insert data:

Insert into person_table

Values (person ('donny ', 'chen'), 30 );

Declare

Rochelle person

Begin

Rochelle person: = person ('hua', 'lil ');

Insert into person_table values (l_person, 33 );

End;

/

Query data:

Select * from person_table

Each attribute of the Access Object Type:

Select p. name. first_name

From person_table p

/

To avoid name resolution problems, you must use the table alias when querying attributes of the object type. Otherwise, an error is reported,

Example: object in an object (merging): create or replace

Type employee as object (

Name person,

Empno number,

Hiredate date)