if/else in pl SQL loop

Question

I am attempting to update the units in stock with IF.. ELSE conditions and statements, but I'm getting an error when trying to run it:

PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
    if

Code below, I am running it on a loop to spit out all the data until it runs out:

DECLARE

--Declare cursor
 CURSOR part_cur
 IS 
 SELECT PARTNUMBER, PARTDESC, ITEMCLASS, UNITSONHAND, UNITSONHAND
 FROM PART
 ORDER BY UPPER(PARTNUMBER);

--Declare variables
 v_PartNumber PART.PARTNUMBER%TYPE;
 v_PartDesc PART.PARTDESC%TYPE;
 v_ItemClass PART.ITEMCLASS%TYPE;
 v_UnitsOnHand PART.UNITSONHAND%TYPE;
 v_nUnitsOnHand PART.UNITSONHAND%TYPE;

BEGIN
 OPEN part_cur;
 LOOP

--Initialise variables
  FETCH part_cur INTO v_PartNumber, v_PartDesc, v_ItemClass, v_UnitsOnHand, v_nUnitsOnHand;
        EXIT WHEN part_cur%NOTFOUND;

--Perform Calculations
    IF ITEMCLASS = 'AO' AND UNITSONHAND <20 THEN v_nUnitsOnHand := UNITSONHAND + 5;
ELSE IF ITEMCLASS = 'AO' AND UNITSONHAND <100 THEN v_nUnitsOnHand := UNITSONHAND + 10;
ELSE IF ITEMCLASS = 'AO' AND UNITSONHAND >=100 THEN v_nUnitsOnHand := UNITSONHAND + 25;
END IF;

--Format for output

--Output results
        DBMS_OUTPUT.PUT_LINE('Part ' || UPPER(v_PartNumber) || ' - ' || INITCAP(v_PartDesc) || ' of item class ' || UPPER(v_ItemClass) || ' had a total of ' || v_UnitsOnHand || ' units on hand, 
        and now has ' || v_nUnitsOnHand || ' units on hand after the database was updated.
        ');
 END LOOP;
 CLOSE part_cur;
END;

Any help or pointers would be greatly appreciated, thanks.

ruakh · Accepted Answer · 2015-09-06 03:13:09Z

3

Your IF statement currently looks like this:

IF ITEMCLASS = 'AO' AND UNITSONHAND <20 THEN
    v_nUnitsOnHand := UNITSONHAND + 5;
ELSE
    IF ITEMCLASS = 'AO' AND UNITSONHAND <100 THEN
        v_nUnitsOnHand := UNITSONHAND + 10;
    ELSE
        IF ITEMCLASS = 'AO' AND UNITSONHAND >=100 THEN
            v_nUnitsOnHand := UNITSONHAND + 25;
        END IF;

so it's missing two END IFs. So when Oracle sees the END LOOP, it complains, because it expected an END IF instead.

To fix this, just replace both of your ELSE IFs with ELSIF, which is a special keyword for exactly this purpose:

IF ITEMCLASS = 'AO' AND UNITSONHAND <20 THEN
    v_nUnitsOnHand := UNITSONHAND + 5;
ELSIF ITEMCLASS = 'AO' AND UNITSONHAND <100 THEN
    v_nUnitsOnHand := UNITSONHAND + 10;
ELSIF ITEMCLASS = 'AO' AND UNITSONHAND >=100 THEN
    v_nUnitsOnHand := UNITSONHAND + 25;
END IF;

answered Sep 6, 2015 at 3:13

ruakh

185k29 gold badges292 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3521826 Over a year ago

Oh cheers thanks, I didn't realise how deep my ELSE.. IF statements were going.

Luis Teijon · Accepted Answer · 2015-09-06 03:19:17Z

-1

Try this:

FOR record_var in part_cur
LOOP
   {...statements...}
END LOOP;

After that you can write:

record_var.v_PartNumber, etc

for accessing these fields.

edited Sep 6, 2015 at 3:19

answered Sep 6, 2015 at 3:12

Luis Teijon

4,9297 gold badges39 silver badges60 bronze badges

Collectives™ on Stack Overflow

if/else in pl SQL loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related