Oracle PL/SQL Tutorial/Object Oriented/Type Inheritance
Содержание
- 1 Insert data into table with inheritecd columns
- 2 Insert into table with sub type column
- 3 NOT FINAL clause
- 4 NOT INSTANTIABLE Object Types
- 5 Query table with sub type column
- 6 select from inherited object columns
- 7 The NOT FINAL clause indicates that object can be inherited from when defining another type.
- 8 Type Inheritance
Insert data into table with inheritecd columns
<source lang="sql">
SQL> SQL> SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
2 street VARCHAR2(15), 3 city VARCHAR2(15), 4 state CHAR(2), 5 zip VARCHAR2(5) 6 ) 7 /
Type created. SQL> SQL> CREATE Or Replace TYPE PersonType AS OBJECT (
2 id NUMBER, 3 first_name VARCHAR2(10), 4 last_name VARCHAR2(10), 5 dob DATE, 6 phone VARCHAR2(12), 7 address AddressType 8 ) NOT FINAL; 9 /
Type created. SQL> SQL> CREATE Or replace TYPE business_PersonType UNDER PersonType (
2 title VARCHAR2(20), 3 company VARCHAR2(20) 4 ) 5 /
Type created. SQL> SQL> SQL> CREATE TABLE object_business_customers OF business_PersonType
2 /
Table created. SQL> SQL> SQL> INSERT INTO object_business_customers VALUES (
2 business_PersonType(1, "John", "Brown", "01-FEB-1933", "800-555-3333",
3 AddressType("2 Ave", "town", "MA", "12345"),"Manager", "XYZ Corp")
4 );
1 row created. SQL> SQL> SQL> SQL> SELECT *
2 FROM object_business_customers; ID FIRST_NAME LAST_NAME DOB PHONE
--- -------------------- -------------------- --------- ------------ ADDRESS(STREET, CITY, STATE, ZIP)
TITLE COMPANY
--------------------
1 John Brown 01-FEB-33 800-555-3333
ADDRESSTYPE("2 Ave", "town", "MA", "12345") Manager XYZ Corp
SQL> SQL> SQL> drop table object_business_customers; Table dropped. SQL> SQL> SQL> drop type business_PersonType; Type dropped. SQL> SQL> drop type persontype; Type dropped. SQL> SQL> drop type addresstype; Type dropped. SQL></source>
Insert into table with sub type column
<source lang="sql">
SQL> SQL> SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
2 id NUMBER, 3 make VARCHAR2(15), 4 model VARCHAR2(15) 5 ) NOT FINAL NOT INSTANTIABLE; 6 /
SQL> SQL> CREATE Or replace TYPE SubProduct1 UNDER ProductType (
2 convertible CHAR(1) 3 ); 4 /
Type created. SQL> CREATE Or replace TYPE SubProduct2 UNDER ProductType (
2 sidecar CHAR(1) 3 ); 4 /
Type created. SQL> CREATE TABLE product OF ProductType; Table created. SQL> CREATE TABLE cars OF SubProduct1; Table created. SQL> CREATE TABLE motorcycles OF SubProduct2; Table created. SQL> SQL> INSERT INTO cars VALUES (
2 SubProduct1(1, "AA", "MR2", "Y") 3 );
1 row created. SQL> SQL> INSERT INTO motorcycles VALUES (
2 SubProduct2(1, "BB", "V-Rod", "N") 3 );
1 row created. SQL> SELECT *
2 FROM cars;
ID MAKE MODEL C
--------------- --------------- -
1 AA MR2 Y
SQL> SQL> drop table product; Table dropped. SQL> drop table cars; Table dropped. SQL> drop table motorcycles; Table dropped. SQL> SQL> SQL></source>
NOT FINAL clause
<source lang="sql">
SQL> SQL> SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
2 street VARCHAR2(15), 3 city VARCHAR2(15), 4 state CHAR(2), 5 zip VARCHAR2(5) 6 ) 7 /
Type created. SQL> SQL> CREATE Or Replace TYPE PersonType AS OBJECT (
2 id NUMBER, 3 first_name VARCHAR2(10), 4 last_name VARCHAR2(10), 5 dob DATE, 6 phone VARCHAR2(12), 7 address AddressType 8 ) NOT FINAL; 9 /
Type created. SQL> SQL> CREATE Or replace TYPE business_PersonType UNDER PersonType (
2 title VARCHAR2(20), 3 company VARCHAR2(20) 4 ) 5 /
Type created. SQL> SQL> CREATE TABLE object_business_customers OF business_PersonType
2 /
Table created. SQL> SQL> INSERT INTO object_business_customers VALUES (
2 business_PersonType(1, "John", "Brown", "01-FEB-1933", "800-555-3333",
3 AddressType("2 Ave", "town", "MA", "12345"),"Manager", "XYZ Corp")
4 );
1 row created. SQL> SQL> SQL> SELECT *
2 FROM object_business_customers; ID FIRST_NAME LAST_NAME DOB PHONE
--- -------------------- -------------------- --------- ------------ ADDRESS(STREET, CITY, STATE, ZIP)
TITLE COMPANY
--------------------
1 John Brown 01-FEB-33 800-555-3333
ADDRESSTYPE("2 Ave", "town", "MA", "12345") Manager XYZ Corp
SQL> SQL> SQL> drop table object_business_customers; Table dropped. SQL> SQL> SQL> drop type business_PersonType; Type dropped. SQL> SQL> drop type persontype; Type dropped. SQL> SQL> drop type addresstype; Type dropped. SQL> SQL></source>
NOT INSTANTIABLE Object Types
You can mark an object type as NOT INSTANTIABLE, which prevents objects of that type from being created.
<source lang="sql">
SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
2 id NUMBER, 3 make VARCHAR2(15), 4 model VARCHAR2(15) 5 ) NOT FINAL NOT INSTANTIABLE; 6 /
Type created.
SQL> SQL> CREATE Or replace TYPE SubProduct1 UNDER ProductType (
2 convertible CHAR(1) 3 ); 4 /
Type created. SQL> CREATE Or replace TYPE SubProduct2 UNDER ProductType (
2 sidecar CHAR(1) 3 ); 4 /
Type created. SQL> CREATE TABLE product OF ProductType; Table created. SQL> CREATE TABLE cars OF SubProduct1; Table created. SQL> CREATE TABLE motorcycles OF SubProduct2; Table created. SQL> SQL> INSERT INTO cars VALUES (
2 SubProduct1(1, "AA", "MR2", "Y") 3 );
1 row created. SQL> SQL> INSERT INTO motorcycles VALUES (
2 SubProduct2(1, "BB", "V-Rod", "N") 3 );
1 row created. SQL> SELECT *
2 FROM cars;
ID MAKE MODEL C
--------------- --------------- -
1 AA MR2 Y
SQL> SQL> drop table product; Table dropped. SQL> drop table cars; Table dropped. SQL> drop table motorcycles; Table dropped. SQL> SQL> SQL></source>
Query table with sub type column
<source lang="sql">
SQL> SQL> SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
2 id NUMBER, 3 make VARCHAR2(15), 4 model VARCHAR2(15) 5 ) NOT FINAL NOT INSTANTIABLE; 6 /
Type created. SQL> SQL> CREATE Or replace TYPE SubProduct1 UNDER ProductType (
2 convertible CHAR(1) 3 ); 4 /
Type created. SQL> CREATE Or replace TYPE SubProduct2 UNDER ProductType (
2 sidecar CHAR(1) 3 ); 4 /
Type created. SQL> CREATE TABLE product OF ProductType; Table created. SQL> CREATE TABLE cars OF SubProduct1; Table created. SQL> CREATE TABLE motorcycles OF SubProduct2; Table created. SQL> SQL> INSERT INTO cars VALUES (
2 SubProduct1(1, "AA", "MR2", "Y") 3 );
1 row created. SQL> SQL> INSERT INTO motorcycles VALUES (
2 SubProduct2(1, "BB", "V-Rod", "N") 3 );
1 row created. SQL> SELECT *
2 FROM cars;
ID MAKE MODEL C
--------------- --------------- -
1 AA MR2 Y
SQL> SQL> drop table product; Table dropped. SQL> drop table cars; Table dropped. SQL> drop table motorcycles; Table dropped. SQL> SQL> SQL> SQL></source>
select from inherited object columns
<source lang="sql">
SQL> SQL> SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
2 street VARCHAR2(15), 3 city VARCHAR2(15), 4 state CHAR(2), 5 zip VARCHAR2(5) 6 ) 7 /
Type created. SQL> SQL> CREATE Or Replace TYPE PersonType AS OBJECT (
2 id NUMBER, 3 first_name VARCHAR2(10), 4 last_name VARCHAR2(10), 5 dob DATE, 6 phone VARCHAR2(12), 7 address AddressType 8 ) NOT FINAL; 9 /
Type created. SQL> SQL> SQL> CREATE Or replace TYPE business_PersonType UNDER PersonType (
2 title VARCHAR2(20), 3 company VARCHAR2(20) 4 ) 5 /
Type created. SQL> SQL> CREATE TABLE object_business_customers OF business_PersonType
2 /
Table created. SQL> SQL> SQL> INSERT INTO object_business_customers VALUES (
2 business_PersonType(1, "John", "Brown", "01-FEB-1933", "800-555-3333",
3 AddressType("2 Ave", "town", "MA", "12345"),"Manager", "XYZ Corp")
4 );
1 row created. SQL> SQL> SQL> SELECT * FROM object_business_customers;
ID FIRST_NAME LAST_NAME DOB PHONE
--- -------------------- -------------------- --------- ------------ ADDRESS(STREET, CITY, STATE, ZIP)
TITLE COMPANY
--------------------
1 John Brown 01-FEB-33 800-555-3333
ADDRESSTYPE("2 Ave", "town", "MA", "12345") Manager XYZ Corp
SQL> SQL> SQL> drop table object_business_customers; Table dropped. SQL> SQL> SQL> drop type business_PersonType; Type dropped. SQL> SQL> drop type persontype; Type dropped. SQL> SQL> drop type addresstype; Type dropped. SQL> SQL></source>
The NOT FINAL clause indicates that object can be inherited from when defining another type.
Default is FINAL, meaning that the object type cannot be inherited from.
To have a new type inherit attributes and methods from an existing type, you use the UNDER clause when defining your new type.
<source lang="sql">
SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
2 street VARCHAR2(15), 3 city VARCHAR2(15), 4 state CHAR(2), 5 zip VARCHAR2(5) 6 ) 7 /
Type created. SQL> SQL> CREATE Or Replace TYPE PersonType AS OBJECT (
2 id NUMBER, 3 first_name VARCHAR2(10), 4 last_name VARCHAR2(10), 5 dob DATE, 6 phone VARCHAR2(12), 7 address AddressType 8 ) NOT FINAL; 9 /
Type created. SQL> SQL> SQL> CREATE Or replace TYPE business_PersonType UNDER PersonType (
2 title VARCHAR2(20), 3 company VARCHAR2(20) 4 ) 5 /
Type created. SQL> SQL> CREATE TABLE object_business_customers OF business_PersonType
2 /
Table created. SQL> SQL> SQL> INSERT INTO object_business_customers VALUES (
2 business_PersonType(1, "John", "Brown", "01-FEB-1933", "800-555-3333",
3 AddressType("2 Ave", "town", "MA", "12345"),"Manager", "XYZ Corp")
4 );
1 row created. SQL> SQL> SELECT *
2 FROM object_business_customers; ID FIRST_NAME LAST_NAME DOB PHONE ADDRESS(STREET, CITY, STATE, ZIP) TITLE COMPANY
--------------------
1 John Brown 01-FEB-33 800-555-3333 ADDRESSTYPE("2 Ave", "town", "MA", "12345") Manager XYZ Corp
SQL> SQL> SQL> drop table object_business_customers; Table dropped. SQL> SQL> drop type business_PersonType; Type dropped. SQL> SQL> drop type persontype; Type dropped. SQL> SQL> drop type addresstype; Type dropped. SQL> SQL></source>
Type Inheritance
<source lang="sql">
SQL> SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
2 street VARCHAR2(15), 3 city VARCHAR2(15), 4 state CHAR(2), 5 zip VARCHAR2(5) 6 ) 7 /
Type created. SQL> SQL> CREATE Or Replace TYPE PersonType AS OBJECT (
2 id NUMBER, 3 first_name VARCHAR2(10), 4 last_name VARCHAR2(10), 5 dob DATE, 6 phone VARCHAR2(12), 7 address AddressType 8 ) NOT FINAL; 9 /
Type created. SQL> SQL> drop type persontype; Type dropped. SQL> SQL> drop type addresstype; Type dropped. SQL></source>
