Oracle PL/SQL/PL SQL/Delete Data
Содержание
- 1 Create dynamic delete from command
- 2 Delete a row and output referenced value
- 3 Delete data statement with WHERE clauses.
- 4 Delete employee with stored procedure
- 5 DELETE statement can be used within PL/SQL programs to delete a row or a set of rows
- 6 DELETE statement in PL/SQL block
- 7 Delete statement with variable
- 8 Use procedure to delete departments
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
-------------------- -------------------- ------------------------------ ---------------
- 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>
</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>