반응형
이글은 제가 개인적으로 기억해두려고 저장한 글입니다.
1. 엑셀을 받아서 import용 테이블에 넣고 밑에 체크하는걸 추천한다.
(밑에 내용을 저장하지 않고 dual을 사용해 임시 테이블 처럼 만들어 체크했다.)
2. myBatis에서 다중 루프가 가능하다.
루프안에 루프 변수를 아래와 같이 호출 가능함
#{uiDatsList.${colList.sCamelColumnId}}
단, $를 사용해야 하기 때문에 해당 List변수(colList)는 반드시 ui단에 받은 리스트가 아닌 서버단에서
만들어지 리스트여야 한다.
3. 날짜 검증은 REGEXP_LIKE를 사용했으니 참고.
<select id="selectValidImport" parameterType="hmap" resultType="hmap">
WITH TMP_TBL_COLS AS
( /*동적컬럼에 명칭 및 타입 등을 갖고 있는 데이터. DB에 저장 된 상태라면 그냥 그 테이블 조회*/
<foreach item="colList" collection="lColList" open="" close="" separator="UNION ALL">
SELECT #{colList.sColumnId} AS S_COLUMN_ID ,#{colList.sColumnIdC} AS S_COLUMN_ID_C ,#{colList.sColumnName} AS S_COLUMN_NAME ,#{colList.sColumnType} AS S_COLUMN_TYPE ,#{colList.sColumnEditType} AS S_COLUMN_EDIT_TYPE ,${colList.iEditMaxLength} AS I_COLUMN_MAX_SIZE FROM DUAL
</foreach>
)
<choose>
<when test="lUIDataList != null and lUIDataList.size != 0">
, TMP_TBL_UI AS
( /*화면 단에서 갖어온 경우 밑에와 같이 처리. DB에 저장 된 상태라면 그냥 그 테이블 조회*/
<foreach item="uiDatsList" collection="lUIDataList" open="" close="" separator="UNION ALL">
SELECT #{uiDatsList.iFalTESTNo} AS I_COL_TEST_NO , #{uiDatsList.sFalTESTDisp} AS S_COL_TEST_DISP FROM DUAL
<!-- <foreach item="colList" collection="lColList" open="" close="" separator=",">
#{uiDatsList.${colList.sCamelColumnId}} AS ${colList.sColumnIdC}
</foreach>
FROM DUAL -->
</foreach>
)
</when>
</choose>
, TMP_TBL_XLS AS
( /* 엑셀로 받은 데이터 테이블화. DB에 저장 된 상태라면 그냥 그 테이블 조회*/
<foreach item="xlsDatsList" collection="lXlsDataList" open="" close="" separator="union all">
SELECT
TO_NUMBER(#{xlsDatsList.iExcelRow}) AS I_EXCEL_ROW <foreach item="colList" collection="lColList" open="" close="" separator=""> ,#{xlsDatsList.${colList.sCamelColumnId}} AS ${colList.sColumnIdC} </foreach>
FROM DUAL
</foreach>
)
, TBL_TEMP AS (
SELECT I_EXCEL_ROW
,S_COLUMN_ID
,S_COLUMN_ID_C
,S_COLUMN_NAME
,S_COLUMN_TYPE
,S_COLUMN_EDIT_TYPE
,I_COLUMN_MAX_SIZE
,S_COLUMN_DATA
,CASE WHEN (UPPER(S_COLUMN_EDIT_TYPE) != 'DATE' and UPPER(S_COLUMN_EDIT_TYPE) != 'COMBO' )/*Date and Combo는 사이즈 체크 안함.*/ and LENGTHB(S_COLUMN_DATA)>TO_NUMBER(DECODE(UPPER(S_COLUMN_EDIT_TYPE),'TEXTAREA',3998,I_COLUMN_MAX_SIZE)) THEN I_COLUMN_MAX_SIZE ELSE 0 END AS OVER_MAX_SIZE
FROM TMP_TBL_COLS C
, (
SELECT I_EXCEL_ROW
,S_COLUMN_IDX
,S_COLUMN_DATA
FROM (
SELECT X.*
FROM TMP_TBL_XLS X
)
UNPIVOT ( S_COLUMN_DATA
FOR S_COLUMN_IDX
IN (
<foreach item="colList" collection="lColList" open="" close="" separator=",">${colList.sColumnIdC}</foreach>
)
)
) D
WHERE C.S_COLUMN_ID_C = D.S_COLUMN_IDX
)
, TMP_TBL_CMB AS
( /*화면 단에서 사용중인 콤보 데이터 */
<include refid="ComboData" />
)
, TMP_TBL_STATUS as
( /*화면 단에서 사용중인 콤보 데이터 */
<include refid="ComboStatus" />
)
SELECT I_EXCEL_ROW ,S_COLUMN_NAME ,S_COLUMN_DATA
, DECODE(M.MSG_TEXT,NULL, R.S_ERR_MSG_CD,REPLACE(REPLACE( M.MSG_TEXT ,'{0}',REGEXP_SUBSTR(R.MSG_ARG,'[^\|]+',1,1)) ,'{1}' ,REGEXP_SUBSTR(R.MSG_ARG,'[^\|]+',1,2)))
AS S_ERR_MSG
FROM
(
/*필수컬럼 검사 msg.common.valid.required*/
SELECT I_EXCEL_ROW ,S_COLUMN_NAME ,S_COLUMN_ID ,S_COLUMN_DATA ,'msg.common.valid.required' AS S_ERR_MSG_CD , S_COLUMN_NAME AS MSG_ARG
FROM TBL_TEMP
WHERE S_COLUMN_ID IN ('COL_TEST_NO') /*엑셀 Import는 COL_TEST_NO만 필수 체크하는 걸로 수정.*//*('COL_TEST_NO','COL_TEST_DISP','COL_TEST_STATUS')*/
AND S_COLUMN_DATA IS NULL
UNION ALL
/*사이즈 체크 msg.common.valid.max.length*/
SELECT I_EXCEL_ROW ,S_COLUMN_NAME ,S_COLUMN_ID ,S_COLUMN_DATA ,'msg.common.valid.max.length' AS S_ERR_MSG_CD , S_COLUMN_NAME||'|'||TO_CHAR(OVER_MAX_SIZE) AS MSG_ARG
FROM TBL_TEMP
WHERE OVER_MAX_SIZE > 0
UNION ALL
/*날짜 검사 msg.import.valid.date.format */
SELECT I_EXCEL_ROW ,S_COLUMN_NAME ,S_COLUMN_ID ,S_COLUMN_DATA ,'msg.import.valid.date.format' AS S_ERR_MSG_CD , '' MSG_ARG
FROM TBL_TEMP
WHERE UPPER(S_COLUMN_TYPE) = 'DATE'
AND S_COLUMN_DATA IS NOT NULL
AND
( -- 숫자형태로서 YYYYMMDD 8자리이어야 하고, 01,03,05,07,08,10,12월은 31일까지가 최대이고, 02월은 최대가 29일이며, 04,06,09,11월은 30일이 최대임.
NOT REGEXP_LIKE(S_COLUMN_DATA, '^[0-9]{4}(((0[13578]|(10|12))(0[1-9]|[1-2][0-9]|3[0-1]))|(02(0[1-9]|[1-2][0-9]))|((0[469]|11)(0[1-9]|[1-2][0-9]|30)))$')
OR -- 년도가 400으로 나누어지거나, 4로 나누어지고 100으로 나누어지지 않는 윤년이 아닌 경우
(NOT REGEXP_LIKE(SUBSTR(S_COLUMN_DATA, 1, 4), '((1[6-9]|[2-9]\d)([02468][48]|[2468][048]|[13579][26]))|((16|[2468][048]|[3579][26])00)') AND SUBSTR(S_COLUMN_DATA, 5, 2) = '02' AND SUBSTR(S_COLUMN_DATA, 7, 2) = '29')
)
UNION ALL
/*숫자타입 검사 msg.import.valid.number*/
SELECT I_EXCEL_ROW ,S_COLUMN_NAME ,S_COLUMN_ID ,S_COLUMN_DATA ,'msg.import.valid.number' AS S_ERR_MSG_CD , '' MSG_ARG
FROM TBL_TEMP
WHERE UPPER(S_COLUMN_TYPE) = 'NUMBER'
AND S_COLUMN_DATA IS NOT NULL
AND REGEXP_LIKE(S_COLUMN_DATA,'[^-0-9.]')
UNION ALL
/*콤보 검사 msg.import.valid.data*/
SELECT I_EXCEL_ROW ,S_COLUMN_NAME ,S_COLUMN_ID ,S_COLUMN_DATA ,'msg.import.valid.data' AS S_ERR_MSG_CD , '' MSG_ARG
FROM TBL_TEMP
WHERE S_COLUMN_ID IN ('COL_CHANGE_QTY','COL_POTENTIAL_CO','COL_SCH_IMPACT')
AND S_COLUMN_DATA IS NOT NULL
AND UPPER(S_COLUMN_DATA) NOT IN (SELECT UPPER(S_VALUE) FROM TMP_TBL_CMB)
UNION ALL
/*상태값(콤보) 검사 msg.import.valid.data*/
SELECT I_EXCEL_ROW ,S_COLUMN_NAME ,S_COLUMN_ID ,S_COLUMN_DATA ,'msg.import.valid.data' AS S_ERR_MSG_CD , '' MSG_ARG
FROM TBL_TEMP
WHERE S_COLUMN_ID = 'COL_TEST_STATUS'
AND S_COLUMN_DATA IS NOT NULL
AND UPPER(REPLACE(S_COLUMN_DATA,'.','')) NOT IN (SELECT UPPER(REPLACE(S_VALUE,'.','')) FROM TMP_TBL_STATUS)
UNION ALL
/*부서 검사 msg.import.valid.data*/
SELECT I_EXCEL_ROW ,S_COLUMN_NAME ,S_COLUMN_ID ,S_COLUMN_DATA ,'msg.import.valid.data' AS S_ERR_MSG_CD , '' MSG_ARG
FROM TBL_TEMP T
WHERE S_COLUMN_ID = 'COL_TEST_DISP'
AND S_COLUMN_DATA IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM TBL_DISP
WHERE PJT_CODE = #{sPjtCode}
AND DISP_USE_YN = 'Y'
AND DIST_USE_YN = 'Y'
AND UPPER(DISP_NAME) = UPPER(T.S_COLUMN_DATA)
)
UNION ALL
/*유저 검사 msg.import.valid.data*/
SELECT I_EXCEL_ROW ,S_COLUMN_NAME ,S_COLUMN_ID ,S_COLUMN_DATA ,'msg.import.valid.data' AS S_ERR_MSG_CD , '' MSG_ARG
FROM TBL_TEMP T
WHERE S_COLUMN_ID = 'COL_TESTER_USER'
AND S_COLUMN_DATA IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM TBL_DISP_USER
WHERE PJT_CODE = #{sPjtCode}
AND USER_ID = T.S_COLUMN_DATA
)
UNION ALL
/*유저기준 부서가 옳은지 검사 msg.import.valid.data*/
SELECT U.I_EXCEL_ROW ,U.S_COLUMN_NAME ,U.S_COLUMN_ID ,U.S_COLUMN_DATA ,'msg.user.diferent.disp' AS S_ERR_MSG_CD , '' MSG_ARG
FROM (
SELECT *
FROM TBL_TEMP T
WHERE S_COLUMN_ID = 'COL_TEST_DISP'
) D
,(
SELECT *
FROM TBL_TEMP T
WHERE S_COLUMN_ID = 'COL_TESTER_USER'
AND S_COLUMN_DATA IS NOT NULL
)U
WHERE D.I_EXCEL_ROW(+) = U.I_EXCEL_ROW
AND NOT EXISTS (
SELECT 1
FROM TBL_DISC_USER US, TBL_DISC DISC
WHERE DISC.DISC_CODE = US.DISC_CODE
AND DISC.PJT_CODE = US.PJT_CODE
AND UPPER(DISC.DISP_NAME) = UPPER(D.S_COLUMN_DATA)
AND US.USER_ID = U.S_COLUMN_DATA
AND US.PJT_CODE = #{sPjtCode}
AND ROWNUM = 1
)
UNION ALL
/*COL_TEST_NO 중복 검사( 2020.12.09 UI + 엑셀 TEST no가 겹치지 않도록*/
SELECT T.*
FROM (
SELECT /*MIN(I_EXCEL_ROW) AS*/ I_EXCEL_ROW ,S_COLUMN_NAME ,S_COLUMN_ID ,S_COLUMN_DATA ,'msg.import.valid.duplicate.col' AS S_ERR_MSG_CD , 'TEST NO[ '||S_COLUMN_DATA||' ]' MSG_ARG
FROM TBL_TEMP T
WHERE S_COLUMN_ID IN ('COL_TEST_NO')
/* GROUP BY S_COLUMN_NAME ,S_COLUMN_DATA ,S_COLUMN_ID */
) T
WHERE EXISTS (
SELECT 1
FROM TMP_TBL_XLS
WHERE I_EXCEL_ROW != T.I_EXCEL_ROW
AND I_COL_TEST_NO = T.S_COLUMN_DATA
AND S_COLUMN_ID IN ('COL_TEST_NO')
)
) R
, ( SELECT * FROM TBL_MSG WHERE MSG_LANGUAGE = #{sLocal} ) M /*에러메시지 테이블*/
, (
SELECT *
FROM TBL_COLUMN /*동적 컬럼 정보 테이블*/
WHERE PJT_CODE = #{sPjtCode}
AND COL_TYPE_CODE= #{sFalTypeCode}
)C
WHERE R.S_ERR_MSG_CD = M.MSG_KEY(+)
AND R.S_COLUMN_ID = C.COLUMN_ID(+)
AND ROWNUM <![CDATA[<=]]> 1000 <!-- 에러 내용 1000건 까지만 출력. -->
ORDER BY C.COLUMN_ORDER, R.I_EXCEL_ROW
</select>
반응형
'IT > Oracle' 카테고리의 다른 글
| 대용량 insert (0) | 2022.07.06 |
|---|---|
| 오라클 페이징 쿼리. (0) | 2022.02.18 |
| 오라클 락 확인 (0) | 2021.02.03 |
| 오라클 CONNECT BY - 재귀호출 / 계층형 쿼리 (0) | 2021.02.03 |
| 오라클 조건문에 특수문자 & or % or ? 문자로 인식시키기 (0) | 2021.02.03 |
댓글