oracle12c--Object Table ($) ____oracle

Introduction: Oracle is an object-oriented database, so users in Oracle also allow the creation of data tables based on the class structure, while the relationship of the class is used to maintain the data in the table Example 1: Define the class structure to use

--delete emp_object, or dept_object cannot be built again DROP TYPE Emp_object; --Define departmental classes CREATE OR REPLACE TYPE dept_object As Object ( Atri_deptno Number (2),--Department No. Atri_dname VARCHAR2 (14),--Department name Atri_loc VARCHAR2 (13),--Department position --Get object information Member FUNCTION ToString return VARCHAR2 ) not FINAL; / --Define the person class specification CREATE OR REPLACE TYPE person_object As Object ( Atri_pid number,--personnel ID Atri_name VARCHAR2 (10),--Name of person Atri_sex VARCHAR2 (10),--Gender of personnel Not instantiable member FUNCTION ToString return VARCHAR2--defining abstract methods --Implementing object sorting Not instantiable MAP member FUNCTION compare return number Not FINAL, not instantiable; --You must use the not Instantiable declaration class here / --Defines the EMP class specification, which is the person subclass CREATE OR REPLACE TYPE emp_object UNDER person_object ( Atri_job VARCHAR2 (9),--Employee position Atri_sal Number (7,2)--Employee pay Atri_comm number (7,2),--Employee Commission Atri_dept Dept_object--employee Department --This function name is the same as the parent class function name, so here is the override of the function Overriding member FUNCTION ToString return VARCHAR2, --Implementing object sorting overriding MAP member FUNCTION compare return number ) ; / --Define the Dept_object class body CREATE OR REPLACE TYPE body dept_object as Member FUNCTION ToString return VARCHAR2 as BEGIN Return ' Department No.: ' | | Self.atri_deptno | | ', Name: ' | | Self.atri_dname | | ', Location: ' | | Self.atri_loc; End; End; / --Define the Emp_object class body CREATE OR REPLACE TYPE body emp_object as Overriding member FUNCTION ToString return VARCHAR2 as BEGIN Return ' person number: ' | | Self.atri_pid | | ', Name: ' | | Self.atri_name | | ', Sex: ' | | Self.atri_sex | | ' Position: ' | | Self.atri_job | | ', Wages: ' | | Self.atri_sal | | ', Commission: ' | | Self.atri_comm; End; overriding MAP member FUNCTION compare return number as BEGIN return self.atri_sal + Self.atri_comm; End; End; /

This program defines 3 class specifications, and because Person_oobject is an abstract class, only two class bodies are defined;

Create an object table after a user has defined a class, you can create the specified object table based on the property structure in this class, as follows:

CREATE table name of class;

When you create an object table, if an inherited action relationship exists in the object table, it automatically changes the inherited property to a column in the datasheet

Example 1: Create an Object table

CREATE TABLE Emp_object_tab of Emp_object;

View the EMP_OBJECT_TAB datasheet structure

DESC Emp_object_tab;

The results of this query can be found that atri_dept this nested type still exists as an object, while Emp_object_tab tables contain all the attributes of Person_object and emp_object; maintaining Object table Data After the object table is established, the data in the table is also divided into 4 categories: Add, modify, delete, query data increase, use INSERT statement: Example 1: Add data, but do not increase departmental attribute data

INSERT into Emp_object_tab (Atri_pid,atri_name,atri_sex,atri_job,atri_sal,atri_comm) VALUES (Ten, ' Hey ', ' men ', ' clerks ', 3500,100);

Example 2: Adding data and using nested types

INSERT into Emp_object_tab (atri_pid,atri_name,atri_sex,atri_job,atri_sal,atri_comm,atri_dept) VALUES (' yy ', ' Male ', ' technician ', 5500,200, dept_object(10, ' development department ', ' Beijing '));

Data query: Example 1: Querying all data in the Emp_object_tab table

SELECT * from Emp_object_tab;

When querying object table data, you can also use the value () or REF () function to query the value function of the data: Use this function to convert the data in the object table to object return, so that you can sort the REF () function by using the object information after the query: VALUE () Functions can store nested object information directly in the object table, which sometimes results in redundancy of data. For example, there will be multiple employees in one department, and you will need to repeatedly save departmental information whenever you increase employee data. So in Oracle also provides the address point of the data, as in figure: Data update, need to consider the object type of Action column: Example 1: Update the Emp_object_tab Object table (this table does not use ref reference) in the Department information

UPDATE emp_object_tab SET atri_job= ' manager ', Atri_dept=dept_object (30, ' magic ', ' Canada ') WHERE atri_pid=10;

Example: Query the modified data in the Emp_object_tab object table

SELECT * from Emp_object_tab WHERE atri_pid=10;

Example 2: Update departmental information in the Emp_object_ref_tab object table (this table uses ref references)

UPDATE Emp_object_ref_tab SET Atri_dept= ( SELECT REF (d) From Dept_object_ref_tab D WHERE atri_deptno=30) WHERE atri_pid=3020;

Example: Query the modified data in the Emp_object_ref_tab object table

SELECT Atri_pid,atri_name,atri_sex,atri_job,atri_sal,atri_comm, DEREF (atri_dept) Dept From Emp_object_ref_tab WHERE atri_pid=3020;

Example 3: In addition to using the Update object columns, you can also remove the corresponding object information set the update condition of the data update Emp_object_ref_tab all 10 employee information in the object table

UPDATE emp_object_ref_tab SET atri_name= ' Wang Yueqing ', atri_sal=6000 WHERE atri_dept= (SELECT REF (d) From Dept_object_ref_tab D WHERE atri_deptno=10);

Querying data for Emp_object_ref_tab data tables

SELECT Atri_pid,atri_name,atri_sex,atri_job,atri_sal,atri_comm, DEREF (atri_dept) Dept From Emp_object_ref_tab;

To delete data, you can follow normal or object fields: Example 1: Delete employee information from 10 departments in the Emp_object_ref_tab table

DELETE from Emp_object_ref_tab WHERE atri_dept= (SELECT REF (d) From Dept_object_ref_tab D WHERE atri_deptno=10);

Take the example: Query Emp_object_ref_tab data table data

SELECT Atri_pid,atri_name,atri_sex,atri_job,atri_sal,atri_comm, DEREF (atri_dept) Dept From Emp_object_ref_tab;