?
Solved

Using a cursor

Posted on 2011-04-22
7
Medium Priority
?
309 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:graziazi
7 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 35451690
Salary doesn't exist as stated.

Try:
...
if v_emp.salary < 20000
...

Qualifying 'salary' everywhere.
0
 

Author Comment

by:graziazi
ID: 35451738
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>
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 800 total points
ID: 35452026
>but when I run it, it only gives me 'joe has a salary less than 20k'

because you raise an error when you encounter the < 20K record and, without an error block,  the procedure is exited immediately.  If you want to continue, you need to trap the error and continue.

BTW, there is no action within your cursor loop...even if you pass all the records, you don't do anything with the good ones.  Try this:

DECLARE
      CURSOR empSal IS
        SELECT * FROM Employee;
           
      v_emp empSal%ROWTYPE;
       v_cntr integer;

    BEGIN
      v_cntr := 0;
      OPEN empSal;
 
     FETCH empSal INTO v_emp;
 
     WHILE empSal%FOUND LOOP
 
 if salary < 20000 then
                   dbms_output.put_line( fname || ' has a salary < 20000');
         elsif salary > 90000 then
                   dbms_output.put_line(  fname || ' has a salary > 90000');
          else
                   v_cntr := v_cntr + 1;
  end if;
     
END LOOP;
 
CLOSE empSal;
dbms_output.put_line( to_char(v_cntr) || ' records processed');
 
END;
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:graziazi
ID: 35453180
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
0
 

Author Comment

by:graziazi
ID: 35453201
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
0
 
LVL 14

Accepted Solution

by:
ajexpert earned 800 total points
ID: 35453232
You have to understand the concept of EXCEPTION BLOCK.

Some EXCEPTIONS you just want to print a msg and continue with next record in loop.  Some EXCEPTIONS are fatal where in you want to come out of program.

Your conditions are logical statements, these are NOT EXCEPTIONS.  If you RAISE EXCEPTION, it will not continue for next record.

If you just want to print it, it can be done this way...
/* Formatted on 4/23/2011 11:12:16 AM (QP5 v5.115.810.9015) */
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
         DBMS_OUTPUT.PUT_LINE (v_emp.fname || ' has a salary < 20000');
      ELSE
         IF v_emp.salary > 90000
         THEN
            DBMS_OUTPUT.PUT_LINE (v_emp.fname || ' has a salary > 90000');
         END IF;
      END IF;
   END LOOP;

   CLOSE empSal;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Oracle Errorr ' || SQLERRM);
END;
/

Open in new window

0
 

Author Closing Comment

by:graziazi
ID: 35453277
Thanks one and all
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question