단일행 함수와 집계 함수, NULL 처리

함수는 2과목에서 문항이 가장 많이 나오면서 3과목의 튜닝 문항으로도 그대로 이어집니다. 이름과 인자를 외우는 것으로는 절반밖에 못 풉니다. 나머지 절반은 그 함수가 NULL을 어떻게 다루는가와 그 함수를 조건절 어느 쪽에 적었는가에서 갈립니다. 앞 노트에서 본 실행 순서 위에 이 두 가지를 얹으면 함수 문항은 거의 계산 문제가 됩니다.

단일행 함수

단일행 함수는 행 하나를 받아 값 하나를 내놓는 함수입니다. 100행에 적용하면 결과도 100행이라 행 수가 변하지 않습니다. 뒤에 나올 집계 함수가 여러 행을 받아 한 값을 내놓는 것과 여기서 갈립니다.

문자 함수

SUBSTR의 시작 위치는 1부터 세고, 음수를 주면 뒤에서부터 셉니다 — SUBSTR('20260915', -4)가 0915입니다.

숫자 함수

ROUND는 반올림, TRUNC는 버림입니다. 둘 다 둘째 인자로 자릿수를 받고, 이 인자는 음수일 수도 있습니다.

• ROUND(3867.365, 2) → 3867.37, TRUNC(3867.365, 2) → 3867.36
• ROUND(3867.365, -2) → 3900, TRUNC(3867.365, -2) → 3800

양수는 소수점 아래 자릿수, 음수는 정수 쪽 자릿수입니다. 그 밖에 MOD(10, 3)이 나머지 1을, CEIL이 올림을, FLOOR가 내림을 돌려줍니다.

날짜 함수

오라클의 DATE 형은 날짜와 시각을 함께 담습니다. 여기서 두 가지가 따라 나옵니다.

• 날짜에서 날짜를 빼면 일수가 나옵니다. 하루가 1이므로 한 시간은 $1/24$ 입니다.
• SYSDATE는 시각을 포함하므로 SYSDATE = DATE '2026-09-15'는 거의 언제나 거짓입니다. 자정으로 맞추려면 TRUNC(SYSDATE)를 씁니다.

ADD_MONTHS(DATE '2026-01-31', 1)은 2026-02-28입니다 — 원래 날짜가 그 달의 마지막 날이면 결과도 마지막 날로 맞춥니다. MONTHS_BETWEEN(DATE '2026-09-15', DATE '2026-06-15')는 3입니다.

형변환

명시적 형변환

TO_CHAR·TO_DATE·TO_NUMBER 셋이 문자·날짜·숫자 사이를 옮깁니다. 형식 모델을 둘째 인자로 줍니다.

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS 지금,
       TO_DATE('20260915', 'YYYYMMDD')           AS 날짜로,
       TO_NUMBER('1,200', '9,999')               AS 숫자로
  FROM DUAL;

HH는 12시간제, HH24가 24시간제입니다. 분은 MI이고 MM은 월이라 바꿔 적으면 오류 없이 엉뚱한 값이 나옵니다.

암시적 형변환의 대가

타입이 다른 값을 비교하면 데이터베이스가 알아서 한쪽을 바꿔 줍니다. 이것이 암시적 형변환입니다. 편해 보이지만 어느 쪽이 바뀌는지가 성능을 가릅니다.

-- 사원번호가 VARCHAR2인 경우
WHERE 사원번호 = 7369        -- TO_NUMBER(사원번호) = 7369 로 바뀐다
WHERE 사원번호 = '7369'      -- 칼럼은 그대로 남는다

문자와 숫자를 비교하면 오라클은 문자 쪽을 숫자로 바꿉니다. 그러면 칼럼에 함수가 씌워진 꼴이 되어 그 칼럼의 인덱스를 쓰지 못합니다. 위 두 줄은 결과가 같은데 한쪽만 인덱스를 탑니다.

NULL 처리

NVL 계열

NULL은 값이 없다는 표시이므로 계산에 섞이면 결과 전체가 NULL이 됩니다. 급여 + 커미션에서 커미션이 NULL이면 합계도 NULL입니다. 그래서 값을 채워 넣는 함수가 필요합니다.

COALESCE는 인자를 몇 개든 받는 표준 SQL 함수입니다. NULLIF는 방향이 반대라 헷갈리기 쉽습니다 — 값을 채우는 것이 아니라 특정 값을 NULL로 바꾸는 함수입니다. NULLIF(급여, 0)은 0을 NULL로 만들어 나눗셈에서 0으로 나누는 오류를 피하는 데 씁니다.

CASE 식과 DECODE

CASE는 조건에 따라 다른 값을 돌려주는 식입니다. 표준 SQL이고 범위 비교를 쓸 수 있습니다.

SELECT 사원명,
       CASE WHEN 급여 >= 7000 THEN '상'
            WHEN 급여 >= 4000 THEN '중'
            ELSE '하'
       END AS 등급
  FROM 사원;

WHEN 절은 위에서부터 평가되어 처음 참인 것에서 멈춥니다. 그래서 위 예제에서 급여 8000은 상이지 중이 아닙니다. ELSE를 빼면 어디에도 걸리지 않은 행이 NULL이 됩니다.

DECODE는 오라클 전용이고 등호 비교만 합니다. DECODE(부서번호, 10, '영업', 20, '기획', '기타')처럼 값과 결과를 짝지어 늘어놓고 마지막에 기본값을 둡니다. 범위 조건을 쓸 수 없어 CASE보다 좁지만, 한 가지 성질이 다릅니다 — DECODE는 NULL과 NULL을 같다고 봅니다. DECODE(커미션, NULL, '없음', '있음')이 뜻대로 동작하는 반면 CASE WHEN 커미션 = NULL은 결코 참이 되지 않습니다.

집계 함수와 NULL

COUNT(*)와 COUNT(칼럼)

집계 함수는 여러 행을 받아 한 값을 내놓는 함수입니다. 그리고 COUNT(*)를 뺀 모든 집계 함수는 NULL인 행을 아예 세지 않습니다.

부서번호가 10, 10, 20, NULL, 30, 30, 30인 7행에서 셋은 각각 7, 6, 3입니다.

AVG가 나누는 수

SUM·AVG·MAX·MIN도 NULL을 건너뜁니다. AVG에서는 그 성질이 분모를 바꾸므로 결과가 달라집니다.

커미션이 300, 500, NULL, NULL, 700인 5행에서 AVG(커미션)은 $1500 \div 3 = 500$ 이고, AVG(NVL(커미션, 0))은 $1500 \div 5 = 300$ 입니다. 「커미션을 못 받은 사람도 평균에 넣을 것인가」라는 업무 질문이 이 한 글자 차이로 갈립니다. 어느 쪽이 맞는지는 함수가 정해 주지 않으므로 요구사항을 읽고 골라야 합니다.

대상이 없을 때

조건에 맞는 행이 하나도 없어도 GROUP BY 없는 집계 질의는 한 행을 돌려줍니다. 그때 COUNT(*)는 0이지만 SUM·AVG·MAX는 NULL입니다. 이 값을 애플리케이션이 숫자로 받으면 그 자리에서 터지므로 NVL(SUM(금액), 0)으로 감싸 두는 것이 안전합니다.

정규표현식 함수

REGEXP_LIKE

LIKE로는 「숫자 세 자리」 같은 패턴을 적을 수 없습니다. 그 자리를 REGEXP_LIKE가 맡습니다 — 조건절에 쓰고 참·거짓을 돌려줍니다.

SELECT 사원명, 연락처
  FROM 사원
 WHERE REGEXP_LIKE(연락처, '^01[016-9]-[0-9]{3,4}-[0-9]{4}$');

^는 문자열의 처음, $는 끝, []는 그중 한 글자, {3,4}는 앞 패턴이 세 번에서 네 번 되풀이된다는 뜻입니다.

값을 뽑고 바꾸기

REGEXP_SUBSTR(문자열, 패턴)은 패턴에 맞는 부분을 잘라 내고, REGEXP_REPLACE(문자열, 패턴, 바꿀값)은 그 부분을 바꿉니다. REGEXP_SUBSTR('주문-20260915-007', '[0-9]{8}')은 20260915를 돌려줍니다.

세 함수 모두 값을 하나씩 뜯어보는 작업이라 행 수가 많으면 부담이 큽니다. 그리고 조건절에 쓰면 아래에서 볼 문제를 그대로 안고 갑니다.

조건절 좌변의 함수

인덱스를 못 쓰는 자리

인덱스는 칼럼의 값을 정렬해 놓은 구조입니다. 칼럼에 함수를 씌우면 그 결과값은 인덱스에 없으므로, 데이터베이스는 모든 행에 함수를 적용해 보는 수밖에 없습니다.

-- 입사일 인덱스를 쓰지 못한다
WHERE TO_CHAR(입사일, 'YYYYMM') = '202601'

-- 같은 뜻이고 인덱스를 쓴다
WHERE 입사일 >= DATE '2026-01-01'
  AND 입사일 <  DATE '2026-02-01'

아래쪽은 칼럼을 그대로 두고 비교 대상 쪽을 가공했습니다. 이것이 조건절을 고치는 기본 방향입니다. 날짜의 오른쪽 경계에 <= 대신 <를 쓴 것도 이유가 있습니다 — DATE 형에는 시각이 붙어 있어 <= DATE '2026-01-31'로 적으면 1월 31일 낮에 입사한 사람이 빠집니다.

함수 기반 인덱스

칼럼을 가공하지 않고는 도저히 적을 수 없는 조건도 있습니다. 그때는 그 함수의 결과에 인덱스를 만듭니다.

CREATE INDEX 사원_주민앞자리_idx ON 사원 (SUBSTR(주민번호, 1, 6));

이렇게 만들어 두면 WHERE SUBSTR(주민번호, 1, 6) = '900101'이 인덱스를 씁니다. 다만 인덱스에 적힌 식과 조건절의 식이 글자까지 같아야 하고, 인덱스가 하나 늘어난 만큼 입력과 갱신이 느려집니다.

연습 문제

1. 커미션 칼럼의 값이 300, 500, NULL, NULL, 700인 5행이 있다. SELECT COUNT(*), COUNT(커미션), AVG(커미션), AVG(NVL(커미션, 0)) FROM 사원의 결과는?
   ① 5, 5, 300, 300
   ② 5, 3, 500, 300
   ③ 3, 3, 500, 500
   ④ 5, 3, 300, 500

   ②. COUNT(*)는 NULL을 포함해 5, COUNT(커미션)은 NULL을 뺀 3입니다. AVG(커미션)은 NULL인 행을 분자에도 분모에도 넣지 않으므로 $1500 \div 3 = 500$ 이고, NVL로 0을 채우면 다섯 행이 모두 대상이 되어 $1500 \div 5 = 300$ 입니다.
2. 다음 네 식의 결과를 바르게 짝지은 것은?
   ㉠ ROUND(3867.365, -2) ㉡ TRUNC(3867.365, -2) ㉢ ROUND(3867.365, 2) ㉣ MOD(10, 3)
   ① 3900, 3800, 3867.37, 1
   ② 3800, 3800, 3867.36, 1
   ③ 3900, 3900, 3867.37, 3
   ④ 3870, 3860, 3867.36, 1

   ①. 자릿수 인자가 음수이면 정수 쪽을 다룹니다. -2는 백의 자리까지 맞추라는 뜻이라 반올림은 3900, 버림은 3800입니다. 2는 소수 둘째 자리까지이므로 셋째 자리 5를 올려 3867.37이 되고, MOD(10, 3)은 나머지 1입니다.
3. 부서번호 칼럼의 값이 10, 10, 20, NULL, 30, 30, 30인 7행에서 COUNT(*), COUNT(부서번호), COUNT(DISTINCT 부서번호)의 결과는?
   ① 7, 7, 4
   ② 7, 6, 3
   ③ 6, 6, 3
   ④ 7, 6, 4

   ②. COUNT(*)는 행 수 그대로 7입니다. 칼럼을 인자로 주면 NULL인 한 행이 빠져 6이고, DISTINCT를 붙이면 NULL을 뺀 뒤 서로 다른 값 10·20·30만 남아 3입니다.
4. 사원번호 칼럼이 VARCHAR2이고 그 칼럼에 인덱스가 있다. 다음 중 그 인덱스를 정상적으로 쓸 수 있는 조건은?
   ① WHERE 사원번호 = 7369
   ② WHERE 사원번호 = '7369'
   ③ WHERE TO_NUMBER(사원번호) = 7369
   ④ WHERE SUBSTR(사원번호, 1, 2) = '73'

   ②. ①은 문자와 숫자를 비교하므로 오라클이 칼럼 쪽을 TO_NUMBER로 감싸고, 결과적으로 ③과 같아져 인덱스를 쓰지 못합니다. ④도 칼럼에 함수를 씌운 꼴입니다. ②만 칼럼이 가공되지 않은 채 남습니다.
5. 다음 두 식의 결과는?
   ㉠ NVL(NULLIF(100, 100), 0) ㉡ COALESCE(NULL, NULL, 300, 500)
   ① 100, 300
   ② 0, 300
   ③ 0, 500
   ④ NULL, 300

   ②. NULLIF(100, 100)은 두 값이 같으므로 NULL을 돌려주고, 그것을 NVL이 0으로 바꿉니다. COALESCE는 앞에서부터 처음 만나는 NULL이 아닌 값을 돌려주므로 300입니다.
6. 어떤 조회가 WHERE TO_CHAR(주문일시, 'YYYYMMDD') = '20260915' 조건을 쓰고 있고, 주문일시에는 인덱스가 있는데도 테이블을 전부 읽고 있다. 원인과 고치는 방법을 두 가지 들고, 각 방법의 대가를 함께 서술하시오.

   원인은 인덱스가 칼럼의 원래 값을 정렬해 두는 구조인데 조건절이 칼럼을 TO_CHAR로 가공해 버렸기 때문입니다. 가공한 결과는 인덱스에 없으므로 모든 행에 함수를 적용해 비교하는 수밖에 없습니다. 첫째 방법은 칼럼을 그대로 두고 비교 대상 쪽을 범위로 바꾸는 것입니다 — 주문일시 >= DATE '2026-09-15' AND 주문일시 < DATE '2026-09-16'으로 적으면 기존 인덱스를 그대로 쓰고 새로 만들 것이 없습니다. 오른쪽 경계에 <를 쓰는 것이 중요한데, <= DATE '2026-09-16'으로 적으면 16일 0시 정각 주문이 딸려 들어오기 때문입니다. 둘째 방법은 함수 기반 인덱스를 만드는 것입니다 — CREATE INDEX ... ON 주문 (TO_CHAR(주문일시, 'YYYYMMDD'))로 만들면 질의를 고치지 않고도 인덱스를 씁니다. 대가는 인덱스가 하나 늘어 입력·갱신 부담이 커지는 것이고, 조건절의 식이 인덱스에 적힌 식과 글자까지 같아야 한다는 제약도 따릅니다. 채점은 원인을 인덱스 구조로 설명한 것에 2점, 두 방법에 각 1.5점, 각 방법의 대가에 1점입니다.