-1

I want to fetch records based on row(row number).I've done using sql query itself. 

select * from (select m.*,rownum r from employees m) where r between 80 and 100

But now I want to do the same thing with use of cursors in stored procedure(oracle) to fetch like records between 80 an 100(based on rownum pseudo column).Can anyone help me with that?

Ravi
  • 30,829
  • 42
  • 119
  • 173
senthil leon
  • 1
  • 1
  • 3

3 Answers3

0

You may need to have a look at Stored Procedures and Cursors to understand the basics. Other than that, your query can be easily enclosed within a cursor and a procedure. If you are going to use explicit cursor, it could be something like this:

CREATE OR REPLACE stored_procedure_name AS
CURSOR IS
  select * from (select rownum r,salary from employees) where r between 80 and 100;

BEGIN
 open c1; 
 loop 
   fetch c1 
   into a,b; 
   dbms_output.put_line(a || b); 
   exit when c1%notfound; 
 end loop;
 close c1;
END;
Hawk
  • 5,060
  • 12
  • 49
  • 74
  • i know the basics of cursors but i have a doubt of using which attributes of cursors(found,notfound,rowcount,isopen) to break the loop statement – senthil leon Jun 29 '15 at 02:17
  • declare a number; b number; cursor c1 is select rownum,salary from employees; begin open c1; loop fetch c1 into a,b; dbms_output.put_line(a || b); exit when c1%rowcount<=25; end loop; end; / – senthil leon Jun 29 '15 at 02:18
  • when i give like that i didnt get what i was actually expected.It just display it as 124000 – senthil leon Jun 29 '15 at 02:19
  • @senthilleon You should have mentioned this in your question statement, and pointed out what is your question exactly. For breaking the loop, it depends on how you want to break it. If you want to run the loop over the entire cursor, then `notfound` is the condition. – Hawk Jun 29 '15 at 02:20
0

There is simplified syntax you can use that avoids having to deal with cursors directly. I think it's less error-prone this way.

Example:

begin
  for rec in (
    select rn, salary
    from (
      select rownum as rn, salary
      from employees
    ) where rn between 80 and 100
  ) loop
    dbms_output.put_line(to_char(rec.rn) || rec.salary);
  end loop;
end;
/

Documentation: Query Result Set Processing With Cursor FOR LOOP Statements

EDIT:

The general form of this construct is:

begin
  for rec in (
    select * from employees -- write any SQL you want here
  ) loop

    -- do whatever you need to do inside the loop...
    -- you can access the results of the query through the "rec" variable.
    dbms_output.put_line(rec.column1);
    dbms_output.put_line(rec.column2);
    dbms_output.put_line(rec.column3);

  end loop;
end;
/
sstan
  • 35,425
  • 6
  • 48
  • 66
  • @hawk what if i want to print all the records.How to use same statement for select * from – senthil leon Jun 29 '15 at 03:01
  • Just write the SQL you need and plug it into the `for rec in (sql) loop ... end loop;`. If you want to select all the records, then plug in `select * from employees`. Have a look at the documentation link I posted. It should be clear enough. – sstan Jun 29 '15 at 03:08
  • it really helped me a lot @sstan. – senthil leon Jun 29 '15 at 03:52
0

You can use simple construct like bellow

declare
  v_e_row  employees%rowtype; --employees table row type
  v_rownum number;
begin
  for emp in (select * from (select m.* , rownum r from employees m) where r between 80 and 100) 
    loop
      v_e_row.emp_id  := emp.emp_id; -- sample value ( you will get all value including rownum)
      v_rownum := emp.r;
      dbms_output.put_line(v_e_row.emp_id||v_rownum); 
    end loop;
end;
vishnu sable
  • 328
  • 1
  • 7