Oracle Study Notes-(5)

Oracle Study Notes --- (5)

V.

1. define abstract data types
/*
Create or replace type animal_ty as object
(Breed varchar2 (25), -- animal type
Name varchar2 (25), -- name
Birthdate date, -- Date of birth
Member function age (birthdate in date) return number -- calculate the age based on the date of birth
); -- Abstract data type with Method
*/
Create or replace type animal_ty as object
(Bread varchar2 (25), -- animal type
Name varchar2 (25), -- name
Holobby varchar2 (10)-hobbies
);

Desc animal_ty;
View user_types;
User_type_attrs;

Ii. Use of abstract data types

1) create a relational table
Drop table animal;
Create Table animal
(ID number primary key,
Anima animal_ty
);
Set DESC depth 3
Desc animal;
2) insert data (using the constructor)
Insert into animal values (1, animal_ty ('mulle', 'frances ', 'play '));
Insert into animal values (2, animal_ty ('dog ', 'benji', 'eat '));
Insert into animal values (3, animal_ty ('crocodile ', 'lyle', 'swim '));

3) Operation
View: Select F. anima. name from animal F;
Update: Update animal F set F. anima. holobby = 'play1' where id = 1;
Delete: delete from animal a where a. anima. holobby = 'play1 ';

2. Delete the object type
Drop type animal_ty force;

3. query correlation
Select name, type from user_dependencies where referenced_name = 'animal _ ty ';

4. Create an index on the abstract data type
Create index idx on animal (anima. Name );

5. Final and not final modifier types
Instantiable and not instantiable modifiable types and Methods

By default, abstract data types cannot be inherited. At the same time, types provide constructors, and methods in types can be implemented.

Now

Declared type plus not final this type can be inherited
Modification Method: Not instantiable type does not provide method implementation
Modifier type: The not instantiable type does not have a constructor. It cannot be instantiated.

2. variable array

1) create a varray type
Create or replace type tools_va as varray (5) of varchar2 (25 );
2) create a relational table. The field type of this table is varray.
Create Table borrower (name varchar2 (25) primary key,
Tools tools_va );
Dictionary
Select typecode, attributes from user_types where type_name = 'tools _ va ';
Select coll_type, elem_type_owner, elem_type_name, upper_bound,
Length from user_coll_types;
3) insert data
Insert into borrower values ('jed Hopkins ',
Tools_va ('hammer ', 'sledge', 'ax '));
Insert into borrower values ('pk ',
Tools_va ('pen1', 'pen2', 'pen3 '));
4) view
Select * from table (select T. tools from borrower t where T. Name = 'pk ');

3. nested tables

Drop type emp_ty force;
1,
Create or replace type emp_ty as object
(Empno number,
Ename char (10 ),
Sal number );
2, create or replace type emps_nt as table of emp_ty;

3,
Create Table ntdept
(
Deptno number,
Dname varchar2 (20 ),
Loc varchar2 (20 ),
Dno_det emps_nt)
Nested table dno_det store as emp_nt_tab;

-- Insert data

Insert into ntdept values
(10, 'marketing Department ', 'haizhu North road', emps_nt (emp_ty (100, 'marry', 1000 ),
Emp_ty (101, 'lili', 1500 ),
Emp_ty (102, 'khk', 3000 ))
);

Insert into ntdept values (20, 'academic authorization', 'haizhu South', emps_nt (emp_ty (103, 'jar', 2200 ),
Emp_ty (104, 'Rose ', 1000 ),
Emp_ty (105, 'lusu', 2000)
));

-- Query
You can use the or table
Select deptno, dname, Loc, dno_det from ntdept;
Select dno_det from ntdept where deptno = 10;
Select nt. * from table (select dno_det from ntdept where deptno = 10) NT;
Select nt. * from table (select dno_det from ntdept where deptno = 20) NT where

NT. E-mapreduce = 103;
Select nt. empno, NT. ename from table (select deptno from ntdept where deptno = 10)

NT where NT. empno = 101;
-- Insert
Insert into table (select dno_det from ntdept where deptno = 10)

Values (106, 'hang', 3000 );
Or Select Insert data from the nested table.
Insert into ntdept values (40, 'newd', 'newloc ',
Cast (Multiset (select * from table (select dno_det from ntdept where deptno = 10)

NT where NT. empno = 100) as emps_nt ))

Cast: converts query results into nested tables.
Multiset: allows the cast query to contain multiple records.

-- Update
Update table (select dno_det from ntdept where deptno = 10) NT set nt. ename = 'llll'

Where nt. empno = 101;
-- Delete
Delete from the (select dno_det from ntdept where deptno = 10) NT where

NT. empno = 101

4. Object table

1. Create an object type
Drop type animal_ty force;
Create type animal_ty as object
(Name varchar2 (15 ),
Holobby varchar2 (20)
)
/
2. Create an object table
Create Table animal of animal_ty (name constraint PK primary key );
3. insert data
Insert into animal values (animal_ty ('snake ', 'play '));
Insert into animal values (animal_ty ('Dog', 'swim '));
Insert into animal values (animal_ty ('cat', 'Sleep '));

4. view the oId
Select REF (f) from animal F;

5. view the object
Select value (r) from animal R;


Use deref

Drop table keeper;
Create Table keeper
(
Keepername varchar2 (10 ),
Animalkept ref animal_ty
)

Insert data

Insert into keeper select 'jek', ref (f) from animal f Where name = 'Dog ';
Insert into keeper select 'blk', ref (f) from animal f Where name = 'cat ';

View data
View objects in a relational table
Select deref (animalkept) from keeper;

Assume that an object is deleted.
Delete from animal where name = 'Dog ';

Select * From keeper; the object is still being referenced.
Update keeper set animalkept = NULL where animalkept is dangling; -- Remove reference

V. Object View

Access the dept relational table as an object table

1. Create an object type

Create or replace type dept_type as object
(DNO number (2 ),
Dname varchar2 (15 ),
Local varchar2 (18)
);
2. Create an object view
Create view dept_view of dept_type with object OID
(DNO)
Select * from Dept;

3. manipulate the relational table data through the object view
Insert into dept_view values (dept_type (60, 'aaa', 'bbb '));
Delete from dept_view where deptno = 60;
The result is reflected in the relational table. When the data in the relational table is changed, it is also reflected in the object view.

Each row of the object view has an oId.

4. When there is a reference Relationship Between Relational Tables, you can also use the object view.
DEPT and EMP are primary and foreign key tables. The deptno of the EMP table references deptno of the dept table.
And an object view has been generated for the relational table Dept. At this time, each row in the relational table has an oId

The rows in the dept table can be accessed as row objects through the dept_view view.
The object view allows you to access any relational table as a row object.

-- Make_ref applies only one Oracle internalAlgorithm.

Reading the view.
Select make_ref (dept_view, deptno) from Dept;
Select make_ref (dept_view, deptno) from EMP;

5. Create a reference relationship through the object view

Create view emp_ov as select make_ref (dept_view, deptno) DEP, empno, ename, Sal

From EMP;
The first column of the view points to the row of the object view dept_view.

Select deref (A. Dep) from emp_ov;