Tuesday, April 14, 2009

PL/SQL Cursor For Loop with Bulk Collect and Limit

Here is an cursor example of how to use a BULK COLLECT with a LIMIT clause. DevNote: %NOTFOUND check must be at the end of the loop

declare

   PROCEDURE bulk_with_limit (
      n_start_in     IN    CONTRACT.CONTRACT_ID%TYPE,
      n_end_in       IN    CONTRACT.CONTRACT_ID%TYPE,
      limit_in       IN    PLS_INTEGER := 100
   )
   IS
      CURSOR contracts_cur
      IS
         SELECT *
         FROM contract
         WHERE contract_id between n_start_in and n_end_in;

      TYPE contract_trec IS TABLE OF contracts_cur%ROWTYPE INDEX BY PLS_INTEGER;

      l_contracts   contract_trec;

   BEGIN
      OPEN contracts_cur;

      LOOP
         FETCH contracts_cur
            BULK COLLECT 
            INTO l_contracts 
            LIMIT limit_in;

         -- Perform business logic ... 
         FOR i IN 1 .. l_contracts.COUNT LOOP
            dbms_output.put_line(lpad(' ',5 - length(i))||i||' : '||l_contracts(i).contract_id);
         END LOOP;

         -- DevNote: %NOTFOUND check must be at the end of the loop 
         EXIT WHEN contracts_cur%NOTFOUND;

      END LOOP;

      CLOSE contracts_cur;
   END bulk_with_limit;

begin
   bulk_with_limit(n_start_in => 10, n_end_in => 100000, limit_in => 100);
end;

3 comments:

Anonymous said...

http://jasonvogel.blogspot.com/2006/11/plsql-join-convert-multiple-returned.html?showComment=1242914171853#c1027413500840209600

Yu "Denis" Sun said...

Thanks for sharing this idea. BTW, how can I set up the code widget as shown in your blog?

- Denis

Jason said...

Take a look at http://jasonvogel.blogspot.com/2009/01/syntax-highlighting-posted-code.html