asked on

Using a cursor

Hi I'm trying to use demonstrate the use of a cursor on the following...........

DECLARE
      CURSOR empSal IS
        SELECT * FROM Employee;
          
      v_emp empSal%ROWTYPE;
    BEGIN
      OPEN empSal;
 
     FETCH empSal INTO v_emp;
 
     WHILE empSal%FOUND LOOP
 
 if salary < 20000 then
                   raise_application_error(-20001, fname || ' has a salary < 20000');
         elsif salary > 90000 then
                   dbms_output.put_line('Is too big');
                   raise_application_error(-20001, name || ' has a salary > 90000');
         end if;
     
END LOOP;

CLOSE empSal;

END;
/

Open in new window

However I get an error message saying 'salary' should be declare (even though salary is a legit column name in the table)

I suspect there are some other errors on the way. Some direction is appreciated, thanks

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

graziazi

ASKER

Thanks very much, that worked. Just one thing about the logic of the if statements.....

I have 1 row where an employee has a salary less that 20k
and 1 row where an employee has a salary more than 90k

but when I run it, it only gives me 'joe has a salary less than 20k'

Surely it should also print out the employee who has a salary > 90k>

SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

graziazi

ASKER

Thanks guys. I've changed my requiremenst slightly. I have created an exception and here's how I've chnaged my code. However it still only deals with the first condition.....

DECLARE
salary_too_big EXCEPTION;
      CURSOR empSal IS
        SELECT * FROM Employee;
          
      v_emp empSal%ROWTYPE;
    BEGIN
      OPEN empSal;
     FETCH empSal INTO v_emp;
     WHILE empSal%FOUND LOOP
       DBMS_OUTPUT.PUT_LINE(v_emp.fname || '' || v_emp.lname);
 FETCH empSal INTO v_emp;
 if v_emp.salary < 20000 then
                   raise_application_error(-20001, v_emp.fname || ' has a salary < 20000');
         else
if v_emp.salary > 90000 then  
                 RAISE salary_too_big;
         end if;
 end if;
END LOOP;
CLOSE empSal;

EXCEPTION
WHEN salary_too_big THEN
dbms_output.put_line('Is too big');

END;
/

Open in new window

I'd like it to deal with both conditions, using the raise_application_error for conditon 1 and EXCEPTION I created for conditon 2.

Thanks

graziazi

ASKER

ooops sorry please ignore last post............ There were a couple of errors. Here's how the code should look...........

DECLARE
salary_too_big EXCEPTION;
      CURSOR empSal IS
        SELECT * FROM Employee;
          
      v_emp empSal%ROWTYPE;
    BEGIN
      OPEN empSal;
     FETCH empSal INTO v_emp;
     WHILE empSal%FOUND LOOP
   
 if v_emp.salary < 20000 then
                   raise_application_error(-20001, v_emp.fname || ' has a salary < 20000');
         else
if v_emp.salary > 90000 then  
                 RAISE salary_too_big;
         end if;
 end if;
END LOOP;
CLOSE empSal;

EXCEPTION
WHEN salary_too_big THEN
dbms_output.put_line('Is too big');

END;

Open in new window

I'd like it to deal with both conditions, using the raise_application_error for conditon 1 and EXCEPTION I created for conditon 2.

Thanks

ASKER CERTIFIED SOLUTION

ajexpert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

graziazi

ASKER

Thanks one and all