5 Replies Latest reply: Nov 17, 2012 12:40 AM by 852595 RSS

    Fetch records from employee table

    852595
      Hi All,
      I am having one table Employee. Employee table having 50 records. I want to fetch 5 records every timeone the query is executed. But it should be like below.


      1-5 records
      6-10 records
      11-15 records
      16-20 records
      .
      .
      .
      .
      46-50 records

      Any one can give the query.

      Thanks
        • 1. Re: Fetch records from employee table
          jeneesh
          Your question is not very clear..

          If you want to se the output in SQLPLUS, you can use PAUSE and PAGESIZE. Something like..
          SQL> set pagesize 8
          SQL> set pause on
          If you want to fetch using Cursor in PL/SQL, you can use BULK COLLECT with LIMIT
          SQL> set serverout on
          
          SQL> declare
            2   cursor c_emp is select * from emp ;--order by will be required
            3   type t_emp is table of c_emp%rowtype;
            4   t_emp1 t_emp;
            5  begin
            6   open c_emp;
            7   loop
            8    fetch c_emp bulk collect into t_emp1 limit 5;
            9    dbms_output.put_line(t_emp1.count||' Records Fetched');
           10    exit when c_emp%notfound;
           11   end loop;
           12   close c_emp;
           13  end;
           14  /
          5 Records Fetched
          5 Records Fetched
          4 Records Fetched
          
          PL/SQL procedure successfully completed.
          Or if you want pagination you can refer AskTom

          Edited by: jeneesh on Oct 28, 2012 11:36 AM
          • 2. Re: Fetch records from employee table
            Etbin
            It's called pagination:

            http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:127412348064
            http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:76812348057
            http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:20663081751269

            Regards

            Etbin
            • 3. Re: Fetch records from employee table
              ranit B
              Article snippet from - http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

              >
              Pagination with ROWNUM

              My all-time-favorite use of ROWNUM is pagination. In this case, I use ROWNUM to get rows N through M of a result set. The general form is as follows:
              select * 
                  from ( select /*+ FIRST_ROWS(n) */ 
                  a.*, ROWNUM rnum 
                      from ( your_query_goes_here, 
                      with order by ) a 
                      where ROWNUM <= 
                      :MAX_ROW_TO_FETCH ) 
                where rnum  >= :MIN_ROW_TO_FETCH;
              where
              FIRST_ROWS(N) tells the optimizer, "Hey, I'm interested in getting the first rows, and I'll get N of them as fast as possible."
              :MAX_ROW_TO_FETCH is set to the last row of the result set to fetch—if you wanted rows 50 to 60 of the result set, you would set this to 60.
              :MIN_ROW_TO_FETCH is set to the first row of the result set to fetch, so to get rows 50 to 60, you would set this to 50.
              >

              Hope that helps.
              Ranit B.
              • 4. Re: Fetch records from employee table
                rp0428
                >
                I am having one table Employee. Employee table having 50 records. I want to fetch 5 records every timeone the query is executed.
                >
                That CANNOT be guaranteed at all unless the table is READONLY and you use ORDER BY on the query. Even then it cannot be done if by 'query' you mean EXACTLY the same string of characters. Oracle has no memory except the sql cache and with the exact same query (either no bind variables or the same bind variables) will return the exact same results.

                If the table is not READONLY then the second (or third, etc) queries could include rows that did not exist before your first query, would not include rows that were deleted after one of your queries and would include rows that were different (updated) between any two of your queries.

                If you allow the query to be different then you can use a pagination query (see other replies) based on ROWID or the primary key of the table. Even then, if there are no unique columns you need to use ORDER BY on ALL columns or you could get the same set of rows in a query that you had already gotten in a previous query.

                The use of ORDER BY is required to ensure the order of rows. So unless you base the query on ROWID or a unique column value you would need to order all of the columns.

                Most likely you are only looking for the simplistic pagination solution already suggested by others.
                • 5. Re: Fetch records from employee table
                  852595
                  Thanks Every one.