Oracle PL/SQL Tutorial/Object Oriented/Insert
Содержание
- 1 Inserting a row into an object table by supplying the values in the same way in a relational
- 2 Inserting a row of object into an object table
- 3 Inserting Rows into the Table with object type column
- 4 INSERT object instance by calling its constructor
- 5 Insert statement with nested type
- 6 INSERT Values into a Table with the Column Type in It
- 7 Insert value to a table with "table of custom type" as table column type
- 8 Object Relational tables
- 9 Reference column name in an object table
- 10 Reference type constructor in insert statement
- 11 Use user-defined type in insert statement
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;