10.단일행 함수 ② (변환 · NULL · 조건)

-- ============================================================ -- 【이해용 예제】 오라클은 알아서 타입을 맞춰준다 (암시적 형변환) -- ============================================================ SELECT '100' + 1 AS 문자더하기숫자, -- 문자 '100' 을 숫자 100 으로 자동 변환 100 || '원' AS 숫자붙이기문자 -- 숫자 100 을 문자 '100' 으로 자동 변환 FROM DUAL; -- 문자더하기숫자 숫자붙이기문자 -- -------------- -------------- -- 101 100원 -- → 편리해 보이지만 위험하다 ⚠️

-- ============================================================ -- 【시험용 예제】 암시적 형변환이 위험한 두 가지 이유 ★★ -- ============================================================ -- ⚠️ 위험 ① 데이터에 따라 '오류'가 발생한다 SELECT '100A' + 1 FROM DUAL; -- ORA-01722: invalid number -- → 숫자로 바꿀 수 없는 문자가 섞이면 즉시 오류. 개발 중엔 몰랐다가 운영에서 터진다. -- ⚠️ 위험 ② 인덱스를 사용하지 못해 느려진다 -- EMPNO 는 숫자 컬럼인데 문자와 비교하면 SELECT * FROM EMP WHERE EMPNO = '7369'; -- → 오라클이 내부적으로 EMPNO 를 문자로 바꿔 비교할 수 있고, -- 그러면 EMPNO 에 걸린 인덱스를 타지 못해 전체 테이블을 훑는다 (옵티마이저) -- ✅ 원칙 : 타입은 '명시적으로' 맞춘다 SELECT * FROM EMP WHERE EMPNO = 7369; -- 숫자는 숫자끼리 SELECT * FROM EMP WHERE EMPNO = TO_NUMBER('7369'); -- 또는 명시적 변환 -- ⭐ 시험 포인트 -- "암시적 형변환은 편리하지만, 오류 가능성과 성능 저하 때문에 권장하지 않는다"

-- ============================================================ -- 【이해용 예제】 입사일을 원하는 형태로 표현하기 -- ============================================================ SELECT ENAME, HIREDATE AS 원본, TO_CHAR(HIREDATE, 'YYYY-MM-DD') AS 기본형, TO_CHAR(HIREDATE, 'YYYY"년" MM"월" DD"일"') AS 한글형, -- 큰따옴표 안의 글자는 '포맷이 아닌 그냥 문자' 로 취급된다 TO_CHAR(HIREDATE, 'DAY') AS 요일, TO_CHAR(HIREDATE, 'Q') AS 분기 FROM EMP WHERE DEPTNO = 10; -- ENAME 원본 기본형 한글형 요일 분기 -- ------ ---------- ---------- ------------------ ------ ---- -- CLARK 1981-06-09 1981-06-09 1981년 06월 09일 화요일 2 -- KING 1981-11-17 1981-11-17 1981년 11월 17일 화요일 4 -- MILLER 1982-01-23 1982-01-23 1982년 01월 23일 토요일 1 -- -- ⚠️ DAY, MON 등의 결과는 DB 언어 설정(NLS_LANGUAGE)에 따라 한글/영문이 달라진다

-- ============================================================ -- 【시험용 예제】 MM 과 MI 를 헷갈리면 어떻게 되는가 ★★ -- ============================================================ SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS 올바른표기, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MM:SS') AS 잘못된표기 -- ↑ 분 자리에 MM(월)을 썼다! FROM DUAL; -- 올바른표기 잘못된표기 -- --------------------- --------------------- -- 2026-07-20 14:35:07 2026-07-20 14:07:07 -- ↑ 분이 아니라 '월(07)' 이 찍혔다 ⚠️ -- -- ⭐ 암기: M M → Month(월) / M I → MInute(분)

-- ============================================================ -- 【시험용 예제】 9 와 0 의 차이 ★ -- ============================================================ SELECT ENAME, SAL, TO_CHAR(SAL, '999,999') AS "9사용", -- 빈 자리는 공백 TO_CHAR(SAL, '000,000') AS "0사용", -- 빈 자리는 0으로 채움 TO_CHAR(SAL, 'L999,999') AS 통화표시 FROM EMP WHERE DEPTNO = 10; -- ENAME SAL 9사용 0사용 통화표시 -- ------ ----- -------- -------- ---------- -- CLARK 2450 2,450 002,450 ₩ 2,450 -- KING 5000 5,000 005,000 ₩ 5,000 -- MILLER 1300 1,300 001,300 ₩ 1,300 -- -- ⭐ 시험 포인트 -- 9 = 자리가 남으면 공백 / 0 = 자리가 남으면 0으로 채움 -- TO_CHAR 의 결과는 '문자' 이므로 오른쪽 정렬이 아니라 왼쪽 정렬로 표시된다

-- 같은 결과를 내는 두 가지 방법 (1981년 입사자) SELECT ENAME FROM EMP WHERE EXTRACT(YEAR FROM HIREDATE) = 1981; -- 숫자 비교 SELECT ENAME FROM EMP WHERE TO_CHAR(HIREDATE, 'YYYY') = '1981'; -- 문자 비교 -- → 둘 다 10건

-- ============================================================ -- 【시험용 예제】 날짜 비교의 올바른 방법 ★★ (08강 회수) -- ============================================================ -- ⚠️ 위험한 방법 : 문자열을 그대로 날짜와 비교 SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE >= '1981-01-01'; -- → DB의 날짜 형식 설정(NLS_DATE_FORMAT)이 'YYYY-MM-DD' 가 아니면 -- ORA-01861: literal does not match format string 오류 발생 ⚠️ -- 설정에 따라 되기도 하고 안 되기도 하는 '불안정한 SQL' 이다. -- ✅ 안전한 방법 : TO_DATE 로 형식을 명시한다 SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE >= TO_DATE('1981-01-01', 'YYYY-MM-DD'); -- 문자열 ──────┘ └────── 이 문자열을 이렇게 해석하라 -- ENAME HIREDATE -- ------ ---------- -- ALLEN 1981-02-20 -- WARD 1981-02-22 -- ... → 13건 (SMITH 제외) -- ✅ 기간 조회도 마찬가지 SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD') AND TO_DATE('1981-12-31', 'YYYY-MM-DD'); -- → 1981년에 입사한 사원 10건 -- -- ⭐ 시험·실무 원칙 : 날짜 리터럴은 반드시 TO_DATE 로 명시적 변환한다

-- ANSI 표준 DATE 리터럴 (반드시 'YYYY-MM-DD' 형식이어야 함) SELECT ENAME FROM EMP WHERE HIREDATE >= DATE '1981-01-01'; -- → TO_DATE 와 동일한 결과. 형식이 고정되어 있어 오히려 안전하다.

-- ============================================================ -- 【이해용 예제】 문자로 저장된 값을 계산에 쓰기 -- PLAYER.JOIN_YYYY 는 CHAR(4) 이므로 '문자' 다 -- ============================================================ SELECT PLAYER_NAME AS 선수명, JOIN_YYYY AS "입단연도(문자)", TO_NUMBER(JOIN_YYYY) AS "입단연도(숫자)", 2026 - TO_NUMBER(JOIN_YYYY) AS 활동연차 -- 숫자로 바꿔야 뺄 수 있다 FROM PLAYER WHERE JOIN_YYYY IS NOT NULL AND ROWNUM <= 3; -- 선수명 입단연도(문자) 입단연도(숫자) 활동연차 -- ------- -------------- -------------- -------- -- 김철수 2012 2012 14 -- 이영희 2010 2010 16 -- 09강에서 SUBSTR 로 잘라낸 값도 '문자' 이므로 계산하려면 변환이 필요하다 SELECT PLAYER_NAME, SUBSTR(PLAYER_ID, 1, 4) AS "연도(문자)", TO_NUMBER(SUBSTR(PLAYER_ID, 1, 4)) AS "연도(숫자)" FROM PLAYER WHERE ROWNUM <= 3;

-- ============================================================ -- 【시험용 예제】 콤마가 들어간 문자는 그냥 변환되지 않는다 ★ -- ============================================================ SELECT TO_NUMBER('1234') AS 정상 FROM DUAL; -- → 1234 ✅ SELECT TO_NUMBER('1,234') FROM DUAL; -- ORA-01722: invalid number -- → 콤마 때문에 숫자로 해석되지 않는다 ⚠️ SELECT TO_NUMBER('1,234', '9,999') AS 포맷지정 FROM DUAL; -- → 1234 ✅ (포맷을 알려주면 해석할 수 있다)

-- ============================================================ -- 【시험용 예제】 NULL 때문에 계산이 깨지던 문제 해결 ★★ -- ============================================================ SELECT ENAME, SAL, COMM, SAL + COMM AS "❌ 그냥 계산", SAL + NVL(COMM, 0) AS "✅ NVL 적용", NVL(COMM, 0) AS "커미션(NULL→0)" FROM EMP WHERE DEPTNO = 30; -- ENAME SAL COMM ❌ 그냥 계산 ✅ NVL 적용 커미션(NULL→0) -- ------ ----- ----- ------------ ----------- -------------- -- ALLEN 1600 300 1900 1900 300 -- WARD 1250 500 1750 1750 500 -- MARTIN 1250 1400 2650 2650 1400 -- BLAKE 2850 (NULL) (NULL) ⚠️ 2850 0 -- TURNER 1500 0 1500 1500 0 -- JAMES 950 (NULL) (NULL) ⚠️ 950 0 -- -- ⭐ 06강 → 07강 → 10강으로 이어진 숙제가 여기서 끝났습니다. -- ⚠️ NVL 의 두 인자는 '데이터 타입이 호환' 되어야 한다 -- NVL(COMM, '없음') → ORA-01722 (숫자 컬럼에 문자를 넣을 수 없음)

-- ============================================================ -- 【이해용 예제】 커미션 수령 여부를 표시하기 -- ============================================================ SELECT ENAME, COMM, NVL2(COMM, '수령', '미수령') AS 커미션여부 -- ↑값 ↑NULL아닐때 ↑NULL일때 FROM EMP WHERE DEPTNO = 30; -- ENAME COMM 커미션여부 -- ------ ----- ---------- -- ALLEN 300 수령 -- WARD 500 수령 -- MARTIN 1400 수령 -- BLAKE (NULL) 미수령 -- TURNER 0 수령 ← 0 은 NULL이 아니다! (06강) ⭐ -- JAMES (NULL) 미수령 -- -- ⭐ TURNER 의 커미션 0 은 '값이 있는 것' 이므로 '수령' 으로 판정된다. -- 이 지점이 시험에 자주 나옵니다.

-- ============================================================ -- 【시험용 예제】 특정 값을 NULL로 만들고 싶을 때 ★ -- "급여가 정확히 3000인 사원은 급여를 표시하지 않는다" -- ============================================================ SELECT ENAME, SAL, NULLIF(SAL, 3000) AS 결과 -- ↑A ↑B A와 B가 같으면 NULL, 다르면 A FROM EMP WHERE DEPTNO = 20; -- ENAME SAL 결과 -- ------ ----- ------ -- SMITH 800 800 -- JONES 2975 2975 -- SCOTT 3000 (NULL) ← 3000 과 같으므로 NULL ⚠️ -- ADAMS 1100 1100 -- FORD 3000 (NULL) ← 3000 과 같으므로 NULL -- -- ⭐ NULLIF(A, B) 는 CASE 로 쓰면 이렇게 된다 -- CASE WHEN A = B THEN NULL ELSE A END

-- ============================================================ -- 【시험용 예제】 여러 후보 중 값이 있는 것을 골라내기 ★ -- "커미션이 있으면 커미션, 없으면 급여, 그것도 없으면 0" -- ============================================================ SELECT ENAME, COMM, SAL, COALESCE(COMM, SAL, 0) AS 지급기준액 -- ① ② ③ 왼쪽부터 검사해 처음 NULL이 아닌 값을 반환 FROM EMP WHERE DEPTNO = 30; -- ENAME COMM SAL 지급기준액 -- ------ ----- ----- ---------- -- ALLEN 300 1600 300 ← COMM 이 있으므로 COMM -- WARD 500 1250 500 -- MARTIN 1400 1250 1400 -- BLAKE (NULL) 2850 2850 ← COMM 이 NULL 이므로 SAL -- TURNER 0 1500 0 ← 0 도 값이므로 COMM 채택 ⭐ -- JAMES (NULL) 950 950 -- -- ⭐ COALESCE 는 인자 개수 제한이 없다 → NVL 을 여러 번 중첩한 것과 같다 -- COALESCE(A, B, C) = NVL(NVL(A, B), C)

-- ============================================================ -- 【이해용 예제】 부서번호를 부서명으로 바꾸기 -- ============================================================ SELECT ENAME, DEPTNO, CASE DEPTNO -- 이 컬럼의 값을 WHEN 10 THEN '경리부' -- 10 이면 '경리부' WHEN 20 THEN '연구부' -- 20 이면 '연구부' WHEN 30 THEN '영업부' -- 30 이면 '영업부' ELSE '기타' -- 어느 것도 아니면 '기타' END AS 부서명 -- CASE 는 반드시 END 로 닫는다 FROM EMP; -- ENAME DEPTNO 부서명 -- ------ ------ ------ -- SMITH 20 연구부 -- ALLEN 30 영업부 -- CLARK 10 경리부

-- ============================================================ -- 【시험용 예제】 급여 구간별 등급 부여 ★★ -- ⚠️ 이런 '범위 조건' 은 단순 CASE 로는 불가능하다 -- ============================================================ SELECT ENAME, SAL, CASE WHEN SAL >= 3000 THEN 'A등급' -- 위에서부터 순서대로 검사 WHEN SAL >= 2000 THEN 'B등급' -- 앞 조건이 FALSE 일 때만 검사됨 WHEN SAL >= 1000 THEN 'C등급' ELSE 'D등급' END AS 급여등급 FROM EMP ORDER BY SAL DESC; -- ENAME SAL 급여등급 -- ------ ----- -------- -- KING 5000 A등급 -- SCOTT 3000 A등급 -- FORD 3000 A등급 -- JONES 2975 B등급 -- BLAKE 2850 B등급 -- CLARK 2450 B등급 -- ALLEN 1600 C등급 -- TURNER 1500 C등급 -- MILLER 1300 C등급 -- WARD 1250 C등급 -- MARTIN 1250 C등급 -- ADAMS 1100 C등급 -- JAMES 950 D등급 -- SMITH 800 D등급 -- -- ⭐ 중요: WHEN 은 '위에서부터 순서대로' 검사하고, 처음 TRUE 인 곳에서 멈춘다. -- 그래서 SAL=5000 은 'SAL >= 3000' 에서 걸려 A등급이 되고 -- 아래의 'SAL >= 2000' 은 아예 검사되지 않는다. -- ⚠️ 순서를 거꾸로 쓰면(1000 → 2000 → 3000) 모두 C등급이 되어버린다!

-- ============================================================ -- 【시험용 예제】 ELSE 생략의 함정 ★★ -- ============================================================ SELECT ENAME, DEPTNO, CASE DEPTNO WHEN 10 THEN '경리부' WHEN 20 THEN '연구부' END AS "ELSE 없음" -- ELSE 를 쓰지 않았다 FROM EMP; -- ENAME DEPTNO ELSE 없음 -- ------ ------ --------- -- CLARK 10 경리부 -- SMITH 20 연구부 -- ALLEN 30 (NULL) ← 어느 WHEN 에도 안 걸림 → NULL ⚠️ -- BLAKE 30 (NULL) -- -- ⭐ 시험 포인트 -- CASE 에 ELSE 가 없고 어떤 WHEN 도 만족하지 않으면 결과는 'NULL' 이다. -- (오류가 나거나 공백이 되는 것이 아니다)

-- ============================================================ -- 【이해용 예제】 DECODE 로 부서명 표시하기 (단순 CASE 와 동일) -- ============================================================ SELECT ENAME, DEPTNO, DECODE(DEPTNO, 10, '경리부', -- DEPTNO 가 10 이면 '경리부' 20, '연구부', -- 20 이면 '연구부' 30, '영업부', -- 30 이면 '영업부' '기타') -- 마지막 짝 없는 인자 = 기본값(ELSE) AS 부서명 FROM EMP; -- ENAME DEPTNO 부서명 -- ------ ------ ------ -- SMITH 20 연구부 -- ALLEN 30 영업부 -- CLARK 10 경리부 -- 위와 완전히 같은 결과를 내는 CASE SELECT ENAME, DEPTNO, CASE DEPTNO WHEN 10 THEN '경리부' WHEN 20 THEN '연구부' WHEN 30 THEN '영업부' ELSE '기타' END AS 부서명 FROM EMP;

-- ============================================================ -- 【시험용 예제】 범위 조건은 DECODE 로 못 한다 ★★★ -- ============================================================ -- ❌ DECODE 로는 이런 걸 쓸 수 없다 -- DECODE(SAL >= 3000, TRUE, 'A등급', ...) ← 문법적으로 불가능 -- ✅ 범위 조건은 반드시 CASE SELECT ENAME, SAL, CASE WHEN SAL >= 3000 THEN 'A등급' WHEN SAL >= 2000 THEN 'B등급' ELSE 'C등급' END AS 등급 FROM EMP; -- 💡 굳이 DECODE 로 하려면 SIGN 함수 같은 편법을 써야 한다 (권장하지 않음) SELECT ENAME, SAL, DECODE(SIGN(SAL - 3000), 1, 'A등급', 0, 'A등급', '그 외') AS 등급 -- SIGN : 양수면 1, 0이면 0, 음수면 -1 (09강) FROM EMP; -- → 억지로 되긴 하지만 읽기 어렵다. CASE 를 쓰는 게 정답이다.

-- ============================================================ -- 【시험용 예제】 DECODE 는 NULL 끼리도 '같다' 고 판단한다 ★ -- ============================================================ SELECT ENAME, COMM, DECODE(COMM, NULL, '커미션 없음', '커미션 있음') AS 판정 -- ↑ NULL 과의 등가비교가 '동작한다' FROM EMP WHERE DEPTNO = 30; -- ENAME COMM 판정 -- ------ ----- ------------ -- ALLEN 300 커미션 있음 -- BLAKE (NULL) 커미션 없음 ← DECODE 는 NULL 을 잡아낸다 ✅ -- TURNER 0 커미션 있음 -- -- ⚠️ 08강에서 배운 WHERE 절의 규칙과 다르다는 점에 주의! -- WHERE COMM = NULL → 0건 (UNKNOWN) -- DECODE(COMM, NULL, …) → 정상 동작 ✅ -- DECODE 는 내부적으로 두 NULL 을 '같은 것' 으로 취급하기 때문이다.

SELECT CASE DEPTNO WHEN 10 THEN '경리부' WHEN 20 THEN '연구부' END AS 부서명 FROM EMP;