PLSQL cursor 선언

2011-08-03 13:59:49

---

SQL 4 Oracle 오라클용 sql 실무
▒ PLSQL cursor 선언

 

declare
    find_CNT  number := 0;
    find_CNT2  number := 0;   
    cursor cg_cursor is
       select COL1 from 타겟_Table  ;
    cursor cg_cursor2 is
       select COL2 from 타겟2_Table  ;      
begin
    for box in cg_cursor loop
          UPDATE 타겟_Table SET LGR_MDF_DT='20110701'
          WHERE COL1 = box.COL1 ;
         
           find_CNT  := find_CNT + 1;
           if mod(find_CNT,1000) = 0 then
            commit;
           -- dbms_output.put_line('** Updating COUNT : '|| find_CNT);
           end if;
    end loop;
    commit;
    -- dbms_output.put_line('** Total Update COUNT==> '|| find_CNT);

 

    for box2 in cg_cursor2 loop
          UPDATE 타겟_Table SET LGR_MDF_DT='20110701'
          WHERE COL2 = box.COL2 ;
         
           find_CNT2  := find_CNT2 + 1;
           if mod(find_CNT2,1000) = 0 then
            commit;
           -- dbms_output.put_line('** Updating COUNT : '|| find_CNT2);
           end if;
    end loop;
    commit;
    -- dbms_output.put_line('** Total Update COUNT==> '|| find_CNT2);   
end;