Oracle PL/SQL - SQL%FOUND Attribute: Were Any Rows Affected?

Introduction

SQL%FOUND returns:

NULL if no SELECT or DML statement has run
TRUE if a SELECT statement returned one or more rows or a DML statement affected one or more rows
FALSE otherwise

The following code uses SQL%FOUND to determine if a DELETE statement affected any rows.

Demo

SQL>
SQL> drop table departments;

Table dropped.--   w w w .ja v a2  s . c  o  m

SQL> CREATE TABLE departments(
  2  department_id NUMBER(4),
  3  department_name VARCHAR2(30)CONSTRAINT dept_name_nn NOT NULL,
  4  manager_id NUMBER(6),
  5  location_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO departments VALUES( 10, 'Administration', 200, 1700);
SQL> INSERT INTO departments VALUES( 20, 'Marketing', 201, 1800);
SQL> INSERT INTO departments VALUES( 30, 'Purchasing', 114, 1700);
SQL> INSERT INTO departments VALUES( 40, 'Human Resources', 203, 2400);
SQL> INSERT INTO departments VALUES( 50, 'Shipping', 121, 1500);
SQL>
SQL>
SQL> DROP TABLE dept_temp;

SQL> CREATE TABLE dept_temp AS
  2    SELECT * FROM departments;
SQL>
SQL> CREATE OR REPLACE PROCEDURE p (
  2    dept_no NUMBER
  3  ) AUTHID DEFINER AS
  4  BEGIN
  5    DELETE FROM dept_temp
  6    WHERE department_id = dept_no;
  7
  8    IF SQL%FOUND THEN
  9      DBMS_OUTPUT.PUT_LINE (
 10        'Delete succeeded for department number ' || dept_no
 11      );
 12    ELSE
 13      DBMS_OUTPUT.PUT_LINE ('No department number ' || dept_no);
 14    END IF;
 15  END;
 16  /

Procedure created.

SQL>
SQL> BEGIN
  2    p(270);
  3    p(400);
  4  END;
  5  /
No department number 270
No department number 400

PL/SQL procedure successfully completed.

SQL>