CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
giggity
giggity

Reputation: 37

Update multiple rows in procedures

CREATE OR REPLACE PROCEDURE FEE_INCR (IN_Percentage IN NUMBER) IS
          v_oldFees number(7,2);
          v_newFees number(7,2);
BEGIN
-- GET THE FEES
          SELECT fees INTO v_oldFees
          from offering

          v_newFees := round(((v_oldFees*IN_Percentage)/100)+v_oldFees);

--UPDATE the record
          UPDATE offering
          set fees = v_newFees;

END;
/

I am trying to write a procedure that will allow user to increase all the fees stored in the offering table. But when I execute the procedure, the error "ORA-01422: exact fetch returns more than requested number of rows" occurred. I assume that procedures can only update a single row? Is there any solution to solve the problem? Thank you

Upvotes: 0

Views: 70

Answers (1)

Popeye
Popeye

Reputation: 35910

Nope. Procedure can update as many records as many you want. But there is issue with your first select which fetches multiple records and you are trying to store them in single variable(v_oldfees) which can hold only one value. That's why you are facing ORA-01422.

You can directly use update statement to update all the fees as follows:

CREATE OR REPLACE PROCEDURE FEE_INCR (IN_Percentage IN NUMBER) IS
         -- v_oldFees number(7,2);
         -- v_newFees number(7,2);
BEGIN

--UPDATE the record
          UPDATE offering
          set fees = round(((fees*IN_Percentage)/100)+fees);

END;
/

Ideally, you can directly use the update statement in your application, there is no need of procedure for doing it until and unless you are asked to explicitly create the procedure.

Cheers!!

Upvotes: 2

Related Questions