Oracle PL/SQL Tutorial/PL SQL Programming/Delete
Содержание
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 rows using PL/SQL literals and variables
<source lang="sql">
SQL> SQL> create table employee (
2 id number, 3 employee_type_id number, 4 external_id varchar2(30), 5 first_name varchar2(30), 6 middle_name varchar2(30), 7 last_name varchar2(30), 8 name varchar2(100), 9 birth_date date, 10 gender_id number);
Table created. SQL> SQL> SQL> SQL> create table gender (
2 id number, 3 code varchar2(30), 4 description varchar2(80), 5 active_date date default SYSDATE not null, 6 inactive_date date );
Table created. SQL> SQL> SQL> SQL> insert into gender ( id, code, description ) values ( 1, "F", "Female" ); 1 row created. SQL> insert into gender ( id, code, description ) values ( 2, "M", "Male" ); 1 row created. SQL> insert into gender ( id, code, description ) values ( 3, "U", "Unknown" ); 1 row created. SQL> SQL> SQL> SQL> set serveroutput on size 1000000; SQL> SQL> declare
2 n_count number; 3 v_code gender.code%TYPE := "M"; 4 begin 5 begin 6 delete from employee d 7 where d.name = "AAA J." 8 and d.birth_date = to_date("19800101", "YYYYMMDD") 9 and d.gender_id = ( 10 select c.id 11 from gender c 12 where c.code = v_code ); 13 n_count := sql%rowcount; 14 exception 15 when OTHERS then 16 raise_application_error(-20001, SQLERRM||" on delete employee"); 17 end; 18 DBMS_OUTPUT.PUT_LINE(to_char(n_count)||" row(s) deleted."); 19 end; 20 /
0 row(s) deleted. PL/SQL procedure successfully completed. SQL> SQL> drop table gender; Table dropped. SQL> SQL> drop table employee; Table dropped. SQL> SQL></source>
DELETE statement with variable
<source lang="sql">
SQL> 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 /
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> SQL> DECLARE
2 v_salary NUMBER; 3 BEGIN 4 v_salary := 2000; 5 6 DELETE FROM employee 7 WHERE salary < v_salary; 8 9 END; 10 /
PL/SQL procedure successfully completed. SQL> SQL> select * from employee; ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
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 6 rows selected. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL></source>
Delete table data in PL/SQL
<source lang="sql">
SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key, 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 /
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> SQL> set serveroutput on SQL> SQL> BEGIN
2 DELETE FROM employee 3 WHERE first_name = "junk"; 4 IF (SQL%NOTFOUND) 5 THEN 6 dbms_output.put_line("No such employee"); 7 END IF; 8 END; 9 /
No such employee PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
How DELETEs work with PL/SQL
<source lang="sql">
SQL> SQL> CREATE TABLE authors (
2 id NUMBER PRIMARY KEY, 3 first_name VARCHAR2(50), 4 last_name VARCHAR2(50) 5 );
Table created. SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (1, "Marlene", "Theriault");
1 row created. SQL> SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (2, "Rachel", "Carmichael");
1 row created. SQL> SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (3, "James", "Viscusi");
1 row created. SQL> SQL> SET SERVEROUTPUT ON SQL> SQL> SQL> DECLARE
2 3 v_author AUTHORS%ROWTYPE; 4 5 BEGIN 6 7 SELECT * 8 INTO v_author 9 FROM authors 10 WHERE id = 1; 11 12 DELETE FROM authors 13 WHERE id = v_author.id; 14 15 DBMS_OUTPUT.PUT_LINE("Author "||v_author.first_name 16 ||" "||v_author.last_name 17 ||" has been deleted"); 18 19 COMMIT; 20 21 EXCEPTION 22 WHEN OTHERS 23 THEN 24 DBMS_OUTPUT.PUT_LINE(SQLERRM); 25 ROLLBACK; 26 END; 27 /
Author Marlene Theriault has been deleted PL/SQL procedure successfully completed. SQL> SQL> set escape off SQL> SQL> drop table authors; Table dropped.</source>