pl sql cursor for loop in

Question

I have two cursors the for loop should use the cursor based on the status.

CURSOR order_hist1 IS 
   SELECT id, ordernum, address FROM order_hist; 

CURSOR order_hist2 IS 
   SELECT id, ordernum, address FROM order_hist_complete; 

so for loop should use cursor order_hist2 is the variable status = 'COMPLETE' else use order_hist1

 FOR aDistinctLine in --     LOOP
   -- 300 lines code in this loop
 END LOOP;

I don't want o use REF Cursors

Answer 1

You can use implicit for loop:

• For your case, it looks suitable to change the two cursors to a single one, using UNION (UNION ALL if you need to process duplicates, or performance reasons), like follows:

---

FOR aDistinctLine in (
  -- first cursor: status <> COMPLETE
  SELECT id, ordernum, address FROM order_hist 
   WHERE status <> 'COMPLETE' 
  UNION 
  SELECT id, ordernum, address FROM order_hist_complete 
   WHERE status = 'COMPLETE' 
) LOOP

-- do things with 
--     aDistinctLine.id, 
--     aDistinctLine.ordernum, 
--     aDistinctLine.address 

END LOOP;

Then it's better to have status look like a local variable, e.g. call it l_status; I had to convince myself it could work to use a plsql variable inside an implicit for loop... guess I learned something today!

declare
  l_status varchar2(8) := 'COMPLETE';
begin
  for x in (select 'realy?' val from dual where l_status = 'COMPLETE')
  loop
    dbms_output.put_line(x.val);
  end loop;

  l_status := 'graby';
  for x in (select 'here: not complete' val from dual where l_status <> 'COMPLETE')
  loop
    dbms_output.put_line(x.val);
  end loop;
end;
/