UBi3

문서의 이전 판입니다!

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

  오라클은 아래 3개의 필수구성 파라미터가 프로시져 끝에 본지해야 한다.
  N_RETURN         OUT  NUMBER,            /* RETURN VALUE */
  V_RETURN         OUT  VARCHAR2,          /* RETURN MESSAGE */
  C_RETURN1        OUT  SYS_REFCURSOR,      /* RETURN CURSOR */

패키지 선언 본체

  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;