游标示例

1、按行取出

  DECLARE
   CURSOR test_c
   IS
      SELECT s.id staff_id,
             s.entrydate entrydate,
             d.id dimission_id,
             l.id liquidation_id
        FROM t_oa_staff s
             LEFT JOIN t_oa_dimission d
                ON d.delete_flag = '0' AND d.create_user = s.id
             LEFT JOIN t_oa_liquidation l
                ON l.delete_flag = '0' AND l.create_user = s.id
       WHERE s.delete_flag = '0';
   test_row test_c%ROWTYPE;
BEGIN
   FOR test_row IN test_c
   LOOP
      INSERT INTO t_oa_staff_workdate (sw_id,
                                       staff_id,
                                       sw_entrydate,
                                       dimission_id,
                                       liquidation_id,create_user)
           VALUES (seq_staff_workdate.NEXTVAL,
                   test_row.staff_id,
                   test_row.entrydate,
                   test_row.dimission_id,
                   test_row.liquidation_id,'60');
   END LOOP;
   COMMIT;
END;

2、取出结果集

DECLARE
   CURSOR mycur
   IS
      SELECT dt.id
        FROM t_oa_department dt
       WHERE DT.DELETE_FLAG = '0'
             AND dt.id NOT IN (187, 189); 
BEGIN
   FOR varA IN mycur
   LOOP
      INSERT INTO T_OA_JOBTITLE (id,
                                 DEPART_CODE,
                                 name,
                                 kind) 
           VALUES (SEQ_JOBTITLE.NEXTVAL,
                   varA.id,
                   '一般员工',
                   144); 
   END LOOP;
   COMMIT;
END;