CODEWITHSUNDEEP
sqloracle-database
Hard_Whey
Hard_Whey

Reputation: 127

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.

Upvotes: 1

Views: 331

Answers (2)

William Robertson
William Robertson

Reputation: 16001

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.

Upvotes: 1

Lukas Eder
Lukas Eder

Reputation: 221145

PL/SQL doesn't support this kind of string interpolation that you have in mind. But just use your input parameter as a bind variable instead:

SELECT ItemPrice
INTO pItemPrice
FROM Item 
WHERE ItemDesc LIKE pItemDesc;

You'll still get NO_DATA_FOUND exceptions if your procedure doesn't find anything. But you don't actually need the extra SELECT. Just run the UPDATE directly:

UPDATE Item
SET ItemPrice = (ItemPrice * percentage) / 100 + ItemPrice
WHERE ItemDesc LIKE pItemDesc;

Upvotes: 1

Related Questions