Static·Dynamic SQL과 선택적 검색조건: NULL 의미·Bind·SQL Shape·보안

핵심 요약

Static SQL과 Dynamic SQL은 SQL 구조가 언제 결정되는가를 구분하는 개념입니다. Literal과 Bind Variable은 값을 SQL Text에 직접 넣는가, 별도로 전달하는가를 구분하는 개념이므로 서로 다른 축입니다.

Static SQL + Bind Variable     가능
Static SQL + Literal           가능
Dynamic SQL + Bind Variable    가능하고 권장되는 형태
Dynamic SQL + Literal 연결     보안·공유성 위험이 큼

선택적 검색조건은 하나의 SQL로 모든 조건 조합을 처리할지, 조건 조합별 SQL 구조를 만들지 결정하는 문제입니다. 정확한 NULL 의미를 지키면서 Access Path, Cursor 수, Hard Parse, SQL Injection 위험을 함께 판단해야 합니다.

구조가 고정됨
→ Static SQL 우선

구조가 Runtime에 달라짐
→ 제한된 Dynamic SQL Shape

데이터 값
→ Bind Variable

Table·Column·정렬 방향·Operator
→ Allowlist로 선택한 SQL 구조

이 이론의 범위

SQLP SQL 고급 활용 및 튜닝 → 고급 SQL 활용 → Static·Dynamic SQL과 선택적 검색조건 범위에서 NULL 의미, OR·NVL·UNION ALL, Native Dynamic SQL, DBMS_SQL, Bind·Cursor 공유·보안·권한을 다룹니다.

학습 목표

이 이론을 학습한 뒤에는 다음을 설명할 수 있어야 합니다.

1. Static SQL과 Dynamic SQL의 차이를 Literal·Bind 구분과 분리한다.
2. SQL 구조가 고정된 경우 Static SQL을 우선 선택하는 이유를 설명한다.
3. Dynamic SQL에서 값과 식별자를 서로 다른 방식으로 처리한다.
4. (:p IS NULL OR column = :p)와 column = NVL(:p, column)의 NULL 결과 차이를 설명한다.
5. UNION ALL 분기와 Dynamic SQL이 선택적 조건의 Access Path를 개선하는 원리를 이해한다.
6. 여러 선택적 조건으로 SQL Shape가 증가할 때 Cursor 공유성을 관리한다.
7. EXECUTE IMMEDIATE, OPEN FOR, DBMS_SQL의 대표 사용 상황을 구분한다.
8. 일반 Dynamic SQL과 Dynamic PL/SQL Block의 반복 Placeholder Bind 규칙을 구분한다.
9. 단일행·다중행 Dynamic SELECT에 맞는 EXECUTE IMMEDIATE·BULK COLLECT·OPEN FOR를 선택한다.
10. DBMS_ASSERT·AUTHID·NLS 변환·Bind 정보 노출의 보안 범위를 설명한다.
11. 조건 조합별 실제 실행계획과 성능을 검증하는 순서를 적용한다.

1. Static SQL과 Dynamic SQL

1.1 Static SQL

Static SQL은 PL/SQL Unit을 Compile할 때 SQL 구조가 정해져 있습니다.

CREATE OR REPLACE PROCEDURE find_orders (
    p_customer_id IN orders.customer_id%TYPE,
    p_from_date   IN DATE,
    p_to_date     IN DATE,
    p_rc          OUT SYS_REFCURSOR
) IS
BEGIN
    OPEN p_rc FOR
        SELECT order_id,
               customer_id,
               order_date,
               amount
        FROM   orders
        WHERE  customer_id = p_customer_id
        AND    order_date >= p_from_date
        AND    order_date <  p_to_date;
END;
/

Table, Column, Predicate 구조가 Compile 시점에 정해져 있으므로 다음 이점이 있습니다.

• 의존 Object와 권한 문제를 일찍 발견하기 쉬움
• Column Type을 Compile 시점에 확인하기 쉬움
• SQL Text가 안정적이라 Cursor 공유 관리가 단순함
• 소스 분석과 변경 영향 추적이 쉬움

1.2 Dynamic SQL

Dynamic SQL은 Runtime에 SQL Text를 만들거나 선택합니다.

l_sql := 'SELECT order_id, amount
          FROM   orders
          WHERE  order_date >= :b_from
          AND    order_date <  :b_to';

OPEN p_rc FOR l_sql USING p_from_date, p_to_date;

다음과 같이 SQL 구조 자체가 Runtime에 달라질 때 사용합니다.

• 선택되는 Predicate가 달라짐
• 조회 Table이나 Column이 달라짐
• 정렬 Column이 달라짐
• DDL을 실행함
• Compile 시점에 Select List의 수와 Type을 알 수 없음

Dynamic SQL은 “값을 문자열로 연결하는 SQL”을 의미하지 않습니다. SQL 구조만 동적으로 만들고 사용자 값은 Bind Variable로 전달할 수 있습니다.

Oracle은 SQL Text를 Compile 시점에 알 수 없거나 Static SQL이 지원하지 않는 문장을 실행할 때 Dynamic SQL을 사용하도록 설명합니다. Dynamic SQL이 필요하지 않다면 Static SQL이 다음 면에서 유리합니다.

• Compile 시점 Object·문법·권한 오류 조기 발견
• Schema Dependency 생성
• Type 확인과 변경 영향 추적
• 안정적인 SQL Text와 Cursor 공유

Dynamic SQL은 Runtime 유연성을 얻는 대신 Compile 시점 검증 일부를 Runtime으로 이동시킵니다.

2. SQL 구조와 값은 분리해서 생각한다

다음은 Dynamic SQL이지만 값을 문자열로 연결한 위험한 형태입니다.

l_sql := 'SELECT *
          FROM orders
          WHERE customer_id = ' || p_customer_id;

문자열 값이라면 Quote 처리, 날짜라면 NLS Format, 숫자라면 변환 문제가 추가됩니다. 사용자 입력이 SQL 구조로 해석되면 SQL Injection도 발생할 수 있습니다.

값은 Placeholder로 남기고 Bind로 전달합니다.

l_sql := 'SELECT order_id, amount
          FROM   orders
          WHERE  customer_id = :b_customer_id';

OPEN p_rc FOR l_sql USING p_customer_id;

Bind Variable의 값은 SQL Text의 문법 요소로 해석되지 않으므로 보안과 Cursor 공유에 유리합니다.

Bind가 대신할 수 있는 것
→ 숫자·문자·날짜·LOB 등 데이터 값

Bind가 대신할 수 없는 것
→ SQL Text 자체
→ Table·Column·Schema 이름
→ ASC·DESC
→ Operator·Keyword
→ DDL의 Object 구조

Bind 사용은 SQL Injection 위험을 크게 줄이지만 Bind 값 자체가 비밀 저장소가 되는 것은 아닙니다. 권한이 있는 사용자는 Trace·Audit·V$SQL_BIND_CAPTURE 등의 진단 정보에서 일부 Bind Metadata나 과거 값을 볼 수 있으므로 민감 정보 조회 권한도 관리합니다.

Bind할 수 없는 항목

Table 이름, Column 이름, 정렬 방향, 연산자 같은 SQL 구조는 값 Bind로 바꿀 수 없습니다.

-- Column 이름을 값 Bind로 대체하는 의미가 되지 않음
ORDER BY :sort_column

:sort_column은 모든 행에 같은 값 하나를 제공할 뿐 실제 Column 이름으로 해석되지 않습니다. 구조 항목은 허용 목록으로 제한합니다.

l_order_by := CASE p_sort_key
                WHEN 'DATE'   THEN 'order_date DESC, order_id DESC'
                WHEN 'AMOUNT' THEN 'amount DESC, order_id DESC'
                ELSE 'order_id DESC'
              END;

입력 문자열을 그대로 붙이지 않고 애플리케이션이 허용한 값에서 안전한 SQL 조각을 선택합니다. Object 이름을 외부에서 받아야 하는 제한적인 상황에서는 허용 목록과 함께 DBMS_ASSERT 같은 검증 도구를 검토합니다.

대표 함수입니다.

DBMS_ASSERT는 입력이 Identifier 형식·Object 존재 조건을 만족하는지 검증하지만, 그 Object를 사용자가 업무상 선택해도 되는지 또는 실행 권한이 있는지를 대신 판단하지 않습니다.

Allowlist
→ 업무적으로 허용한 Table·Column·정렬 방식

DBMS_ASSERT
→ Identifier 형식·존재 검증

권한 모델
→ 실제 실행 권한 확인

3. 선택적 검색조건의 업무 의미부터 확정한다

검색 화면에서 고객번호가 입력되면 해당 고객만 조회하고, 입력되지 않으면 모든 고객을 조회한다고 가정합니다.

업무 규칙은 다음과 같습니다.

:p_customer_id가 값 있음
→ CUSTOMER_ID가 같은 행만 반환

:p_customer_id가 NULL
→ CUSTOMER_ID가 NULL인 행을 포함해 날짜 조건을 만족하는 모든 행 반환

이 의미를 먼저 정한 뒤 SQL 패턴을 비교합니다.

4. OR 선택조건

SELECT order_id,
       customer_id,
       order_date,
       amount
FROM   orders
WHERE  (:p_customer_id IS NULL
        OR customer_id = :p_customer_id)
AND    order_date >= :p_from_date
AND    order_date <  :p_to_date;

결과 의미

• Bind에 값이 있으면 customer_id = :p_customer_id가 적용됩니다.
• Bind가 NULL이면 첫 조건이 TRUE가 되어 CUSTOMER_ID가 NULL인 행도 포함합니다.

성능 특성

하나의 SQL Shape가 NULL 입력과 값 입력을 모두 처리합니다. 각 입력의 최적 Access Path가 크게 다르면 하나의 실행계획이 모든 상황에서 효율적이지 않을 수 있습니다.

값 있음  → 고객 Index로 소량 조회가 유리할 수 있음
값 없음  → 날짜 범위 Scan이나 Full Scan이 유리할 수 있음

Optimizer가 OR Expansion 같은 변환을 선택할 수 있지만 모든 SQL·통계·Version에서 같은 형태를 보장하지 않습니다. 실제 Child Cursor와 Predicate 위치를 확인합니다.

5. NVL 선택조건과 NULL 결과

WHERE customer_id = NVL(:p_customer_id, customer_id)

Bind가 NULL이면 조건은 논리적으로 다음과 비슷해집니다.

customer_id = customer_id

CUSTOMER_ID가 NULL인 행에서는 NULL = NULL의 결과가 UNKNOWN이므로 행이 제외됩니다.

따라서 NVL 패턴은 “입력이 없으면 NULL 행까지 포함한 모든 행”이라는 업무 규칙과 결과가 다릅니다.

Column에 함수를 적용하는 다음 형태도 주의합니다.

WHERE NVL(customer_id, -1) = NVL(:p_customer_id, -1)

• 일반 Index의 Search Key를 직접 사용하기 어려울 수 있음
• -1이 실제 데이터와 충돌할 수 있음
• Bind가 NULL일 때 “모든 행”이 아니라 “Column도 NULL인 행”을 찾는 의미가 됨

문장을 짧게 만드는 목적보다 정확한 NULL 의미를 먼저 확인합니다.

COALESCE(:p_customer_id, customer_id)도 이 목적에서는 NVL과 같은 NULL Column 제외 문제가 발생할 수 있습니다.

customer_id = COALESCE(:p_customer_id, customer_id)

Bind가 NULL이고 Column도 NULL이면 NULL = NULL이므로 UNKNOWN입니다. 함수 이름이 달라도 업무 의미를 먼저 검증합니다.

6. UNION ALL 분기

Bind가 NULL인 경우와 값이 있는 경우를 서로 배타적인 Branch로 분리할 수 있습니다.

SELECT order_id,
       customer_id,
       order_date,
       amount
FROM   orders
WHERE  :p_customer_id IS NULL
AND    order_date >= :p_from_date
AND    order_date <  :p_to_date

UNION ALL

SELECT order_id,
       customer_id,
       order_date,
       amount
FROM   orders
WHERE  :p_customer_id IS NOT NULL
AND    customer_id = :p_customer_id
AND    order_date >= :p_from_date
AND    order_date <  :p_to_date;

두 Branch는 Bind 상태에 따라 동시에 결과를 만들지 않으므로 UNION ALL을 사용할 수 있습니다.

NULL 입력  → 첫 Branch만 결과 생성
값 입력    → 둘째 Branch만 결과 생성

각 Branch에서 서로 다른 Access Path를 선택할 여지가 생깁니다.

• NULL Branch: 날짜 Index 또는 Full Scan
• 값 Branch: (customer_id, order_date) Index

실행계획에 두 Branch가 모두 보이더라도 실행 시 한 Branch의 하위 Operation이 시작되지 않을 수 있습니다. ALLSTATS LAST의 Starts를 확인해 Branch Pruning 여부를 검증합니다.

장점과 비용

선택조건이 5개라면 모든 조합을 분기로 만들 때 최대 32개 Branch가 필요할 수 있습니다. 이 경우 Dynamic SQL이 더 단순한 구조가 될 수 있습니다.

Branch가 서로 배타적이지 않으면 같은 Row가 여러 Branch에서 반환되어 UNION ALL 중복이 발생합니다.

안전한 배타 조건
:p IS NULL
:p IS NOT NULL

위험한 겹침 조건
:p IS NULL OR column = :p
column = :p

Projection의 Column 수·순서·Type도 모든 Branch에서 호환돼야 합니다.

7. 선택적 Predicate를 Dynamic SQL로 구성하기

고객번호가 입력될 때만 해당 Predicate를 추가하는 예제입니다.

CREATE OR REPLACE PROCEDURE search_orders (
    p_customer_id IN orders.customer_id%TYPE,
    p_from_date   IN DATE,
    p_to_date     IN DATE,
    p_rc          OUT SYS_REFCURSOR
) IS
    l_sql VARCHAR2(32767);
BEGIN
    l_sql := 'SELECT order_id,
                     customer_id,
                     order_date,
                     amount
              FROM   orders
              WHERE  order_date >= :b_from
              AND    order_date <  :b_to';

    IF p_customer_id IS NULL THEN
        OPEN p_rc FOR l_sql
        USING p_from_date, p_to_date;
    ELSE
        l_sql := l_sql || ' AND customer_id = :b_customer_id';

        OPEN p_rc FOR l_sql
        USING p_from_date, p_to_date, p_customer_id;
    END IF;
END;
/

입력 조합에 따라 생성되는 SQL Shape는 다음 두 개입니다.

날짜 조건만 있는 SQL
날짜 조건 + 고객 조건이 있는 SQL

각 Shape는 자신의 조건에 적합한 실행계획을 가질 수 있습니다.

SQL Shape 관리 원칙

Dynamic SQL을 사용해도 SQL Text를 무제한으로 만들면 Shared Pool에 많은 Parent Cursor가 생길 수 있습니다.

• Predicate를 항상 같은 순서로 조립
• 같은 기능의 공백·대소문자·주석을 일관되게 유지
• 값은 Bind로 전달
• 사용 빈도가 낮은 조합을 과도하게 세분화하지 않음
• 대표 조건 조합과 Data Skew별 성능을 측정

Dynamic SQL은 Hard Parse를 없애는 기능이 아니라, 업무상 필요한 몇 개의 안정적인 SQL Shape를 설계하는 방법입니다.

Repeated Placeholder의 Bind 규칙

일반 SQL·DML Dynamic Statement에서는 Placeholder 이름보다 등장 위치가 중요합니다.

l_sql := 'INSERT INTO t VALUES (:x, :x, :y)';
EXECUTE IMMEDIATE l_sql USING a, a, b;

:x가 두 번 나타나므로 같은 값을 전달하려면 a도 두 번 적습니다.

반면 Dynamic Anonymous PL/SQL Block 또는 CALL Statement에서는 반복 Placeholder 이름이 중요합니다.

l_block := 'BEGIN p(:x, :x, :y); END;';
EXECUTE IMMEDIATE l_block USING a, b;

고유 이름 :x, :y별로 Bind하며 반복된 :x는 같은 Bind를 참조합니다.

SQL Text 종류에 따라 규칙이 다르므로 Placeholder 이름만 보고 USING 인수 수를 정하지 않습니다.

8. EXECUTE IMMEDIATE·OPEN FOR·DBMS_SQL

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE
    'UPDATE orders
     SET    status = :b_status
     WHERE  order_id = :b_order_id'
USING p_status, p_order_id;

단일행 SELECT는 INTO, 입력 Bind는 USING에 둡니다.

EXECUTE IMMEDIATE
    'SELECT amount
       FROM orders
      WHERE order_id = :b_order_id'
INTO l_amount
USING p_order_id;

다중행을 Collection으로 한 번에 받을 수 있으면 BULK COLLECT INTO를 사용할 수 있습니다.

EXECUTE IMMEDIATE
    'SELECT order_id
       FROM orders
      WHERE customer_id = :b_customer_id'
BULK COLLECT INTO l_order_ids
USING p_customer_id;

결과가 매우 크면 전체 Collection Memory와 OPEN FOR의 점진적 Fetch를 비교합니다.

OPEN FOR

OPEN p_rc FOR
    'SELECT order_id, amount
     FROM   orders
     WHERE  customer_id = :b_customer_id'
USING p_customer_id;

OPEN FOR는 Dynamic SELECT Result Set을 Cursor Variable에 연결합니다. 호출자는 FETCH를 반복하고 완료 후 CLOSE해야 합니다. Return Column 구조를 Compile 시점에 알고 있고 여러 Row를 Stream 형태로 처리할 때 적합합니다.

DBMS_SQL

사용자가 선택한 임의 Column 목록처럼 반환 구조를 Runtime에 기술해야 할 때 사용합니다. PARSE, BIND_VARIABLE, DESCRIBE_COLUMNS, DEFINE_COLUMN, EXECUTE, FETCH_ROWS, COLUMN_VALUE, CLOSE_CURSOR 같은 단계를 직접 관리합니다.

Select List 구조를 알고 있음
→ Native Dynamic SQL 우선

Select List Column 수·Type도 모름
→ DBMS_SQL 검토

단순 선택적 Predicate만 처리하는 경우에는 Native Dynamic SQL이 더 읽기 쉽고 구현이 간단하며 일반적으로 더 적합합니다. Exception 경로에서도 열린 DBMS_SQL Cursor를 닫도록 설계합니다.

9. 선택적 검색조건과 실행계획 안정성

같은 Predicate라도 입력값 분포에 따라 최적 Plan이 달라질 수 있습니다.

고객 A: 주문 2건
고객 B: 주문 2,000,000건

둘 다 customer_id = :p_customer_id이지만 고객 A에는 Index NL 방식이, 고객 B에는 넓은 Scan 방식이 유리할 수 있습니다.

다음 항목을 함께 확인합니다.

• SQL Shape와 SQL_ID
• Bind 입력 상태와 실제 값
• Child Cursor와 Plan Hash Value
• Predicate의 access·filter 위치
• E-Rows, A-Rows, Starts, Buffers
• Hard Parse·Parse Call 수
• 전체 Fetch 행 수
• 특정 값의 Data Skew와 Histogram

선택적 조건 문제는 SQL 문법만의 문제가 아니라 Cursor 공유, Bind Peeking, Adaptive Cursor Sharing, 통계정보와 연결됩니다.

첫 Hard Parse에서 Optimizer는 사용자 Bind를 Peeking해 Cardinality를 추정할 수 있습니다. 데이터가 편중되면 하나의 Plan이 모든 값에 적합하지 않을 수 있고, Adaptive Cursor Sharing은 실행 통계를 관찰해 Bind 범위에 따라 여러 Child Cursor와 Plan을 사용할 수 있습니다.

Parent Cursor
→ 동일 SQL Text

Child Cursor
→ Bind Metadata·Optimizer 환경·Plan 차이

Bind-Sensitive
→ 값에 따라 Selectivity 차이를 관찰

Bind-Aware
→ Bind 범위별 Plan 선택 가능

ACS가 모든 Optional Predicate 문제를 자동 해결한다고 가정하지 않고 IS_BIND_SENSITIVE, IS_BIND_AWARE, Child별 A-Rows·Buffers를 확인합니다.

10. 보안과 정확성 점검

값

사용자가 입력한 검색어·숫자·날짜는 Bind로 전달합니다.

구조

Table·Column·정렬 방향은 허용 목록에서 선택합니다.

날짜·숫자 변환

문자열 연결이 불가피한 특수 상황에서는 NLS 설정에 의존하지 않도록 명시적인 Format을 사용합니다. 가능하면 해당 값도 Bind로 전달합니다.

날짜·Timestamp·숫자를 문자열로 연결하면 NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_NUMERIC_CHARACTERS가 SQL Text에 영향을 줄 수 있으며, 조작된 NLS Format을 통한 Injection 가능성도 있습니다.

가장 안전한 우선순위
1. 값을 Bind로 전달
2. 불가피하면 명시적 Format + 검증
3. Session NLS에 의존한 묵시적 문자열 변환 금지

권한

Dynamic SQL은 실행 권한 모델과 Object 권한을 함께 검토해야 합니다. Stored Program의 AUTHID DEFINER·AUTHID CURRENT_USER에 따라 이름 해석과 권한 확인 범위가 달라집니다.

AUTHID DEFINER
→ 기본값
→ Definer의 Schema·권한 Domain 중심

AUTHID CURRENT_USER
→ Invoker의 현재 사용자·Schema·활성 Role 중심
→ Runtime 외부 이름과 권한 확인

Invoker Rights Unit은 호출자의 권한을 상속하므로 INHERIT PRIVILEGES 관련 권한도 운영 환경에서 확인합니다. Definer Rights에 Dynamic Object 이름을 결합하면 권한 상승 경로가 될 수 있으므로 Allowlist를 특히 엄격히 적용합니다.

11. 적용 판단 순서

1. 조건이 없을 때 NULL Column을 포함할지 업무 의미를 정한다.
2. SQL 구조가 실제로 Runtime에 달라지는지 확인한다.
3. 구조가 고정되면 Static SQL을 우선 검토한다.
4. OR·NVL·UNION ALL의 결과를 NULL 데이터로 비교한다.
5. 조건 조합별 필요한 Access Path를 정한다.
6. Dynamic SQL이면 값은 Bind, 구조는 허용 목록으로 처리한다.
7. 생성 가능한 SQL Shape 수를 관리한다.
8. Placeholder 반복 규칙과 USING 인수 위치를 확인한다.
9. 단일행·다중행 결과 처리 도구를 선택한다.
10. 대표 Bind와 Data Skew별 Parent·Child Cursor Plan을 확인한다.
11. Parse Calls·Hard Parse와 전체 실행 비용을 함께 측정한다.
12. SQL Injection·권한·NLS·Bind 정보 노출을 점검한다.

혼동하기 쉬운 판단