Q:

Write a program in PL/SQL to show the uses of SQL%NOTFOUND to determine if a UPDATE statement affected any rows

hamza
Save to bookmark
|
2022-05-18
|
  94 
0
Write a program in PL/SQL to show the uses of SQL%NOTFOUND to determine if a UPDATE statement affected any rows.
pl/sql,
login and add your own answer to this question

All Answers

hamza
2022-05-18
need an explanation for this answer? contact us directly to get an explanation for this answer 

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.

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

Similar questions

need a help?

find thousands of online teachers now