MariaDB
mariadb 프로지서 생성 2중 호출 에러 파라미터체크
밥돌이밥순이
2024. 8. 19. 10:22
반응형
mariadb 프로지서 생성후 프로지서 호출 프러저안에서 에러처 파라미터 체크 프로지서에서 프로시저 호출
1. 기본프로시저 생성
- 프로시저 생성
- 프로시저 존재하면 삭제
- 파라미터 추가 한글 사용시 CHARACTER SET UTF8 설정 추가 할것
- 프로시저 호출 CALL SP_LOGIN('user', 'password')
DROP PROCEDURE IF EXISTS SP_LOGIN;
DELIMITER $$
CREATE PROCEDURE SP_LOGIN(
IN I_LOGIN_ID VARCHAR(50) CHARACTER SET utf8,
IN I_PASSWORD VARCHAR(50) CHARACTER SET UTF8
)
BEGIN
/*
CALL SP_LOGIN('user', 'password')
*/
END $$
DELIMITER ;2.try catch ... SQL에러 처리 (문법 ,로직 등등)
- UDE 사용자 정의 에러 (로젝체크 할때
- Maria에서 제공하는 에러
- 99999는 사용정의 한 값 일 때 에러 로그 기록 하지 않습니다.
- 기타 오류는 로그 기록 추후 디버그 할때 사용
- V_PARAM 입력 파라미터 Json으로 기록,JSON_OBJECT를 통해 json 으로 변경
- SP_SYS_LOG_ERR_INSERT 로그 기록
/*--------------------------------------------------------------------*/
/* 변수 추가
/*--------------------------------------------------------------------*/
DECLARE V_PARAM TEXT CHARACTER SET UTF8;
DECLARE V_RTN_CD VARCHAR(50) CHARACTER SET UTF8 DEFAULT 'OK';
DECLARE V_RTN_MSG VARCHAR(100) CHARACTER SET utf8;
-- 사용장 정의
DECLARE V_USER_CNT INT DEFAULT 0;
/*--------------------------------------------------------------------*/
/* EXCEPTION 로그 기록
/*--------------------------------------------------------------------*/
#DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_loop_done = TRUE; --NOT FOUND 예외 선언
DECLARE UDE CONDITION FOR SQLSTATE '99999' ; -- user-defined exception
DECLARE EXIT HANDLER FOR SQLEXCEPTION -- 아래 INSERT에서 SQLEXCEPTION 에러 발생시 BEGIN~END 실행하는 예외처리
BEGIN
GET DIAGNOSTICS CONDITION 1 @rs = RETURNED_SQLSTATE , @mt = MESSAGE_TEXT;
-- 사용자 정의 예외 정보 설정
IF @rs ='99999' THEN
SET V_RTN_MSG = concat(V_RTN_MSG);
ELSE
IF V_RTN_CD ='OK' THEN SET V_RTN_CD='E'; END IF;
SET V_RTN_MSG = concat(@rs,' : ' ,@mt);
-- IN, INOUT 파라미터 기술
SET V_PARAM = JSON_OBJECT('I_LOGIN_ID', I_LOGIN_ID ,'I_PASSWORD',I_PASSWORD);
CALL SP_SYS_LOG_ERR_INSERT('SP','SP_LOGIN',concat(V_RTN_CD,' : ',V_RTN_MSG,'V_PARAM:',V_PARAM),NULL,'192.168.1.*',I_LOGIN_ID);
END IF;
SELECT V_RTN_CD AS RTN_CD , V_RTN_MSG AS RTN_MSG;
END;3.파라미터 체크
- 매개 변수 체크
- SIGNAL UDE 를 통해 사용자 정의로 유도(DECLARE UDE CONDITION FOR SQLSTATE '99999')
- 로직 체크 하다고 롤백 할때 사용 할수 있음
/*--------------------------------------------------------------------*/
/* 파라미터 체크
/*--------------------------------------------------------------------*/
IF I_LOGIN_ID IS NULL OR LENGTH(I_LOGIN_ID) <1 THEN
SET V_RTN_CD ='00001';
SET V_RTN_MSG ='I_LOGIN_ID 필수 입력 입니다.';
SIGNAL UDE;
END IF ;4.결과
- 개발자와 정의
- 결과가 있든 없든 필수 출력
- RTN_CD ='OK' 이면 처리 성공 실패시 RTN_MSG 값 화면에 표시
/*--------------------------------------------------------------------*/
/* 처리 결과
/*--------------------------------------------------------------------*/
SELECT V_RTN_CD AS RTN_CD , V_RTN_MSG AS RTN_MSG,I_LOGIN_ID AS LOGIN_ID;9. 소스
DROP PROCEDURE IF EXISTS SP_API_LOGIN;
DELIMITER $$
CREATE PROCEDURE SP_API_LOGIN(
IN I_LOGIN_ID VARCHAR(50) CHARACTER SET utf8,
IN I_PASSWORD VARCHAR(50) CHARACTER SET UTF8,
IN I_IP VARCHAR(50) CHARACTER SET UTF8
)
BEGIN
/*
CALL SP_API_LOGIN(NULL, 'api', '192.168.1.1')
*/
-- 기본
DECLARE V_PARAM TEXT CHARACTER SET UTF8;
DECLARE V_RTN_CD VARCHAR(50) CHARACTER SET UTF8 DEFAULT 'OK';
DECLARE V_RTN_MSG VARCHAR(100) CHARACTER SET utf8;
-- 사용장 정의
DECLARE V_USER_CNT INT DEFAULT 0;
/*--------------------------------------------------------------------*/
/* EXCEPTION 로그 기록
/*--------------------------------------------------------------------*/
#DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_loop_done = TRUE; --NOT FOUND 예외 선언
DECLARE UDE CONDITION FOR SQLSTATE '99999' ; -- user-defined exception
DECLARE EXIT HANDLER FOR SQLEXCEPTION -- 아래 INSERT에서 SQLEXCEPTION 에러 발생시 BEGIN~END 실행하는 예외처리
BEGIN
GET DIAGNOSTICS CONDITION 1 @rs = RETURNED_SQLSTATE , @mt = MESSAGE_TEXT;
-- 사용자 정의 예외 정보 설정
IF @rs ='99999' THEN
SET V_RTN_MSG = concat(V_RTN_MSG);
ELSE
IF V_RTN_CD ='OK' THEN SET V_RTN_CD='E'; END IF;
SET V_RTN_MSG = concat(@rs,' : ' ,@mt);
-- IN, INOUT 파라미터 기술
SET V_PARAM = JSON_OBJECT('I_LOGIN_ID', I_LOGIN_ID ,'I_PASSWORD',I_PASSWORD,'I_IP',I_IP);
CALL SP_SYS_LOG_ERR_INSERT('SP','SP_API_LOGIN',concat(V_RTN_CD,' : ',V_RTN_MSG,'V_PARAM:',V_PARAM),null,I_IP,I_LOGIN_ID);
END IF;
SELECT V_RTN_CD AS RTN_CD , V_RTN_MSG AS RTN_MSG;
END;
/*--------------------------------------------------------------------*/
/* 파라미터 체크
/*--------------------------------------------------------------------*/
IF I_LOGIN_ID IS NULL OR LENGTH(I_LOGIN_ID) <1 THEN
#SET V_RTN_CD ='00000';
#SET V_RTN_MSG =FN_GET_MSG('KOR','MSG_00002','');
SET V_RTN_CD ='00001';
SET V_RTN_MSG ='I_LOGIN_ID 필수 입력 입니다.';
SIGNAL UDE;
END IF ;
SELECT COUNT(*) INTO V_USER_CNT FROM tb_sys_user u WHERE u.USER_ID =I_LOGIN_ID AND U.PW =I_PASSWORD AND U.SYS_CD ='API';
IF V_USER_CNT = 1 THEN
SET V_RTN_CD ='OK';
SET V_RTN_MSG =FN_GET_MSG('KOR','MSG_00001','');
/*------------------------------------------------------------------------*/
/* 시스템 접속 로그 생성 - 시스템 로그인 시 생성
/*------------------------------------------------------------------------*/
CALL SP_SYS_LOG_LOGIN_INSERT( I_LOGIN_ID -- I_LOGIN_ID
, 'Y' -- Y 록인 성공 N:로그인 실패
, I_IP -- I_IP
);
/*------------------------------------------------------------------------*/
/*로그인 여부 기록
/*------------------------------------------------------------------------*/
UPDATE tb_sys_user
SET LOGIN_YN ='Y'
WHERE USER_ID =I_LOGIN_ID;
ELSE
SET V_RTN_CD ='00000';
SET V_RTN_MSG =FN_GET_MSG('KOR','MSG_00002','');
/*------------------------------------------------------------------------*/
/* 시스템 접속 로그 생성 - 시스템 로그인 시 생성
/*------------------------------------------------------------------------*/
CALL SP_SYS_LOG_LOGIN_INSERT( I_LOGIN_ID -- I_LOGIN_ID
, 'Y' -- Y 록인 성공 N:로그인 실패
, I_IP -- I_IP
);
SIGNAL UDE;
END IF;
/*--------------------------------------------------------------------*/
/* 처리 결과
/*--------------------------------------------------------------------*/
SELECT V_RTN_CD AS RTN_CD , V_RTN_MSG AS RTN_MSG,I_LOGIN_ID AS LOGIN_ID;
END $$
DELIMITER ;
반응형