PL/SQL Cursor Exercises: Show the uses of SQL%FOUND to determine if a DELETE statement affected any rows

PL/SQL Cursor: Exercise-5 with Solution

Write a program in PL/SQL to show the uses of SQL%FOUND to determine if a DELETE statement affected any rows.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
  SELECT employee_id, first_name, last_name 
  FROM employees;
 
CREATE OR REPLACE PROCEDURE test_proc (
  z_emp_id NUMBER
) AUTHID DEFINER AS
BEGIN
  DELETE FROM emp_temp
  WHERE employee_id = z_emp_id;
 
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE (
      'Delete succeeded for employee_id: ' || z_emp_id
    );
  ELSE
    DBMS_OUTPUT.PUT_LINE ('No employee of ID '|| z_emp_id||'is found.');
  END IF;
END;
/
BEGIN
  test_proc(175);
  test_proc(444);
END;
/

Sample Output:

Delete succeeded for employee_id: 175
No employee of ID 444is found.

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to show the uses of implicit cursor without using any attribute.
Next: Write a program in PL/SQL to show the uses of SQL%NOTFOUND to determine if a UPDATE statement affected any rows.