PL/SQL Conditional Statement

user12864747 0
Viewed 30

This is regarding PL/SQL conditional statements. Since I'm new to this language, can someone help me to find the error on this code? The error msg is given below.

DECLARE
    stock_rec stock%ROWTYPE;
    var_company stock.company%TYPE := 'IBM';
BEGIN
    SELECT * INTO stock_rec
    FROM stock s
    WHERE s.company = var_company;
        BEGIN
            IF (stock_rec.price < 45) THEN
                DBMS_PUTPUT.PUT_LINE('Current price is very low !');
            ELSIF (stock_rec.price >= 45 AND stock_rec.price < 55) THEN
                DBMS_PUTPUT.PUT_LINE('Current price is low !');
            ELSIF (stock_rec.price >= 55 AND stock_rec.price < 65) THEN
                DBMS_PUTPUT.PUT_LINE('Current price is medium !');
            ELSIF (stock_rec.price >= 65 AND stock_rec.price < 75) THEN
                DBMS_PUTPUT.PUT_LINE('Current price is medium high !');
            ELSE (stock_rec.price >= 75) 
                DBMS_PUTPUT.PUT_LINE('Current price is high !');
            END IF;
        END;
END;
/

-------error msg---------------------------------------------

ORA-06550: line 17, column 40: PLS-00103: Encountered the symbol ")" when expecting one of the following:

  • & - + / at mod remainder rem <an exponent (**)> and or as || multiset The symbol "* was inserted before ")" to continue.
plsql oracle conditional-statements
2 Answers

The last ELSE should actually be ELSIF (and then you have THEN missing):

ELSIF (stock_rec.price >= 75) THEN

Also, you most probably don't need inner BEGIN-END.

DECLARE
    stock_rec stock%ROWTYPE;
    var_company stock.company%TYPE := 'IBM';
BEGIN
    SELECT * INTO stock_rec
    FROM stock s
    WHERE s.company = var_company;
        
    IF (stock_rec.price < 45) THEN
        DBMS_PUTPUT.PUT_LINE('Current price is very low !');
    ELSIF (stock_rec.price >= 45 AND stock_rec.price < 55) THEN
        DBMS_PUTPUT.PUT_LINE('Current price is low !');
    ELSIF (stock_rec.price >= 55 AND stock_rec.price < 65) THEN
        DBMS_PUTPUT.PUT_LINE('Current price is medium !');
    ELSIF (stock_rec.price >= 65 AND stock_rec.price < 75) THEN
        DBMS_PUTPUT.PUT_LINE('Current price is medium high !');
    ELSIF (stock_rec.price >= 75) THEN 
        DBMS_PUTPUT.PUT_LINE('Current price is high !');
    END IF;
    
END;
/
Littlefoot0

You can simplify the code to use a single DBMS_OUTPUT.PUT_LINE statement if you use a CASE expression:

DECLARE
  stock_rec stock%ROWTYPE;
  var_company stock.company%TYPE := 'IBM';
BEGIN
  SELECT *
  INTO   stock_rec
  FROM   stock
  WHERE  company = var_company;

  DBMS_PUTPUT.PUT_LINE(
    CASE
    WHEN stock_rec.price < 45
    THEN 'Current price is very low !'
    WHEN stock_rec.price < 55
    THEN 'Current price is low !'
    WHEN stock_rec.price < 65
    THEN 'Current price is medium !'
    WHEN stock_rec.price < 75
    THEN 'Current price is medium high !'
    WHEN stock_rec.price >= 75
    THEN 'Current price is high !'
    ELSE NULL
    END
  );
END;
/
MT00
Related