🌳 12편. 동적 SQL과 EXECUTE IMMEDIATE

🌳 12편. 동적 SQL과 EXECUTE IMMEDIATE

고정된 쿼리 대신 조건에 따라 SQL 문장을 동적으로 생성해야 하는 상황이 많습니다. 동적 SQL(Dynamic SQL)이란, 실행 시점에 SQL 문을 동적으로 구성하거나 실행하는 방식을 의미하며, 일반적인 정적 SQL(고정된 쿼리)과 달리 쿼리 내용을 문자열로 조합하거나 변수에 따라 문장을 바꾸는 것이 특징입니다.

사용하는 DBMS마다 방식은 조금씩 다르지만, 주요 방식은 다음과 같습니다:

✅ 1. EXECUTE IMMEDIATE: 대부분의 DBMS가 지원하는 대표적인 동적 SQL 실행 방식

✅ 2. PREPARE / EXECUTE: MySQL, PostgreSQL, SQL Server 등, 실행 계획을 먼저 준비한 뒤 실행하는 구조

✅ 3. DBMS_SQL 패키지 (Oracle 전용 고급 방식): 오라클에서 커서 조작, 바인딩, 실행을 세밀하게 제어할 수 있는 방식

✅ 4. 문자열 직접 조합 + 실행 (비추천)

✅ 5. 프로시저 내 IF/CASE문으로 동적 분기

✅ 6. ORM 또는 백엔드 코드에서의 동적 SQL

Oracle PL/SQL에서는 EXECUTE IMMEDIATE를 사용해 동적 SQL을 수행할 수 있습니다.
이번 글에서는 동적 SQL의 기본 개념, 바인딩 방식, DDL 실행, 실무 활용 예제를 다룹니다.

---

📌 동적 SQL이 필요한 상황

• 테이블 또는 컬럼 이름이 런타임에 결정될 때
• 조건이 다양하게 바뀌는 WHERE절 처리
• 동적으로 테이블 생성 / 수정 / 삭제 시

---

🧩 기본 사용법: EXECUTE IMMEDIATE

DECLARE
  v_sql VARCHAR2(1000);
BEGIN
  v_sql := 'UPDATE EMP SET SAL = SAL * 1.1 WHERE DEPTNO = 10';
  EXECUTE IMMEDIATE v_sql;
END;

→ 실행 시점에 문자열을 SQL로 인식하고 수행

---

🧷 바인드 변수 사용하기

값을 직접 SQL에 삽입하지 않고 변수를 바인딩할 수 있습니다.

DECLARE
  v_sql VARCHAR2(1000);
BEGIN
  v_sql := 'DELETE FROM EMP WHERE DEPTNO = :dept';
  EXECUTE IMMEDIATE v_sql USING 10;
END;

DECLARE
  v_sql VARCHAR2(1000);
  v_dept NUMBER := 20;
BEGIN
  v_sql := 'DELETE FROM EMP WHERE DEPTNO = :deptno';
  EXECUTE IMMEDIATE v_sql USING v_dept;
END;

• :변수명 자리에는 실제 값이 바인딩됨
• 바인딩을 사용하면 SQL Injection 방지 및 성능 유리

---

📦 DDL 실행: 테이블 동적 생성

BEGIN
  EXECUTE IMMEDIATE '
    CREATE TABLE TEMP_USER (
      ID NUMBER,
      NAME VARCHAR2(50)
    )
  ';
END;

→ DDL도 EXECUTE IMMEDIATE로 실행 가능 (단, 트랜잭션 처리 주의)

-- 1. 시작 전 확인
SELECT * FROM emp WHERE empno = 100; //없음

-- 2. 트랜잭션 테스트
BEGIN
  INSERT INTO emp VALUES (100, '홍길동'); //삽입
  EXECUTE IMMEDIATE 'CREATE TABLE temp_test (id NUMBER)';
  ROLLBACK;
END;

-- 3. 다시 확인
SELECT * FROM emp WHERE empno = 100; //롤백안됨

👉 결과: 홍길동은 그대로 있음 → INSERT는 롤백되지 않음 (DDL 문은 실행 전까지의 트랜젝션 자동커밋, 실행 직후에도 자동 커밋)

---

📥 INTO 절로 SELECT 결과 받기

DECLARE
  v_sql VARCHAR2(100);
  v_ename EMP.ENAME%TYPE;
  v_sal   EMP.SAL%TYPE;
BEGIN
  v_sql := 'SELECT ENAME, SAL FROM EMP WHERE EMPNO = :empno'; --컬럼 2개
  EXECUTE IMMEDIATE v_sql INTO v_ename, v_sal USING 10; --INTO 뒤에도 변수 2개 (개수 불일치 시 에러)
  DBMS_OUTPUT.PUT_LINE('이름: ' || v_ename || ', 급여: ' || v_sal);
END;

• %TYPE이란?: 기존 테이블(또는 변수)의 컬럼 타입을 그대로 따라오겠다는 뜻

📌 컬럼 개수와 변수 개수가 반드시 일치해야 함. 컬럼 2개인데 변수는 1개 → ORA-00947: not enough values 에러

---

🔁 반복적으로 테이블을 조회하는 동적 SQL

DECLARE
  v_table_name VARCHAR2(30) := 'EMP';
  v_sql        VARCHAR2(100);
  v_cnt        NUMBER;
BEGIN
  v_sql := 'SELECT COUNT(*) FROM ' || v_table_name;
  EXECUTE IMMEDIATE v_sql INTO v_cnt;
  DBMS_OUTPUT.PUT_LINE('행 개수: ' || v_cnt);
END;

→ 테이블명을 런타임에 지정 가능

---

⚠ 주의사항

• SQL 구문 오류는 실행 시점에만 발생하므로 테스트 필요
• SQL Injection 방지를 위해 반드시 USING을 통한 바인딩 사용
• DDL 실행 후 COMMIT을 명시하지 않으면 트랜잭션 영향 있을 수 있음

---

🧠 실무 팁

• 정말 필요할 때만 동적 SQL을 사용해야 합니다. 
  예: 동적으로 테이블명 변경, 컬럼 동적 조합, 파라미터 개수가 가변적인 상황 등
  
  • 동적SQL을 최소화해야해는 이유:
    - 정적 SQL과 달리 실행계획을 캐시하지 않아서 파싱비용, 캐시부하, 성능저하 
    - 정적 SQL은 파라미터 바인딩이 내장되어 있지만, 동적 SQL은 문자열 조합과정에 USING 없이 구현 시 SQL Injection
    - 디버깅 가독성 안좋아 예외 발생 시 실제 실행된 SQL을 확인하기 어렵고, 변수 조건 바인딩 누락 실수 쉬움
    - 정적SQL과 달리 권한통제 감사 어려움. 실행시점에 쿼리가 구성되니까 실제 수행된 쿼리 추적이 어려워 
    - 정적SQL은 프로시저 컴파일시점에 구문오류, 테이블 존재여부 체그 가능하지만 동적SQL은 실행시점에만 오류발생해서 사전 검증불가
    
    
• 실제 운영 환경에서는 동적 SQL 남용보다는 프로시저 구조 내 정적 SQL 위주 ( 정적 SQL + IF/CASE문 분기 구조로 프로시저 ) 권장
• 예외 처리 블록 EXCEPTION WHEN OTHERS와 함께 사용 추천

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE TEMP_USER';
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('DROP 실패: ' || SQLERRM);
END;

---

📝 마무리

이번 글에서는 Oracle에서 EXECUTE IMMEDIATE를 활용한 동적 SQL의 기본 구조와 다양한 예제를 살펴봤습니다.
다음 글에서는 커서(Cursor)와 BULK COLLECT를 통한 반복 처리와 성능 향상 기법을 소개합니다.