프로시저 내에서 다른 프로시저를 호출하는 방법은 간단하며, 단순히 CALL 프로시저명(파라미터); 하면 호출이 된다.
[그림1] SP 내에서 다른 SP의 호출
여기서 중요한 점은 호출하는 상위 SP(호출자, caller)와 호출 당하는 하위 SP(피호출자, callee) 사이의 데이터 전달 및 에러 처리 방식에 대하여 어떻게 할 것인가 이다.
상위 SP는 외부 Application(예를들어 WAS)에게 데이터를 SELECT구문을 통한 Recordset[n] 형태로 전달해야 하는 경우, 해당 상위 SP를 Wrapping 하는 API용 SP를 만들어야 할 수도 있다.
왜냐하면 SP에서 SELECT 구문에 의한 Recordset[n] 형태의 반환 결과는 다른 SP에서 호출하여 사용할 방법이 없기 때문이며, 하위 SP에서 반환된 Recordset은 상위 SP를 통해서 외부 Application으로 바로 전달된다. 한편으로는 편리하기도 하지만 SP의 재사용성의 측면에서는 제약점이기도 하다. 이러한 점 때문에 정책적으로 SP의 반환 결과는 OUT 변수 또는 임시 테이블을 통해 반환하는 방식으로 결정한다면, 외부 Application과 데이터를 주고받는 그룹과 내부적으로 재사용을 위한 그룹으로 분리하여 개발/관리 되어야 하는 번거러움이 발생하기도 한다.
[그림2] SP_INNER의 재사용을 위한 결과 반환 예시
에러 처리에 대해서는 하위 SP에서 에러(사용자 정의 에러 포함)가 발생할 경우 상위 SP로 해당 에러를 전달해야 하고, 전달된 에러에 대해서 외부 Application에서 에러 발생에 대한 트랜젝션 ROLLBACK이 수행되도록 해야 한다.
1. SP 사이에서의 데이터 전달
데이터의 전달은 Oracle의 sys_refcursor 같이 데이터를 전달할 수 있는 유형이 MariaDB에서는 존재하지 않아 여러 row의 데이터를 전달하기가 상당히 난감한 편이다. (단순히 SELECT문으로 Recordset을 반환할 수 있어서 편리하다고 할 수도 있으나
이런 경우는 JSON 형태로 데이터를 변환해서 OUT 파라미터로 전달하거나, 상위 SP에서 임시 테이블을 만들어서 하위 SP에서 임시 테이블에 데이터를 담아서 전달하는 방식을 사용한다. (임시 테이블은 해당 SP에 대한 호출이 완료되고 세션이 종료되는 시점에 자동으로 삭제가 된다.)
임시 테이블 생성시, 메모리 형태의 임시 테이블(ENGINE = MEMORY)은 생성하지 않아야 하는데, 이는 해당 SP가 얼마나 호출 될지 알수가 없기 때문에 DB서버의 메모리가 full 되어 다운되는 것을 막기 위함이다. 물리 테이블 형태로 생성/삭제 되는 경우가 반복되므로 DB서버에 overhead가 생기는건 어쩔수 없이 감수해야하는 상황이나, 대용량 배치 환경에서 운용해봐도 크게 무리가 되지는 않았다.
샘플로 상위 SP (SP_SAMPLE_OUTTER)에서 입력값으로 10을 입력받아 10을 곱하고 그 결과를 하위 SP (SP_SAMPLE_INNER)로 전달하면, 하위 SP에서 5를 더하여 OUT 변수에 계산 결과를 반환하는 샘플이 있다고 하자.
그리고 하위 SP에서는 [그림3]의 데이터를 JSON ARRAY로 반환하고 [그림4]의 데이터를 임시테이블에 담아서 반환한다고 가정하자.
[그림3] TBL_SAMPLE 데이터
[그림4] 임시 테이블(TMP_TST) 데이터
(1) OUT 파라미터를 통한 단순 데이터의 전달
단순히 SP에서 정의된 OUT 파라미터(out_val1)에 값을 할당하여 호출자에게 전달(v_rlt_val1) 처리한다.
-- Return of numeric data
SET out_val1 := v_temp_val;[코드1] 하위 SP에서 계산값의 OUT 변수 할당
-- 내부 SP 호출 (pseudo code)
CALL SP_SAMPLE_INNER(v_rlt_val1);
-- 수치 데이터의 반환
SELECT v_rlt_val1 AS VAL1,
'Recordset[0]' AS RS_DVSN_CD;[코드2] 상위 SP에서 하위 SP의 OUT 변수값 반환 처리
(2) OUT 파라미터를 통한 JSON ARRAY 데이터의 전달
하위 SP에서 테이블 조회 데이터를 JSON_OBJECT 함수를 이용하여 JSON 포맷으로 변환하여 OUT 변수(out_val2)에 값을 할당하여 호출자에게 전달(v_rlt_out2) 처리한다.
상위 SP에서는 외부 Application에게 JSON 데이터를 JSON_EXTRACT 함수와 순번 데이터(Generate된 일련번호 데이터)를 이용하여 Recordset 형태로 변환하여 데이터를 전달한다.
여기서는 순번 데이터를 CTE를 이용하여 생성하였으나, 물리 테이블에 미리 생성된 순번 데이터를 넣어놓고 그 데이터를 이용하는 것이 성능에 유리하다.
(참고로 MariaDB 10.6 이상의 버전에서는 JSON_TABLE 함수를 이용할 수도 있다. JSON_TABLE - MariaDB Knowledge Base)
-- Return of json array data
SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT(
'IDX', T.IDX,
'VAL1', T.VAL1,
'VAL2', T.VAL2
)), ']') AS JDOC
INTO out_val2
FROM TBL_SAMPLE T
;[코드3] 하위 SP에서 JSON 데이터의 OUT 변수 할당
-- 내부 SP 호출 (pseudo code)
CALL SP_SAMPLE_INNER(v_rlt_val2);
-- JSON 데이터의 반환 (여기서는 테이블 형태로 반환)
SELECT JSON_UNQUOTE(JSON_EXTRACT(V2.JDOC, CONCAT('$[', V1.ROW_CNT - 1, '].IDX'))) AS IDX,
JSON_UNQUOTE(JSON_EXTRACT(V2.JDOC, CONCAT('$[', V1.ROW_CNT - 1, '].VAL1'))) AS VAL1,
JSON_UNQUOTE(JSON_EXTRACT(V2.JDOC, CONCAT('$[', V1.ROW_CNT - 1, '].VAL2'))) AS VAL2,
'Recordset[1]' AS RS_DVSN_CD
FROM (
#CTE사용 Row generate (Mastering 되므로 많이 사용되면 성능상 좋지 않음)
#Row generation 용 물리 테이블 이용을 권장함
WITH RECURSIVE
CTE (ROW_CNT) AS (
SELECT 1
UNION ALL
SELECT ROW_CNT + 1
FROM CTE
WHERE ROW_CNT < 100 #JSON ARRAY 수 보다 조금 크게 ROW_CNT 수 적당히 조절 처리해야 함
)
SELECT *
FROM CTE
) V1
INNER JOIN (
SELECT v_rlt_val2 AS JDOC
) V2
WHERE V1.ROW_CNT <= JSON_LENGTH(V2.JDOC)
;
-- MariaDB 버전이 10.6 이상일 경우, JSON_TABLE() 함수를 사용할 수도 있다.
SELECT IDX,
VAL1,
VAL2,
'Recordset[1]' AS RS_DVSN_CD
FROM JSON_TABLE(v_rlt_val2,
'$[*]'
COLUMNS(
IDX int PATH '$.IDX',
VAL1 varchar(10) PATH '$.VAL1',
VAL2 varchar(10) PATH '$.VAL2'
)
) JT
;[코드4] 상위 SP에서 Json document 데이터를 Recordset 형태로 반환 처리
JSON 데이터의 사용시, 가급적 데이터가 적을 경우 사용을 권장하고 싶다. OUT 파라미터의 데이터 유형에 맞추어 가능한 데이터의 크기가 결정되겠지만, 가급적 수 MB 이내의 크기로 제한을 두어 사용하는것이 성능에 유리하지 않을까 싶다.
(3) 임시 테이블을 활용한 데이터의 전달
임시 테이블을 활용하여 데이터를 전달하고자 할때에는 어느 SP에서 임시 테이블을 생성하고 삭제할지에 대한 문제와 임시 테이블 이름에 대한 관리 문제가 따른다. 임시 테이블의 life-cycle은 세션 기준이므로 접속 세션이 종료되면 자동으로 삭제가 되지만, 여러 SP가 서로 호출하고 호출되기도 하는 상황에서는 관리가 쉽지 않다.
보통은 임시 테이블 관리 장표를 활용하여 어느 SP에서 생성하여 사용하는지, 이름은 무엇인지 등을 관리한다.
다수의 임시 테이블을 사용해야 하는 모듈에서는 최상위 SP에서 임시 테이블을 생성/삭제하는 관리 SP를 별도로 만들어 호출하여 사용하기도 한다. (임시 테이블의 생성 DDL: MariaDB knowledge base 테이블 생성 문서 참조)
여기서는 상위 SP에서 임시 테이블을 생성하고 하위 SP에서 임시 테이블에 데이터를 INSERT한 뒤, 상위 SP에서 데이터 조회후 임시 테이블을 삭제하는 형태로 구현하였다.
-- Return of temporary table data
INSERT INTO TMP_TST
(
MSG
)
VALUES
(
'TEST'
);[코드5] 하위 SP에서 임시 테이블에 데이터 할당
-- 임시 테이블의 생성
DROP TEMPORARY TABLE IF EXISTS TMP_TST;
CREATE TEMPORARY TABLE TMP_TST
(
SEQ int NOT NULL AUTO_INCREMENT, #순번
MSG varchar(255), #메세지
PRIMARY KEY(SEQ)
) ENGINE = INNODB;
-- 내부 SP 호출 (pseudo code)
CALL SP_SAMPLE_INNER();
-- 임시 테이블 데이터의 반환
SELECT T.SEQ,
T.MSG,
'Recordset[2]' AS RS_DVSN_CD
FROM TMP_TST T
;
-- 임시 테이블의 삭제
DROP TEMPORARY TABLE IF EXISTS TMP_TST;[코드6] 상위 SP에서 임시 테이블 데이터 조회 및 반환 처리
2. SP 사이에서의 에러 핸들링
SP 사이에서의 에러 핸들링의 중요 사항은 하위 SP에서 발생한 에러를 상위 SP로 이벤트 버블링 처럼 계속해서 상위로 상위로 전달해줘야 한다는데 있다.
이러한 에러의 전달은 간단하게 아래 [코드1]과 같이 SP호출시 반환되는 결과코드를 확인하여 에러코드(SP에서 out_rtn_no 값이 음수일 경우 에러발생으로 정의)일 경우, "사용자 정의 SQL STATE 값 45000"으로 처리할 수 있다.
-- 내부 SP 호출
CALL SP_SAMPLE_INNER(v_temp_val, #in_val
v_rlt_val1, #out_val1 -> numeric data
v_rlt_val2, #out_val2 -> json array data
v_rtn_no, v_rtn_msg, v_rtn_txt);
-- 내부 SP 호출시, 에러 확인
IF v_rtn_no < 0 THEN
-- 사용자 정의 에러 메세지 설정 (내부 SP에서 발생한 에러 메세지의 반환 포함)
SET v_rtn_msg := CONCAT('내부 프로시저(SP_SAMPLE_INNER) 호출중 에러가 발생했습니다.', v_new_line, '(Error Message: ', v_rtn_msg, ')');
-- 사용자 정의 예외 발생
SIGNAL SQLSTATE '45000';
END IF;[코드7] 상위 SP에서 하위 SP의 호출 및 에러 확인 처리
샘플로 하위 SP의 계산 결과 값을 0으로 나누는 처리를 하여 에러를 발생시켜 보면 다음과 같이 에러가 상위 SP쪽으로 전달 처리할 수 있다.
#-------------------------------------
# (2) 파라미터 값 계산 처리
#-------------------------------------
SET v_temp_val := IFNULL(in_val, 0) + 5;
-- 강제 에러 발생을 위한 예제 코드
SET v_temp_val := v_temp_val / 0;[코드8] 하위 SP에서 강제로 Division by 0 에러 발생 시키기
[그림5] 상위 SP 호출 시, 에러 발생 확인
[그림6] 에러 테이블의 순차적 에러발생 내역 (하위 SP -> 상위 SP)
위의 [그림6]에서와 같이 에러 테이블에 SP 호출 순서대로 에러 로그가 남게된다.
[예제] 상위 SP에서 하위 SP의 호출
- SP 실행 (상위 SP)
- SP 실행 결과
- 상위 및 하위 SP 샘플 소스
CREATE DEFINER=`USER_ID`@`%` PROCEDURE `SP_SAMPLE_INNER`(IN in_val INT, OUT out_val1 INT, OUT out_val2 LONGTEXT, OUT out_rtn_no INT, OUT out_rtn_msg VARCHAR(1000), OUT out_rtn_txt TEXT)
COMMENT '[Sample] SP 내에서 다른 SP 호출 예제 - 내부 SP'
whole_proc:
BEGIN
/*
title: [Sample] SP 내에서 다른 SP 호출 예제
desc.: 내부 SP (피호출자, callee)
param:
<IN 파라미터 기술>
in_val = 입력값
<INOUT 파라미터 기술>
<OUT 파라미터 기술>
out_val1 = 출력값1
out_val2 = 출력값2 (JSON ARRAY)
<사용한 임시 테이블 기술>
TMP_TST = 상위 SP에서 생성/삭제 (SP_SAMPLE_OUTTER)
<Application용 파라미터 기술>
out_rtn_no = Return value
out_rtn_msg = Return message
out_rtn_txt = Return text
return value:
0 = 정상실행
-1 = 실행오류(SQL 예외 발생)
음수 = 실행오류(사용자 정의 예외 발생)
usage:
SET @in_val := 10;
CALL SP_SAMPLE_INNER(@in_val, @out_val1, @out_val2,
@out_rtn_no, @out_rtn_msg, @out_rtn_txt);
SELECT @out_val1, @out_val2;
SELECT @out_rtn_no AS RTN_NO, @out_rtn_msg AS RTN_MSG, @out_rtn_txt AS RTN_TXT;
history:
작성일 작성자 내용
9999-01-01 홍길동 1. 초기 작성
*/
/**************************************************/
/* [1] 변수 선언 영역 */
/**************************************************/
-- (1) 공통 변수 선언
DECLARE v_proc_name varchar(100) DEFAULT 'SP_SAMPLE_INNER'; #프로시져 이름
DECLARE v_proc_step tinyint UNSIGNED DEFAULT 0; #프로시저 내에서 에러발생 위치번호
DECLARE v_call_stack longtext; #프로시저 호출 Parameter(IN, INOUT)
DECLARE v_sql_state text; #SQL STATE 값
DECLARE v_error_no int; #에러 번호
DECLARE v_error_msg text; #에러 메세지
DECLARE v_loop_done int DEFAULT FALSE; #Loop 종료여부 확인용
DECLARE v_loop_cnt int DEFAULT 0; #Loop 처리 횟수
DECLARE v_new_line varchar(2) DEFAULT '\n\r'; #디버깅 데이터 확인용 줄바꿈 처리
DECLARE v_rtn_no int DEFAULT 0; #결과반환 코드
DECLARE v_rtn_msg varchar(1000) DEFAULT '프로시저 실행성공'; #결과반환 메세지
DECLARE v_rtn_txt text DEFAULT '정상'; #결과반환 텍스트
-- (2) 지역 변수 선언
DECLARE v_temp_val int;
-- (3) 커서 선언
/**************************************************/
/* [2] 예외 선언 영역 */
/**************************************************/
-- (1) SQL 예외 선언
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- 1-1) SQL STATE 및 에러 정보 조회
GET DIAGNOSTICS CONDITION 1 v_sql_state = RETURNED_SQLSTATE,
v_error_no = MYSQL_ERRNO,
v_error_msg = MESSAGE_TEXT;
-- 1-2) 트랜젝션 rollback
#ROLLBACK; #외부 Application에서 트랜젝션 처리하므로 주석 처리
-- 1-3) IN, INOUT 파라미터 기술
SET v_call_stack = JSON_OBJECT(
'in_val', in_val
);
-- 1-4) 사용자 정의 예외 정보 설정
IF v_sql_state = '45000' THEN
SET v_error_no = v_rtn_no;
set v_error_msg = v_rtn_msg;
END IF;
-- 1-5) 에러 로그 기록
INSERT TBL_CM_ERR_LOG (prce_nm, prce_step_no, sql_sts_val, err_no, err_msge_cn, prce_call_param_cn, prce_call_dt, log_usr, log_dt)
VALUE (v_proc_name, v_proc_step, v_sql_state, v_error_no, v_error_msg, v_call_stack, DATE_FORMAT(NOW(0), '%Y%m%d%H%i%s'), 'SYSTEM', NOW(0));
-- 1-6) SQL 예외 발생시, 반환 값 설정
IF v_sql_state <> '45000' THEN
SET v_rtn_no = -1;
SET v_rtn_msg = CONCAT('프로시저 실행오류 (', v_proc_name, ')');
SET v_rtn_txt = v_error_msg;
END IF;
-- 1-7) 실행결과 반환 처리
SET out_rtn_no := v_rtn_no;
SET out_rtn_msg := v_rtn_msg;
SET out_rtn_txt := v_rtn_txt;
-- 1-8) 예외 발생 후, 계속 진행여부 설정
#RESIGNAL; -- Keep going process
END;
-- (2) NOT FOUND 예외 선언
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_loop_done = TRUE;
/**************************************************/
/* [3] 프로세스 핸들링 영역 */
/**************************************************/
#-------------------------------------
# (1) 임시 테이블의 생성
#-------------------------------------
-- 1-1) 임시 테이블 미존재 시, 생성 처리
CREATE TEMPORARY TABLE IF NOT EXISTS TMP_TST
(
SEQ int NOT NULL AUTO_INCREMENT, #순번
MSG varchar(255), #메세지
PRIMARY KEY(SEQ)
) ENGINE = INNODB;
-- 1-2) 임시 테이블 초기화
DELETE FROM TMP_TST;
#-------------------------------------
# (2) 파라미터 값 계산 처리
#-------------------------------------
SET v_temp_val := IFNULL(in_val, 0) + 5;
#-------------------------------------
# (3) 계산 결과 반환 처리
#-------------------------------------
-- 3-1) Return of numeric data
SET out_val1 := v_temp_val;
-- 3-2) Return of json array data
SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT(
'IDX', T.IDX,
'VAL1', T.VAL1,
'VAL2', T.VAL2
)), ']') AS JDOC
INTO out_val2
FROM TBL_SAMPLE T
;
-- 3-3) Return of temporary table data
INSERT INTO TMP_TST
(
MSG
)
VALUES
(
'TEST'
);
#-------------------------------------
# (99) 트랜젝션 commit 처리 및 실행결과 반환 처리
#-------------------------------------
-- 트랜젝션 commit 처리
#COMMIT; #외부 Application에서 트렌젝션 처리하므로 주석 처리
-- 결과 반환 처리
SET out_rtn_no := v_rtn_no;
SET out_rtn_msg := v_rtn_msg;
SET out_rtn_txt := v_rtn_txt;
END[코드9] 하위 SP (SP_SAMPLE_INNER)
CREATE DEFINER=`USER_ID`@`%` PROCEDURE `SP_SAMPLE_OUTTER`(IN in_val INT, OUT out_rtn_no INT, OUT out_rtn_msg VARCHAR(1000), OUT out_rtn_txt TEXT)
COMMENT '[Sample] SP 내에서 다른 SP 호출 예제 - 외부 SP'
whole_proc:
BEGIN
/*
title: [Sample] SP 내에서 다른 SP 호출 예제
desc.: 외부 SP (호출자, caller)
param:
<IN 파라미터 기술>
in_val = 입력값
<INOUT 파라미터 기술>
<OUT 파라미터 기술>
<Application용 파라미터 기술>
out_rtn_no = Return value
out_rtn_msg = Return message
out_rtn_txt = Return text
return value:
0 = 정상실행
-1 = 실행오류(SQL 에러 발생)
음수 = 실행오류(사용자 정의 에러 발생)
usage:
SET @in_val := 10;
CALL SP_SAMPLE_OUTTER(@in_val,
@out_rtn_no, @out_rtn_msg, @out_rtn_txt);
SELECT @out_rtn_no AS RTN_NO, @out_rtn_msg AS RTN_MSG, @out_rtn_txt AS RTN_TXT;
history:
작성일 작성자 내용
9999-01-01 홍길동 1. 초기 작성
*/
/**************************************************/
/* [1] 변수 선언 영역 */
/**************************************************/
-- (1) 공통 변수 선언
DECLARE v_proc_name varchar(100) DEFAULT 'SP_SAMPLE_OUTTER'; #프로시져 이름
DECLARE v_proc_step tinyint UNSIGNED DEFAULT 0; #프로시저 내에서 에러발생 위치번호
DECLARE v_call_stack longtext; #프로시저 호출 Parameter(IN, INOUT)
DECLARE v_sql_state text; #SQL STATE 값
DECLARE v_error_no int; #에러 번호
DECLARE v_error_msg text; #에러 메세지
DECLARE v_loop_done int DEFAULT FALSE; #Loop 종료여부 확인용
DECLARE v_loop_cnt int DEFAULT 0; #Loop 처리 횟수
DECLARE v_new_line varchar(2) DEFAULT '\n\r'; #디버깅 데이터 확인용 줄바꿈 처리
DECLARE v_rtn_no int DEFAULT 0; #결과반환 코드
DECLARE v_rtn_msg varchar(1000) DEFAULT '프로시저 실행성공'; #결과반환 메세지
DECLARE v_rtn_txt text DEFAULT '정상'; #결과반환 텍스트
-- (2) 지역 변수 선언
DECLARE v_temp_val int;
DECLARE v_rlt_val1 int;
DECLARE v_rlt_val2 text;
-- (3) 커서 선언
/**************************************************/
/* [2] 예외 선언 영역 */
/**************************************************/
-- (1) SQL 예외 선언
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- 1-1) SQL STATE 및 에러 정보 조회
GET DIAGNOSTICS CONDITION 1 v_sql_state = RETURNED_SQLSTATE,
v_error_no = MYSQL_ERRNO,
v_error_msg = MESSAGE_TEXT;
-- 1-2) 트렌젝션 rollback
#ROLLBACK; #외부 Application에서 트렌젝션 처리하므로 주석 처리
-- 1-3) IN, INOUT 파라미터 기술
SET v_call_stack = JSON_OBJECT(
'in_val', in_val
);
-- 1-4) 사용자 정의 예외 정보 설정
IF v_sql_state = '45000' THEN
SET v_error_no = v_rtn_no;
set v_error_msg = v_rtn_msg;
END IF;
-- 1-5) 에러 로그 기록
INSERT TBL_CM_ERR_LOG (prce_nm, prce_step_no, sql_sts_val, err_no, err_msge_cn, prce_call_param_cn, prce_call_dt, log_usr, log_dt)
VALUE (v_proc_name, v_proc_step, v_sql_state, v_error_no, v_error_msg, v_call_stack, DATE_FORMAT(NOW(0), '%Y%m%d%H%i%s'), 'SYSTEM', NOW(0));
-- 1-6) 실행결과 반환 설정
IF v_sql_state <> '45000' THEN
SET v_rtn_no = -1;
SET v_rtn_msg = CONCAT('프로시저 실행오류 (', v_proc_name, ')');
SET v_rtn_txt = v_error_msg;
END IF;
-- 1-7) 실행결과 반환
SET out_rtn_no := v_rtn_no;
SET out_rtn_msg := v_rtn_msg;
SET out_rtn_txt := v_rtn_txt;
-- 1-8) 예외 발생 후, 계속 진행여부 설정
#RESIGNAL; -- Keep going process
END;
-- (2) NOT FOUND 예외 선언
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_loop_done = TRUE;
/**************************************************/
/* [3] 프로세스 핸들링 영역 */
/**************************************************/
#-------------------------------------
# (1) 임시 테이블의 생성
#-------------------------------------
DROP TEMPORARY TABLE IF EXISTS TMP_TST;
CREATE TEMPORARY TABLE TMP_TST
(
SEQ int NOT NULL AUTO_INCREMENT, #순번
MSG varchar(255), #메세지
PRIMARY KEY(SEQ)
) ENGINE = INNODB;
#-------------------------------------
# (2) 내부 SP 전달값 처리
#-------------------------------------
SET v_temp_val := IFNULL(in_val, 0) * 10;
#-------------------------------------
# (3) 내부 SP 호출 (피호출자, callee)
#-------------------------------------
-- 3-1) 내부 SP 호출
CALL SP_SAMPLE_INNER(v_temp_val, #in_val
v_rlt_val1, #out_val1 -> numeric data
v_rlt_val2, #out_val2 -> json array data
v_rtn_no, v_rtn_msg, v_rtn_txt);
-- 3-2) 에러 확인
IF v_rtn_no < 0 THEN
-- 사용자 정의 에러 메세지 설정
SET v_rtn_msg := CONCAT('내부 프로시저(SP_SAMPLE_INNER) 호출중 에러가 발생했습니다.', v_new_line, '(Error Message: ', v_rtn_msg, ')');
-- 사용자 정의 예외 발생
SIGNAL SQLSTATE '45000';
END IF;
#-------------------------------------
# (4) 계산 결과 반환 처리
#-------------------------------------
-- 4-1) 수치 데이터의 반환
SELECT v_rlt_val1 AS VAL1,
'Recordset[0]' AS RS_DVSN_CD;
-- 4-2) JSON 데이터의 반환 (여기서는 테이블 형태로 반환)
SELECT JSON_UNQUOTE(JSON_EXTRACT(V2.JDOC, CONCAT('$[', V1.ROW - 1, '].IDX'))) AS IDX,
JSON_UNQUOTE(JSON_EXTRACT(V2.JDOC, CONCAT('$[', V1.ROW - 1, '].VAL1'))) AS VAL1,
JSON_UNQUOTE(JSON_EXTRACT(V2.JDOC, CONCAT('$[', V1.ROW - 1, '].VAL2'))) AS VAL2,
'Recordset[1]' AS RS_DVSN_CD
FROM (
#CTE사용 Row generate (Mastering 되므로 많이 사용되면 성능상 좋지 않음) -> Row generate 용 물리 테이블 이용을 권장함.
WITH RECURSIVE
CTE (ROW) AS (
SELECT 1
UNION ALL
SELECT ROW + 1
FROM CTE
WHERE ROW < 100 #Array수보다 조금 크게 ROW수 적당히 조절 처리
)
SELECT *
FROM CTE
) V1
INNER JOIN (
SELECT v_rlt_val2 AS JDOC
) V2
WHERE V1.ROW <= JSON_LENGTH(V2.JDOC)
;
-- 4-3) 임시 테이블 데이터의 반환
SELECT T.SEQ,
T.MSG,
'Recordset[2]' AS RS_DVSN_CD
FROM TMP_TST T
;
#-------------------------------------
# (98) 임시 테이블의 삭제
#-------------------------------------
DROP TEMPORARY TABLE IF EXISTS TMP_TST;
#-------------------------------------
# (99) 트랜젝션 commit 처리 및 실행결과 반환 처리
#-------------------------------------
-- 트랜젝션 commit 처리
#COMMIT; #외부 Application에서 트랜젝션 처리하므로 주석 처리
-- 결과 반환 처리
SET out_rtn_no := v_rtn_no;
SET out_rtn_msg := v_rtn_msg;
SET out_rtn_txt := v_rtn_txt;
END[코드10] 상위 SP (SP_SAMPLE_OUTTER)
'Database > MariaDB & MySQL' 카테고리의 다른 글
| MariaDB || 커서를 이용한 Loop의 사용 (명시적 커서의 사용) (0) | 2022.11.07 |
|---|---|
| MariaDB || Stored Procedure(SP) 및 Function(FN)의 기본 구조 (0) | 2022.11.03 |
| MariaDB || Stored Procedure의 활용 (0) | 2022.11.01 |
댓글