이거 하나면 프로시저를 다시 보지 않아도
다시 상기 시켜서 할 수 있을것 같다.
한달 간 프로시저에 대한 종지부를
다음 내용으로 끝내보려고 한다.
data.xml
<!-- 자료목록 -->
<select id="getDataMgmtList" parameterType="Map" resultType="Map">
SELECT
A.DATA_ID,
A.LGE_CD,
A.MID_CD,
A.DATA_NM,
B.DATA_NM AS CATE_NM,
A.REG_DT,
A.REG_DT,
A.REG_USR,
A.UPT_DT,
A.UPT_USR,
A.DEL_YN,
C.FILE_NM,
A.DNLD_CNT
FROM
TB_WAP050_010 A,
TB_WAP050_060 B,
TB_WAP050_070 C
<where>
A.DEL_YN = 'N'
AND C.DEL_YN ='N'
AND A.MID_CD = B.DATA_CD
AND A.DATA_ID = C.RFR_ID
<if test='SEARCH_CLSFC != NULL and SEARCH_CLSFC != "" and SEARCH_CLSFC != "전체"'>
AND A.MID_CD = #{SEARCH_CLSFC}
</if>
<if test='SEARCH_DTNM != null and SEARCH_DTNM != ""'>
AND UPPER(A.DATA_NM) LIKE UPPER('%'||#{SEARCH_DTNM}||'%')
</if>
</where>
ORDER BY
A.DATA_ID DESC, A.REG_DT DESC
</select>
이 data.xml 파일을
프로시저 바꿔보는 예제를
하나씩 설명해 나가겠다.
data.xml 파일
방금 위에있던 긴 쿼리를
<!-- 자료목록 -->
<select id="GET_DATA_MGMT_LIST" statementType="CALLABLE">
{
CALL PKG_TEST_LEE.GET_DATA_MGMT_LIST(
#{JSON_PARAM},
#{PROC_RESULT, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=resultMap}
)
}
</select>
다음과 같이 바꿔주었다.
#{JSON_PARAM} 은 IN_PARAM 데이터를 받는것을 의미한다.
#{PROC_RESULT}은 내가 내보낼 데이터를 말한다.
(나머지 부분은 mode=OUT 이런건 그냥 프로시저의 타입 설정 관련된 것이다.)
DataService.java
// 자료관리 목록 조회
public List<Map<String, Object>> getDataMgmtList(Map<String, Object> param) throws IOException, SQLException {
return sqlSession.selectList("portalMngr.dataPrvd.getDataMgmtList", param);
}
일반적으로
Service를 다음과 같이 만드는데,
// 자료관리 목록 조회
public void getDataMgmtList(Map<String, Object> param) throws IOException, SQLException {
sqlSession.selectList("portalMngr.dataPrvd.GET_DATA_MGMT_LIST", param);
}
이것은 void 랑 return 타입을 지워줘야한다.
왜냐면 필요가 없기 때문이다.
DataController.java
//============================================
//자료관리 목록 조회
//============================================
@RequestMapping(value = "/getDataMgmtList.do", produces = "application/json; charset=UTF-8", method = RequestMethod.POST)
@ResponseBody
public String getDataMgmtList(@RequestParam Map<String, Object> param, HttpServletRequest request) {
Map<String, Object> result = new HashMap<String, Object>();
try {
logger.debug("URL ::: {}", request.getRequestURL());
List<Map<String, Object>> dataList = dataPrvdService.getDataMgmtList(param);
int totalCnt = dataPrvdService.getDataMgmtCount(param);
result.put("RESULT", dataList);
result.put("TOTAL_COUNT", totalCnt);
result.put("SUCCESS", true);
} catch (SQLException e) {
logger.error("SQLException", e);
result.put("SUCCESS", false);
result.put("MESSAGE", e);
} catch (IOException e) {
logger.error("IOException", e);
result.put("SUCCESS", false);
result.put("MESSAGE", e);
}
return ObjectMapperSupport.objectToJson(result);
}
이게 일반적으로 우리가 데이터를 불러올 때 사용하는
방식이다.
이제 코드가 아래 처럼 바뀌면서
프로시저의 데이터를 불러 오게 된다.
//============================================
//자료관리 목록 조회
//============================================
@RequestMapping(value = "/getDataMgmtList.do", produces = "application/json; charset=UTF-8", method = RequestMethod.POST)
@ResponseBody
public String getDataMgmtList(@RequestParam Map<String, Object> param, HttpServletRequest request) {
Map<String, Object> result = new HashMap<String, Object>();
try {
logger.debug("URL ::: {}", request.getRequestURL());
param.put("JSON_PARAM", ObjectMapperSupport.objectToJson(param));
dataPrvdService.getDataMgmtList(param);
int totalCnt = dataPrvdService.getDataMgmtCount(param);
result.put("RESULT", param.get("PROC_RESULT"));
result.put("TOTAL_COUNT", totalCnt);
result.put("SUCCESS", true);
} catch (SQLException e) {
logger.error("SQLException", e);
result.put("SUCCESS", false);
result.put("MESSAGE", e);
} catch (IOException e) {
logger.error("IOException", e);
result.put("SUCCESS", false);
result.put("MESSAGE", e);
}
return ObjectMapperSupport.objectToJson(result);
}
이것이 프로시저를
사용하는 코드이다.
이렇게 까지 했다면
프로시저 백서비스 단은 전부 해준것이다.
이제 Oracle에 가서 직접
프로시저 쿼리를 짜주고 호출해주자!
Oracle
자 오라클 디비 기반으로
프로시저를 시작해보자!
패키지
다음과 같이 PKG_TEST_LEE
(이름이 담겨있으니까 빨간색은 ㅋㅋ 피하겠다.!)
파일을 만들어준다.
하나만 만들어줘도 좋고,
⭐⭐⭐
가독성을 높이고 싶으면 패키지 하나는 프로시저를 호출만하고
나머지 PKG BODY 안에다가 프로시저 코드를 직접 넣는 방법이 있다.
선택은 본인 몫이다.
PKG_TEST_LEE
-- 자료제공 목록
PROCEDURE GET_DATA_MGMT_LIST(
IN_PARAM IN CLOB,
P_CURSOR OUT LIST_CURSOR
);
다음과 같이 호출 할 수 있게 페키지를
적는다
프로시저 이름 : GET_DATA_MGMT_LIST -> DATA.XML로 매칭 시켜야된다.
첫줄 : IN_PARAM 안에 데이터를 CLOB 엄청 많은 데이터를 받겠다는 의미이다.
(궁금하면 CLOB 과 BLOB 차이를 공부하면 좋다.)
두번째 줄: P_CURSOR로 데이터를 보낼거보 LIST_CURSOR은 그냥 이름이다.
P_CURSOR도 그냥 이름같다.
PKG_TEST_LEE Body
자 이제 본격적으로 쿼리를 짜고
프로시저 형태로 사용하는 공간이 바로
"Body"
공간이다.
⭐⭐⭐⭐⭐
보통 PROCEDURE -> IS -> BEGIN (실제쿼리) ~ ORDER BY -> EXCEPTION -> END
이 구조로 시작하고 끝난다.
이것만 기억 해줘도 좋다.
-- 자료제공 목록
PROCEDURE GET_DATA_MGMT_LIST(
IN_PARAM IN CLOB,
P_CURSOR OUT LIST_CURSOR
)
IS
P_SEARCH_CLSFC VARCHAR(30) := JSON_VALUE (IN_PARAM, '$.SEARCH_CLSFC');
P_SEARCH_DTNM VARCHAR(30) := JSON_VALUE (IN_PARAM, '$.SEARCH_DTNM');
BEGIN
OPEN P_CURSOR FOR
SELECT
A.DATA_ID,
A.LGE_CD,
A.MID_CD,
A.DATA_NM,
B.DATA_NM AS CATE_NM,
A.REG_DT,
A.REG_DT,
A.REG_USR,
A.UPT_DT,
A.UPT_USR,
A.DEL_YN,
C.FILE_NM,
A.DNLD_CNT
FROM
TB_WAP050_010 A,
TB_WAP050_060 B,
TB_WAP050_070 C
WHERE
1 = 1
AND A.DEL_YN = 'N'
AND C.DEL_YN ='N'
AND A.MID_CD = B.DATA_CD
AND A.DATA_ID = C.RFR_ID
AND A.MID_CD LIKE CASE WHEN P_SEARCH_CLSFC IS NOT NULL THEN P_SEARCH_CLSFC ELSE '%' END
AND UPPER(A.DATA_NM) LIKE CASE WHEN P_SEARCH_DTNM IS NOT NULL THEN '%' || P_SEARCH_DTNM || '%' ELSE '%' END
ORDER BY
A.DATA_ID DESC
, A.REG_DT DESC ;
END GET_DATA_MGMT_LIST;
프로시저 완성이다.
앞 선에 말했던 것처럼 프로시저가 다음과 같은
이름을 타고 실행하게된다.
보통 앞에 P_ 또는 V_ 를 안적어줘도 상관은 없지만,
나는 그냥 뭔가 이게 편하다라고할까?
이름 [데이터 타입] := JSON 타입으로 받겠다, IN_PARAM 안에, SEARCH_CLSFC 컬럼을
이런식으로 사용하는 건데,
한마디로 ...
TABLE에 없는 컬럼을 사용하기 위해서
⭐⭐⭐
다음과 같이 선언해준 것이다.
(테이블 안에 컬럼이있다면 선언해주지 않아도 BEGIN 절에서 에러가 발생하지 않는다.)
이것은 평소에 우리가 사용하는 SELECT 절 그대로
적으면 된다.
중요한게 WHERE 절인데,
1 = 1 은 기본적으로 적어주는게 좋다.
하나에 값에 하나를 가져오겠다는 뜻으로
오류가 절대 안나기 때문에
1 = 1 때문에 오류라고 절대 생각할 필요없다.
CASE WHEN 문
이게 바로 중요한 CASE WHEN 문이다.
AND [컬럼명] LIKE CASE WHEN P_SEARCH_CLSFC IS NOT NULL THEN P_SEARCH_CLSFC ELSE '%' END;
⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐
[컬럼명]에 데이터를 받겠다 P_SEARCH_CLSFC가 NULL값이 아닐때, 그 때 P_SEARCH_CLSFC(입력받은 값)과 같은
데이터를 검색하겠다는 의미이다.
(작동 성공)
To be continue..
'Career > Procedure' 카테고리의 다른 글
| [Procedure] PLS-00905: 프로시저명 오브젝트가 부당합니다PL/SQL: Statement ignored 오류해결 (0) | 2022.09.01 |
|---|