CODEWITHSUNDEEP
oracle-databaseplsql
MDP
MDP

Reputation: 4287

ELSE IF not working in PL/SQL

I got a weird error when I try to use a ELSE IF in a PROCEDURE, and I can't understand what I'm doing wrong. I'm creating the procedure with TOAD.

This code works:

CREATE OR REPLACE PROCEDURE findMin(valore1 in integer, valore2 in integer, risultato out integer) IS
BEGIN
    IF(valore1 < valore2) THEN
        risultato:= valore1;
    ELSE
        risultato:= valore2;             
    END IF;
END;   
/

This doesn't. I get this error on the last line of code: Found: ';' - Expecting IF

CREATE OR REPLACE PROCEDURE findMin(valore1 in integer, valore2 in integer, risultato out integer) IS
BEGIN
    IF(valore1 < valore2) THEN
        risultato:= valore1;
    ELSE IF (valore1 > valore2) THEN
        risultato:= valore2;    
    ELSE
        risultato := 0;              
    END IF;
END;  --here I get the error 
/

What is my error?

Thank you

Upvotes: 3

Views: 2665

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

If you indent the else block you can see that you're missing an end if:

BEGIN
    IF(valore1 < valore2) THEN
        risultato:= valore1;
    ELSE
        IF (valore1 > valore2) THEN
            risultato:= valore2;    
        ELSE
            risultato := 0;              
        END IF;
    -- missing an END IF
END;

You either need to add that:

CREATE OR REPLACE PROCEDURE findMin(valore1 in integer, valore2 in integer,
  risultato out integer) IS
BEGIN
    IF valore1 < valore2 THEN
        risultato:= valore1;
    ELSE
        IF valore1 > valore2 THEN
            risultato:= valore2;    
        ELSE
            risultato := 0;              
        END IF;
    END IF;
END;
/

Procedure FINDMIN compiled

Or more simply, change to elsif:

CREATE OR REPLACE PROCEDURE findMin(valore1 in integer, valore2 in integer,
  risultato out integer) IS
BEGIN
    IF valore1 < valore2 THEN
        risultato:= valore1;
    ELSIF valore1 > valore2 THEN
        risultato:= valore2;    
    ELSE
        risultato := 0;              
    END IF;
END;
/

Procedure FINDMIN compiled

You could also use a case statement here:

CREATE OR REPLACE PROCEDURE findMin(valore1 in integer, valore2 in integer, risultato out integer) IS
BEGIN
    CASE
        WHEN valore1 < valore2 THEN
            risultato:= valore1;
        WHEN valore1 > valore2 THEN
            risultato:= valore2;    
        ELSE
            risultato := 0;              
    END CASE;
END;
/

Procedure FINDMIN compiled

or even just a case expression as this example is a simple single assignment:

CREATE OR REPLACE PROCEDURE findMin(valore1 in integer, valore2 in integer,
  risultato out integer) IS
BEGIN
    risultato := CASE
            WHEN valore1 < valore2 THEN valore1
            WHEN valore1 > valore2 THEN valore2
            ELSE 0
        END;
END;
/

Procedure FINDMIN compiled

Read more about conditional statements in PL/SQL.

Upvotes: 8

Ori Marko
Ori Marko

Reputation: 58892

This is a typo mistake, replace ELSE IF with PLSQL's ELSIF

ELSIF (valore1 > valore2) THEN

ELSIF

Introduces a Boolean expression that is evaluated if none of the preceding conditions returned TRUE.

Upvotes: 4

Related Questions