데이터베이스 질문과 답변
해당 쿼리 변경하고 있는데 어떤식으로 해야할 지 감이 안오네요 ㅠㅠ 도와주시면 감사하겠습니다.
IF(IN_MUNSEO_GB = '02')
FOR C1_REC IN (
SELECT S.GYEOLJAE_NO
, S.MUNSEO_GB
, SF_CS_COMMON_CODE('EA006', S.MUNSEO_GB) AS MUNSEO_GB_NM
, M.YANGSIK_CD
, (
SELECT YANGSIK_NM
FROM EA_YANGSIK_M
WHERE YANGSIK_CD = M.YANGSIK_CD
) AS YANGSIK_NM
, M.MIS_KEY
, M.GYEOLJAE_DOC_NO
, M.GYEOLJAE_JEMOK
, M.GIANJA_ID
, M.GIANJA_ID_GB
, M.GIANJA_NM
, M.GIANJA_BUSEO_CD
, M.GIANJA_BUSEO_NM
, M.GIANJA_BOJIK_CD
, M.GIANJA_BOJIK_NM
, M.GIAN_DATE
, '03' AS GONGGAE_GB
, SF_CS_COMMON_CODE('EA007', '03') AS GONGGAE_GB_NM
, M.GINGEUP_YN
, M.BOAN_YN
, M.GYEOLJAE_UP_NO
, SF_EA_TOP_GYEOLJAE_NO(M.GYEOLJAE_NO) AS TOP_GYEOLJAE_NO
, M.GYEOLJAE_LVL
, M.GYEOLJAE_REQ_CNT
, M.GYEOLJAE_PRCS_CNT
, M.GYEOLJAE_JINHAENG_GB
, SF_CS_COMMON_CODE('EA003', M.GYEOLJAE_JINHAENG_GB) AS GYEOLJAE_JINHAENG_GB_NM
, M.LAST_GYEOLJAE_DATE
, M.CANCEL_YN
, M.CANCEL_ID
, M.CANCEL_ID_GB
, (
SELECT X.USER_NM
FROM CS_USER_V X
WHERE X.USER_ID = M.CANCEL_ID
AND X.USER_ID_GB = M.CANCEL_ID_GB
AND X.BONJIK_YN = 'Y'
) AS CANCEL_NM
, M.CANCEL_DATE
, M.CANCEL_SAYU
, M.DELETE_YN
, M.DELETE_ID
, M.DELETE_ID_GB
, (
SELECT X.USER_NM
FROM CS_USER_V X
WHERE X.USER_ID = M.DELETE_ID
AND X.USER_ID_GB = M.DELETE_ID_GB
AND X.BONJIK_YN = 'Y'
) AS DELETE_NM
, M.DELETE_DATE
, M.FILE_PATH
, M.FILE_NM
, S.JEOPSU_BUSEO_CD
, S.JEOPSU_BUSEO_NM
, S.GYEOLJAE_YN
, S.GYEOLJAEJA_ID
, S.GYEOLJAEJA_ID_GB
, S.DOC_GYEOLJAE_BUSEO_CD
, S.DAERIJA_ID
, S.DAERIJA_ID_GB
FROM EA_GYEOLJAE_M M
JOIN (
-- 사용자 본인 또는 사용자가 대리자로 설정된 결재자가 결재순서인 경우 (승인/반려)
SELECT A.GYEOLJAE_NO
, SF_EA_TOP_GYEOLJAE_NO(A.GYEOLJAE_NO, '2') AS ORI_GYEOLJAE_NO
, '02' AS MUNSEO_GB
, '' AS JEOPSU_BUSEO_CD
, '' AS JEOPSU_BUSEO_NM
, '' AS GYEOLJAE_YN
, B.GYEOLJAEJA_ID
, B.GYEOLJAEJA_ID_GB
, CASE WHEN A.GYEOLJAE_LVL = 1 THEN NULL
WHEN A.GYEOLJAE_LVL = 2 THEN NVL(A.DOC_GYEOLJAE_BUSEO_CD2, C.DOC_GYEOLJAE_BUSEO_CD2)
WHEN A.GYEOLJAE_LVL = 3 THEN NVL(A.DOC_GYEOLJAE_BUSEO_CD3, C.DOC_GYEOLJAE_BUSEO_CD3)
WHEN A.GYEOLJAE_LVL = 4 THEN NVL(A.DOC_GYEOLJAE_BUSEO_CD4, C.DOC_GYEOLJAE_BUSEO_CD4)
END AS DOC_GYEOLJAE_BUSEO_CD
, (
SELECT US1.DAERIJA_ID
FROM EA_USER_SEOLJEONG_M US1
WHERE US1.GYEOLJAEJA_ID = B.GYEOLJAEJA_ID
AND US1.BUJAE_YN = '1'
AND US1.BUJAE_STT_DT <= CONVERT(NVARCHAR, GETDATE(), 112)
AND US1.BUJAE_END_DT >= CONVERT(NVARCHAR, GETDATE(), 112)
AND US1.DAERIJA_YN = '1'
AND US1.DAERIJA_ID = IN_USER_ID
AND US1.DAERIJA_ID_GB = IN_USER_ID_GB
) AS DAERIJA_ID
, (
SELECT US1.DAERIJA_ID_GB
FROM EA_USER_SEOLJEONG_M US1
WHERE US1.GYEOLJAEJA_ID = B.GYEOLJAEJA_ID
AND US1.BUJAE_YN = '1'
AND US1.BUJAE_STT_DT <= CONVERT(NVARCHAR, GETDATE(), 112)
AND US1.BUJAE_END_DT >= CONVERT(NVARCHAR, GETDATE(), 112)
AND US1.DAERIJA_YN = '1'
AND US1.DAERIJA_ID = IN_USER_ID
AND US1.DAERIJA_ID_GB = IN_USER_ID_GB
) AS DAERIJA_ID_GB
FROM EA_GYEOLJAE_M A
JOIN EA_GYEOLJAE_D B
ON A.GYEOLJAE_NO = B.GYEOLJAE_NO
AND (
(B.GYEOLJAEJA_ID = IN_USER_ID AND B.GYEOLJAEJA_ID_GB = IN_USER_ID_GB)
OR CASE WHEN (B.GYEOLJAEJA_ID, B.GYEOLJAEJA_ID_GB) = (
SELECT US2.GYEOLJAEJA_ID
, US2.GYEOLJAEJA_ID_GB
FROM EA_USER_SEOLJEONG_M US2
WHERE US2.BUJAE_YN = '1'
AND US2.BUJAE_STT_DT <= CONVERT(NVARCHAR, GETDATE(), 112)
AND US2.BUJAE_END_DT >= CONVERT(NVARCHAR, GETDATE(), 112)
AND US2.DAERIJA_YN = '1'
AND US2.DAERIJA_ID = IN_USER_ID
AND US2.DAERIJA_ID_GB = IN_USER_ID_GB
) THEN IN_USER_ID + '_' + IN_USER_ID_GB
ELSE B.GYEOLJAEJA_ID + '_' + B.GYEOLJAEJA_ID_GB
END = IN_USER_ID + '_' + IN_USER_ID_GB
)
AND B.GYEOLJAE_ST_GB = '2'
JOIN EA_YANGSIK_M C
ON A.YANGSIK_CD = C.YANGSIK_CD
WHERE dbo.SF_CS_NVL(A.DELETE_YN,'0') = '0' -- 삭제가 아닌것
AND dbo.SF_CS_NVL(A.CANCEL_YN,'0') = '0' -- 취소가 아닌것
AND A.GYEOLJAE_JINHAENG_GB IN ('1', '2')
UNION
SELECT A.GYEOLJAE_NO
, SF_EA_TOP_GYEOLJAE_NO(A.GYEOLJAE_NO, '2') AS ORI_GYEOLJAE_NO
, '10' AS MUNSEO_GB
, '' AS JEOPSU_BUSEO_CD
, '' AS JEOPSU_BUSEO_NM
, '1' AS GYEOLJAE_YN
, '' AS GYEOLJAEJA_ID
, '' AS GYEOLJAEJA_ID_GB
, CASE WHEN A.GYEOLJAE_LVL = 1 THEN NVL(A.DOC_GYEOLJAE_BUSEO_CD2, B.DOC_GYEOLJAE_BUSEO_CD2)
WHEN A.GYEOLJAE_LVL = 2 THEN NVL(A.DOC_GYEOLJAE_BUSEO_CD3, B.DOC_GYEOLJAE_BUSEO_CD3)
WHEN A.GYEOLJAE_LVL = 3 THEN NVL(A.DOC_GYEOLJAE_BUSEO_CD4, B.DOC_GYEOLJAE_BUSEO_CD4)
END AS DOC_GYEOLJAE_BUSEO_CD
, (
CASE WHEN G.DAERIJA_YN = '1' THEN G.USER_ID
ELSE ''
END
) AS DAERIJA_ID
, (
CASE WHEN G.DAERIJA_YN = '1' THEN G.USER_ID_GB
ELSE ''
END
) AS DAERIJA_ID_GB
FROM EA_GYEOLJAE_M A
JOIN EA_YANGSIK_M B
ON A.YANGSIK_CD = B.YANGSIK_CD
JOIN (
SELECT IN_USER_ID AS USER_ID
, IN_USER_ID_GB AS USER_ID_GB
, '0' AS DAERIJA_YN
FROM DUAL
UNION
SELECT GYEOLJAEJA_ID AS USER_ID
, GYEOLJAEJA_ID_GB AS USER_ID_GB
, '1' AS DAERIJA_YN
FROM EA_USER_SEOLJEONG_M S
WHERE S.BUJAE_YN = '1'
AND S.BUJAE_STT_DT <= CONVERT(NVARCHAR, GETDATE(), 112)
AND S.BUJAE_END_DT >= CONVERT(NVARCHAR, GETDATE(), 112)
AND S.DAERIJA_YN = '1'
AND S.DAERIJA_ID = IN_USER_ID
AND S.DAERIJA_ID_GB = IN_USER_ID_GB
) G
ON 1 = 1
JOIN CS_USER_V C /* 문서담당자의 접수부서를 가져온다. */
ON CASE WHEN A.GYEOLJAE_LVL = 1 THEN A.EOPMU_GYEOLJAEJA_ID2
WHEN A.GYEOLJAE_LVL = 2 THEN A.EOPMU_GYEOLJAEJA_ID3
WHEN A.GYEOLJAE_LVL = 3 THEN A.EOPMU_GYEOLJAEJA_ID4
END = G.USER_ID
AND CASE WHEN A.GYEOLJAE_LVL = 1 THEN A.EOPMU_GYEOLJAEJA_ID2_GB
WHEN A.GYEOLJAE_LVL = 2 THEN A.EOPMU_GYEOLJAEJA_ID3_GB
WHEN A.GYEOLJAE_LVL = 3 THEN A.EOPMU_GYEOLJAEJA_ID4_GB
END = G.USER_ID_GB
AND C.USER_ID = G.USER_ID
AND C.USER_ID_GB = G.USER_ID_GB
AND C.BONJIK_YN = 'Y' 크 */
WHERE NVL(A.DELETE_YN,'0') = '0'
AND NVL(A.CANCEL_YN,'0') = '0'
AND A.GYEOLJAE_NO = SF_EA_TOP_GYEOLJAE_NO(A.GYEOLJAE_NO, '1') /* 최종결재번호만 가져온다*/
AND A.GYEOLJAE_UP_NO IS NULL
AND A.GYEOLJAE_JINHAENG_GB IN ('2')
) S
ON S.ORI_GYEOLJAE_NO = M.GYEOLJAE_NO
)
LOOP PIPE ROW (
EA_GYEOLJAE_RECORD(
C1_REC.GYEOLJAE_NO
, C1_REC.MUNSEO_GB
, C1_REC.MUNSEO_GB_NM
, C1_REC.YANGSIK_CD
, C1_REC.YANGSIK_NM
, C1_REC.MIS_KEY
, C1_REC.GYEOLJAE_DOC_NO
, C1_REC.GYEOLJAE_JEMOK
, C1_REC.GIANJA_ID
, C1_REC.GIANJA_ID_GB
, C1_REC.GIANJA_NM
, C1_REC.GIANJA_BUSEO_CD
, C1_REC.GIANJA_BUSEO_NM
, C1_REC.GIANJA_BOJIK_CD
, C1_REC.GIANJA_BOJIK_NM
, C1_REC.GIAN_DATE
, C1_REC.GONGGAE_GB
, C1_REC.GONGGAE_GB_NM
, C1_REC.GINGEUP_YN
, C1_REC.BOAN_YN
, C1_REC.GYEOLJAE_UP_NO
, C1_REC.TOP_GYEOLJAE_NO
, C1_REC.GYEOLJAE_LVL
, C1_REC.GYEOLJAE_REQ_CNT
, C1_REC.GYEOLJAE_PRCS_CNT
, C1_REC.GYEOLJAE_JINHAENG_GB
, C1_REC.GYEOLJAE_JINHAENG_GB_NM
, C1_REC.LAST_GYEOLJAE_DATE
, C1_REC.CANCEL_YN
, C1_REC.CANCEL_ID
, C1_REC.CANCEL_ID_GB
, C1_REC.CANCEL_NM
, C1_REC.CANCEL_DATE
, C1_REC.CANCEL_SAYU
, C1_REC.DELETE_YN
, C1_REC.DELETE_ID
, C1_REC.DELETE_ID_GB
, C1_REC.DELETE_NM
, C1_REC.DELETE_DATE
, C1_REC.FILE_PATH
, C1_REC.FILE_NM
, C1_REC.JEOPSU_BUSEO_CD
, C1_REC.JEOPSU_BUSEO_NM
, C1_REC.GYEOLJAE_YN
, C1_REC.GYEOLJAEJA_ID
, C1_REC.GYEOLJAEJA_ID_GB
, C1_REC.DOC_GYEOLJAE_BUSEO_CD
, C1_REC.DAERIJA_ID
, C1_REC.DAERIJA_ID_GB
)
);
END LOOP;
for ~ in
loop를 어떤 형태로 바꿔야할지 알려주시면 감사하겠습니다.
커서를 이용하여 반복문을 돌려야 하는 것처럼 보이네요. 저도 검색을 통해 확인했습니다.
https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-cursor/
) 버튼을 클릭하여 작성 하시면 됩니다.