Oracle PL/SQL Tutorial/Object Oriented/Insert

Материал из SQL эксперт
Версия от 10:04, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Inserting a row into an object table by supplying the values in the same way in a relational

SQL>
SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
  2    id          NUMBER,
  3    name        VARCHAR2(15),
  4    description VARCHAR2(22),
  5    price       NUMBER(5, 2),
  6    days_valid  NUMBER
  7  )
  8  /
Type created.
SQL>
SQL> CREATE TABLE object_products OF ProductType
  2  /
Table created.
SQL>
SQL> INSERT INTO object_products (
  2    id, name, description, price, days_valid
  3  ) VALUES (
  4    2, "AAA", "BBB", 2.99, 5
  5  );
1 row created.
SQL>
SQL> select * from object_products;
 ID NAME            DESCRIPTION                 PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
  2 AAA             BBB                          2.99          5
SQL>
SQL> drop table object_products;
Table dropped.
SQL>


Inserting a row of object into an object table

SQL>
SQL>
SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
  2    id          NUMBER,
  3    name        VARCHAR2(15),
  4    description VARCHAR2(22),
  5    price       NUMBER(5, 2),
  6    days_valid  NUMBER
  7  )
  8  /
Type created.
SQL>
SQL> CREATE TABLE object_products OF ProductType
  2  /
Table created.
SQL>
SQL>
SQL> INSERT INTO object_products VALUES (
  2    ProductType(1, "AAA", "BBB", 3.95, 10)
  3  );
1 row created.
SQL>
SQL> select * from object_products;
 ID NAME            DESCRIPTION                 PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
  1 AAA             BBB                          3.95         10
SQL>
SQL> drop table object_products;
Table dropped.
SQL>
SQL>


Inserting Rows into the Table with object type column

When inserting a row into a table containing a column object, you must supply the attribute for that object using a constructor.

The constructor has the same name as the object type and accepts parameters fo attributes of the object.



SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
  2    id          NUMBER,
  3    name        VARCHAR2(15),
  4    description VARCHAR2(22),
  5    price       NUMBER(5, 2),
  6    days_valid  NUMBER
  7  );
  8  /
Type created.
SQL>
SQL> CREATE TABLE products (
  2    product           ProductType,
  3    count NUMBER
  4  );
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO products (product,count) VALUES (
  2            ProductType(1, "AA", "BBB", 3.95, 10),50
  3  );
1 row created.
SQL>
SQL> INSERT INTO products (product,count) VALUES (
  2            ProductType(2, "CC", "DDDD", 2.99, 5),25
  3  );
1 row created.
SQL>
SQL> select * from products;
PRODUCT(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID)     COUNT
----------
PRODUCTTYPE(1, "AA", "BBB", 3.95, 10)                 50
PRODUCTTYPE(2, "CC", "DDDD", 2.99, 5)                 25

SQL>
SQL>
SQL> drop table products;
Table dropped.
SQL>
SQL>


INSERT object instance by calling its constructor

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT
  2              (line1 VARCHAR2(20),
  3               line2 VARCHAR2(20),
  4               city VARCHAR2(20),
  5               state_code VARCHAR2(2),
  6               zip VARCHAR2(13),
  7    MEMBER FUNCTION get_address RETURN VARCHAR2,
  8    MEMBER PROCEDURE set_address
  9              (addressLine1 VARCHAR2,
 10               addressLine2 VARCHAR2,
 11               address_city VARCHAR2,
 12               address_state VARCHAR2,
 13               address_zip VARCHAR2)
 14  );
 15  /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY address AS
  2    MEMBER FUNCTION get_address RETURN VARCHAR2
  3    IS
  4    BEGIN
  5      RETURN (SELF.line1||" "||SELF.line2||" "||SELF.city||", "||SELF.state_code||" "||SELF.zip);
  6    END get_address;
  7    MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
  8                  addressLine2 VARCHAR2,
  9                  address_city VARCHAR2,
 10                  address_state VARCHAR2,
 11                  address_zip VARCHAR2)
 12    IS
 13    BEGIN
 14      line1 :=addressLine1;
 15      line2 :=addressLine2;
 16      city :=address_city;
 17      state_code :=address_state;
 18      zip :=address_zip;
 19    END set_address;
 20  END;
 21  /
Type body created.
SQL>
SQL> CREATE TABLE address_master OF address;
Table created.
SQL>
SQL> INSERT INTO address_master VALUES (address("19 J","Reading Rd","Vancouver","NJ","00000"));
1 row created.
SQL>
SQL> select * from address_master;
LINE1                LINE2                CITY                 ST
-------------------- -------------------- -------------------- --
ZIP
-------------
19 J                 Reading Rd           Vancouver            NJ
00000

1 row selected.
SQL>
SQL>
SQL> DECLARE
  2    addressValue_ref REF address;
  3  BEGIN
  4    INSERT INTO address_master a VALUES (address("5 Street","#101C","F","NY","00000")) RETURNING REF(a)INTO addressValue_ref;
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table address_master;
Table dropped.
SQL>
SQL>


Insert statement with nested type

SQL> create type addressType as object
  2  (Street   VARCHAR2(50),
  3  City      VARCHAR2(25),
  4  State     CHAR(2),
  5  Zip       NUMBER);
  6  /
SQL>
SQL> create type personType as object
  2  (Name     VARCHAR2(25),
  3   Address  addressType);
  4  /
SQL>
SQL>
SQL> create or replace type personType as object
  2  (Name     VARCHAR2(25),
  3   Address  addressType);
  4  /
SQL>
SQL>
SQL> create table myemp
  2  (cid    NUMBER,
  3   Person         personType);
SQL>
SQL>
SQL> insert into myemp values(1,personType("SomeName",addressType("StreetValue","CityValue","ST",11111)));
SQL>
SQL>
SQL>
SQL> select C.cid, C.Person.Name
  2    from myemp C;
                                                              
       CID  PERSON.NAME
----------  -------------------------
         1  SomeName
                   
SQL>
SQL>
SQL> drop table myemp;
SQL> drop type personType;
SQL> drop type addressType;
SQL>


INSERT Values into a Table with the Column Type in It

SQL>
SQL> CREATE OR REPLACE TYPE ADDRESS_OBJ as OBJECT(
  2  street VARCHAR2(20),
  3  city VARCHAR2(20),
  4  state CHAR(2),
  5  zip CHAR(5))
  6  /
Type created.
SQL>
SQL> CREATE TABLE emp (
  2  empno NUMBER(3),
  3  name VARCHAR2(20),
  4  address ADDRESS_OBJ)
  5  /
Table created.
SQL>
SQL> INSERT INTO emp VALUES (101, "A",ADDRESS_OBJ("1 St.","AAA","AL","36608"))
  2  /
1 row created.
SQL> select * from emp;
     EMPNO NAME
---------- --------------------
ADDRESS(STREET, CITY, STATE, ZIP)
---------------------------------------
       101 A
ADDRESS_OBJ("1 St.", "AAA", "AL", "36608")

SQL>
SQL> drop table emp;
Table dropped.
SQL> drop type ADDRESS_OBJ;
Type dropped.
SQL>
SQL>


Insert value to a table with "table of custom type" as table column type

SQL>
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> create or replace type emp_type
  2    as object
  3    (empno       number(4),
  4     ename       varchar2(10),
  5     job         varchar2(9),
  6     mgr         number(4),
  7     hiredate    date,
  8     sal         number(7, 2),
  9     comm        number(7, 2)
 10    );
 11  /
Type created.
SQL>
SQL> create or replace type emp_tab_type
  2    as table of emp_type
  3  /
Type created.
SQL> create table dept_and_emp
  2    (deptno number(2) primary key,
  3     dname     varchar2(14),
  4     loc       varchar2(13),
  5     emps      emp_tab_type
  6    )
  7    nested table emps store as emps_nt;
Table created.
SQL>
SQL> alter table emps_nt add constraint emps_empno_unique
  2               unique(empno)
  3  /
Table altered.

SQL>
SQL> insert into dept_and_emp
  2    select dept.*,
  3       CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm
  4                         from emp
  5                         where emp.deptno = dept.deptno ) AS emp_tab_type )
  6      from dept
  7  /
4 rows created.
SQL> select deptno, dname, loc, d.emps AS employees
  2    from dept_and_emp d
  3    where deptno = 10
  4  /
    DEPTNO DNAME          LOC
---------- -------------- -------------
EMPLOYEES(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM)
----------------------------------------------------------------------------------------------------
        10 ACCOUNTING     NEW YORK
EMP_TAB_TYPE(EMP_TYPE(7782, "CLARK", "MANAGER", 7839, "09-JUN-81", 2450, NULL), EMP_TYPE(7839, "KING
", "PRESIDENT", NULL, "17-NOV-81", 5000, NULL), EMP_TYPE(7934, "MILLER", "CLERK", 7782, "23-JAN-82",
 1300, NULL))

SQL> drop table dept cascade constraint;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL> drop table dept_and_emp;
Table dropped.
SQL> drop type emp_tab_type;
Type dropped.
SQL> drop type emp_type;
Type dropped.
SQL>


Object Relational tables

SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT
  2              (line1 VARCHAR2(20),
  3               line2 VARCHAR2(20),
  4               city VARCHAR2(20),
  5               state_code VARCHAR2(2),
  6               zip VARCHAR2(13),
  7    MEMBER FUNCTION get_address RETURN VARCHAR2,
  8    MEMBER PROCEDURE set_address
  9              (addressLine1 VARCHAR2,
 10               addressLine2 VARCHAR2,
 11               address_city VARCHAR2,
 12               address_state VARCHAR2,
 13               address_zip VARCHAR2)
 14  );
 15  /
Type created.
SQL> CREATE OR REPLACE TYPE BODY address AS
  2    MEMBER FUNCTION get_address RETURN VARCHAR2
  3    IS
  4    BEGIN
  5      RETURN (SELF.line1||" "||SELF.line2||" "||SELF.city||", "||
  6              SELF.state_code||" "||SELF.zip);
  7    END get_address;
  8    MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
  9                  addressLine2 VARCHAR2,
 10                  address_city VARCHAR2,
 11                  address_state VARCHAR2,
 12                  address_zip VARCHAR2)
 13    IS
 14    BEGIN
 15      line1 :=addressLine1;
 16
 17      line2 :=addressLine2;
 18
 19      city :=address_city;
 20
 21      state_code :=address_state;
 22
 23      zip :=address_zip;
 24
 25    END set_address;
 26  END;
 27  /
Type body created.
SQL>
SQL> CREATE TABLE employee
  2  (empid number(10)PRIMARY KEY,
  3   lastname varchar2(30)NOT NULL,
  4   firstname varchar2(30)NOT NULL,
  5   middle_initial varchar2(2),
  6   emp_address address);
Table created.
SQL>
SQL> INSERT INTO employee VALUES (101,"L","BULUSU",null,
  2  address("50 UNION SQUARE","SUITE 101","NEW YORK","NY","10020"));
1 row created.
SQL>
SQL> select * from employee;
 EMPID LASTNAME                       FIRSTNAME
------ ------------------------------ ------------------------------
MI
--
EMP_ADDRESS(LINE1, LINE2, CITY, STATE_CODE, ZIP)
----------------------------------------------------------------------
   101 L                              BULUSU
nu
ll
ADDRESS("50 UNION SQUARE", "SUITE 101", "NEW YORK", "NY", "10020")

1 row selected.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>


Reference column name in an object table

SQL>
SQL>
SQL> create or replace type address_type as object
  2    ( city    varchar2(30),
  3      street  varchar2(30),
  4      state   varchar2(2),
  5      zip     number
  6    )
  7  /
Type created.
SQL> create or replace type person_type as object
  2    ( name             varchar2(30),
  3      dob              date,
  4      home_address     address_type,
  5      work_address     address_type
  6    )
  7  /
Type created.
SQL> create table people of person_type
  2  /
Table created.
SQL> insert into people values ( "Tom", "15-mar-1965",
  2    address_type( "Reston", "1 Street", "Va", "45678" ),
  3    address_type( "Redwood", "1 Way", "Ca", "23456" ) );
1 row created.
SQL> /
1 row created.
SQL>
SQL> insert into people(name) select rownum from all_objects;
12663 rows created.
SQL>
SQL> drop table people;
Table dropped.
SQL>
SQL> drop type person_type;
Type dropped.
SQL> drop type address_type;
Type dropped.
SQL>


Reference type constructor in insert statement

SQL>
SQL> create or replace type address_type as object
  2    ( city    varchar2(30),
  3      street  varchar2(30),
  4      state   varchar2(2),
  5      zip     number
  6    )
  7  /
Type created.
SQL> create or replace type person_type as object
  2    ( name             varchar2(30),
  3      dob              date,
  4      home_address     address_type,
  5      work_address     address_type
  6    )
  7  /
Type created.
SQL> create table people of person_type
  2  /
Table created.
SQL> insert into people values ( "T", "15-mar-1965",
  2                   address_type( "R", "1 Street", "Va", "45678" ),
  3                   address_type( "A", "1 Way", "Ca", "23456" ) );
1 row created.
SQL> /
1 row created.
SQL> insert into people (name) values ( "Hello World!" );
1 row created.
SQL>
SQL>
SQL> drop table people;
Table dropped.
SQL>
SQL> drop type person_type;
Type dropped.
SQL> drop type address_type;
Type dropped.


Use user-defined type in insert statement

SQL> create type addressType as object
  2  (Street  VARCHAR2(50),
  3   City    VARCHAR2(25),
  4   State   CHAR(2),
  5   Zip     NUMBER);
  6  /
SQL> create type personType as object
  2  (Name     VARCHAR2(25),
  3   Address  addressType);
  4  /
SQL>
SQL>
SQL> create table CUSTOMER(
  2    cid  NUMBER,
  3    Person       personType
  4  );
SQL>
SQL> insert into CUSTOMER values(1,personType("SomeName",addressType("Street","City","ST",11111)));
SQL> insert into CUSTOMER values(1,personType("SomeName",addressType("Street2","City2","ST",11111)));
SQL>
SQL> drop type personType force;
SQL>
SQL> drop type addressType force;
SQL>
SQL> drop table CUSTOMER;