PL/SQL · 저장 프로시저 · 트리거 · 커서
저장 프로시저 + 트리거(BEFORE/AFTER + INSERT/UPDATE/DELETE) + 커서(암시/명시).
실기 2번 유형(SQL 작성) 심화. 저장 프로시저 CREATE 구문, 트리거 6조합(BEFORE·AFTER × I·U·D), 커서 4단계(OPEN·FETCH·CLOSE·DEALLOCATE)가 빈칸 채우기로 매회 출제. 2023 이후 ROW vs STATEMENT 레벨 트리거 차이 신규.
PL/SQL은 Oracle의 SQL 확장 언어. 변수·제어문·예외 처리·모듈화 기능을 제공해 저장 프로시저·트리거·사용자 정의 함수를 작성한다. T-SQL(MS SQL Server)·PL/pgSQL(PostgreSQL)도 유사 구조.
-- 저장 프로시저 예시
CREATE OR REPLACE PROCEDURE give_raise (
emp_id IN NUMBER,
pct IN NUMBER
) IS
BEGIN
UPDATE employees
SET salary = salary * (1 + pct/100)
WHERE id = emp_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN ROLLBACK;
END;
/
-- 호출
EXEC give_raise(100, 10);IN 매개변수 2개 + UPDATE + 예외 처리. EXEC로 실행. 함수는 값을 반환하고, 프로시저는 작업만 수행.
트리거 6조합 — (BEFORE / AFTER) × (INSERT / UPDATE / DELETE). `FOR EACH ROW` 추가 시 ROW 레벨 트리거(행마다 실행), 없으면 STATEMENT 레벨(쿼리당 1회). INSTEAD OF 트리거는 뷰에만 쓴다.
-- 트리거 예시 — 급여 인상 로그 기록
CREATE OR REPLACE TRIGGER trg_salary_log
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > OLD.salary)
BEGIN
INSERT INTO salary_log(emp_id, old_salary, new_salary, changed_at)
VALUES (:NEW.id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/`:NEW`=변경 후, `:OLD`=변경 전. UPDATE 시 급여가 오른 경우만 로그 기록. `AFTER`라서 UPDATE 완료 후 실행.
| 커서 종류 | 설명 | 명령 순서 |
|---|---|---|
| 암시적 (Implicit) | SQL문마다 자동 생성 | (자동) |
| 명시적 (Explicit) | DECLARE로 선언 | DECLARE → OPEN → FETCH → CLOSE |
| REF 커서 | 변수로 전달 가능 | TYPE 선언 후 OPEN FOR |
커서 속성 4종 — `%FOUND`(행 있음), `%NOTFOUND`(없음), `%ROWCOUNT`(누적 행 수), `%ISOPEN`(열림 여부). FETCH 후 루프 종료 조건에 `%NOTFOUND`를 쓴다.
행마다 실행되는 트리거와 쿼리당 1회 실행되는 트리거를 구분하는 키워드는?
명시적 커서의 4단계 명령 순서는 DECLARE → ( ㉠ ) → ( ㉡ ) → CLOSE이다.
아래 트리거 본문에서 `:NEW.salary`가 의미하는 것은? ```sql CREATE TRIGGER t AFTER UPDATE ON emp FOR EACH ROW BEGIN -- ... :NEW.salary ... END; ```
저장 프로시저와 사용자 정의 함수의 차이는, 함수는 값을 반환하지만 프로시저는 작업만 수행하는 것이다.