Oracle PL/SQL Tutorial/PL SQL Programming/Delete

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

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>