CODEWITHSUNDEEP
oracle-databaseplsql
user2788235
user2788235

Reputation: 31

PL/SQL: Executing Procedure

I have created a procedure. It is giving an error ( ORA-01422: exact fetch returns more than requested number of rows). As for a specific department_id there are more than one employees. But how to solve this problem ?

Create Procedure PP1
(ID in number, Percent in number, Sal out number, increase_sal out number) IS    
Begin
Select salary, salary *(1+percent/100) into sal, increase_sal    
From employees    
where department_id= id;    
DBMS_OUTPUT.PUT_LINE (sal || ' ' || increase_sal);    
END;    
/    

Variable a number
Variable b number
Exec PP1 (100, 10, :a, :b)
Print a b

Thanks, Kuntal Roy

Upvotes: 0

Views: 107

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

My guess is that you want something like (untested)

CREATE TYPE num_tbl IS TABLE OF NUMBER;

 CREATE PROCEDURE raise_dept_salaries( p_dept_id   IN employees.department_id%type,
                                      p_raise_pct IN NUMBER,
                                      p_old_sals OUT num_tbl,
                                      p_new_sals OUT num_tbl )
 AS
 BEGIN
   SELECT salary
     BULK COLLECT INTO p_old_sals
     FROM employees
    WHERE department_id = p_dept_id;

   UPDATE employees
      SET salary = salary * (1 + p_raise_pct)
    WHERE department_id = p_dept_id
RETURNING salary
     BULK COLLECT INTO p_new_sals;
 END;

Now, splitting things up this way does introduce the possibility that some other session will modify the data between your first SELECT and your UPDATE so this isn't really safe to use in a multi-user environment. Of course, you really wouldn't want to return both the old and the new salaries in the first place since you already know that they are going to be directly related to each other. If you only returned the collection of new salaries, then you would only need a single UPDATE statement and you wouldn't have the race condition.

Upvotes: 1

Related Questions