PL/SQL · 절차형 SQL 기초

1. 절차형 SQL이란

표준 SQL은 선언형이지만, 대부분의 DBMS는 절차 구조(변수·반복·조건·예외)를 제공합니다.

• Oracle: PL/SQL
• PostgreSQL: PL/pgSQL
• MS SQL Server: T-SQL
• MySQL: Stored Procedure 언어

2. PL/SQL 블록 구조

DECLARE
  v_count NUMBER := 0;
  v_name  VARCHAR2(50);
BEGIN
  SELECT name INTO v_name FROM emp WHERE emp_id = 1;
  DBMS_OUTPUT.PUT_LINE(v_name);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('사원 없음');
END;

블록은 4부분: DECLARE / BEGIN / EXCEPTION / END.

3. 변수와 대입

v_sal  NUMBER := 3000000;
v_rec  emp%ROWTYPE;        -- 테이블 행 전체를 담는 타입
v_name emp.name%TYPE;      -- 해당 컬럼 타입을 그대로

4. 제어문

IF / ELSIF

IF v_sal >= 5000000 THEN
  v_grade := 'A';
ELSIF v_sal >= 3000000 THEN
  v_grade := 'B';
ELSE
  v_grade := 'C';
END IF;

LOOP / FOR / WHILE

FOR i IN 1..10 LOOP
  DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

WHILE v_count < 5 LOOP
  v_count := v_count + 1;
END LOOP;

5. 커서

여러 행을 한 번에 담을 수 없을 때 행 단위로 순회.

DECLARE
  CURSOR c_emp IS SELECT emp_id, name FROM emp WHERE dept_id = 10;
  r c_emp%ROWTYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO r;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(r.name);
  END LOOP;
  CLOSE c_emp;
END;

암묵 커서 속성

• SQL%ROWCOUNT — 마지막 DML이 처리한 행 수.
• SQL%FOUND / SQL%NOTFOUND — 마지막 DML 성공 여부.

6. 예외 처리

EXCEPTION
  WHEN NO_DATA_FOUND THEN ...
  WHEN TOO_MANY_ROWS THEN ...
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);

• NO_DATA_FOUND — 조회 결과가 없을 때.
• TOO_MANY_ROWS — SELECT INTO 결과가 2행 이상일 때.
• OTHERS — 그 외 모든 예외. 무분별 사용은 비추천.

7. 저장 프로시저 / 함수 / 트리거

프로시저 (Procedure)

반환값 없음. IN/OUT 파라미터.

CREATE OR REPLACE PROCEDURE raise_salary(p_emp_id NUMBER, p_pct NUMBER) IS
BEGIN
  UPDATE emp SET salary = salary * (1 + p_pct / 100) WHERE emp_id = p_emp_id;
END;
/

함수 (Function)

반환값 있음. SELECT에서 사용 가능.

CREATE OR REPLACE FUNCTION annual_salary(p_emp_id NUMBER) RETURN NUMBER IS
  v NUMBER;
BEGIN
  SELECT salary * 12 INTO v FROM emp WHERE emp_id = p_emp_id;
  RETURN v;
END;
/

트리거 (Trigger)

테이블 이벤트(INSERT/UPDATE/DELETE)에 반응.

CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER UPDATE OF salary ON emp
FOR EACH ROW
BEGIN
  INSERT INTO emp_audit(emp_id, old_sal, new_sal)
  VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary);
END;
/

• :OLD, :NEW 는 UPDATE 전/후 행.
• BEFORE / AFTER, FOR EACH ROW vs 문장 트리거의 차이가 시험 포인트.

8. 동적 SQL

실행 시점에 SQL 문자열을 조립해 실행.

EXECUTE IMMEDIATE 'ALTER TABLE emp ADD (x NUMBER)';
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table INTO v_cnt;

9. 자주 출제되는 포인트

• PL/SQL 블록 4부분: DECLARE / BEGIN / EXCEPTION / END.
• %TYPE / %ROWTYPE 의 목적: 테이블 스키마 변경에 유연.
• NO_DATA_FOUND vs TOO_MANY_ROWS.
• 트리거의 :OLD / :NEW 와 타이밍(BEFORE/AFTER).
• 프로시저·함수의 차이: 반환값 유무와 SELECT 사용 가능 여부.

10. 요약 체크리스트

• PL/SQL 블록 4부분의 역할을 설명할 수 있다.
• 커서를 OPEN/FETCH/CLOSE 플로우로 순회할 수 있다.
• 대표 예외 3가지를 처리할 수 있다.
• 프로시저/함수/트리거를 구분해 작성할 수 있다.
• 동적 SQL(EXECUTE IMMEDIATE)을 사용할 수 있다.