How to fetch multiple data through cursors using FOR loop for a select statement?

Question

I have a old school HR schema which consist of Employees table. The table has a column of salary for employees. Minimum salary is 2100 and maximum is 24000. If I were to find all the thousand salaries (3000,4000,5000...) which are not present in the salary column of the table (between 2100 and 24000) then what should I do ?

I thought of using a FOR loop over the select statement of a cursor then fetching the data through cursor and displaying them. But it throws an error. Following is something which I tried for the mentioned problem :

declare
cursor c1 is
for i in 2000..25000
loop
select salary
from employees where salary<>i;
end loop;
sal number(10);
begin
for cur IN c1
loop
dbms_output.put_line(c1.sal);
end loop;
end;

Above code throws and error saying "Expected instead of "

Anyone with a cure ?

Why "through cursors using FOR loop"? Sounds like you want a method for listing missing values within a numeric range. btw a cursor definition has to use valid SQL, therefore it should at least begin with select or with, not for. — William Robertson
– William Robertson, Commented Jan 11, 2017 at 12:38
Yes Sir. It always threw error saying - 'Expected "select" instead of "for" at line number so and so. This makes quite clear that a cursor definition expects a select statement at its beginning. — hashir
– hashir, Commented Jan 12, 2017 at 5:55

Aleksej · Accepted Answer · 2017-01-11 09:03:21Z

3

This could be a pl/sql solution, simpler than your attempt; a bit of explanation in the comments:

declare
    vNum number;
begin
    /* a loop for 2000 ... 25000 would give 2000, 2001, 2002, ... 25000 */
    for i in 2..25 loop
        /* check if the salary exists */
        select count(1)
        into vNum
        from employees
        where salary = i * 1000;
        --
        /* print the result */
        if vNum = 0 then
            dbms_output.put_line('Salary ' || i*1000 || ' does not exist');
        else        
            dbms_output.put_line('Salary ' || i*1000 || ' exists');
        end if;
    end loop;
end;

Notice that this is not an efficient solution, neither the way I would implement this, but I hope it is clear enough to give you some hints to build your procedures.

This could be an SQL solution:

select sal
from ( 
        select (level +1) * 1000 as sal
        from dual
        connect by  (level +1) * 1000 <= 25000
     ) salaries
where salaries.sal not in ( select salary from employees)

the "tricky" part here is the inner query, used to generate a list of values 2000, 3000, ... 25000:

select (level +1) * 1000 as sal
from dual
connect by  (level +1) * 1000 <= 25000

here I used a NOT IN, which may not be the perfect choice, but I hope it could be quite self-explanatory; the logic is something like "list all the values in 2000, ... 25000 that are not in the list of salaries".

edited Jan 11, 2017 at 9:03

answered Jan 11, 2017 at 8:36

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hashir Over a year ago

Thank you Aleksej for the above solutions plus the response time was overwhelming. I would like to add that the first solution you provided is quite a casual approach while the second one is a smarter one. Yet both do the job quite efficiently

MT0 · Accepted Answer · 2017-01-11 09:46:03Z

1

Here is a pure SQL solution that does not require you to hard-code the lowest and highest salary and uses MINUS instead of a correlated sub-query:

SELECT min_salary + ( LEVEL - 1 ) * 1000 As salary
FROM   (
  SELECT MIN( CEIL( salary / 1000 ) * 1000 ) AS min_salary,
         MAX( FLOOR( salary / 1000 ) * 1000 ) AS max_salary
  FROM   employees
)
CONNECT BY min_salary + ( LEVEL - 1 ) * 1000 <= max_salary

MINUS

SELECT salary
FROM   employees;

answered Jan 11, 2017 at 9:46

MT0

173k12 gold badges70 silver badges136 bronze badges

1 Comment

hashir Over a year ago

Thanks for the quick response MT0. The solution you provide is quite cool and efficient one. I allows me to swipe away the hard-coding issue. The code runs smooth for all conditions. Thanks again!

Collectives™ on Stack Overflow

How to fetch multiple data through cursors using FOR loop for a select statement?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related