Mega Code Archive
Delete rows using PLSQL literals and variables
<![CDATA[
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> 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> 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> 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>
]]>