- SQL 내에서 구성한 저장 프로시저를 MyBatis에서 호출을 하는 경우에 프로시저의 파라미터(IN, OUT, INOUT)를 고려하여서 프로시저를 호출하는 방법에 대해 알아보고, 프로시저의 결과 값이 존재하면 반환받는 방법에 대해서 알아봅니다.
2) CALL
💡 CALL
- PostgreSQL 해당 명령어는 저장 프로시저를 실행하기 위한 SQL 명령입니다.
💡 CALL 명령의 구조
- procedure_name: 실행하려는 프로시저의 이름 - parameter: 프로시저가 필요로하는 매개변수입니다. 매개변수는 쉼표로 구분되며 필요한 매개변수가 없는 경우에는 괄호 안을 비워둘 수 있습니다.
CALL procedure_name ( [ parameter [, ...] ] )
3) 저장 프로시저 구성 및 호출
1. 저장 프로시저 구성
💡 저장 프로시저 구성
- 해당 프로시저는 ‘PROC_INSERT_FIND_USER’ 이름의 Dummy 데이터로 100건의 사용자를 쌓은 이후에 파라미터로 받은 생성된 아이디를 찾고 몇 명인지 반환해 주는 프로시저입니다.
1. pi_user_name: 동일한 사용자 이름으로 생성을 위해 파라미터로 받습니다(IN) 2. pi_find_id : 난수로 생성된 사용자 아이디를 검색하기 위한 텍스트를 파라미터로 받습니다(IN) 3. po_find_id_cnt: 검색된 아이디를 기반으로 Counting 하는 반환받는 파라미터입니다.(OUT)
create procedure proc_insert_find_user(IN pi_user_name character varying, IN pi_find_id character varying, OUT po_find_id_cnt integer)
language plpgsql
as
$$
DECLARE
LOOP_CNT INTEGER;
USER_CNT INTEGER;
BEGIN
-- 지역변수로 사용할 변수 지정
LOOP_CNT := 0;
-- 반복 구문
WHILE LOOP_CNT < 100 LOOP
INSERT INTO TB_USER (user_id, user_pw, user_nm, user_st)
VALUES(SUBSTRING(gen_random_uuid()::text, 1, 5), '1234', pi_user_name, 'A');
LOOP_CNT := LOOP_CNT + 1;
END LOOP;
-- pi_find_id에 해당하는 user_id 찾기
SELECT COUNT(*) INTO USER_CNT
FROM TB_USER
WHERE user_id LIKE '%' || pi_find_id || '%';
-- 찾은 user_id 개수를 po_find_id_cnt에 할당
po_find_id_cnt := USER_CNT;
END;
$$;
alter procedure proc_insert_find_user(varchar, varchar, out integer) owner to localmaster;
2. SQL내에서 프로시저를 호출해 봅니다.
💡 프로시저를 호출합니다.
- 프로시저를 호출하였을 때 OUT 값으로 po_find_id_cnt라는 값을 반환받습니다.
- /api/v1/user/dummyUser 엔드포인트로 HTTP Method POST를 이용하면 해당 API가 호출이 됩니다.
@Slf4j
@RestController
@RequestMapping("/api/v1/user")
public class UserController {
private final UserService userService;
public UserController(UserService userService) {
this.userService = userService;
}
/**
* 테스트용 Dummy 사용자를 생성하고 추가된 사용자를 반환 받습니다.
*
* @param dummyUserDto UserDto
* @return ApiResponseWrapper<ApiResponse> : 응답 결과 및 응답 코드 반환
*/
@PostMapping("/dummyUser")
public ResponseEntity<ApiResponse<Object>> dummyUser(@RequestBody DummyUserDto dummyUserDto) {
int result = userService.procInsertFindUser(dummyUserDto);
ApiResponse<Object> ar = ApiResponse.builder()
.result(result)
.resultCode(SuccessCode.INSERT.getStatus())
.resultMsg(SuccessCode.INSERT.getMessage())
.build();
return new ResponseEntity<>(ar, HttpStatus.OK);
}
}
3. UserService
@Service
public interface UserService {
int procInsertFindUser(DummyUserDto dummyUserDto);
}
4. UserServiceImpl
💡 UserServiceImpl - 해당 부분에서는 void 형태의 반환 값을 가지는 procInsertFindUser() 함수를 호출하고 return 값으로 dummyUserDto 내에 값이 반환됩니다. - 해당 반환된 값을 결과값으로 반환하도록 구성하였습니다.
/**
* Dummy User 를 생성하고 아이디를 기반으로 생성된 사용자 수를 조회합니다.
*
* @param dummyUserDto DummyUserDto
* @return int
*/
@Override
@Transactional
public int procInsertFindUser(DummyUserDto dummyUserDto) {
UserMapper um = sqlSession.getMapper(UserMapper.class);
int result = 0;
try {
um.procInsertFindUser(dummyUserDto);
result = dummyUserDto.getPoFindIdCnt();
} catch (Exception e) {
throw new BusinessExceptionHandler(e.getMessage(), ErrorCode.INSERT_ERROR)
}
return result;
}
5. UserMapper
@Repository
public interface UserMapper {
void procInsertFindUser(DummyUserDto dummyUserDto);
}
6. UserMapper.xml
💡 UserMapper.xml - 해당 xml 내에서 select 태그로 프로시저를 구성하였습니다.
1. <select statementType="CALLABLE">
- statementType 속성은 SQL 문장을 실행하는 방법을 결정합니다. - STATEMENT, PREPARED(Default)또는 CALLABLE 중 하나를 선택할 수 있는데 ‘CALLABLE’는 프로시저를 실행하는 방식입니다. 이를 사용합니다.
2. CALL multiflex_scma.PROC_INSERT_FIND_USER - 구성한 multiflex_scma 스키마 내에 PROC_INSERT_FIND_USER() 이름의 프로시저를 CALL 명령어를 통해 호출합니다.
3. #{piUserName, mode=IN, jdbcType=VARCHAR},
- 사전에 구성한 DTO의 값 중 하나로 매핑이 되며 mode는 프로시저의 파라미터를 의미하며 입력(IN)으로 지정하며, jdbcType=VARCHAR를 통해 VARCHAR 타입으로 전달합니다.
4. #{piFindUserId, mode=IN, jdbcType=VARCHAR},
- 동일하게 사전에 구성한 DTO의 값중 하나로 매핑이 되며 mode는 프로시저의 파라미터를 의미하며 입력(IN)으로 지정하며, jdbcType=VARCHAR를 통해 VARCHAR 타입으로 전달합니다.
5. #{poFindIdCnt, mode=OUT, jdbcType=INTEGER})
- 프로시저 내의 결과 값으로 ‘poFindIdCnt’ 값 형태로 반환을 받습니다. mode로 프로시저의 파라미터를 출력(OUT)으로 지정하였으며 jdbcType=INTEGER를 통해 INTEGER 타입으로 반환받습니다.
