CODEWITHSUNDEEP
oracle-databaseplsqlora-06550
issam5
issam5

Reputation: 9

what's wrong with if statement?

I'am trying to use more than one if condition in PL\SQL, and it gives me an error: 

ORA-06550: line 16, column 5:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
   if

here's my code:

    declare
price_var number;
st_numb number;
co_pr number;
cursor student_count is select STUDENT_NUMBER,COURSE_PRICE from COURSES;
begin
open student_count;
loop
fetch student_count into st_numb,co_pr;
if st_numb<10 then
update COURSES set COURSE_PRICE=co_pr*1.05;
elseif st_numb>10 then
update COURSES set COURSE_PRICE=co_pr*1.07;
end if;
exit when student_count%notfound;
end loop;
end

can you tell me where is the error ? thanks.

Upvotes: 0

Views: 104

Answers (3)

William Robertson
William Robertson

Reputation: 16001

Just to add to massko's answer -

The ordinary Cursor FOR loop is simpler, more efficient and more reliable than the explicit open-fetch-exit-close. Also, you have to specify which row to update within the loop, otherwise you will update every row in the table. (And although the compiler doesn't care how you lay out your code or whether you put random words in uppercase, it's good to get into the habit of coding neatly.) Therefore as a first refactor we get this:

begin
    for r in (
        select course_id  -- Assuming courses have a unique ID
             , student_number, course_price
        from   courses
    )
    loop
        if r.student_number < 10 then
            update courses set course_price = r.course_price * 1.05
            where  course_id = r.course_id;

        elsif r.student_number > 10 then
            update courses set course_price = r.course_price * 1.07
            where  course_id = r.course_id;
        end if;

    end loop;
end;

But then why repeat the update statement twice when all that changes is the multiplication factor, and why we are looping through rows we don't do anything with? We can therefore simplify it a stage further:

begin
    for r in (
        select course_id, student_number, course_price
        from   courses
        where  student_number <> 10
    )
    loop
        update courses
        set    course_price = r.course_price *
               case
                   when r.student_number < 10 then 1.05
                   when r.student_number > 10 then 1.07
               end
        where  course_id = r.course_id;
    end loop;
end;

Then again, why do we even need a laborious row-by-row approach when we can do it in one shot?

begin
    update courses c
    set    course_price = r.course_price *
           case
               when c.student_number < 10 then 1.05
               when c.student_number > 10 then 1.07
           end
    where  c.student_number <> 10;
end;

btw I don't know your data model, but storing a count of the number of students within each course record doesn't seem a reliable approach.

Upvotes: 1

massko
massko

Reputation: 589

You would need elsif, not elseif and also ; after end. Try this one corrected:

declare
  price_var number;
  st_numb   number;
  co_pr     number;
  cursor student_count is
    select student_number
          ,course_price
      from courses;
begin
  open student_count;
  loop
    fetch student_count
      into st_numb
          ,co_pr;
    if st_numb < 10 then
      update courses set course_price = co_pr * 1.05;
    elsif st_numb > 10 then   --elsif, not elseif
      update courses set course_price = co_pr * 1.07;
    end if;
    exit when student_count%notfound;
  end loop;
end;  --also need ";" after end

Upvotes: 1

Ludonope
Ludonope

Reputation: 1093

First, I think in PL/SQL it's "elsif" and not "elseif". Then (I dont know if this is important or not), maybe you need the parenthesis around the conditions, I don't know.

Source: https://www.tutorialspoint.com/plsql/plsql_if_then_elsif.htm

Upvotes: 4

Related Questions