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.
order by
to return data in the "right" order?