Oracle PL/SQL/Insert Delete Update/Insert with subquery
Содержание
- 1 Adding Multiple Rows to a Table
- 2 Conditional INSERT Statement
- 3 Insert bulk by insert ... into ... select
- 4 Insert into ... select
- 5 Merge into a table
- 6 Perform more complicated inserts using sub-selects
- 7 To insert records into a table using a subquery:
- 8 Use bulk collect and rownum to insert first 10 records
Adding Multiple Rows to a Table
SQL>
SQL> CREATE TABLE Student (
2 StudentID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL);
Table created.
SQL>
SQL> INSERT INTO Student (StudentID,Name) VALUES (1,"Tom");
1 row created.
SQL> INSERT INTO Student (StudentID,Name) VALUES (2,"Jack");
1 row created.
SQL> INSERT INTO Student (StudentID,Name) VALUES (3,"Mary");
1 row created.
SQL> INSERT INTO Student (StudentID,Name) VALUES (4,"Bill");
1 row created.
SQL> INSERT INTO Student (StudentID,Name) VALUES (5,"Cat");
1 row created.
SQL>
SQL>
SQL> CREATE TABLE Professor (
2 ProfessorID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL);
Table created.
SQL>
SQL>
SQL> INSERT INTO Professor (ProfessorID, Name)
2 SELECT StudentID + 7, Name
3 FROM Student;
5 rows created.
SQL>
SQL> drop table Professor;
Table dropped.
SQL> drop table Student;
Table dropped.
Conditional INSERT Statement
SQL>
SQL> CREATE TABLE BookBorrow(
2 member CHAR(5)
3 ,book CHAR(5)
4 ,dueDate DATE
5 );
Table created.
SQL> INSERT INTO BookBorrow VALUES ("bb01","bk002",DATE "2005-03-22");
1 row created.
SQL> INSERT INTO BookBorrow VALUES ("bb01","bk002",DATE "2005-09-21");
1 row created.
SQL> INSERT INTO BookBorrow VALUES ("bb02","bk005",DATE "2005-09-21");
1 row created.
SQL> INSERT INTO BookBorrow(member,book,dueDate)VALUES ("bb01","bk002",CURRENT_DATE + INTERVAL "14" DAY );
1 row created.
SQL> INSERT INTO BookBorrow(member,book,dueDate)VALUES ("bb02","bk005",CURRENT_DATE - INTERVAL "1" DAY );
1 row created.
SQL>
SQL>
SQL> CREATE TABLE BookReturn(
2 book CHAR(5)
3 ,member CHAR(5)
4 ,returnDate DATE
5 ,fine DECIMAL(10,2)
6 );
Table created.
SQL>
SQL> CREATE TABLE room(
2 who VARCHAR(20)
3 ,wht VARCHAR(20) NOT NULL
4 ,whn DATE NOT NULL
5 ,PRIMARY KEY (whn,wht)
6 );
Table created.
SQL>
SQL> INSERT INTO BookReturn(book,member,returnDate,fine)
2 VALUES ("bb01","bk002",CURRENT_DATE,(SELECT 0.20 FROM BookBorrow
3 WHERE member="bb01" AND book="bk002"
4 HAVING MAX(dueDate) < CURRENT_DATE));
1 row created.
SQL>
SQL> INSERT INTO BookReturn(book,member,returnDate,fine)
2 VALUES ("bb02","bk005",CURRENT_DATE,(SELECT 0.20 FROM BookBorrow
3 WHERE member="bb02" AND book="bk005"
4 HAVING MAX(dueDate) < CURRENT_DATE));
1 row created.
SQL>
SQL> SELECT * FROM BookReturn;
BOOK MEMBE RETURNDAT FINE
----- ----- --------- ----------
bb01 bk002 26-OCT-09
bb02 bk005 26-OCT-09 .2
2 rows selected.
SQL>
SQL> INSERT INTO room(whn,wht,who)
2 SELECT DATE "2006-07-13","Room 1","Prof. Plum"
3 FROM dual
4 WHERE NOT EXISTS (SELECT who FROM room
5 WHERE whn = DATE "2006-07-13"
6 AND wht="Room 1");
1 row created.
SQL>
SQL> INSERT INTO room(whn,wht,who)
2 SELECT DATE "2006-07-13","Ballroom","Miss. Scarlet"
3 FROM dual
4 WHERE NOT EXISTS (SELECT who FROM room
5 WHERE whn = DATE "2006-07-13"
6 AND wht="Ballroom");
1 row created.
SQL>
SQL> SELECT * FROM room;
WHO WHT WHN
-------------------- -------------------- ---------
Prof. Plum Room 1 13-JUL-06
Miss. Scarlet Ballroom 13-JUL-06
2 rows selected.
SQL>
SQL> DROP TABLE BookBorrow;
Table dropped.
SQL> DROP TABLE BookReturn;
Table dropped.
SQL> DROP TABLE room;
Table dropped.
SQL>
SQL>
SQL>
Insert bulk by insert ... into ... select
SQL>
SQL> CREATE TABLE project (
2 pro_id NUMBER(4),
3 pro_name VARCHAR2(40),
4 budget NUMBER(9,2),
5 CONSTRAINT project_pk PRIMARY KEY (pro_id)
6 );
Table created.
SQL>
SQL>
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345);
1 row created.
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456);
1 row created.
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567);
1 row created.
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678);
1 row created.
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789);
1 row created.
SQL>
SQL>
SQL> SET ECHO ON
SQL> INSERT INTO PROJECT (pro_id, pro_name)
2 SELECT pro_id+8000,
3 SUBSTR(pro_name,1,31) || " Overhead"
4 FROM project;
5 rows created.
SQL>
SQL>
SQL>
SQL> drop table project ;
Table dropped.
Insert into ... select
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 EMP1 (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>
SQL>
SQL> BEGIN
2 INSERT INTO emp1
3 SELECT * FROM emp;
4 COMMIT;
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from emp1;
EMPNO ENAME JOB MGR
---------- ---------- --------- ----------
HIREDATE SAL COMM
-------------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902
17-DEC-1980 00:00:00 800
20
7499 ALLEN SALESMAN 7698
20-FEB-1981 00:00:00 1600 300
30
7521 WARD SALESMAN 7698
22-FEB-1981 00:00:00 1250 500
30
7566 JONES MANAGER 7839
02-APR-1981 00:00:00 2975
20
7654 MARTIN SALESMAN 7698
28-SEP-1981 00:00:00 1250 1400
30
7698 BLAKE MANAGER 7839
01-MAY-1981 00:00:00 2850
30
7782 CLARK MANAGER 7839
09-JUN-1981 00:00:00 2450
10
7788 SCOTT ANALYST 7566
09-DEC-1982 00:00:00 3000
20
7839 KING PRESIDENT
17-NOV-1981 00:00:00 5000
10
7844 TURNER SALESMAN 7698
08-SEP-1981 00:00:00 1500 0
30
7876 ADAMS CLERK 7788
12-JAN-1983 00:00:00 1100
20
7900 JAMES CLERK 7698
03-DEC-1981 00:00:00 950
30
7902 FORD ANALYST 7566
03-DEC-1981 00:00:00 3000
20
7934 MILLER CLERK 7782
23-JAN-1982 00:00:00 1300
10
14 rows selected.
SQL>
SQL> drop table emp1;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL>
SQL> --
Merge into a table
SQL>
SQL>
SQL> create table table_a( id number );
Table created.
SQL> insert into table_a
2 select rownum from all_tables where rownum < 7;
6 rows created.
SQL>
SQL> select * from table_a;
ID
----------
1
2
3
4
5
6
6 rows selected.
SQL> create table table_b(
2 id number,
3 status varchar2(255) );
Table created.
SQL>
SQL> insert into table_b values( 1, "NEW" );
1 row created.
SQL> insert into table_b values( 3, "NEW" );
1 row created.
SQL> insert into table_b values( 5, "NEW" );
1 row created.
SQL> select * from table_b;
ID STATUS
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 NEW
3 NEW
5 NEW
SQL> merge into table_b b using ( select * from table_a ) a
2 on ( a.id = b.id )
3 when matched then update set status = "OLD"
4 when not matched then insert values ( a.id, "NEW" );
6 rows merged.
SQL>
SQL> select * from table_b;
ID STATUS
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 OLD
3 OLD
5 OLD
6 NEW
4 NEW
2 NEW
6 rows selected.
SQL>
SQL> drop table table_a;
Table dropped.
SQL> drop table table_b;
Table dropped.
Perform more complicated inserts using sub-selects
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));
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL>
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
SQL> -- perform more complicated inserts using sub-selects.
SQL>
SQL> INSERT INTO emp
2 (empno, ename)
3 SELECT deptno, dname
4 FROM dept;
SQL>
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
SQL>
SQL>
SQL>
SQL> drop table dept;
SQL> drop table emp;
SQL>
SQL>
To insert records into a table using a subquery:
SQL>
SQL>
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL>
SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2),
3 DNAME VARCHAR2(14),
4 LOC VARCHAR2(13)
5 );
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> INSERT INTO dept
2 SELECT * FROM dept;
4 rows created.
SQL>
SQL> drop table dept;
Table dropped.
Use bulk collect and rownum to insert first 10 records
SQL>
SQL>
SQL> create table myTable
2 as
3 select rownum id, a.*
4 from all_objects a
5 where 1=0
6 /
Table created.
SQL>
SQL> declare
2 l_owner dbms_sql.varchar2_table;
3 l_object_name dbms_sql.varchar2_table;
4 l_object_type dbms_sql.varchar2_table;
5 l_created dbms_sql.varchar2_table;
6
7 cursor c is
8 select owner, object_name, object_type, created
9 from myTable
10 order by created DESC;
11 begin
12 select owner, object_name, object_type, created
13 bulk collect into l_owner, l_object_name, l_object_type, l_created from ( select owner, object_name, object_type, created from myTable order by created DESC )
14 where ROWNUM <= 10;
15
16 open c;
17 fetch c bulk collect
18 into l_owner, l_object_name, l_object_type, l_created
19 limit 10;
20 close c;
21 end;
22 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>