samedi 5 septembre 2015

if/else in pl SQL loop

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.

Aucun commentaire:

Enregistrer un commentaire