How to iterate over 1000 records in a particular order

oracleoracle-11g-r2plsql

I am writing a procedure to migrate two tables into different tables. This procedure will have two cursors. One to fetch the parent records and then second cursor to fetch the details for each parent record. The parent record is a table with the following columns —

ID || YYYYMMDD || submitdate.

The combination of ID and YYYYMMDD gives the unique id. So now I want to migrate this to another table in the order it was created. I can order this table by submitdate, but I need to fetch 1000 records at a time. How can I achieve this?

Best Answer

First, I'll cover the SQL row-limiting side of things.

In Oracle 11.2.x and lower, you have to use rownum and a subquery, as it doesn't support the LIMIT or FETCH clauses:

select * from
(
  select *
  from yourtable
  order by submitdate ASC
)
where rownum <=1000;

In Oracle 12 you can use:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
FETCH FIRST 1000 ROWS ONLY;

... then, for the next 1k:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
OFFSET 1000 ROWS FETCH NEXT 1000 ROWS ONLY;

Sample table:

create table yourtable
(
  ID number,
  YYYYMMDD varchar(8),
  submitdate date
);

The PL/SQL:

CREATE OR REPLACE PROCEDURE process_records(offset IN NUMBER)
IS
  CNT number;
  ID yourtable.id%type;
  YYYYMMDD yourtable.yyyymmdd%type;
  SUBMITDATE yourtable.submitdate%type;
  ID_YYYYMMDD varchar(100); -- variable to hold the concatenated value for the 2nd cursor
  CURSOR yourcursor
  IS
    SELECT * 
    FROM (
      SELECT * 
      FROM yourtable
      ORDER BY submitdate ASC
    ) WHERE rownum > offset;
BEGIN
  CNT:=0;
  OPEN yourcursor;
  LOOP
    FETCH yourcursor into ID,YYYYMMDD,SUBMITDATE;
    CNT:=CNT+1;
    EXIT WHEN CNT=1000;
    ID_YYYYMMDD := ID||YYYYMMDD;
    --
    -- put your child cursor here
    --
  END LOOP;
END process_records;
/

The stored procedure takes an input, which is the start offset.

Create another cursor, and use your concatenated ID ID_YYYYMMDD in the WHERE clause.

Why do you need to process 1000 records at a time? It'll be slower iterating this way. Often you can achieve the same thing using one or two lone SQL statements, which end up way more efficient.

Related Question