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
<source lang="sql">
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>
</source>
Insert inherited object
<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> 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>
</source>
INSERT Values into a Table that Contains Row Objects (TCRO)
<source lang="sql">
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>
</source>
INSERT Values into a Table with the Column Type in It
<source lang="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> 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>
</source>
Insert value to an objec table as normal table
<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> 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>
</source>
Insert value to a specific column for an object table with insert...select statement
<source lang="sql">
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> --
</source>
Insert value to one column in an object table
<source lang="sql">
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> --
</source>
Insert value with type variable
<source lang="sql">
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>
</source>
Object table insert
<source lang="sql">
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> --
</source>
One-step INSERTs into an object table
<source lang="sql">
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>
</source>
Use table function in insert statement for a user-defined type column
<source lang="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 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> --
</source>
