Conditional INSERT Statement : Insert with subquery « Insert Delete Update « Oracle PL / SQL

1. Oracle PL / SQL
2. Insert Delete Update
3. Insert with subquery

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>

   
    
    
  

Related examples in the same category