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:
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?
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics