변수 선언, 대입, IF, 반복 문법이 DB마다 다릅니다. 아래 표는 뼈대만 비교합니다.
| 항목 | Oracle · Tibero | MySQL | MSSQL |
|---|---|---|---|
| 선언 | v NUMBER; (IS 와 BEGIN 사이) | DECLARE v INT; | DECLARE @v INT; |
| 대입 | v := 1; | SET v = 1; | SET @v = 1; |
| IF | IF .. THEN .. ELSIF .. END IF; | IF .. THEN .. ELSEIF .. END IF; | IF .. BEGIN .. END ELSE .. |
| 반복 | FOR i IN 1..3 LOOP .. END LOOP; | WHILE .. DO .. END WHILE; | WHILE .. BEGIN .. END |
팁반복문으로 행을 하나씩 UPDATE 하기 전에 집합 SQL 한 문장으로 되는지 봅니다. 예제 1 의 UPDATE 한 문장은 3행을 한 번에 처리합니다. 반복문은 행마다 오류를 나눠 처리해야 할 때 정도에 씁니다.
인상률에 -150 을 넣으면 개발팀 급여가 음수가 되어 CHECK 위반이 납니다. 프로시저 흐름은 로그 INSERT 가 먼저 성공하고 UPDATE 가 실패하는 순서입니다. 소스는 이 상황을 평문 SQL 로 재현합니다.
SET AUTOCOMMIT OFF;
INSERT INTO sal_log SELECT id, sal, sal + sal * -150 / 100 FROM emp WHERE dept = '개발';
SELECT COUNT(*) AS log_before_error FROM sal_log;LOG_BEFORE_ERROR
----------------
3
(1행)로그가 3건 들어 있는 상태에서 UPDATE 가 실패합니다.
-- @error
UPDATE emp SET sal = sal + sal * -150 / 100 WHERE dept = '개발';예상 오류: Check constraint violation: "CK_EMP_SAL: "실패한 UPDATE 는 아무 행도 바꾸지 않지만 앞의 로그 INSERT 는 남아 있습니다. 트랜잭션이 열려 있기 때문입니다. 오류를 받은 쪽이 ROLLBACK 해야 정리됩니다.
ROLLBACK;
SELECT
id
, name
, sal
FROM emp
ORDER BY id;ID | NAME | SAL
---+--------+----
1 | 김대표 | 900
2 | 이개발 | 500
3 | 박개발 | 450
4 | 최개발 | 380
5 | 정영업 | 420
6 | 한영업 | 350
7 | 오영업 | 300
8 | 윤인사 | 400
(8행)급여는 그대로입니다. 로그도 ROLLBACK 뒤에 0건이 됩니다(소스에서 조회). 프로시저가 오류를 삼키고 정상 종료하면 호출자는 실패를 모른 채 COMMIT 할 수 있어서, 예외 처리는 보통 다시 던지는 것으로 끝냅니다.
세 DB 모두 사용자 정의 오류를 만드는 문장과 오류를 잡는 구문이 있습니다.
| 동작 | Oracle · Tibero | MySQL | MSSQL |
|---|---|---|---|
| 오류 발생 | RAISE_APPLICATION_ERROR | SIGNAL SQLSTATE '45000' | THROW (이전은 RAISERROR) |
| 잡기 | EXCEPTION WHEN .. THEN | DECLARE .. HANDLER | TRY .. CATCH |
Oracle 은 인상률 범위를 검사해 오류를 일으키고, EXCEPTION 블록에서 다시 던집니다.
-- Oracle · Tibero
CREATE OR REPLACE PROCEDURE raise_sal_safe (
p_dept IN VARCHAR2, p_rate IN NUMBER, p_cnt OUT NUMBER
) IS
BEGIN
IF p_rate < -50 OR p_rate > 100 THEN
RAISE_APPLICATION_ERROR(-20001, '인상률 범위 오류');
END IF;
raise_sal(p_dept, p_rate, p_cnt);
EXCEPTION
WHEN OTHERS THEN
p_cnt := 0;
RAISE;
END raise_sal_safe;
/핸들러의 RAISE; 가 같은 오류를 다시 던집니다. 이 줄이 없으면 오류가 사라지고 호출자는 성공으로 압니다.
MySQL 은 핸들러를 DECLARE 로 만듭니다.
-- MySQL
DELIMITER //
CREATE PROCEDURE raise_sal_safe (
IN p_dept VARCHAR(10), IN p_rate INT, OUT p_cnt INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET p_cnt = 0;
RESIGNAL;
END;
IF p_rate < -50 OR p_rate > 100 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '인상률 범위 오류';
END IF;
CALL raise_sal(p_dept, p_rate, p_cnt);
END //
DELIMITER ;MSSQL 은 TRY...CATCH(2005 부터)를 쓰고, THROW 는 2012 부터입니다.
-- MSSQL
CREATE OR ALTER PROCEDURE raise_sal_safe
@p_dept VARCHAR(10), @p_rate INT, @p_cnt INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
IF @p_rate < -50 OR @p_rate > 100
THROW 50001, '인상률 범위 오류', 1;
EXEC raise_sal @p_dept, @p_rate, @p_cnt OUTPUT;
END TRY
BEGIN CATCH
SET @p_cnt = 0;
THROW;
END CATCH
END;CATCH 안의 인자 없는 THROW; 가 원래 오류를 다시 던집니다. 그 앞 문장은 세미콜론으로 끝나야 하므로 위처럼 씁니다.
주의예외 핸들러에서 오류를 삼키지 않습니다. 호출자가 실패를 알아야 ROLLBACK 할 수 있고, 프로시저 안에서 COMMIT 을 하면 호출자의 트랜잭션 경계와 어긋납니다.
get_grade(sal) 은 급여로 등급을 돌려줍니다. 500 이상 A, 400 이상 B, 나머지 C 입니다. 함수가 하는 판단은 중급 05 CASE 식과 같아서 H2 에서 CASE 로 실행해 근거를 얻습니다.
SELECT
name
, sal
, CASE
WHEN sal >= 500 THEN 'A'
WHEN sal >= 400 THEN 'B'
ELSE 'C'
END AS grade
FROM emp
ORDER BY id;NAME | SAL | GRADE
-------+-----+------
김대표 | 900 | A
이개발 | 500 | A
박개발 | 450 | B
최개발 | 380 | C
정영업 | 420 | B
한영업 | 350 | C
오영업 | 300 | C
윤인사 | 400 | B
(8행)함수로 정의하면 SELECT 는 get_grade(sal) 한 줄이 됩니다. Oracle 함수는 RETURN 타입을 선언합니다.
-- Oracle · Tibero
CREATE OR REPLACE FUNCTION get_grade (p_sal IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
IF p_sal >= 500 THEN
RETURN 'A';
ELSIF p_sal >= 400 THEN
RETURN 'B';
END IF;
RETURN 'C';
END get_grade;
/
SELECT name, sal, get_grade(sal) AS grade FROM emp;MySQL 은 RETURNS 를 쓰고, 바이너리 로그가 켜져 있으면 DETERMINISTIC, NO SQL, READS SQL DATA 중 하나가 있어야 정의됩니다. 이 함수는 같은 입력에 같은 결과이므로 DETERMINISTIC 을 씁니다.
-- MySQL
DELIMITER //
CREATE FUNCTION get_grade (p_sal INT)
RETURNS CHAR(1)
DETERMINISTIC
BEGIN
IF p_sal >= 500 THEN RETURN 'A'; END IF;
IF p_sal >= 400 THEN RETURN 'B'; END IF;
RETURN 'C';
END //
DELIMITER ;
SELECT name, sal, get_grade(sal) AS grade FROM emp;MSSQL 스칼라 함수는 호출할 때 스키마 이름(dbo.)을 붙여야 합니다.
-- MSSQL
CREATE OR ALTER FUNCTION dbo.get_grade (@sal INT)
RETURNS CHAR(1)
AS
BEGIN
RETURN CASE
WHEN @sal >= 500 THEN 'A'
WHEN @sal >= 400 THEN 'B'
ELSE 'C'
END;
END;
GO
SELECT name, sal, dbo.get_grade(sal) AS grade FROM emp;세 DB 의 SELECT 는 위 CASE 결과와 같은 8행을 낼 것으로 그린 도식입니다. 함수 호출 자체는 H2 미지원이라 실행 결과가 아니고, 판단 로직만 위 CASE 로 확인했습니다.
애플리케이션은 JDBC 의 CallableStatement 로 {call raise_sal(?, ?, ?)} 를 실행합니다. 출력 파라미터는 registerOutParameter 로 등록하고 실행 뒤 값을 읽습니다. 이 호출 형식은 세 DB 가 같습니다.
MyBatis 에서는 <select> 나 <update> 에 statementType="CALLABLE" 을 씁니다. 파라미터는 #{p_cnt, mode=OUT, jdbcType=INTEGER} 처럼 방향을 적습니다. 실무 프로젝트 "백엔드 04 MyBatis 다중 DB" 레슨의 databaseId 분기와 함께 쓰면 DB별 호출 문법 차이를 매퍼에서 흡수할 수 있습니다.
COMMIT 은 호출하는 서비스 메서드의 트랜잭션이 담당합니다. 프로시저가 안에서 확정해 버리면 서비스가 다른 작업과 묶어 롤백할 수 없습니다.