Check sql%rowcount : Cursor Attributes « Cursor « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Cursor
3. Cursor Attributes

SQL>
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> create table employee_type (
  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 employee_type(id,code,description)values(1,'C','Contractor' );

1 row created.

SQL> insert into employee_type(id,code,description)values(2,'E','Employee' );

1 row created.

SQL> insert into employee_type(id,code,description)values(3,'U','Unknown' );

1 row created.

SQL>
SQL>
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
  2      v_first_name   employee.first_name%TYPE;
  3      v_middle_name  employee.middle_name%TYPE;
  4      v_last_name    employee.last_name%TYPE;
  5      v_name         employee.name%TYPE;
  6      d_birth_date   employee.birth_date%TYPE;
  7      n_count        number;
  8
  9  begin
 10    v_first_name  := 'JOHN';
 11    v_middle_name := 'J.';
 12    v_last_name   := 'DOE';
 13    v_name        := rtrim(v_last_name||', '||v_first_name||' '||v_middle_name);
 14    d_birth_date  := to_date('19800101', 'YYYYMMDD');
 15
 16    begin
 17      insert into employee (
 18             id,
 19             employee_type_id,
 20             external_id,
 21             first_name,
 22             middle_name,
 23             last_name,
 24             name,
 25             birth_date,
 26             gender_id )
 27      select 12,
 28             myCursor.id,
 29             lpad('12', 9, '0'),
 30             v_first_name,
 31             v_middle_name,
 32             v_last_name,
 33             v_name,
 34             d_birth_date,
 35             c2.id
 36      from   employee_type myCursor,
 37             gender c2
 38      where  myCursor.code = 'C'
 39      and    c2.code = 'M'
 40      and not exists (
 41        select 1
 42        from   employee x
 43        where  x.name = v_name
 44        and    x.birth_date = d_birth_date
 45        and    x.gender_id  = c2.id );
 46
 47      n_count := sql%rowcount;
 48    exception
 49      when OTHERS then
 50        raise_application_error(-20006, SQLERRM||' on insert employee');
 51    end;
 52
 53    DBMS_OUTPUT.PUT_LINE(to_char(n_count)||' row(s) inserted.');
 54  end;
 55  /
1 row(s) inserted.

PL/SQL procedure successfully completed.

SQL>
SQL> drop table gender;

Table dropped.

SQL>
SQL> drop table employee;

Table dropped.

SQL>
SQL> drop table employee_type;

Table dropped.

SQL>
SQL>