CODEWITHSUNDEEP
plsql
SITANSHU YADAV
SITANSHU YADAV

Reputation: 27

PL/SQL if then else statements not running

I have written following code in oracle pl/sql

create or replace procedure sorting_criteria(criteria in varchar)
as 
begin
 if(criteria='lowest price')
 then
 declare 
 p_name product.p_name%type;
 cursor ptr is select p_name from product order by amount ASC;
 begin
  open ptr;
  loop
  fetch ptr into p_name;
  exit when ptr%notfound;
  dbms_output.put_line(p_name);
  end loop;
  close ptr;
 end;
 else if(criteria='highest price')
 then
 declare 
 p_name product.p_name%type;
 cursor ptr is select p_name from product order by amount DESC;
 begin
  open ptr;
  loop
  fetch ptr into p_name;
  exit when ptr%notfound;
  dbms_output.put_line(p_name);
  end loop;
  close ptr;
 end;
 else
 dbms_output.put_line('Enter valid criteria!');
 end if;
end;
/

But it is giving following error: Error at line 35: PLS-00103: Encountered the symbol ";" when expecting one of the following: Please help

Upvotes: 0

Views: 144

Answers (2)

Belayer
Belayer

Reputation: 14861

The answer by @GregorioPalamà correctly addresses your issues. But you can drastically reduce the workload by changing your thinking away from "If...then...else" to the "set of" and letting SQL do the work. In this case the only difference is sorting either ascending or descending on amount. The same effect can be achieved by sorting ascending on amount or minus amount; and SQL can make that decision. So you can reduce the procedure to validating the parameter and a single cursor for loop:

create or replace procedure sorting_criteria(criteria in varchar2)
as 
    cursor ptr(c_sort_criteria varchar2) is 
           select p_name    
             from product 
            order by case when c_sort_criteria = 'lowest price' 
                          then  amount
                          else -amount
                     end ;
                     
begin      
     if criteria in ('lowest price', 'highest price')
     then 
        for rec in ptr(criteria)
        loop
           dbms_output.put_line('Product: ' || rec.p_name );
        end loop; 
     else 
        dbms_output.put_line('Enter valid criteria!');
 end if;
end sorting_criteria;
/

See demo here. For demonstration purposed I added the amount to the dbms_output.

A couple notes:

  1. While it is not incorrect using p_... as a column name, it is also not a good idea. A very common convention (perhaps almost a standard) to use p_... to indicate parameters. This easily leads to confusion; confusion amongst developers is a bad thing.
  2. IMHO it is a bug to name a local variable the same as a table column name. While the compiler has scoping rules which one to use it again leads to confusion. The statement "where table.name = name" is always true, except when at least one of them is null, which possible could lead to updating/deleting every row in your table. In this case p_name is both a column and a local variable.

Upvotes: 1

Gregorio Palamà
Gregorio Palamà

Reputation: 1952

The ELSE-IF statement in PL/SQL has to be written as ELSIF. Otherwise, you should close the second IF with an other END IF; statement.

You can solve the issue by changing the ELSE IF at line 17 to an ELSIF

Upvotes: 4

Related Questions