в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:02:41Z

1 версия

Новая страница

==Crate table with object column==



<source lang="sql">

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>

</source>

==Create a new type and add it to a table==



<source lang="sql">

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>

</source>

==Create a table based on user-defined object only==



<source lang="sql">

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.

</source>

==Create a table with nested user defined type as column==



<source lang="sql">

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>

</source>

==Create a table with user define varray as column type==



<source lang="sql">

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>

</source>

==Create table based on single data type==



<source lang="sql">

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.

</source>

==Create table with nested types==



<source lang="sql">

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>

</source>

==Create table with user defined type as column==



<source lang="sql">

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>

</source>

==Create type and use it as table column==



<source lang="sql">

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;

</source>

==Implementation of many to many using object references==



<source lang="sql">

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>

</source>

==Implementation of multiple inheritance relationship==



<source lang="sql">

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>

</source>

==Implementation of one to many using object references==



<source lang="sql">

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>

</source>

==Multilevel aggregation relationships using nested tables==



<source lang="sql">

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.

</source>

==Nested table==



<source lang="sql">

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> --

</source>

==Object table: a table of type==



<source lang="sql">

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>

</source>

==One to one using object references==



<source lang="sql">

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.

</source>

==Use a table alias and the name of the object==



<source lang="sql">

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>

</source>

==Use user-defined type to combine query logic==



<source lang="sql">

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>

</source>

==Use user-defined varray type as column type==



<source lang="sql">

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> --

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Object Oriented Database/Object Table - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия