Oracle PL/SQL/Object Oriented Database/Object Table — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 Crate table with object column
- 2 Create a new type and add it to a table
- 3 Create a table based on user-defined object only
- 4 Create a table with nested user defined type as column
- 5 Create a table with user define varray as column type
- 6 Create table based on single data type
- 7 Create table with nested types
- 8 Create table with user defined type as column
- 9 Create type and use it as table column
- 10 Implementation of many to many using object references
- 11 Implementation of multiple inheritance relationship
- 12 Implementation of one to many using object references
- 13 Multilevel aggregation relationships using nested tables
- 14 Nested table
- 15 Object table: a table of type
- 16 One to one using object references
- 17 Use a table alias and the name of the object
- 18 Use user-defined type to combine query logic
- 19 Use user-defined varray type as column type
Crate table with object column
SQL>
SQL>
SQL> --User-defined types
SQL>
SQL> CREATE TYPE address_typ AS OBJECT
2 (StreetNo NUMBER(10),
3 StreetName VARCHAR2(100),
4 AptNo NUMBER(5),
5 City VARCHAR2(100),
6 State VARCHAR2(100),
7 ZipCode NUMBER(9),
8 Country VARCHAR2(100));
9 /
Type created.
SQL>
SQL> CREATE TABLE people
2 (ID NUMBER(5),
3 FirstName VARCHAR2(100),
4 LastName VARCHAR2(100),
5 Address address_typ);
Table created.
SQL>
SQL>
SQL> INSERT INTO people
2 VALUES(10,
3 "John",
4 "Smith",
5 address_typ(123,"Happy Lane", NULL,
6 "Smalltown","Alaska", 12345,"USA") );
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT * FROM people;
ID FIRSTNAME
------------------------ ----------------------------------------------------------------------------------------------------
LASTNAME
----------------------------------------------------------------------------------------------------
ADDRESS(STREETNO, STREETNAME, APTNO, CITY, STATE, ZIPCODE, COUNTRY)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10.00 John
Smith
ADDRESS_TYP(123.00, "Happy Lane", NULL, "Smalltown", "Alaska", 12345.00, "USA")
SQL>
SQL> drop table people;
Table dropped.
SQL>
SQL>
Create a new type and add it to a table
SQL>
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL>
SQL> create table e
2 as
3 select empno, ename, init, mgr, deptno
4 from emp;
SQL>
SQL>
SQL> create or replace type numberlist_t
2 as varray(4) of varchar2(20);
3 /
Type created.
SQL>
SQL> column numlist format a60
SQL>
SQL> alter table e add (numlist numberlist_t);
Table altered.
SQL>
SQL> describe e
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(8)
INIT VARCHAR2(5)
JOB VARCHAR2(8)
MGR NUMBER(4)
BDATE DATE
SAL NUMBER(6,2)
COMM NUMBER(6,2)
DEPTNO NUMBER(2)
NUMLIST NUMBERLIST_T
SQL>
SQL>
SQL> select empno, numlist from e;
SQL>
SQL>
SQL> drop table e;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL>
Create a table based on user-defined object only
SQL>
SQL> create or replace
2 type person as object (
3 first_name varchar2(100),
4 last_name varchar2(100),
5 dob date,
6 phone varchar2(100),
7 member function get_last_name return varchar2,
8 member function get_phone_number return varchar2 )
9 not final
10 /
Type created.
SQL>
SQL>
SQL> create or replace
2 type body person as
3 member function get_last_name return varchar2 is
4 begin
5 return self.last_name;
6 end;
7 member function get_phone_number return varchar2 is
8 begin
9 return self.phone;
10 end;
11 end;
12 /
Type body created.
SQL>
SQL> create table person_table( p person );
Table created.
SQL>
SQL>
SQL> drop table person_table;
Table dropped.
Create a table with nested user defined type as column
SQL>
SQL> create type order_item_type as object (
2 line_item_id number(3),
3 product_id number(6),
4 unit_price number(8,2),
5 quantity number(4)
6 )
7 /
Type created.
SQL>
SQL>
SQL> create type order_item_list_type as table of order_item_type
2 /
Type created.
SQL>
SQL>
SQL> create table orders(
2 order_id number(12) not null,
3 order_date timestamp(6) with local time zone,
4 customer_id number(6),
5 order_items order_item_list_type )
6 nested table order_items store as order_items_tab
7 /
Table created.
SQL>
SQL> drop table orders;
Table dropped.
SQL> drop type order_item_list_type;
Type dropped.
SQL> drop type order_item_type;
Type dropped.
SQL>
Create a table with user define varray as column type
SQL>
SQL> create type employee_type as object (
2 employee_id number,
3 first_name varchar2(30),
4 last_name varchar2(30)
5 );
6 /
Type created.
SQL>
SQL> create type employee_list_type as varray(50) of employee_type
2 /
Type created.
SQL>
SQL>
SQL> create table departments (
2 department_id number,
3 department_name varchar2(30),
4 manager employee_type,
5 employees employee_list_type )
6 /
Table created.
SQL>
SQL>
SQL>
SQL> drop table departments;
Table dropped.
SQL> drop type employee_list_type;
Type dropped.
SQL> drop type employee_type;
Type dropped.
SQL>
SQL>
Create table based on single data type
SQL> create table myTable of xmltype;
Table created.
SQL>
SQL> insert into myTable values(XMLTYPE("
2 <customer>
3 <name>name value</name>
4 <telephone>123 555-1234</telephone>
5 </customer>"))
6 /
1 row created.
SQL>
SQL> select * from myTable;
SYS_NC_ROWINFO$
------------------------------------------------------
<customer>
<name>name value</name>
<telephone>123 555-1234</telephone>
1 row selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
Create table with nested types
SQL>
SQL> --CREATE [OR REPLACE] TYPE <object table schema> AS TABLE OF (object schema)
SQL> --/
SQL> --CREATE TABLE <table schema>
SQL> -- (attribute attribute type, ....,
SQL> -- attribute attribute type,
SQL> -- nested item object table schema);
SQL> -- NESTED TABLE nested item STORE AS storage table schema;
SQL>
SQL> --CREATE TABLE <table schema>
SQL> -- (attribute attribute type, ....,
SQL> -- outer nested item object table schema);
SQL> -- NESTED TABLE <outer nested item>
SQL> -- STORE AS <outer storage table schema>
SQL> -- (NESTED TABLE <inner nested item>
SQL> -- STORE AS <inner storage table schema>);
SQL>
SQL> --Example:
SQL>
SQL> CREATE OR REPLACE TYPE personType AS OBJECT
2 (person_id VARCHAR2(10),
3 person_name VARCHAR2(30))
4 /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE Person_Table AS TABLE OF personType
2 /
Type created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE Course
2 (course_id VARCHAR2(10),
3 course_name VARCHAR2(20),
4 Programmer Person_Table)
5 NESTED TABLE Programmer STORE AS Person_tab;
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> drop table course;
Table dropped.
SQL>
SQL>
SQL>
Create table with user defined type as column
SQL>
SQL> create type employee_type as object (
2 employee_id number,
3 first_name varchar2(30),
4 last_name varchar2(30)
5 );
6 /
Type created.
SQL>
SQL> create type employee_list_type as varray(50) of employee_type
2 /
Type created.
SQL>
SQL>
SQL> create table departments (
2 department_id number,
3 department_name varchar2(30),
4 manager employee_type,
5 employees employee_list_type )
6 /
Table created.
SQL>
SQL>
SQL>
SQL> drop table departments;
Table dropped.
SQL> drop type employee_list_type;
Type dropped.
SQL> drop type employee_type;
Type dropped.
SQL>
Create type and use it as table column
SQL> create or replace type addressType as object
2 (
3 Street VARCHAR2(50),
4 City VARCHAR2(25),
5 State CHAR(2),
6 Zip NUMBER
7 );
8 /
SQL> create table CUSTOMER
2 (
3 Name VARCHAR2(25),
4 Address addressType
5 );
SQL> /
SQL> insert into CUSTOMER values(1,addressType("My Street", "Some City", "ST", 10001));
SQL>
SQL> drop type addressType force;
SQL> drop table customer;
Implementation of many to many using object references
SQL> CREATE OR REPLACE TYPE personType AS OBJECT
2 (person_id VARCHAR2(10),
3 person_name VARCHAR2(30))
4 /
SQL> CREATE OR REPLACE TYPE courseType AS OBJECT
2 (course_id VARCHAR2(10),
3 course_name VARCHAR2(30))
4 /
Type created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE emp OF personType
2 (person_id NOT NULL,
3 PRIMARY KEY (person_id));
Table created.
SQL>
SQL> CREATE TABLE Course OF courseType
2 (course_id NOT NULL,
3 PRIMARY KEY (course_id));
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE Enrolls_in
2 (emp REF personType,
3 course REF courseType);
Table created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL> drop table Course;
Table dropped.
SQL>
SQL>
SQL>
SQL> drop table Enrolls_in;
Table dropped.
SQL>
SQL>
SQL>
Implementation of multiple inheritance relationship
SQL> CREATE TABLE Person
2 (id VARCHAR2(10) NOT NULL,
3 name VARCHAR2(20),
4 address VARCHAR2(35),
5 PRIMARY KEY (id));
SQL>
SQL>
SQL> CREATE TABLE emp
2 (id VARCHAR2(10) NOT NULL,
3 course VARCHAR2(10),
4 year VARCHAR2(4),
5 PRIMARY KEY (id),
6 FOREIGN KEY (id) REFERENCES Person (id) ON DELETE CASCADE);
Table created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE Staff
2 (id VARCHAR2(10) NOT NULL,
3 department VARCHAR2(10),
4 room_no VARCHAR2(4),
5 PRIMARY KEY (id),
6 FOREIGN KEY (id) REFERENCES Person (id) ON DELETE CASCADE);
SQL>
SQL>
SQL> CREATE TABLE Tutor
2 (id VARCHAR2(10) NOT NULL,
3 no_hours NUMBER,
4 rate NUMBER,
5 PRIMARY KEY (id) ,
6 FOREIGN KEY (id) REFERENCES Person (id) ON DELETE CASCADE);
Table created.
SQL>
SQL>
SQL> drop table Person cascade constraints;
Table dropped.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL> drop table Staff;
SQL>
SQL> drop table Tutor;
Table dropped.
SQL>
Implementation of one to many using object references
SQL>
SQL> CREATE OR REPLACE TYPE personType AS OBJECT
2 (person_id VARCHAR2(10),
3 person_name VARCHAR2(30))
4 /
SQL>
SQL> CREATE OR REPLACE TYPE courseType AS OBJECT
2 (course_id VARCHAR2(10),
3 course_name VARCHAR2(30),
4 course_Programmer REF personType)
5 /
Type created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE Programmer OF personType
2 (person_id NOT NULL,
3 PRIMARY KEY (person_id));
Table created.
SQL>
SQL>
SQL> CREATE TABLE Course OF courseType
2 (course_id NOT NULL,
3 PRIMARY KEY (course_id));
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> drop type personType force;
Type dropped.
SQL>
SQL>
SQL>
SQL> drop type courseType force;
Type dropped.
SQL>
SQL>
SQL>
SQL> drop table Programmer;
Table dropped.
SQL>
SQL>
SQL> drop table Course;
Table dropped.
SQL>
Multilevel aggregation relationships using nested tables
SQL>
SQL> CREATE OR REPLACE TYPE PartType AS OBJECT
2 (PartType_id VARCHAR2(10),
3 description VARCHAR2(30))
4 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE PartType_Table AS TABLE OF PartType
2 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE Hard_Disk AS OBJECT
2 (hd_id VARCHAR2(10),
3 capacity VARCHAR2(20),
4 controller PartType_Table)
5 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE Hard_Disk_Table AS TABLE OF Hard_Disk
2 /
Type created.
SQL>
SQL> CREATE TABLE PC
2 (pc_id VARCHAR2(10) NOT NULL,
3 hd Hard_Disk_Table,
4 PRIMARY KEY (pc_id))
5 NESTED TABLE hd STORE AS HD_tab
6 (NESTED TABLE controller STORE AS PartType_tab);
Table created.
SQL>
SQL> drop type PartType force;
Type dropped.
SQL> drop type PartType_Table force;
Type dropped.
SQL> drop type Hard_Disk force;
Type dropped.
SQL> drop type Hard_Disk_Table force;
Type dropped.
SQL> drop table PC;
Table dropped.
Nested table
SQL>
SQL> create or replace type myTableType
2 as table of number(12,2)
3 /
Type created.
SQL>
SQL> create table t
2 ( x int primary key, y myTableType )
3 nested table y store as y_tab
4 /
Table created.
SQL>
SQL> drop table t;
Table dropped.
SQL> drop type myTableType;
Type dropped.
SQL>
SQL>
SQL> --
Object table: a table of type
SQL>
SQL> create or replace
2 type address as object(
3 id number,
4 street varchar2(100),
5 state varchar2(2),
6 zipcode varchar(11)
7 )
8 /
Type created.
SQL>
SQL> create table address_table of address
2 /
Table created.
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL> drop type address;
Type dropped.
SQL>
SQL>
One to one using object references
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE officeType AS OBJECT
2 (office_id VARCHAR2(10),
3 building_name VARCHAR2(20))
4 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE personType AS OBJECT
2 (person_id VARCHAR2(10),
3 person_name VARCHAR2(30),
4 person_office REF officeType)
5 /
Type created.
SQL>
SQL> CREATE TABLE Office OF officeType
2 (office_id NOT NULL,
3 PRIMARY KEY (office_id));
Table created.
SQL>
SQL> CREATE TABLE Programmer OF personType
2 (person_id NOT NULL,
3 PRIMARY KEY (person_id));
Table created.
SQL>
SQL> drop type officeType force;
Type dropped.
SQL> drop type personType force;
Type dropped.
SQL> drop table Office;
Table dropped.
SQL> drop table Programmer;
Table dropped.
Use a table alias and the name of the object
SQL> CREATE OR REPLACE TYPE aobj AS object (
2 state CHAR(2),
3 amt NUMBER(5),
4
5 MEMBER FUNCTION mult (times in number) RETURN number,
6 PRAGMA RESTRICT_REFERENCES(mult, WNDS));
7 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY aobj AS
2 MEMBER FUNCTION mult (times in number) RETURN number
3 IS
4 BEGIN
5 RETURN times * self.amt; /* SEE BELOW */
6 END;
7 END;
8 /
Type body created.
SQL> CREATE TABLE aobjtable (arow aobj);
Table created.
SQL> /
CREATE TABLE aobjtable (arow aobj)
SQL>
SQL>
SQL> INSERT INTO aobjtable VALUES (aobj("FL",25));
1 row created.
SQL> INSERT INTO aobjtable VALUES (aobj("AL",35));
1 row created.
SQL> INSERT INTO aobjtable VALUES (aobj("OH",15));
1 row created.
SQL>
SQL> -- Use a table alias and the name of the object
SQL>
SQL> SELECT x.arow.state, x.arow.amt
2 FROM aobjtable x;
AR AROW.AMT
-- ----------
FL 25
AL 35
OH 15
SQL>
SQL> DESC aobjtable;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
AROW AOBJ
SQL>
SQL> drop table aobjtable;
Table dropped.
SQL>
SQL>
Use user-defined type to combine query logic
SQL> create or replace type myScalarType as object( cnt number, average number )
2 /
Type created.
SQL>
SQL> select username, a.data.cnt, a.data.average
2 from (
3 select username, (select myScalarType( count(*), avg(object_id) ) from all_objects b where b.owner = a.username ) data from all_users a ) A
4 /
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
SYS 6520 5009.74064
SYSTEM 422 6095.87678
OUTLN 7 1172.57143
DIP 0
TSMSYS 2 8606.5
INV15 2 16237.5
DBSNMP 46 9592.65217
INV10 2 16227.5
CTXSYS 338 9877.92012
XDB 334 10800.7485
ANONYMOUS 0
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
MDSYS 458 11667.2009
HR 34 12104.5
FLOWS_FILES 11 12717.2727
FLOWS_020100 1085 12813.424
sqle 530 16254.6849
INV11 2 16229.5
INV12 2 16231.5
INV13 2 16233.5
INV14 2 16235.5
PLSQL 0
INV16 2 16239.5
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
INV17 2 16241.5
INV18 2 16243.5
INV19 2 16245.5
INV20 2 16247.5
DEFINER 4 16250.5
27 rows selected.
SQL>
SQL>
SQL>
Use user-defined varray type as column type
SQL> create type myArrayType
2 as varray(10) of number(12,2)
3 /
Type created.
SQL>
SQL> create table t
2 ( x int primary key, y myArrayType )
3 /
Table created.
SQL>
SQL> drop table t;
Table dropped.
SQL> drop type myArrayType;
Type dropped.
SQL>
SQL>
SQL> --