CODEWITHSUNDEEP
oracle-databaseplsqlcursor
hashir
hashir

Reputation: 197

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

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 ?

Upvotes: 1

Views: 1355

Answers (2)

MT0
MT0

Reputation: 167867

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;

Upvotes: 1

Aleksej
Aleksej

Reputation: 22949

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".

Upvotes: 3

Related Questions