oracle설명
문서의 이전 판입니다!
오라클은 패키지를 기분으로 구성한다 패키지 명은 화면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;
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;
oracle설명.1751806488.txt.gz · 마지막으로 수정됨: (바깥 편집)