/* 처음과 끝의 delimiter는 감싸고 있는 부분이 연속적으로 실행될 수 있게 하는 역할을 한다. */
delimiter $$
/* 기존에 procedure가 있다면 삭제 */
DROP PROCEDURE if exists mydb.TestProcedure$$
/*
procedure를 생성하는 구문. procedure의 형식을 선언한다.
in은 전달받는 parameter, out은 결과값을 담아 보낼 parameter
*/
CREATE PROCEDURE mydb.TestProcedure(in num INT, in str varchar(20))
/* procedure의 내용은 begin과 end로 감싼다. */
BEGIN
/*
procedure 내부에서 사용할 수 있는 변수를 선언한다.
err 라는 변수를 선언하였으며 기본값을 0으로 선언했다.
SQLEXCEPTION이 발생한 경우 err에 -1을 set 하도록 선언했다.
*/
DECLARE err INT default '0';
DECLARE continue handler for SQLEXCEPTION set err = -1;
DECALRE result INT default '0';
start transaction;
/* 테스트로 임의의 테이블(primarytest)에 insert를 수행. */
insert into primarytest(
num1, num2, str1, str2
)values(
num, num*2, str, concat(str,' by primary')
); /* 성공 */
insert into primarytest(
num1, num2, str1, str2
) values(
str, str, num, num
); /* int형 대신 str값을 넣어 오류를 유도. 실패 */
/* SQLEXCEPTION 발생으로 인해 err 값이 -1로 setting이 되어 rollback이 수행된다. */
if err < 0 then
rollback;
else
commit;
end if;
/* 실패했기 때문에 아무것도 조회되지 않을 것이다. */
select num1, num2, str1, str2
from primarytest
/* select 한 결과를 바로 result에 저장한다. */
select sum(num1) into result
from primarytest
END$$
delimiter
;
BEGIN NOT ATOMIC -- 세션에 활성 트랜잭션이 없는 경우 새 트랜잭션을 시작
-- 예외 발생시 롤백 후 Error 반환
DECLARE EXIT HANDLER FOR SQLEXCEPTION -- 에러 캐치시 handler의 begin ~ end 블럭을 실행
BEGIN
ROLLBACK;
RESIGNAL; -- error condition 정보를 Stored Procedure를 호출한 클라이언트에게 전합니다.
END;
-- 현재 자동 커밋인 경우에만 트렌젝션 처리 추가
SET @IS_AUTOCOMMIT = @@AUTOCOMMIT;
IF @IS_AUTOCOMMIT = 1 THEN
START TRANSACTION;
END IF;
IF @IS_AUTOCOMMIT = 1 THEN
COMMIT;
END IF;
END;
