CODEWITHSUNDEEP
sqloracle-databaseplsql
JohnD
JohnD

Reputation: 353

Procedure cannot be executed because fetch returns more than requested number of rows

CREATE OR REPLACE
PROCEDURE P_raise
AS
  v_salary     NUMBER;
  v_first_name VARCHAR2(20);
  v_min_salary NUMBER;
BEGIN
  SELECT first_name,
    salary
  INTO v_first_name,
    v_salary
  FROM employees
  WHERE salary =
    (SELECT MIN(salary) FROM employees
    );
  dbms_output.put_line(v_first_name||' has a minimum salary of '||v_salary);
  SELECT MIN(salary) INTO v_min_salary FROM employees;
  IF v_salary = v_min_salary THEN
    UPDATE employees SET salary = salary*1.15 WHERE salary=v_min_salary;
    dbms_output.Put_line(v_first_name||' has an increase of 15% in his salary ');
  END IF;
END;
/

This messy code is used to find the min(salary) and then give it a raise. There are two minimum salary records with the same value. How do I rework my code to by pass the error?

Thanks

Upvotes: 0

Views: 65

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

You can't use SELECT INTO if the statement doesn't return exactly one row. Here you could use a loop, for instance with an implicit cursor:

CREATE OR REPLACE PROCEDURE P_raise AS
BEGIN
   FOR cc IN (SELECT first_name, salary, ROWID
                INTO v_first_name, v_salary
                FROM employees
               WHERE salary = (SELECT MIN(salary) FROM employees)
                 FOR UPDATE NOWAIT) LOOP

      dbms_output.put_line(cc.first_name || ' has a minimum salary of ' 
                           || cc.salary);
      UPDATE employees SET salary = salary * 1.15 WHERE ROWID = cc.rowid;
      dbms_output.Put_line(cc.first_name 
                           || ' has an increase of 15% in his salary');

   END LOOP;
END;

Or use a straight update (which would be more efficient in general, although you won't have the details of which row got updated):

CREATE OR REPLACE PROCEDURE P_raise AS
BEGIN
   UPDATE employees 
      SET salary = salary * 1.15 
    WHERE salary = (SELECT MIN(salary) FROM employees);
   dbms_output.put_line(SQL%ROWCOUNT 
                        || ' employees had their salary increased by 15%');
END;

Upvotes: 1

Related Questions