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
<source lang="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> 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></source>
Inserting a row of object into an object table
<source lang="sql">
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></source>
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.
<source lang="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 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></source>
INSERT object instance by calling its constructor
<source lang="sql">
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></source>
Insert statement with nested type
<source lang="sql">
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></source>
INSERT Values into a Table with the Column Type in It
<source lang="sql">
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></source>
Insert value to a table with "table of custom type" as table column type
<source lang="sql">
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></source>
Object Relational tables
<source lang="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> 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></source>
Reference column name in an object table
<source lang="sql">
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></source>
Reference type constructor in insert statement
<source lang="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 ( "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.</source>
Use user-defined type in insert statement
<source lang="sql">
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;</source>
