Modifying a value in a table using PL/SQL

I have only just begun to learn procedures in SQL. I have hit a bit of a wall and can’t seem to come to a fix. I am trying to create a procedure that will allow me to pass the item description and a percentage of how much I would like to increase that items price. This is the code I have got so far:

CREATE OR REPLACE PROCEDURE ADJUST_PRICE(
pItemDesc IN ITEM.ItemDesc%TYPE,
percentage IN NUMBER)
IS
pItemPrice NUMBER;
incAmt NUMBER;
BEGIN
SELECT itemprice into pItemPrice
FROM item WHERE itemdesc LIKE '%pItemDesc%';
incAmt := (pItemPrice*percentage)/100;
pItemPrice := incAmt + pItemPrice;
UPDATE Item
SET ItemPrice = pItemPrice
WHERE ItemDesc LIKE '%pItemDesc%';
END;

The procedure will compile but will not accept my calling block:

BEGIN 
ADJUST_PRICE('%Dish%', 10);
END; 

The error report I receive:

Error report -
ORA-01403: no data found
ORA-06512: at "S270131.ADJUST_PRICE", line 8
ORA-06512: at line 2
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

Any help would be greatly appreciated, Thankyou.

Answer

It’s not advisable to hit the ITEM table twice. Firstly, in a multi-user system, the details could have changed between your initial query and the update. Secondly it’s inefficient, as it does twice as much work as it needs to.

I would simplify it to something like this:

create or replace procedure adjust_price
    ( p_itemdesc  in item.itemdesc%type
    , p_percentage in number )
as
    l_increase_factor number := p_percentage/100 + 1;
begin
    update item i
    set    i.itemprice = i.itemprice * l_increase_factor
    where  itemdesc like '%'||p_itemdesc||'%' escape '';
end;

I have included an escape character to allow callers to treat wildcard characters % and _ as literals if they need to.

I’ve converted the percentage to a multiplication factor, so for example 50 percent becomes 1.5, rather than multiplying the price by the percentage, dividing by 100, and adding the original price, as I find that clearer arithmetically, but that’s just my personal preference.

You used a p prefix for one of your two parameters (pItemDesc) and also for a local variable (pItemPrice). Code becomes confusing if variables are named like parameters and parameters are named like variables, so I recommend choosing one naming strategy and sticking with it.

Notice that code is easier to follow, work with and fix if it is neatly laid out, so I strongly recommend formatting like a programmer.