오라클은 패키지를 기준으로 구성한다
패키지 명은 화면id 또는 업무 기준으로 구성한다.

Oracle은 Package 기반이어서 클라이언트에서 호출시에 '패키지명.프로시져' 형식으로 호출한다.\\
예) PKG_SYS.GET_CHILDS

    오라클은 아래 3개의 필수구성 파라미터를 프로시져 끝에 유지해야 한다.
    N_RETURN         OUT  NUMBER,            /* 프로시져 수행결과 오류여부 0:정상, 그외 에러발생 */
    V_RETURN         OUT  VARCHAR2,          /* 클라이언트에게 보내는 메세지 */
    C_RETURN1        OUT  SYS_REFCURSOR,      /* 클라이언트에 보내는 데이터 */
    
    
패키지 PKG_MATRIL002

    
    ************** 패키지 헤더 시작 ************
    create or replace package PKG_MATRIL002 is
    
    PROCEDURE INT_LIST( A_PLANT          IN   EWIPMSTPLT.PLANT%TYPE,
                        A_LANG           IN   VARCHAR2,
                        // =============== 아래 필수구성 파라미터 ===========
                        N_RETURN         OUT  NUMBER,            /* RETURN VALUE */
                        V_RETURN         OUT  VARCHAR2,          /* RETURN MESSAGE */
                        C_RETURN1        OUT  SYS_REFCURSOR,      /* RETURN CURSOR */
                       );
    PROCEDURE PUT_LIST( A_PLANT          IN   EWIPMSTPLT.PLANT%TYPE,
                        A_XML            IN   CLOB,
                        A_REMARKS        IN   VARCHAR2,
                        A_TRAN_USER_ID   IN   VARCHAR2,
                        // =============== 아래 필수구성 파라미터 ===========
                        N_RETURN         OUT  NUMBER,            /* RETURN VALUE */
                        V_RETURN         OUT  VARCHAR2,          /* RETURN MESSAGE */
                        C_RETURN         OUT  SYS_REFCURSOR       /* RETURN CURSOR */
                       );
    end PKG_MATRIL002;
    ************** 패키지 헤더 끝 ************
    
    
    create or replace package body PKG_MATRIL002 is
    
    ***********************   여기에 Procedure 본체를 코딩한다. **********************
    
    end PKG_MATRIL002;


==== 1. SELECT Procedure ====


    PROCEDURE GET_LIST( A_PLANT          IN   EWIPMSTPLT.PLANT%TYPE,
                        A_LANG           IN   VARCHAR2,
                        A_FROM_DATE      IN   VARCHAR2,
                        A_TO_DATE        IN   VARCHAR2,
                        A_WH_CODE        IN   VARCHAR2,
                        A_INVOCE_NO      IN   VARCHAR2,
                        A_LOT_ID         IN   VARCHAR2,
                        N_RETURN         OUT  NUMBER,            /* RETURN VALUE */
                        V_RETURN         OUT  VARCHAR2,          /* RETURN MESSAGE */
                        C_RETURN         OUT  SYS_REFCURSOR      /* RETURN CURSOR */
                   )
    IS
    BEGIN
    
    OPEN C_RETURN FOR **************** 데이터 리턴을 위해 cursor를 선언한다.
        SELECT 'N' AS SEL,
               T1.LOT_ID AS LOT_ID,
               T1.QTY AS QTY,
               M.UNIT,
               CEIL(CASE WHEN M.MAT_TYPE IN ('FERT', 'HALB') THEN T1.QTY * M.SIZE_X * M.SIZE_Y ELSE 0 END / 1000000000) * 100 AS M2,
               T1.OUT_DOC_NO AS INVOICE_NO,
               T1.MAT_ID AS MAT_ID,
               T1.MAT_ID AS MAT_ID_DESC,
               T1.VENDOR_CODE AS VENDOR_CODE,
               T1.VENDOR_CODE AS VENDOR_CODE_DESC,
               T1.WH_CODE AS WH_CODE,
               T1.WH_CODE AS WH_CODE_DESC,
               CHAR_TO_DATE(T1.TRAN_TIME) AS TRAN_TIME,
               T2.LAST_HIST_SEQ AS HIST_SEQ
        FROM EWIPLOTPOI T1
             INNER JOIN EWIPLOTSTS T2 ON T2.PLANT = T1.PLANT
                    AND T2.LOT_ID = T1.LOT_ID
                    AND T2.LAST_ACTIVE_HIST_SEQ = T1.HIST_SEQ
         INNER JOIN EWIPMSTMAT M ON M.PLANT = T1.PLANT 
                                 AND M.MAT_ID = T1.MAT_ID
        WHERE T1.PLANT = A_PLANT
            AND T1.TRAN_TIME >= A_FROM_DATE ||'000000'
            AND T1.TRAN_TIME <= A_TO_DATE ||'235959'
            AND T1.LOT_ID = NVL(A_LOT_ID, T1.LOT_ID)
            AND T1.OUT_DOC_NO = NVL(A_INVOCE_NO, T1.OUT_DOC_NO)
        ORDER BY T1.TRAN_TIME, T1.MAT_ID, T1.QTY;
    
        N_RETURN := 0;
        V_RETURN := 'MSG_COM_004'; --정상적으로 처리되었습니다.
    
        EXCEPTION
            WHEN OTHERS THEN
                N_RETURN := 1;
                V_RETURN := SQLERRM;
    END GET_LIST;

==== 2, 3, 4. Insert, Udate, Delete Procedure ====

    
    PROCEDURE PUT_LIST( A_PLANT          IN   EWIPMSTPLT.PLANT%TYPE,
                        A_XML            IN   CLOB,
                        A_REMARKS        IN   VARCHAR2,
                        A_TRAN_USER_ID   IN   VARCHAR2,
                        N_RETURN         OUT  NUMBER,            /* RETURN VALUE */
                        V_RETURN         OUT  VARCHAR2,          /* RETURN MESSAGE */
                        C_RETURN         OUT  SYS_REFCURSOR       /* RETURN CURSOR */
                       )
    IS
    
    V_FORM_CODE         EWIPLOTHIS.TRAN_FORM%TYPE := 'MATRIL002';
    V_TRAN_TIME         EWIPLOTHIS.TRAN_TIME%TYPE := FUN_CURR_TIME();
    V_GROUP_TR_KEY_C    EWIPLOTHIS.GROUP_TR_KEY_C%TYPE;
    
    BEGIN
    
        FOR R_REC IN (SELECT S1.COLUMN_VALUE.EXTRACT('//LOT_ID/text()' ).GETSTRINGVAL()          AS LOT_ID,
                             S1.COLUMN_VALUE.EXTRACT('//HIST_SEQ/text()' ).GETSTRINGVAL()        AS HIST_SEQ
                        FROM TABLE( XMLSEQUENCE(XMLTYPE(A_XML).EXTRACT('//Table'))) S1
                     )
        LOOP
            ***************** 일료시 다른프로시져 호출
            PRC_COMM_LOT_CHECK(
                A_PLANT => A_PLANT,
                A_LOT_ID => R_REC.LOT_ID,
                A_TRAN_FORM => V_FORM_CODE,
                A_WH_CODE => NULL,
                A_LAST_ACTIVE_HIST_SEQ => R_REC.HIST_SEQ,
                N_RETURN => N_RETURN,
                V_RETURN => V_RETURN
                );
    
            IF N_RETURN != 0 THEN
                RETURN;
            END IF;
    
            SELECT T1.GROUP_TR_KEY_C
               INTO V_GROUP_TR_KEY_C
            FROM EWIPLOTHIS T1
            WHERE T1.PLANT = A_PLANT
               AND T1.LOT_ID = R_REC.LOT_ID
               AND T1.HIST_SEQ = R_REC.HIST_SEQ;
    
            *********************** 삭제 프로시져 호출 *******************
            PRC_LOT_HIST_DEL(
                A_PLANT => A_PLANT,
                A_TRAN_TIME => V_TRAN_TIME,
                A_TRAN_USER_ID => A_TRAN_USER_ID,
                A_TRAN_FORM => V_FORM_CODE,
                A_REMARKS => A_REMARKS,
                A_GROUP_TR_KEY => V_GROUP_TR_KEY_C,
                A_GROUP_TYPE => 'C',
                N_RETURN => N_RETURN,
                V_RETURN => V_RETURN
                );
            IF N_RETURN != 0 THEN
                RETURN;
            END IF;
    
        END LOOP;
    
        N_RETURN := 0;
        V_RETURN := 'MSG_COM_001'; --정상적으로 처리되었습니다.
    
    EXCEPTION
        WHEN OTHERS THEN
        N_RETURN := 1;
        V_RETURN := SQLERRM;
    
    END PUT_LIST;
    
[[database_crud|↑ 상위로 가기]]