Oracle object type (object)

Oracle object type (object)
Oracle object types also have attributes and methods.
The object creation type is similar to the table creation, but it does not actually allocate space for the stored data:

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 record whose gender is female (F), 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;

For 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. It simplifies the use of objects to some extent, because the field type of the object table is the same as that of the object type, you do not need to use an object name 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 ');
Of course, the following method can be used to insert data:
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 a package, if the object type has a method, you must declare a body:
Create type body type_name

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
Member function get_age return number
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.
Taking the students table as an example (Note: if the type is used by a table, it cannot be modified. You must delete the relevant table and then delete the type to create one by one, this is omitted here, as described above)
Select S. Student. get_age () from students s

　　
Note: The brackets of the table name. get_age () must be unique. Otherwise, an error is prompted.

The following shows the usage of an anonymous process:
Set serveroutput on
Declare
AA human;
Age number;
Begin
AA: = human ('xingfairy', 'M', to_date ('201312', 'yyyymmdd'), 'process ');
Age: = AA. get_age ();
Dbms_output.put_line (AGE );
End;

Ing method:
The ing method is a method that returns standard scalar Oracle SQL data types without parameters, such as number and varchar2. Oracle indirectly uses these methods to perform comparative operations.
One of the most important features of the ing method is that when an object is used in a comparative clause such as where or order by, the ing method is used.
The Declaration of the ing method only adds a map before the declaration of the common method. Note: The ing method is a method without parameters.
MAP member function function_name return return_type

Modify the human type mentioned above:
-- Map Method

Create type human as object (
Name varchar2 (20 ),
Sex varchar2 (1), -- F: female M: Male
Birthday date, -- Registration Date
Registerday date,
Note varchar2 (300 ),

Member function get_age return number,
MAP member function get_grade return number
)

Create type body human
-----------------------
Member function get_age return number
V_months number;
Begin
Select floor (months_between (sysdate, birthday)/12) into v_months from dual;
Return v_months;
End;
------------------------
MAP member function get_grade return number
Begin
Return months_between (sysdate, birthday );
End;
End;

Insert data:
Insert into students (student) values (Human ('xling', 'M', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd '), 'test map method '));
Insert into students (student) values (Human ('fairy', 'M', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd '), 'test map method '));
Insert into students (student) values (Human ('snow', 'M', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd '), 'test map method '));

After executing the above operation, we can see the effect of the ing method using the SELECT statement below:
Select S. Student. Name, S. Student. get_grade () from students s order by student
It is sorted by the value of the map method get_grade. note: order by student. Note that the table alias must be used once mentioned. The brackets after the method cannot be omitted, even if there is no parameter.
If you want to use the result of the map method as the condition, you can:
Select S. Student. Name, S. Student. get_grade () from students s where S. Student. get_grade ()> 50
Select S. student. name, S. student. get_grade () from students s where student> Human (null, to_date ('20170101', 'yyyymmdd'), null );

Sorting method:
First, let's talk about self. The self in Oracle and this in Java mean the same.
The object sorting method has the same parameter as the object type. It is currently called arg1 and used for comparison with the self object. if the self object of the call method is smaller than arg1, a negative value is returned. If it is equal, 0 is returned. If self is greater than arg1, the return value is greater than 0.

-- Map Method

Create type human as object (
Name varchar2 (20 ),
Sex varchar2 (1), -- F: female M: Male
Birthday date,
Registerday date,
Note varchar2 (300 ),

Member function get_age return number,
Order member function match (I _student IN HUMAN) return number
)

Create type body human
-----------------------
Member function get_age return number
V_months number;
Begin
Select floor (months_between (sysdate, birthday)/12) into v_months from dual;
Return v_months;
End;
------------------------
Order member function match (I _student IN HUMAN) return number
Begin
Return registerday-I _student.registerday;
End;
End;

Note: During the declaration, the parameter type of the order method must be the same as that of self.

Set serveroutput on
Declare
S1 human;
S2 human;
Begin

S1: = human ('xling', null, null, to_date ('201312', 'yyyymmdd'), null );
S2: = human ('snow', null, null, to_date ('000000', 'yyyymmdd'), null );

If S1> S2 then
Dbms_output.put_line (s1.name );
Elsif S1 <S2 then
Dbms_output.put_line (s2.name );
Else
Dbms_output.put_line ('equal ');
End if;
End;

Note how S1 and S2 are compared.
The ing method has the advantage of efficiency because it associates each object with a Single Scalar Value. The sorting method has the advantage of flexibility, it can perform any complex comparison between two objects. the sorting method is slower than the ing method.