Oracle PL/SQL/PL SQL/Delete Data

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

Create dynamic delete from command

   <source lang="sql">
   

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>



 </source>
   
  


Delete a row and output referenced value

   <source lang="sql">
  

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.


 </source>
   
  


Delete data statement with WHERE clauses.

   <source lang="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> 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>


 </source>
   
  


Delete employee with stored procedure

   <source lang="sql">
  

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.


 </source>
   
  


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

   <source lang="sql">
 

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>


 </source>
   
  


DELETE statement in PL/SQL block

   <source lang="sql">
  

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

-------------------- -------------------- ------------------------------ ---------------
                1. Scott Lawson Computer Science 11.00
                2. Mar Wells History 4.00
                3. Jone Bliss Computer Science 8.00
                4. Man Kyte Economics 8.00
                5. Pat Poll History 4.00
                6. Tim Viper History 4.00
                7. Barbara Blues Economics 7.00
                8. David Large Music 4.00
                9. Chris Elegant Nutrition 8.00
                10. Rose Bond Music 7.00
                11. Rita Johnson Nutrition 8.00
     ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS

-------------------- -------------------- ------------------------------ ---------------
                1. 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

-------------------- -------------------- ------------------------------ ---------------
                1. Scott Lawson Computer Science 11.00
                2. Mar Wells History 4.00
                3. Jone Bliss Computer Science 8.00
                4. Man Kyte Economics 8.00
                5. Pat Poll History 4.00
                6. Tim Viper History 4.00
                7. Barbara Blues Economics 7.00
                8. David Large Music 4.00
                9. Chris Elegant Nutrition 8.00
                10. Rose Bond Music 7.00
                11. Rita Johnson Nutrition 8.00
     ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS

-------------------- -------------------- ------------------------------ ---------------
                1. 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>


 </source>
   
  


Delete statement with variable

   <source lang="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> 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>


 </source>
   
  


Use procedure to delete departments

   <source lang="sql">
   

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;



 </source>