절차형 SQL 작성

절차형 SQL은 왜 필요할까요

보통의 SQL은 '무엇을 원하는지'만 적는 선언형 언어입니다. '개발 부서 직원의 급여 합계를 보여 달라'고 적으면, 어떤 순서로 찾을지는 DBMS가 알아서 정합니다. 그런데 업무에는 '재고가 있으면 주문을 받고, 없으면 대기 목록에 넣고, 대기자가 10명을 넘으면 담당자에게 알린다'처럼 조건에 따라 갈라지고 되풀이되는 절차가 많습니다. SQL 한 문장으로는 이런 흐름을 담기 어렵습니다.

절차형 SQL은 SQL 문 사이에 변수, 조건문, 반복문, 예외 처리를 끼워 넣어 일의 순서를 적을 수 있게 한 것입니다. 일반 프로그래밍 언어처럼 흐름을 짜되, 그 안에서 SQL을 바로 쓸 수 있다는 점이 특징입니다. DBMS마다 이름과 문법이 조금씩 다른데, Oracle은 PL/SQL, SQL Server는 T-SQL(Transact-SQL)이라고 부르고, MySQL도 저장 프로그램(Stored Program)이라는 이름으로 비슷한 기능을 제공합니다. 이 정리에서는 PL/SQL 문법을 기준으로 설명합니다.

절차형 SQL로 만드는 대표적인 것은 세 가지입니다. 이름을 불러 실행하는 프로시저(Procedure), 값 하나를 돌려주는 사용자 정의 함수(User Defined Function), 데이터에 변화가 생기면 저절로 실행되는 트리거(Trigger)입니다. 셋 다 데이터베이스 안에 저장되어 서버에서 실행되므로, 애플리케이션이 SQL을 한 문장씩 여러 번 주고받는 것보다 네트워크 왕복이 줄어듭니다.

SQL 문법 ① 블록의 뼈대와 변수

PL/SQL 프로그램의 기본 단위는 블록(Block)입니다. 블록은 DECLARE로 시작하는 선언부, BEGIN으로 시작하는 실행부, EXCEPTION으로 시작하는 예외 처리부로 나뉘고 END;로 끝납니다. 선언부에는 쓸 변수와 커서를, 실행부에는 실제로 할 일을, 예외 처리부에는 오류가 났을 때 할 일을 적습니다. 선언부와 예외 처리부는 필요 없으면 빼도 되지만, BEGIN ~ END로 감싼 실행부는 꼭 있어야 합니다.

변수는 선언부에서 '이름 자료형' 순서로 만듭니다. v_total NUMBER := 0;처럼 쓰는데, 여기서 :=는 값을 넣는 대입 연산자입니다. 같은지 비교할 때 쓰는 =와 헷갈리지 않도록 주의하세요. 테이블의 열과 같은 자료형을 쓰고 싶으면 v_sal 직원.급여%TYPE;처럼 %TYPE을 붙이고, 한 행 전체를 담을 변수가 필요하면 %ROWTYPE을 씁니다.

실행부에서 조회 결과를 변수에 담을 때는 SELECT 급여 INTO v_sal FROM 직원 WHERE 사번 = 101;처럼 INTO를 씁니다. 이 방식은 결과가 정확히 한 행일 때만 쓸 수 있고, 행이 없거나 여러 개면 예외가 발생합니다. 값을 화면에 찍어 확인할 때는 DBMS_OUTPUT.PUT_LINE을 씁니다. 이름 없이 한 번 실행하고 끝나는 블록을 익명 블록이라 하고, 이름을 붙여 저장해 두는 프로시저·함수·트리거와 구별합니다.

SQL 문법 ② 조건문과 반복문

조건에 따라 갈라질 때는 IF 문을 씁니다. IF 조건 THEN 처리 ELSIF 조건 THEN 처리 ELSE 처리 END IF; 형태입니다. 'ELSE IF'가 아니라 'ELSIF'로 붙여 쓰고, 끝은 END IF;로 닫는다는 점을 철자까지 정확히 기억해 두세요. 갈래가 많을 때는 CASE 문으로 값이나 조건별 처리를 나열할 수도 있습니다.

반복은 세 가지 모양이 있습니다. 가장 기본인 LOOP ~ END LOOP는 스스로 멈추지 않으므로 안에 EXIT WHEN 조건;을 넣어 빠져나옵니다. WHILE 조건 LOOP ~ END LOOP는 조건이 참인 동안 되풀이합니다. FOR i IN 1..5 LOOP ~ END LOOP는 i를 1부터 5까지 1씩 늘리며 다섯 번 반복하고, IN REVERSE를 쓰면 5부터 1까지 거꾸로 셉니다. FOR 문의 카운터 변수는 선언부에 따로 선언하지 않아도 됩니다.

실행 결과를 묻는 문제는 대부분 반복과 조건이 섞인 짧은 블록입니다. 변수 값을 표로 적으며 한 바퀴씩 따라가면 실수가 줄어듭니다. 1부터 4까지 돌며 짝수일 때만 더하는 블록이라면 i=1(0), i=2(2), i=3(2), i=4(6)처럼 바퀴마다 값을 적고 마지막 값을 답으로 씁니다. MOD(a, b)는 a를 b로 나눈 나머지를 돌려주는 함수로, 짝수·홀수를 가릴 때 자주 등장합니다.

프로시저: 이름으로 불러 쓰는 작업 묶음

저장 프로시저는 자주 하는 작업을 이름 붙여 데이터베이스에 저장해 둔 것입니다. CREATE [OR REPLACE] PROCEDURE 이름(매개변수) IS 선언부 BEGIN 실행부 END; 형태로 만듭니다. OR REPLACE를 붙이면 같은 이름이 이미 있을 때 새 내용으로 바꿉니다. 실행할 때는 EXECUTE 이름(값); 또는 줄여서 EXEC을 쓰고, DBMS에 따라 CALL을 쓰기도 하며, 다른 블록 안에서는 이름만 적어 호출합니다.

매개변수에는 값이 흐르는 방향을 정하는 모드가 있습니다. IN은 호출한 쪽이 값을 넘겨주는 입력용으로, 모드를 생략하면 IN이 됩니다. OUT은 프로시저가 결과를 담아 호출한 쪽에 돌려주는 출력용입니다. IN OUT은 값을 받아 고친 뒤 다시 돌려주는 양방향용입니다. 프로시저는 함수처럼 RETURN으로 값을 돌려주지 않으므로, 결과를 알려 주고 싶으면 OUT 매개변수를 씁니다.

프로시저 안에서는 INSERT, UPDATE, DELETE는 물론 COMMIT, ROLLBACK 같은 트랜잭션 제어도 할 수 있습니다. 그래서 '주문 저장 → 재고 감소 → 포인트 적립'처럼 하나로 처리돼야 하는 작업을 묶어 두고, 중간에 실패하면 전부 되돌리게 만드는 데 많이 씁니다.

사용자 정의 함수: 값 하나를 돌려주는 계산기

사용자 정의 함수는 DBMS가 기본으로 주는 SUM, ROUND 같은 함수처럼 쓰되, 사용자가 직접 만든 함수입니다. CREATE [OR REPLACE] FUNCTION 이름(매개변수) RETURN 자료형 IS ... BEGIN ... RETURN 값; END; 형태로 만듭니다. 머리 부분의 RETURN 뒤에는 돌려줄 자료형을 적고, 본문의 RETURN 문으로 실제 값을 돌려줍니다.

함수의 가장 큰 특징은 값 하나를 반드시 돌려준다는 점, 그래서 SQL 문 안에서 식처럼 쓸 수 있다는 점입니다. SELECT 이름, 세후급여(급여) FROM 직원;처럼 열 목록에 넣거나 WHERE 세후급여(급여) > 300처럼 조건 안에 넣을 수 있습니다. 프로시저는 이렇게 SQL 문 안에서 부를 수 없고 따로 실행해야 합니다.

SQL 문 안에서 불리는 함수는 그 SQL이 데이터를 읽는 도중에 실행되므로, 함수 안에서 테이블을 고치는 INSERT·UPDATE·DELETE나 COMMIT을 쓰면 오류가 나는 경우가 많습니다. 함수는 계산하고 조회해서 값을 돌려주는 일에, 데이터 변경은 프로시저에 나눠 맡기는 것이 기본입니다.

트리거와 이벤트: 일이 생기면 저절로 실행되기

트리거는 특정 테이블에 INSERT, UPDATE, DELETE 같은 사건(이벤트)이 일어나면 DBMS가 자동으로 실행하는 절차형 SQL입니다. 아무도 부르지 않아도 방아쇠가 당겨지듯 실행된다고 해서 이런 이름이 붙었습니다. 주문이 들어오면 재고를 줄이거나, 급여가 바뀌면 변경 이력을 남기는 일처럼 데이터가 바뀔 때마다 늘 따라와야 하는 작업에 씁니다.

형태는 CREATE [OR REPLACE] TRIGGER 이름 BEFORE 또는 AFTER INSERT OR UPDATE OR DELETE ON 테이블 [FOR EACH ROW] [WHEN 조건] BEGIN ... END; 입니다. BEFORE는 변경이 반영되기 전에, AFTER는 반영된 뒤에 실행합니다. FOR EACH ROW를 붙이면 영향받은 행마다 한 번씩 실행되는 행 트리거가 되고, 빼면 SQL 문 하나에 한 번만 실행되는 문장 트리거가 됩니다. 행 트리거 안에서는 :NEW로 바뀐 뒤의 값을, :OLD로 바뀌기 전의 값을 읽습니다. INSERT에서는 :OLD가, DELETE에서는 :NEW가 비어 있습니다(NULL).

트리거는 데이터를 바꾼 SQL 문의 트랜잭션 안에서 함께 실행되므로, 트리거 안에서 COMMIT이나 ROLLBACK을 직접 쓰는 것은 기본적으로 허용되지 않습니다. 또 눈에 보이지 않게 실행되기 때문에 너무 많이 걸어 두면 '이 값이 왜 바뀌었는지' 추적하기 어려워지고, 트리거가 다른 트리거를 연달아 일으키면 성능도 떨어집니다.

이벤트라는 말은 두 가지로 쓰입니다. 하나는 방금 본 것처럼 트리거를 일으키는 사건, 즉 INSERT·UPDATE·DELETE 같은 데이터 변경입니다. DBMS에 따라 테이블 생성·삭제 같은 DDL이나 로그인 같은 시스템 사건에 반응하는 트리거도 만들 수 있습니다. 다른 하나는 MySQL의 이벤트(Event)처럼 정해진 시각이나 주기에 맞춰 SQL을 자동으로 실행하는 예약 작업입니다. 트리거가 '어떤 일이 생기면' 실행된다면, 예약 이벤트는 '정해진 때가 되면' 실행된다는 점이 다릅니다.

커서와 예외 처리

SELECT ... INTO는 결과가 한 행일 때만 쓸 수 있습니다. 여러 행을 하나씩 꺼내 처리하려면 커서(Cursor)가 필요합니다. 커서는 조회 결과 위를 한 줄씩 짚어 가는 손가락이라고 생각하면 됩니다. 명시적 커서는 선언부에서 CURSOR 이름 IS SELECT ...;로 선언하고, OPEN으로 조회를 실행해 결과를 준비하고, FETCH로 한 행씩 꺼내 변수에 담고, 다 쓰면 CLOSE로 닫습니다. 더 꺼낼 행이 없는지는 커서이름%NOTFOUND로 확인해 반복을 끝냅니다.

개발자가 선언하지 않아도 DBMS가 SQL 문을 실행할 때마다 내부에서 쓰는 커서를 암시적 커서라고 합니다. 바로 앞에 실행한 SQL의 결과는 SQL%ROWCOUNT(영향받은 행 수), SQL%FOUND(한 행이라도 처리됐는지) 같은 속성으로 확인합니다. UPDATE 뒤에 SQL%ROWCOUNT가 0이라면 조건에 맞는 행이 하나도 없었다는 뜻입니다.

실행 중 오류가 나면 실행부를 멈추고 EXCEPTION 부분으로 넘어갑니다. WHEN 예외이름 THEN 처리; 형태로 적는데, SELECT INTO의 결과가 없을 때의 NO_DATA_FOUND, 여러 행일 때의 TOO_MANY_ROWS, 0으로 나눴을 때의 ZERO_DIVIDE, 기본키 같은 유일 제약을 어겼을 때의 DUP_VAL_ON_INDEX가 자주 쓰입니다. 앞에서 다루지 못한 나머지 오류는 마지막에 WHEN OTHERS로 한꺼번에 받습니다.

업무 규칙 위반처럼 DBMS가 오류로 보지 않는 상황도 직접 예외로 만들 수 있습니다. 예외 이름을 선언하고 RAISE로 일으키거나, RAISE_APPLICATION_ERROR로 사용자 정의 오류 번호(-20000 ~ -20999)와 메시지를 호출한 쪽에 돌려줍니다. 예외 처리부에서 ROLLBACK으로 작업을 되돌리고 기록을 남겨 두면, 중간에 실패해도 데이터가 어중간한 상태로 남지 않습니다.