Friday, March 10, 2017

Useful Loops for Oracle Developer


BEGIN
    GO_BLOCK('MY_BLOCK');
    FIRST_RECORD;
    LOOP
    MESSAGE(:MY_BLOCK.DSP_NAME);
    EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
    NEXT_RECORD;          
    END LOOP;  
END;

---------------------------------------------------------------
DECLARE
   /* All the jobs in the Fall to prepare for the Winter */
   CURSOR fall_jobs_cur
   IS
      SELECT task, expected_hours, tools_required, do_it_yourself_flag
        FROM winterize
       WHERE year = TO_CHAR (SYSDATE, 'YYYY')
         AND completed_flag = 'NOTYET'
       FOR UPDATE OF task;
BEGIN
   /* For each job fetched by the cursor... */
   FOR job_rec IN fall_jobs_cur
   LOOP
      IF job_rec.do_it_yourself_flag = 'YOUCANDOIT'
      THEN
         /*
         || I have found my next job. Assign it to myself (like someone
         || is going to do it!) and then commit the changes.
         */
         UPDATE winterize SET responsible = 'STEVEN'
          WHERE task = job_rec.task
            AND year = TO_CHAR (SYSDATE, 'YYYY');
         COMMIT;
      END IF;
   END LOOP;
END;
-----------------------------------------------------------

This is possible. In when validate record you write like this,

declare
v_rec_no number;
begin
if :system.cursor_record > 1 then
v_rec_no = :system.cursor_record ;
first_record;
for i in 1 to v_rec_no loop
< your validation>
.....
.....
next_record;
end loop;
end; 

No comments:

Post a Comment