CODEWITHSUNDEEP
oracle-databaseplsqlsyntax-error
user2995951
user2995951

Reputation: 25

What am I doing wrong in this PL/SQL statement?

Can anyone help me with what I'm doing wrong in this small statement?

declare

   cursor emp_cursor is 
    select employee_id, quantity * salary AS price 
      from employees;

   emp_row emp_cursor%rowtype;

begin

   open emp_cursor;

   if (price < 2,000)
      then price := 2,000 * 0.15;
   else if (price >= 2,000)
      then price := 2,000 * 0.20; 
      loop
         fetch emp_cursor into emp_row;
         exit when emp_cursor%notfound;
         dbms_output.put_line(emp_row.employee_id || ' ' || emp_row.price);
      end loop;

   close emp_cursor;

end;

I'm using pl/sql developer.

Upvotes: 0

Views: 84

Answers (3)

Art
Art

Reputation: 5782

Your price should be emp_row.price. And put your if after exit when... You have to open, fetch cursor first. Then process rows and close the cursor. The code is cleaner when you user a variable instead of emp_row.price. For ex, if emp_row.price < 2000 - then v_price:= 2000 * 0.15... And get rid of commas in your numbers.

 loop
     fetch emp_cursor into emp_row;
     exit when emp_cursor%notfound;

     if (emp_row.price < 2000) then....
      ....
     end if;
     dbms_output.put_line(emp_row.employee_id || ' ' || emp_row.price);
  end loop;
  ....

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

  1. You must not use a thousand delimiter. Write IF price < 2000 instead of IF price < 2,000.
  2. You forgot an END IF;

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30775

Get rid of the ',' in your comparisons:

if (price < 2,000)

is a syntax error. If you meant 2E0, simply write 2, if you meant 2E3, write 2000.

Upvotes: 1

Related Questions