동적 검색 조건

2. 핵심 원리

2.1 선택 조건 패턴

WHERE (:p_dept IS NULL OR e.dept = :p_dept)
  AND (:p_status IS NULL OR e.status = :p_status)

값이 없으면 앞쪽 IS NULL 이 참이라 조건이 사라지고, 값이 있으면 뒤쪽 비교가 걸립니다. 조건이 몇 개든 같은 문장이라 화면 코드가 단순합니다. 이 레슨의 예제는 :p_dept 자리에 값을 넣기 어려우므로 파라미터 CTE WITH p AS (SELECT ... AS p_dept ...) 로 값을 대신합니다.

e.dept = COALESCE(:p_dept, e.dept) 로 줄여 쓰면 안 됩니다. 값이 없을 때 e.dept = e.dept 가 되는데, dept 가 NULL 인 행은 NULL = NULL 이라 참이 아니어서 빠집니다.

핵심

선택 조건은 (p IS NULL OR col = p) 로 씁니다. NVL(p, col)·COALESCE(p, col) 로 줄이면 col 이 NULL 인 행이 조건 없이도 빠집니다.

2.2 바인드 변수

바인드 변수를 쓰면 문장 텍스트가 같아서 한 번 파싱한 결과, 곧 실행 계획을 재사용합니다. Oracle 은 리터럴이 다르면 문장마다 하드 파싱을 하고, MSSQL 은 매개 변수화된 계획을 캐시하고, MySQL 은 prepared statement 로 같은 효과를 냅니다.

보안 이유가 더 큽니다. 값은 SQL 문법이 아니라 데이터로만 전달되므로 ' OR '1'='1 같은 입력이 조건을 바꾸지 못합니다. 이것이 SQL 인젝션 방어의 기본입니다.

2.3 하나의 문장 대 조건만 붙이는 동적 SQL

(:p IS NULL OR col = :p) 는 하나의 문장으로 값이 있는 경우와 없는 경우를 모두 처리해야 합니다. 그래서 옵티마이저가 어느 경우에도 맞는 계획을 골라야 하고, 인덱스를 못 타는 경우가 생깁니다. 조건이 많은 화면일수록 이 영향이 커집니다.

그래서 조건이 많은 화면은 값이 들어온 조건만 문장에 붙이는 동적 SQL 을 씁니다. 조합마다 문장이 달라지지만 각 문장은 필요한 조건만 갖고 있어 인덱스 선택이 자연스럽습니다. MSSQL 은 단일 문장을 유지한 채 OPTION (RECOMPILE) 힌트로 실행 때마다 계획을 다시 세우기도 합니다.

2.4 IN 목록과 정렬

IN 목록은 값 개수가 요청마다 달라서 ? 자리 수가 변합니다. Oracle 은 IN 에 리터럴 1000개를 넘기면 ORA-01795 입니다. 정렬 컬럼이나 테이블명 같은 식별자는 바인드 변수로 넘길 수 없어 화이트리스트로 검사한 뒤 문장에 넣거나, CASE 로 고릅니다.

빈 입력은 DB 마다 다릅니다. Oracle 은 '' 를 NULL 로 저장해 p IS NULL 검사에 같이 걸리지만, MySQL·MSSQL 은 '' 와 NULL 이 다릅니다. 화면에서 빈 입력을 NULL 로 바꿔 보내거나, 조건에서 p <> '' 까지 검사합니다.

주의

빈 문자열 '' 는 Oracle 에서는 NULL 이고 MySQL·MSSQL 에서는 NULL 이 아닙니다. 같은 검색 코드가 DB 를 바꾸면 다르게 동작할 수 있습니다.