09. 함수 ① (문자 · 숫자 · 날짜)

-- Oracle의 SELECT 문은 FROM 절을 반드시 요구한다. -- 그런데 "1+1이 뭐지?" 처럼 테이블이 필요 없는 계산을 하고 싶을 때가 있다. -- 그때 형식을 맞추기 위해 빌려 쓰는 테이블이 DUAL 이다. SELECT 1 + 1 AS 계산결과 FROM DUAL; -- 테이블이 필요 없지만 FROM 절 형식을 맞추기 위해 사용 -- 계산결과 -- -------- -- 2 → 항상 1행만 나온다

-- ============================================================ -- 【이해용 예제】 사원명을 세 가지 형태로 변환 -- ============================================================ SELECT ENAME AS 원본, LOWER(ENAME) AS 소문자, -- 전부 소문자 INITCAP(ENAME) AS 첫글자대문자 -- 첫 글자만 대문자 FROM EMP; -- 원본 소문자 첫글자대문자 -- ------ ------ ------------ -- SMITH smith Smith -- ALLEN allen Allen -- MARTIN martin Martin

-- ============================================================ -- 【시험용 예제】 08강 숙제 해결 — 대소문자 상관없이 검색하기 ★ -- ============================================================ -- ❌ 08강에서 본 문제 : 데이터는 'SMITH' 인데 'smith' 로 찾으면 0건 SELECT ENAME FROM EMP WHERE ENAME = 'smith'; -- → 0건 -- ✅ 해결 : 컬럼 값을 대문자로 바꿔서 비교한다 SELECT ENAME FROM EMP WHERE UPPER(ENAME) = 'SMITH'; -- ENAME 을 대문자로 변환한 뒤 비교 -- ENAME -- ----- -- SMITH → 1건 ✅ -- ✅ 사용자가 무엇을 입력하든 안전한 형태 (양쪽 모두 대문자로 통일) SELECT ENAME FROM EMP WHERE UPPER(ENAME) = UPPER('sMiTh'); -- → 동일하게 1건 ✅ -- -- ⚠️ 실무 주의: WHERE 절의 컬럼에 함수를 씌우면 그 컬럼의 인덱스를 쓸 수 없어 -- 조회가 느려질 수 있다. (23강 옵티마이저에서 다시 다룹니다)

-- ============================================================ -- 【이해용 예제】 이름이 몇 글자인지 세기 -- ============================================================ SELECT ENAME, LENGTH(ENAME) AS 글자수 FROM EMP WHERE LENGTH(ENAME) >= 6; -- 6글자 이상인 사원만 -- ENAME 글자수 -- ------ ------ -- MARTIN 6 -- TURNER 6 -- MILLER 6 → 3건

문자열 : S M I T H 양수 위치 : 1 2 3 4 5 음수 위치 : -5 -4 -3 -2 -1

-- ============================================================ -- 【이해용 예제】 이름의 앞부분과 뒷부분 잘라내기 -- ============================================================ SELECT ENAME, SUBSTR(ENAME, 1, 3) AS 앞3글자, -- 1번째부터 3글자 SUBSTR(ENAME, -2) AS 뒤2글자, -- 뒤에서 2번째부터 끝까지 SUBSTR(ENAME, 2, 2) AS "2~3번째" -- 2번째부터 2글자 FROM EMP; -- ENAME 앞3글자 뒤2글자 2~3번째 -- ------ ------- ------- ------- -- SMITH SMI TH MI -- MARTIN MAR IN AR -- KING KIN NG IN

-- ============================================================ -- 【시험용 예제】 SUBSTR 로 의미 있는 정보 뽑아내기 ★ -- PLAYER_ID 는 7자리 (예: 2012001) = 앞 4자리가 연도, 뒤 3자리가 일련번호 -- ============================================================ SELECT PLAYER_NAME AS 선수명, PLAYER_ID AS 선수ID, SUBSTR(PLAYER_ID, 1, 4) AS 등록연도, -- 앞 4자리 SUBSTR(PLAYER_ID, 5) AS 일련번호 -- 5번째부터 끝까지 FROM PLAYER WHERE SUBSTR(PLAYER_ID, 1, 4) = '2012'; -- 2012년에 등록된 선수만 -- 선수명 선수ID 등록연도 일련번호 -- ------- ------- -------- -------- -- 김철수 2012001 2012 001 -- 이영희 2012002 2012 002 -- -- ⭐ 시험 포인트 -- SUBSTR의 시작 위치는 '1'부터다. 프로그래밍 언어(0부터)와 달라 가장 많이 틀린다.

-- ============================================================ -- 【시험용 예제】 INSTR 는 못 찾으면 0을 반환한다 ★★ -- ============================================================ SELECT ENAME, INSTR(ENAME, 'A') AS A의위치 FROM EMP; -- ENAME A의위치 -- ------ ------- -- SMITH 0 ← A가 없다! NULL이 아니라 0 ⚠️ -- ALLEN 1 -- WARD 2 -- MARTIN 2 -- BLAKE 3 -- CLARK 3 -- ADAMS 1 -- 이 성질을 이용해 "특정 문자를 포함한 행"을 찾을 수 있다 SELECT ENAME FROM EMP WHERE INSTR(ENAME, 'A') > 0; -- 위치가 0보다 크다 = 존재한다 -- → ALLEN, WARD, MARTIN, BLAKE, CLARK, ADAMS, JAMES (7건) -- 위와 같은 결과를 LIKE 로도 얻을 수 있다 (08강) SELECT ENAME FROM EMP WHERE ENAME LIKE '%A%'; -- → 동일한 7건 -- ⭐ INSTR 는 '위치까지' 알려주고, LIKE 는 '포함 여부만' 판단한다.

-- ============================================================ -- 【이해용 예제】 자리수를 맞춰 정렬된 것처럼 보이게 하기 -- ============================================================ SELECT ENAME, LPAD(ENAME, 10, '*') AS 왼쪽채움, -- 총 10자리가 되도록 왼쪽에 * 채움 RPAD(ENAME, 10, '.') AS 오른쪽채움 -- 총 10자리가 되도록 오른쪽에 . 채움 FROM EMP; -- ENAME 왼쪽채움 오른쪽채움 -- ------ ---------- ---------- -- SMITH *****SMITH SMITH..... -- WARD ******WARD WARD...... -- MARTIN ****MARTIN MARTIN.... -- -- ⚠️ 전체 길이가 원본보다 짧으면 오히려 '잘린다' -- LPAD('MARTIN', 3, '*') → 'MAR'

-- ============================================================ -- 【시험용 예제】 TRIM 과 LTRIM/RTRIM 의 차이 ★ -- ============================================================ SELECT TRIM(' SQL ') AS 양쪽공백제거, -- 'SQL' LTRIM(' SQL ') AS 왼쪽만, -- 'SQL ' LTRIM('xyxSQL', 'xy') AS 문자집합제거, -- 'SQL' (x,y 를 모두 제거) TRIM('x' FROM 'xxxSQLxxx') AS 특정문자양쪽 -- 'SQL' FROM DUAL; -- 양쪽공백제거 왼쪽만 문자집합제거 특정문자양쪽 -- ------------ ------- ------------ ------------ -- SQL SQL␣␣ SQL SQL -- -- ⚠️ 핵심 차이 -- LTRIM/RTRIM 의 두 번째 인자는 '문자 집합' → 'xy' 를 주면 x 든 y 든 모두 제거 -- TRIM 의 제거 문자는 '딱 한 글자'만 지정 가능 → TRIM('xy' FROM ...) 은 오류

-- ============================================================ -- 【이해용 예제】 특정 문자열을 다른 것으로 바꾸기 -- ============================================================ SELECT JOB AS 원본직무, REPLACE(JOB, 'MAN', '***') AS 치환결과 -- 'MAN' 을 '***' 로 바꿈 FROM EMP; -- 원본직무 치환결과 -- --------- --------- -- CLERK CLERK ← 'MAN' 이 없으면 그대로 -- SALESMAN SALES*** -- MANAGER ***AGER -- PRESIDENT PRESIDENT

-- ============================================================ -- 【시험용 예제】 CONCAT 은 인자를 딱 2개만 받는다 ★ -- ============================================================ -- ✅ 인자 2개 → 정상 SELECT CONCAT(ENAME, JOB) AS 결합 FROM EMP; -- 결합 -- ------------- -- SMITHCLERK -- ALLENSALESMAN -- ❌ 인자 3개 → 오류 SELECT CONCAT(ENAME, ' - ', JOB) FROM EMP; -- ORA-00909: invalid number of arguments -- ✅ 해결 ① 함수를 중첩한다 SELECT CONCAT(CONCAT(ENAME, ' - '), JOB) AS 결합 FROM EMP; -- 결합 -- ---------------- -- SMITH - CLERK -- ✅ 해결 ② || 를 쓴다 (훨씬 간단) SELECT ENAME || ' - ' || JOB AS 결합 FROM EMP; -- → 동일한 결과 -- -- ⭐ 시험 포인트 -- CONCAT 은 2개, || 는 개수 제한 없음. -- 그리고 둘 다 NULL 을 만나면 '무시' 한다

1 2 3 4 . 5 6 7 8 자릿수 지정 : -4 -3 -2 -1 0 1 2 3 4 ↑ 기본값(생략 시 0) = 정수로 만듦

-- ============================================================ -- 【이해용 예제】 급여를 3으로 나눈 값을 소수 2자리까지 -- ============================================================ SELECT ENAME, SAL, ROUND(SAL / 3, 2) AS 반올림, -- 소수 2자리까지 반올림 TRUNC(SAL / 3, 2) AS 버림 -- 소수 2자리까지 버림 FROM EMP; -- ENAME SAL 반올림 버림 -- ------ ----- -------- -------- -- SMITH 800 266.67 266.66 ← 266.666… 에서 갈린다 -- ALLEN 1600 533.33 533.33 -- JONES 2975 991.67 991.66

-- ============================================================ -- 【시험용 예제】 음수 자릿수 — 천 단위로 맞추기 ★★ -- ============================================================ SELECT ENAME, SAL, ROUND(SAL, -3) AS "천단위 반올림", TRUNC(SAL, -3) AS "천단위 버림" FROM EMP; -- ENAME SAL 천단위 반올림 천단위 버림 -- ------ ----- ------------- ----------- -- SMITH 800 1000 0 ← 800 은 반올림하면 1000, 버리면 0 -- ALLEN 1600 2000 1000 -- WARD 1250 1000 1000 -- JONES 2975 3000 2000 -- KING 5000 5000 5000 -- -- ⭐ 시험 포인트 -- ROUND(숫자, -N) : 소수점 '왼쪽' N번째 자리에서 반올림 -- TRUNC 는 절대 올라가지 않는다 (800 → 0 이 되는 것에 주의!)

-- ============================================================ -- 【이해용 예제】 사원번호가 홀수인 사람 찾기 -- ============================================================ SELECT EMPNO, ENAME, MOD(EMPNO, 2) AS 나머지 -- EMPNO 를 2로 나눈 나머지 FROM EMP WHERE MOD(EMPNO, 2) = 1; -- 나머지가 1 = 홀수 -- EMPNO ENAME 나머지 -- ----- ------ ------ -- 7369 SMITH 1 -- 7499 ALLEN 1 -- 7521 WARD 1 -- 7839 KING 1

-- ============================================================ -- 【시험용 예제】 CEIL / FLOOR 의 음수 처리 ★ -- 테이블에 음수 데이터가 없으므로 DUAL 로 규칙을 확인한다 -- ============================================================ SELECT CEIL(1.5) AS "CEIL(1.5)", -- 2 FLOOR(1.5) AS "FLOOR(1.5)", -- 1 CEIL(-1.5) AS "CEIL(-1.5)", -- -1 ⚠️ FLOOR(-1.5) AS "FLOOR(-1.5)", -- -2 ⚠️ SIGN(-100) AS "SIGN(-100)" -- -1 FROM DUAL; -- CEIL(1.5) FLOOR(1.5) CEIL(-1.5) FLOOR(-1.5) SIGN(-100) -- --------- ---------- ---------- ----------- ---------- -- 2 1 -1 -2 -1

-- ============================================================ -- 【이해용 예제】 입사일 기준으로 날짜 계산하기 -- ============================================================ SELECT ENAME, HIREDATE AS 입사일, HIREDATE + 100 AS "입사 100일 후", -- 날짜 + 숫자 = 날짜 HIREDATE - 7 AS "입사 1주일 전" -- 날짜 - 숫자 = 날짜 FROM EMP WHERE DEPTNO = 10; -- ENAME 입사일 입사 100일 후 입사 1주일 전 -- ------ ---------- ------------- ------------- -- CLARK 1981-06-09 1981-09-17 1981-06-02 -- KING 1981-11-17 1982-02-25 1981-11-10 -- MILLER 1982-01-23 1982-05-03 1982-01-16

-- ============================================================ -- 【시험용 예제】 날짜 − 날짜 = 숫자 ★★ -- "MILLER 는 KING 보다 며칠 늦게 입사했는가?" -- ============================================================ SELECT SYSDATE AS 오늘, -- 현재 날짜와 시각 SYSDATE - 1 AS 어제, TRUNC(SYSDATE) AS "오늘 0시" -- 시분초를 잘라낸다 FROM DUAL; -- ⚠️ 결과는 실행하는 날짜에 따라 달라집니다 -- 사원별 근속일수 (오늘 - 입사일) SELECT ENAME, HIREDATE AS 입사일, TRUNC(SYSDATE - HIREDATE) AS 근속일수 -- 날짜 - 날짜 = 숫자(일수) FROM EMP WHERE DEPTNO = 10; -- ENAME 입사일 근속일수 -- ------ ---------- -------- -- CLARK 1981-06-09 16478 ← 실행일에 따라 달라짐 -- KING 1981-11-17 16317 -- -- ⭐ 시험 포인트 -- '날짜 - 날짜' 의 결과는 날짜가 아니라 '숫자(일수)' 다. -- 소수점이 나오는 이유는 시분초까지 계산되기 때문 → TRUNC 로 정리한다.

-- ============================================================ -- 【이해용 예제】 날짜 함수 4종을 한 번에 -- ============================================================ SELECT ENAME, HIREDATE AS 입사일, ADD_MONTHS(HIREDATE, 6) AS "수습종료(6개월후)", LAST_DAY(HIREDATE) AS "입사월 말일", NEXT_DAY(HIREDATE, '월요일') AS "입사후 첫 월요일" FROM EMP WHERE ENAME IN ('BLAKE', 'SMITH'); -- ENAME 입사일 수습종료(6개월후) 입사월 말일 입사후 첫 월요일 -- ----- ---------- ----------------- ----------- ---------------- -- SMITH 1980-12-17 1981-06-17 1980-12-31 1980-12-22 -- BLAKE 1981-05-01 1981-11-01 1981-05-31 1981-05-04 -- -- ⚠️ NEXT_DAY 의 요일은 DB 언어 설정(NLS_LANGUAGE)을 따른다. -- 한글 환경이면 '월요일', 영어 환경이면 'MONDAY'. -- 숫자로도 지정 가능 : 1=일요일, 2=월요일 … NEXT_DAY(HIREDATE, 2)

-- ============================================================ -- 【시험용 예제】 MONTHS_BETWEEN 은 '앞 − 뒤' 순서다 ★★ -- 테이블 데이터로는 순서를 대조하기 어려우므로 DUAL 로 규칙을 확인한다 -- ============================================================ SELECT MONTHS_BETWEEN(DATE '2026-07-20', DATE '2026-01-20') AS "뒤가 과거", MONTHS_BETWEEN(DATE '2026-01-20', DATE '2026-07-20') AS "뒤가 미래" FROM DUAL; -- 뒤가 과거 뒤가 미래 -- --------- --------- -- 6 -6 ← 순서를 바꾸면 부호가 뒤집힌다 ⚠️ -- 실제 테이블에 적용 : 사원별 근속 개월 수 SELECT ENAME, HIREDATE AS 입사일, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS 근속개월, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE) / 12) AS 근속년수 FROM EMP WHERE DEPTNO = 20; -- ENAME 입사일 근속개월 근속년수 -- ------ ---------- -------- -------- -- SMITH 1980-12-17 547 45 ← 실행일에 따라 달라짐 -- SCOTT 1987-07-13 468 39 -- -- ⭐ 시험 포인트 -- MONTHS_BETWEEN(A, B) = A - B (최신 날짜를 앞에 써야 양수) -- 결과는 소수점이 나올 수 있다 → TRUNC 또는 ROUND 로 정리

-- ============================================================ -- 【이해용 예제】 입사일에서 연도·월·일을 각각 뽑아내기 -- ============================================================ SELECT ENAME, HIREDATE AS 입사일, EXTRACT(YEAR FROM HIREDATE) AS 입사년, -- 연도만 (숫자) EXTRACT(MONTH FROM HIREDATE) AS 입사월, -- 월만 (숫자) EXTRACT(DAY FROM HIREDATE) AS 입사일자 -- 일만 (숫자) FROM EMP WHERE DEPTNO = 10; -- ENAME 입사일 입사년 입사월 입사일자 -- ------ ---------- ------ ------ -------- -- CLARK 1981-06-09 1981 6 9 -- KING 1981-11-17 1981 11 17 -- MILLER 1982-01-23 1982 1 23 -- -- ⭐ 결과가 '숫자' 이므로 앞에 0이 붙지 않는다 (6월 → 6, 06 아님)

-- ============================================================ -- 【시험용 예제】 EXTRACT 로 조건 걸기 + 반환 타입 주의 ★★ -- ============================================================ -- 1981년에 입사한 사원만 조회 SELECT ENAME, HIREDATE FROM EMP WHERE EXTRACT(YEAR FROM HIREDATE) = 1981; -- 숫자와 비교 (따옴표 없음!) -- ENAME HIREDATE -- ------ ---------- -- ALLEN 1981-02-20 -- WARD 1981-02-22 -- ... → 10건 -- 12월에 입사한 사원 (연도 무관) SELECT ENAME, HIREDATE FROM EMP WHERE EXTRACT(MONTH FROM HIREDATE) = 12; -- ENAME HIREDATE -- ------ ---------- -- SMITH 1980-12-17 -- JAMES 1981-12-03 -- FORD 1981-12-03 → 3건 -- ❌ 시·분·초는 DATE 타입에서 추출할 수 없다 SELECT EXTRACT(HOUR FROM HIREDATE) FROM EMP; -- ORA-30076: invalid extract field for extract source -- → HOUR/MINUTE/SECOND 는 TIMESTAMP 타입에서만 가능하다 ⚠️ -- ⭐ 시험 포인트 : EXTRACT 는 '숫자'를 반환한다 -- TO_CHAR(HIREDATE, 'YYYY') 는 '문자' 를 반환한다 (10강) -- EXTRACT(YEAR FROM HIREDATE) = 1981 ← 숫자 비교 ✅ -- TO_CHAR(HIREDATE, 'YYYY') = '1981' ← 문자 비교 (따옴표 필요) ✅

-- ============================================================ -- 【시험용 예제】 이름 마스킹 처리 — 3중 중첩 ★ -- 목표: 앞 2글자만 남기고 나머지는 * 로 가린다 (길이는 유지) -- ============================================================ SELECT ENAME AS 원본, LENGTH(ENAME) AS 글자수, SUBSTR(ENAME, 1, 2) AS 앞2글자, RPAD( SUBSTR(ENAME, 1, 2), -- ① 앞 2글자를 자르고 LENGTH(ENAME), -- ② 원래 길이만큼 '*' ) -- ③ 나머지를 * 로 채운다 AS 마스킹 FROM EMP; -- 원본 글자수 앞2글자 마스킹 -- ------ ------ ------- -------- -- SMITH 5 SM SM*** -- WARD 4 WA WA** -- MARTIN 6 MA MA**** -- KING 4 KI KI** -- -- ⭐ 함수는 '가장 안쪽부터' 계산된다. 괄호를 짝지어 안에서 밖으로 읽으세요.