w3resource

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

PL/SQL Cursor: Exercise-6 with Solution

Write a program in PL/SQL to show the uses of SQL%NOTFOUND to determine if a UPDATE 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,email 
  FROM employees;
  
DECLARE
	z_emp_id NUMBER:=&employee_id;
BEGIN
    UPDATE emp_temp
    SET    email = 'not available'
    WHERE employee_id = z_emp_id;
 
  IF SQL%NOTFOUND THEN
      DBMS_OUTPUT.PUT_LINE ('No employee of ID '|| z_emp_id||' is found.');
  ELSE
    DBMS_OUTPUT.PUT_LINE (
      'Update succeeded for employee_id: ' || z_emp_id
    );
  END IF;
END;
/

Sample Output:

SQL> /
Enter value for employee_id: 298
old   2:        z_emp_id NUMBER:=&employee_id;
new   2:        z_emp_id NUMBER:=298;
No employee of ID 298 is found.

PL/SQL procedure successfully completed.

SQL> /
Enter value for employee_id: 157
old   2:        z_emp_id NUMBER:=&employee_id;
new   2:        z_emp_id NUMBER:=157;
Update succeeded for employee_id: 157

PL/SQL procedure successfully completed.

Flowchart:

Flowchart: PL/SQL Cursor Exercises - Show the uses of SQL%NOTFOUND to determine if a UPDATE statement affected any rows

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to show the uses of SQL%FOUND to determine if a DELETE statement affected any rows.
Next: Write a program in PL/SQL to create a table-based record using the %ROWTYPE attribute.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/plsql-exercises/cursor/plsql-cursor-exercise-6.php