Oracle - Procedure 문법_Cursor_Fetch

[ Oracle - Procedure 문법_Cursor_Fetch ]

 

CREATE OR REPLACE PROCEDURE HRMS.Op_Odm_Shift_Work_Daily_Update (

    in_yyyymm                           IN  VARCHAR2,   -- 작업년월

    in_emp_id           IN  VARCHAR2,   -- 사번

    in_apply_ym_m       IN  VARCHAR2,   -- 소급계산 처리시 수정처리년월

    in_mod_emp_id                    IN  VARCHAR2,   -- 작업자사번

    out_return                            OUT VARCHAR2    -- return 변수

)

IS

    CURSOR cur1 IS

        SELECT a.apply_ymd,

              a.shift_work_cd,

              a.data_type,

              a.sta_hm, a.end_hm

         FROM  ODM_SHIFT_WORK_DAILY a,

                      ODM_SHIFT_WORK_CODE b,

                       ODM_CALENDAR c

         WHERE a.shift_work_cd = b.cd

            AND     a.apply_ymd = c.cal_ymd

           AND    b.idx_id = '1109'

           AND    a.emp_id = in_emp_id

           AND    a.apply_ymd LIKE in_yyyymm || '%' ;

 

BEGIN

 

   --이때 cur1에 있는  in_emp_id 변수를 활용해서 sql 문이 실행 된다.

    OPEN cur1;

    LOOP

 

    FETCH cur1 INTO     v_f_apply_ymd,

            v_f_shift_work_cd,

            v_f_data_type,

            v_f_sta_hm,

            v_f_end_hm;

    EXIT WHEN cur1%NOTFOUND;

 

        ............... business logic ..........

    END LOOP;

    CLOSE cur1;

END;