Oracle PL/SQL/Object Oriented Database/Object Table

Материал из SQL эксперт
Перейти к: навигация, поиск

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 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
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>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/Object_Oriented_Database/Object_Table&oldid=2698»