Delete rows using PL/SQL literals and variables : Delete « PL SQL Programming « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Programming
3. Delete

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>