ORA-01422 Error Occurred When I Call Procedure with Number Parameter

Question

I want to write PL/SQL Procedure that updates salary which is less than 2000. I wrote this procedure.And when i call it with integer id 'ORA-01422: exact fetch returns more than requested number of rows' error is thrown by TOAD. My procedure is like below:

DECLARE
PROCEDURE update_salary(ID customers.id%type) is
 c_sal customers.salary%type;
BEGIN
 SELECT salary 
 INTO c_sal 
 FROM customers 
 WHERE id = ID;
 dbms_output.put_line ('Before update operation salary is:' || c_sal);
 --dbms_output.put_line ('Parameter :' || ID);
 IF (c_sal <= 2000) THEN
 UPDATE customers 
 SET salary = salary + 1000
 WHERE id = ID;
 dbms_output.put_line ('Salary updated');
 END IF;
 SELECT salary 
 INTO c_sal 
 FROM customers 
 WHERE id=ID;
 dbms_output.put_line ('After update operation salary is:' || c_sal);
END;
BEGIN
update_salary(1);
END;
/

I print parameter id with dbms_output. The parameter is coming correctly. How can i fix this error !

Answer 1

Naming your parameter the same way you named your column is quite unfortunate.

 SELECT salary 
 INTO c_sal 
 FROM customers 
 WHERE id = ID;

In the above statement, typing id in lowercase or ID in uppercase makes no difference, it is case insensitive. Just because you named your PL/SQL parameter ID (in uppercase), does not mean the database will use it when you type ID (in uppercase) in the above. Even if the id in customers has unique values, the above SELECT returns all* rows from the table, because the id column precedes the ID parameter, and id always* equals ID (*except for NULLs).

You could just simply use a different name for your parameter, for example I usually use the p_ prefix for them, so your ID parameter could be changed to P_ID:

DECLARE
PROCEDURE update_salary(P_ID customers.id%type) is
 c_sal customers.salary%type;
BEGIN
 SELECT salary 
 INTO c_sal 
 FROM customers 
 WHERE id = P_ID;
 dbms_output.put_line ('Before update operation salary is:' || c_sal);
 --dbms_output.put_line ('Parameter :' || ID);
 SELECT salary 
 INTO c_sal
 FROM customers
 WHERE id = P_ID;
 IF (c_sal <= 2000) THEN
 UPDATE customers 
 SET salary = salary + 1000
 WHERE id = P_ID;
 dbms_output.put_line ('Salary updated');
 END IF;
 SELECT salary 
 INTO c_sal 
 FROM customers 
 WHERE id=P_ID;
 dbms_output.put_line ('After update operation salary is:' || c_sal);
END;
BEGIN
update_salary(1);
END;
/

Answer 2

This mean query:

SELECT salary 
 INTO c_sal 

return more than 1 row check the table for count records with ID = 1

if ID by any legal reason non unique - need make LOOP