Oracle PL/SQL/Object Oriented Database/Insert
Содержание
- 1 Insert data into an object table
- 2 Insert inherited object
- 3 INSERT Values into a Table that Contains Row Objects (TCRO)
- 4 INSERT Values into a Table with the Column Type in It
- 5 Insert value to an objec table as normal table
- 6 Insert value to a specific column for an object table with insert...select statement
- 7 Insert value to one column in an object table
- 8 Insert value with type variable
- 9 Object table insert
- 10 One-step INSERTs into an object table
- 11 Use table function in insert statement for a user-defined type column
Insert data into an object table
SQL>
2
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>
SQL>
SQL> insert into address_table values ( address( 2, "123 Main Street", "NJ", "07728" ) )
2 /
1 row created.
SQL>
SQL> select * from address_table;
ID
----------
STREET
--------------------------------------------------------------------------------
ST ZIPCODE
-- -----------
2
123 Main Street
NJ 07728
SQL> drop table address_table;
Table dropped.
SQL>
Insert inherited object
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>
SQL> create or replace
2 type new_employee under person (
3 empno number,
4 hiredate date,
5 work_phone varchar2(100),
6 overriding member function get_phone_number return varchar2,
7 member function get_home_phone_number return varchar2 )
8 not final
9 /
Type created.
SQL>
SQL> create or replace
2 type body new_employee as
3 overriding member function get_phone_number return varchar2 is
4 begin
5 return self.work_phone;
6 end;
7 member function get_home_phone_number return varchar2 is
8 begin
9 return self.phone;
10 end;
11 end;
12 /
Type body created.
SQL>
SQL>
SQL> select treat( x.p as new_employee ).empno empno,
2 x.p.last_name last_name
3 from person_table x
4 where p is of ( new_employee )
5 /
no rows selected
SQL>
SQL> select * from person_table;
no rows selected
SQL>
SQL> drop table person_table;
Table dropped.
SQL>
SQL> drop type new_employee;
Type dropped.
SQL>
SQL> drop type person;
Type dropped.
SQL>
INSERT Values into a Table that Contains Row Objects (TCRO)
SQL> -- INSERT Values into a Table that Contains Row Objects (TCRO)
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE addressType 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 address_table OF addressType;
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
2 address REF addressType scope is address_table);
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO client VALUES ("Jones",null);
1 row created.
SQL>
SQL>
SQL> SELECT * FROM client;
NAME ADDRESS
--------- --------------------------------------------------
Jones
SQL>
SQL>
SQL>
SQL> drop table client;
Table dropped.
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL>
INSERT Values into a Table with the Column Type in It
SQL> CREATE OR REPLACE TYPE addressType as OBJECT(
2 street VARCHAR2(20),
3 city VARCHAR2(20),
4 state CHAR(2),
5 zip CHAR(5));
6 /
Type created.
SQL> CREATE TABLE emp (empno NUMBER(3),
2 name VARCHAR2(20),
3 address addressType);
Table created.
SQL>
SQL>
SQL> INSERT INTO emp VALUES (101, "Adam", addressType("1 A St.","Mobile","AL","36608"));
1 row created.
SQL>
SQL> SELECT * FROM emp;
EMPNO NAME
---------- --------------------
ADDRESS(STREET, CITY, STATE, ZIP)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
101 Adam
addressType("1 A St.", "Mobile", "AL", "36608")
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL>
Insert value to an objec table as normal table
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>
SQL> insert into address_table values ( 1, "1910 Oracle Way", "VA", "21090" )
2 /
1 row created.
SQL>
SQL> select * from address_table;
ID
----------
STREET
--------------------------------------------------------------------------------
ST ZIPCODE
-- -----------
1
1910 Oracle Way
VA 21090
SQL> drop table address_table;
Table dropped.
SQL>
SQL>
Insert value to a specific column for an object table with insert...select statement
SQL>
SQL> create or replace type address_type
2 as object
3 ( city varchar2(30),
4 street varchar2(30),
5 state varchar2(2),
6 zip number
7 )
8 /
Type created.
SQL> create or replace type person_type
2 as object
3 ( name varchar2(30),
4 dob date,
5 home_address address_type,
6 work_address address_type
7 )
8 /
Type created.
SQL>
SQL>
SQL> create table people1 of person_type
2 /
Table created.
SQL>
SQL>
SQL> insert into people1(name)
2 select rownum from all_objects;
12213 rows created.
SQL>
SQL> drop table people1;
Table dropped.
SQL>
SQL> drop type person_type;
Type dropped.
SQL> drop type address_type;
Type dropped.
SQL> --
Insert value to one column in an object table
SQL>
SQL> create or replace type address_type
2 as object
3 ( city varchar2(30),
4 street varchar2(30),
5 state varchar2(2),
6 zip number
7 )
8 /
Type created.
SQL> create or replace type person_type
2 as object
3 ( name varchar2(30),
4 dob date,
5 home_address address_type,
6 work_address address_type
7 )
8 /
Type created.
SQL>
SQL>
SQL> create table people1 of person_type
2 /
Table created.
SQL>
SQL>
SQL> insert into people1 (name)
2 values ( "Hello World!" );
1 row created.
SQL>
SQL> select * from people1;
NAME DOB
-------------------- --------------------
HOME_ADDRESS(CITY, STREET, STATE, ZIP)
------------------------------------------------------------------------------------------------------------------------------------------------------
WORK_ADDRESS(CITY, STREET, STATE, ZIP)
------------------------------------------------------------------------------------------------------------------------------------------------------
Hello World!
1 row selected.
SQL>
SQL> drop table people1;
Table dropped.
SQL>
SQL> drop type person_type;
Type dropped.
SQL> drop type address_type;
Type dropped.
SQL> --
Insert value with type variable
SQL>
SQL>
SQL> create or replace
2 type person as object(
3 first_name varchar2(100),
4 last_name varchar2(100) )
5 /
Type created.
SQL>
SQL>
SQL> create table person_table( p person );
Table created.
SQL>
SQL>
SQL> declare
2 l_person person;
3 begin
4 l_person := person( "C", "B" );
5 insert into person_table
6 values ( l_person );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from person_table;
P(FIRST_NAME, LAST_NAME)
--------------------------------------------------------------------------------
PERSON("C", "B")
SQL>
SQL> drop table person_table;
Table dropped.
SQL>
SQL>
Object table insert
SQL>
SQL> create or replace type address_type
2 as object
3 ( city varchar2(30),
4 street varchar2(30),
5 state varchar2(2),
6 zip number
7 )
8 /
Type created.
SQL> create or replace type person_type
2 as object
3 ( name varchar2(30),
4 dob date,
5 home_address address_type,
6 work_address address_type
7 )
8 /
Type created.
SQL>
SQL>
SQL> create table people1 of person_type
2 /
Table created.
SQL>
SQL>
SQL> desc people
ERROR:
ORA-24372: invalid object for describe
SQL>
SQL>
SQL> insert into people1 values ( "Tom", "15-mar-1965",
2 address_type( "Reston", "123 Main Street", "Va", "45678" ),
3 address_type( "Redwood", "1 Oracle Way", "Ca", "23456" ) );
1 row created.
SQL> /
1 row created.
SQL>
SQL>
SQL> select * from people1;
NAME DOB
-------------------- --------------------
HOME_ADDRESS(CITY, STREET, STATE, ZIP)
------------------------------------------------------------------------------------------------------------------------------------------------------
WORK_ADDRESS(CITY, STREET, STATE, ZIP)
------------------------------------------------------------------------------------------------------------------------------------------------------
Tom 15-MAR-1965 00:00:00
ADDRESS_TYPE("Reston", "123 Main Street", "Va", 45678)
ADDRESS_TYPE("Redwood", "1 Oracle Way", "Ca", 23456)
Tom 15-MAR-1965 00:00:00
ADDRESS_TYPE("Reston", "123 Main Street", "Va", 45678)
ADDRESS_TYPE("Redwood", "1 Oracle Way", "Ca", 23456)
2 rows selected.
SQL>
SQL>
SQL> drop table people1;
Table dropped.
SQL> drop type person_type;
Type dropped.
SQL> drop type address_type;
Type dropped.
SQL>
SQL> --
One-step INSERTs into an object table
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE addressType 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 address_table OF addressType;
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
2 address REF addressType scope is address_table);
Table created.
SQL>
SQL>
SQL> INSERT INTO client SELECT "Walsh", REF(aa) FROM address_table aa;
0 rows created.
SQL>
SQL> SELECT name, DEREF(address) FROM client;
no rows selected
SQL>
SQL>
SQL> drop table client;
Table dropped.
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL>
Use table function in insert statement for a user-defined type column
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 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> 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 as table of emp_type;
2 /
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>
SQL>
SQL> insert into dept_and_emp
2 select dept.*,
3 CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm from emp ) AS emp_tab_type )
4 from dept
5 /
4 rows created.
SQL> insert into table
2 ( select emps from dept_and_emp where deptno = 10 )
3 values
4 ( 1234, "NewEmp", "CLERK", 7782, sysdate, 1200, null );
1 row created.
SQL>
SQL>
SQL> drop table emp cascade constraints;
Table dropped.
SQL> drop table dept cascade constraints;
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>
SQL> --