Oracle Note 12, PL/SQL Object-oriented OOP programming

------------------------Abstract data Types-----------

--Create an address type, be sure to add as object, and add a procedure or method to the type

Create or replace type address as Object (

       Province Varchar2 (10),--province properties

       City VARCHAR2 (10)--Municipal properties

) not final; --not final indicates that the type can have subtypes

--Define a sub-type

--under Address Description This type inherits to address type

Create or replace type detailaddress under Address (

       Street VARCHAR2 (20)--Street attribute  3rd member

)；

--Create an employee information table, and the last column is the detailaddress type

drop table Empinfo

CREATE TABLE Empinfo (

       Ename varchar2 (),  --employee name

       Esex char (2),  --gender

       eage int,--age

       Eaddress detailaddress   --employee address

);

--Increase the data, only with the construction method

INSERT into empinfo values (' aaa ', ' Male ', detailaddress, ' Hubei ', ' Xiangfan ', ' Army Day '));

INSERT into empinfo values (' bbb ', ' Male ', ' detailaddress ', ' Hubei ', ' Wuhan ', ' Yongqing Street ');

INSERT into empinfo values (' CCC ', ' female ', detailaddress, ' Hubei ', ' Wuhan ', ' Optics Valley ');

--Query

SELECT * from Empinfo;

SELECT * from empinfo where esex = ' male ';

SELECT * from Empinfo e where e.eaddress.city = ' Wuhan '; --If the query condition contains the attribute must use the alias of the table

--There are 2 ways of updating:

--First way: Overall update

Update Empinfo e Set e.eaddress = detailaddress (' Hubei ', ' Wuhan ', ' Wuchang ') where e.ename = ' CCC ';

--The second way: only one column of the abstract type is updated

Update Empinfo e Set e.eaddress.city = ' Wuhan ' where e.ename = ' CCC ';

--Delete

Delete from Empinfo e where  e.eaddress.city = ' Wuhan ';

--indexing of attributes of an abstract data type

Create INDEX idxemp on Empinfo (eaddress.city);

--Delete

drop table Empinfo;

Drop type address force; --Force Delete abstract type

-------------------------------end of abstract data type----------------------

------------------Object table, each row in the table is an object-----------------------

--Create abstract data type person, and as base type

Create or replace type Person as Object (

       PName varchar2 (),  --Name

       Psex char (2),   --gender

       PAge int      --age

) not final;

--Create subtype student, inherit person

--don't add as object behind

Create or replace type student under person (

       Stuid int

);

--Create Object Table Stuinfo

CREATE table Stuinfo of student;

--Create a PRIMARY KEY constraint for the object table

ALTER TABLE STUINFO ADD constraint Pk_stuinfo primary key (STUID);

--Insert data when normal table is inserted

INSERT into stuinfo values (' aaa ', ' Male ', 29, 1001);

--inserting data, using a construction method

INSERT into Stuinfo values (student (' BBB ', ' Male ', 26, 1002));

INSERT into Stuinfo values (student (' CCC ', ' female ', 29, 1003));

--query, when the ordinary table with

SELECT * from stuinfo where stuid = 1002;

--both updates and deletions are made with normal SQL statements.

Update Stuinfo Set pAge = where PName = ' CCC ';

Delete from stuinfo where stuid = 1001;

Rollback

The--ref (table alias) function is used to return the OID of an object, the object identifier, and the object table rowID

Select ref (s) from Stuinfo s;

Select rowID, ref (s) OIDS from  stuinfo s;

--Create Student score table, note foreign key

CREATE TABLE Stuscore (

       Stu ref student,--stu the value of this column must appear in the Stuinfo table and Stu the OID of the stored object instead of the object itself

       Score int  --Fractions

);

--insert data into the score table, use Select only, cannot use normal values

--Wrong approach: INSERT INTO Stuscore values (select ref (s) from stuinfo where Stuid = 1001, 90)

--The right approach:

Insert into Stuscore Select ref (s), stuinfo s where stuid = 1001;

Insert into Stuscore Select ref (s), Stuinfo s; --Insert 3 rows of data

Insert into Stuscore Select ref (s), and from Stuinfo s where stuid = 1003;

--Query

SELECT * from Stuscore;

--deref (column name) function can restore OID to object, primary key column display problem

Select Deref (s.stu), score from Stuscore s where s.stu.stuid = 1001;

--Modify, the following 2 can be

Update Stuscore set score=100 where Stu = (select ref (s) from Stuinfo s where stuid = 1001);

Update Stuscore s Set score = where S.stu.stuid = 1001;

--Delete, the following 3 can be

Delete from Stuscore where Stu = (select ref (s) from Stuinfo s where stuid = 1001);

Delete from Stuscore s where s.stu.stuid = 1001;

Delete from Stuscore where stuid = 1001;

----------------------------------Object Table End----------------------

----------------------------------Object View-----------------------

--Object View: Converts an existing relational table to an object table, without changing the original table

--First create a type that is the same as the original table

--Then create the view

CREATE TABLE AAA

(a int);

Create type AAAA As Object

(a int);

Create or replace view view_stu of AAAA with Object OID (a)

As

SELECT * from AAA;

SELECT * from View_stu;

--the same as the object table for adding and deleting changes

-------------------------------Object View End-----------------------

--------------------------------abstract types, including procedures and methods-------------

Create or replace type ADDRESS as Object (

       Province Varchar2 (10),--province

       City Varchar2 (10),--town, back, not less

       member function Get_pro return VARCHAR2,  --functions, followed by, not;

       member function get_city return VARCHAR2,

       Member procedure Set_pro (pro varchar2),   --process

       Member Procedure set_city (CY VARCHAR2)

);

Create or replace type body address--cannot be appended as Object

As--cannot be added after the begin

   member function Get_pro return VARCHAR2

   Is

   Begin

       return province;

   End Get_pro;

   member function get_city return VARCHAR2

   Is

   Begin

       return city;

   End

   Member procedure Set_pro (pro varchar2)

   Is

   Begin

        Province: = Pro;

   End

   Member Procedure set_city (CY VARCHAR2)

   Is

   Begin

        City: = cy;

   End

End

--Test the above member functions and procedures

Declare

       addr address;

Begin

     Addr: = Address (' Hubei ', ' Wuhan ');

     Dbms_output.put_line (addr.get_city);

End

--drop table Stuinfo;

CREATE TABLE Stuinfo (

       Stuid int PRIMARY KEY,

       Addr Address

);

Declare

       addr address;

Begin

     Addr: = Address (' Hubei ', ' Wuhan ');

     INSERT into Stuinfo values (1, addr);

     Addr.set_city (' Zhengzhou ');

     Addr.set_pro (' Henan ');

     INSERT into Stuinfo values (2, addr);

End

SELECT * from Stuinfo;

--Delete type

Drop type address force;

--------------------------abstract type, containing the procedure and method end-------------

----------------------------variable Array------------------------------

--is a maximum-length array that can store multiple values, and the members of an array can be any type

--Create a variable array type, length is 10, the data type is number (4)

Create or replace type Arrtype as Varray (4);

Create or replace type Scoretype as Object (

       SubName VARCHAR2 (10),

       Score int

);

--Create a variable array of length 10, the data type is Scortype

Create or replace type Arrscoretype as Varray (ten) of Scoretype;

--Create Student information table

--drop table Stuinfo;

CREATE TABLE Stuinfo (

       Stuid int PRIMARY KEY,

       Score Arrscoretype  --variable array, up to 10 members

);

--inserting data, using a variable array constructor

INSERT into Stuinfo values (1, Arrscoretype (

       Scoretype (' sql ', '), Scoretype (' C # ', ' n '), Scoretype (' Java ', 90));

INSERT into Stuinfo values (2, Arrscoretype (

       Scoretype (' sql ', ' Max '), Scoretype (' C # ', ', '), Scoretype (' Java ', ' Up '), Scoretype (' HTML ', 60));

INSERT into Stuinfo values (3, Arrscoretype (

       Scoretype (' sql ', ' n '), Scoretype (' Java ', 93));

--Query

SELECT * from Stuinfo;  --The query result is a collection

-How can I query the data in a variable group? The idea is to use the table function to convert the set into a table and then query the data from this table

SELECT * FROM table (select S.score from Stuinfo s where s.stuid = 2);

The--table function can only be a mutable array

Select S.stuid, t.* from Stuinfo s,

       Table (select score from Stuinfo  where stuid = s.stuid) t

       where s.stuid = 2;

--Update, update the entire mutable array, cannot update only an element of an array

Update Stuinfo Set score = Arrscoretype (

       Scoretype (' sql ', '), Scoretype (' C # ', ') where stuid = 1;

--Delete, delete by primary key

-----------------------------variable array End---------------------------------

Drop type Scoretype force;

Drop type Arrscoretype force;

drop table Stuinfo;

-----------------------------Nested Tables---------------------------

--Create abstract types

Create or replace type Scoretype as Object (

       SubName VARCHAR2 (10),

       Score int

);

--Create a nested table type

Create or Replace type nesttable is table of Scoretype;

--Create a Student information table with nested tables

CREATE TABLE Stuinfo (

       Stuid int,

       Score Nesttable  -in fact, there is a reference, the actual data exists in the ABC table

) Nested table score store as ABC;

--nested Table score Store as ABC means: Stuinfo This table score this column is a nested table type, the nested table actually exists in the table of ABC

--the same as adding or deleting a variable array

INSERT into Stuinfo values (3, nesttable (

       Scoretype (' sql ', ' n '), Scoretype (' Java ', 93));

--Query, thinking: the nested table is first found, and then the nested table and stuinfo for the joint query

SELECT * FROM table (select Ss.score from Stuinfo ss where Stuid = 3);

Select S.stuid, t.* from Stuinfo S, table (select Ss.score from Stuinfo ss where Stuid = S.stuid) t

where s.stuid = 3;

--Update

Update table (select Ss.score from Stuinfo SS where stuid=3) t

Set T.score = T.subname = ' sql ';

--Delete

Delete from table (select Ss.score from Stuinfo ss where Stuid = 3) t

where t.subname= ' SQL ';

The similarities and differences between----variable arrays and nested tables----------------

Same point:

1, are abstract types

2. Can be used as a data type for a column in a table (record and fast table are not data types for columns)

Different points:

1. The mutable array itself is stored in the original table, and the nested table is stored in a different table

2, variable arrays have size limitations, and nested tables do not have

3, variable array updates must update the entire mutable array, while nested table updates can update only some of the records in the nested table

Oracle Note 12, PL/SQL Object-oriented OOP programming