Oracle PL/SQL/PL SQL/Delete Data — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Версия 13:45, 26 мая 2010

Create dynamic delete from command

    
SQL>
SQL> CREATE or replace PROCEDURE delete_rows (table_name IN VARCHAR2,condition IN VARCHAR2 DEFAULT NULL) AS
  2      where_clause VARCHAR2(100) := " WHERE " || condition;
  3  BEGIN
  4      IF condition IS NULL THEN
  5          where_clause := NULL;
  6      END IF;
  7      EXECUTE IMMEDIATE "DELETE FROM " || table_name || where_clause;
  8  END;
  9  /
Procedure created.
SQL>



Delete a row and output referenced value

   
SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z");
1 row created.
SQL>
SQL>
SQL> DECLARE
  2     v_emp emp%ROWTYPE;
  3  BEGIN
  4     SELECT * INTO v_emp FROM emp WHERE id = 54;
  5
  6     DELETE FROM emp WHERE id = v_emp.id;
  7
  8     DBMS_OUTPUT.PUT_LINE("emp "||v_emp.fname||" "||v_emp.lname||" has been deleted");
  9
 10     COMMIT;
 11
 12  EXCEPTION
 13     WHEN OTHERS
 14     THEN
 15        DBMS_OUTPUT.PUT_LINE(SQLERRM);
 16        ROLLBACK;
 17  END;
 18  /
ORA-01403: no data found
PL/SQL procedure successfully completed.
SQL>
SQL> set escape off
SQL>
SQL>
SQL> drop table emp;
Table dropped.



Delete data statement with WHERE clauses.

   
SQL>
SQL> CREATE TABLE session (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    description      VARCHAR2(2000),
  5    max_lecturer     NUMBER(3),
  6    current_lecturer NUMBER(3),
  7    num_credits      NUMBER(1),
  8    room_id          NUMBER(5)
  9    );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL> select * from session;
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
HIS   101.00
History 101
       30.00            11.00        4.00 ########
HIS   301.00
History 301
       30.00              .00        4.00 ########
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
CS    101.00
Computer Science 101
       50.00              .00        4.00 ########
ECN   203.00
Economics 203
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
       15.00              .00        3.00 ########
CS    102.00
Computer Science 102
       35.00             3.00        4.00 ########
MUS   410.00
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
Music 410
        5.00             4.00        3.00 ########
ECN   101.00
Economics 101
       50.00              .00        4.00 ########

DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
NUT   307.00
Nutrition 307
       20.00             2.00        4.00 ########
MUS   100.00
Music 100
      100.00              .00        3.00
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------

9 rows selected.
SQL>
SQL> DECLARE
  2    v_Department  CHAR(3);
  3  BEGIN
  4    v_Department := "CS";
  5    DELETE FROM session
  6      WHERE department = v_Department;
  7  END;
  8  /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from session;
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
HIS   101.00
History 101
       30.00            11.00        4.00 ########
HIS   301.00
History 301
       30.00              .00        4.00 ########
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
ECN   203.00
Economics 203
       15.00              .00        3.00 ########
MUS   410.00
Music 410
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
        5.00             4.00        3.00 ########
ECN   101.00
Economics 101
       50.00              .00        4.00 ########
NUT   307.00
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
Nutrition 307
       20.00             2.00        4.00 ########
MUS   100.00
Music 100
      100.00              .00        3.00

7 rows selected.
SQL>
SQL> drop table session;
Table dropped.
SQL>



Delete employee with stored procedure

   
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE emp
  2     (id         VARCHAR2(10) NOT NULL,
  3      course     VARCHAR2(10),
  4      year       VARCHAR2(4),
  5      PRIMARY KEY (id));
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE Delete_emp(delete_id emp.id%TYPE) AS
  2
  3  BEGIN
  4     DELETE FROM emp
  5     WHERE id = delete_id;
  6  END Delete_emp;
  7  /
Procedure created.
SQL> drop table emp;
Table dropped.



DELETE statement can be used within PL/SQL programs to delete a row or a set of rows

  

SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
Hit a key to continue
ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL>
SQL> -- DELETE statement can be used within PL/SQL programs to delete a row or a set of rows.
SQL>
SQL>
SQL> BEGIN
  2     DELETE FROM employee;
  3  END;
  4  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from employee;
no rows selected
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>
SQL>



DELETE statement in PL/SQL block

   
SQL>
SQL>
SQL> CREATE TABLE session (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    description      VARCHAR2(2000),
  5    max_lecturer     NUMBER(3),
  6    current_lecturer NUMBER(3),
  7    num_credits      NUMBER(1),
  8    room_id          NUMBER(5)
  9    );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL> select * from lecturer;
      ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS
-------- -------------------- -------------------- ------------------------------ ---------------
######## Scott                Lawson               Computer Science                         11.00
######## Mar                  Wells                History                                   4.00
######## Jone                 Bliss                Computer Science                          8.00
######## Man                  Kyte                 Economics                                 8.00
######## Pat                  Poll                 History                                   4.00
######## Tim                  Viper                History                                   4.00
######## Barbara              Blues                Economics                                 7.00
######## David                Large                Music                                     4.00
######## Chris                Elegant              Nutrition                                 8.00
######## Rose                 Bond                 Music                                     7.00
######## Rita                 Johnson              Nutrition                                 8.00
      ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS
-------- -------------------- -------------------- ------------------------------ ---------------
######## Sharon               Clear                Computer Science                          3.00
12 rows selected.
SQL>
SQL> select * from session;
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
HIS   101.00
History 101
       30.00            11.00        4.00 ########
HIS   301.00
History 301
       30.00              .00        4.00 ########
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
CS    101.00
Computer Science 101
       50.00              .00        4.00 ########
ECN   203.00
Economics 203
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
       15.00              .00        3.00 ########
CS    102.00
Computer Science 102
       35.00             3.00        4.00 ########
MUS   410.00
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
Music 410
        5.00             4.00        3.00 ########
ECN   101.00
Economics 101
       50.00              .00        4.00 ########

DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
NUT   307.00
Nutrition 307
       20.00             2.00        4.00 ########
MUS   100.00
Music 100
      100.00              .00        3.00
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------

9 rows selected.
SQL>
SQL> DECLARE
  2    myLecturerCutoff  NUMBER;
  3  BEGIN
  4    myLecturerCutoff := 10;
  5    DELETE FROM session
  6      WHERE current_lecturer < myLecturerCutoff;
  7
  8    DELETE FROM lecturer
  9      WHERE current_credits = 0
 10      AND   major = "Economics";
 11  END;
 12  /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from lecturer;
      ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS
-------- -------------------- -------------------- ------------------------------ ---------------
######## Scott                Lawson               Computer Science                         11.00
######## Mar                  Wells                History                                   4.00
######## Jone                 Bliss                Computer Science                          8.00
######## Man                  Kyte                 Economics                                 8.00
######## Pat                  Poll                 History                                   4.00
######## Tim                  Viper                History                                   4.00
######## Barbara              Blues                Economics                                 7.00
######## David                Large                Music                                     4.00
######## Chris                Elegant              Nutrition                                 8.00
######## Rose                 Bond                 Music                                     7.00
######## Rita                 Johnson              Nutrition                                 8.00
      ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS
-------- -------------------- -------------------- ------------------------------ ---------------
######## Sharon               Clear                Computer Science                          3.00
12 rows selected.
SQL>
SQL> select * from session;
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
HIS   101.00
History 101
       30.00            11.00        4.00 ########

SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL>
SQL>



Delete statement with variable

   
SQL>
SQL> CREATE TABLE session (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    description      VARCHAR2(2000),
  5    max_lecturer     NUMBER(3),
  6    current_lecturer NUMBER(3),
  7    num_credits      NUMBER(1),
  8    room_id          NUMBER(5)
  9    );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL> select * from session;
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
HIS   101.00
History 101
       30.00            11.00        4.00 ########
HIS   301.00
History 301
       30.00              .00        4.00 ########
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
CS    101.00
Computer Science 101
       50.00              .00        4.00 ########
ECN   203.00
Economics 203
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
       15.00              .00        3.00 ########
CS    102.00
Computer Science 102
       35.00             3.00        4.00 ########
MUS   410.00
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
Music 410
        5.00             4.00        3.00 ########
ECN   101.00
Economics 101
       50.00              .00        4.00 ########

DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------
NUT   307.00
Nutrition 307
       20.00             2.00        4.00 ########
MUS   100.00
Music 100
      100.00              .00        3.00
DEP   COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS  ROOM_ID
------------ ---------------- ----------- --------

9 rows selected.
SQL>
SQL> DECLARE
  2   Department  CHAR(3);
  3  BEGIN
  4    Department := "CS";
  5    DELETE FROM session
  6      WHERE department = Department;
  7  END;
  8  /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from session;
no rows selected
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL>



Use procedure to delete departments

    
SQL> CREATE TABLE Department (
  2  DepartmentID INT NOT NULL PRIMARY KEY,
  3  Name VARCHAR(50) NOT NULL,
  4  Description VARCHAR(200) NULL);
SQL>
SQL> CREATE SEQUENCE DepartmentIDSeq;
SQL>
SQL> CREATE OR REPLACE TRIGGER DepartmentAutonumberTrigger
  2  BEFORE INSERT ON Department
  3  FOR EACH ROW
  4  BEGIN
  5    SELECT DepartmentIDSeq.NEXTVAL
  6    INTO :NEW.DepartmentID FROM DUAL;
  7  END;
  8  /
SQL>
SQL> INSERT INTO Department (Name, Description) VALUES ("Software", "Coding");
SQL> INSERT INTO Department (Name, Description) VALUES ("Hardware", "Building");
SQL> INSERT INTO Department (Name, Description) VALUES ("QA", "Testing");
SQL>
SQL> CREATE TABLE Category (
  2  CategoryID INT NOT NULL PRIMARY KEY,
  3  DepartmentID INT NOT NULL,
  4  Name VARCHAR(50) NOT NULL,
  5  Description VARCHAR (200) NULL);
SQL>
SQL> CREATE SEQUENCE CategoryIDSeq;
SQL>
SQL> CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
  2  BEFORE INSERT ON Category
  3  FOR EACH ROW
  4  BEGIN
  5     SELECT CategoryIDSeq.NEXTVAL
  6     INTO :NEW.CategoryID FROM DUAL;
  7  END;
  8  /
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, "Local", "In town");
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, "Remote", "Telecommute");
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (2, "Masks", "By bits");
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, "Wireless", "Not connected");
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, "Wired", "Connected");
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE Product (
  2  ProductID INT NOT NULL PRIMARY KEY,
  3  Name VARCHAR(50) NOT NULL,
  4  Description VARCHAR(1000) NOT NULL,
  5  Price NUMBER NULL,
  6  ImagePath VARCHAR(50) NULL,
  7  soldout NUMBER(1,0) NULL,
  8  Promotion NUMBER(1,0) NULL);
SQL>
SQL> CREATE SEQUENCE ProductIDSeq;
SQL>
SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
  2  BEFORE INSERT ON Product
  3  FOR EACH ROW
  4  BEGIN
  5     SELECT ProductIDSeq.NEXTVAL
  6     INTO :NEW.ProductID FROM DUAL;
  7  END;
  8  /
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion) VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion) VALUES ("Ruler","Long",14.99, "ruler.jpg", 0, 0);
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion) VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion) VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion) VALUES ("Mouse", "Wireless",9.99, "mouse.jpg",  1, 0);
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion) VALUES ("Keyboard","keyboard",3.75, "keyboard.jpg", 0, 0);
SQL>
SQL>
SQL> CREATE TABLE ProductCategory (
  2  ProductID INT NOT NULL,
  3  CategoryID INT NOT NULL,
  4  PRIMARY KEY (ProductID, CategoryID)
  5  );
SQL>
SQL>
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5);
SQL>
SQL> CREATE PROCEDURE DeleteDepartment
  2  (DeptID IN integer)
  3  AS
  4    BEGIN
  5      DELETE FROM Department
  6      WHERE DepartmentID = DeptID
  7      AND NOT EXISTS (SELECT Name FROM Category WHERE DepartmentID = DeptID);
  8    END;
  9  /

SQL>
SQL> drop table department;
SQL> drop sequence DepartmentIDSeq;
SQL> drop table Product;
SQL> drop table ProductCategory;
SQL> drop table Category;
SQL> drop sequence CategoryIDSeq;
SQL> drop sequence ProductIDSeq;