Oracle 함수 완벽 가이드 (SQLD 대비)

1. 문자형 함수

문자 데이터를 가공하는 함수들입니다.

1.1 기본 문자 함수

*-- LOWER, UPPER, INITCAP 예제*
SELECT first_name,
       LOWER(first_name) as lower_name,
       UPPER(first_name) as upper_name,
       INITCAP(first_name) as initcap_name
FROM employees
WHERE department_id = 60;

*-- LENGTH, LENGTHB 예제*
SELECT first_name,
       LENGTH(first_name) as length_char,  *-- 문자 개수*
       LENGTHB(first_name) as length_byte  *-- 바이트 수*
FROM employees
WHERE department_id = 60;

*-- CONCAT 예제*
SELECT first_name,
       last_name,
       CONCAT(first_name, last_name) as full_name,
       first_name || ' ' || last_name as full_name2
FROM employees
WHERE department_id = 60;

1.2 문자열 조작 함수

-- SUBSTR 예제
SELECT first_name,
       SUBSTR(first_name, 1, 3) as first_three_chars,
       SUBSTR(first_name, -3) as last_three_chars,
       SUBSTR(first_name, 2, 4) as from_second_four_chars
FROM employees
WHERE department_id = 60;

-- INSTR 예제
SELECT phone_number,
       INSTR(phone_number, '.') as first_dot_pos,        -- 첫 번째 '.' 위치
       INSTR(phone_number, '.', 1, 2) as second_dot_pos, -- 두 번째 '.' 위치
       INSTR(phone_number, '650') as area_code_pos       -- 지역번호 '650' 위치
FROM employees
WHERE department_id = 60;;

-- LPAD, RPAD 예제
SELECT salary,
       LPAD(salary, 10, '*') as salary_left_pad,
       RPAD(salary, 10, '*') as salary_right_pad,
       LPAD(salary, 10, '0') as salary_zero_pad
FROM employees
WHERE department_id = 60;

-- LTRIM, RTRIM, TRIM 예제
SELECT first_name,
       LTRIM(first_name, 'A') as ltrim_result,
       RTRIM(first_name, 'n') as rtrim_result,
       TRIM('A' FROM first_name) as trim_result,
       TRIM(' ' FROM '  HELLO  ') as space_trim
FROM employees
WHERE department_id = 60;

-- REPLACE 예제
SELECT job_id,
       REPLACE(job_id, 'IT', 'Information_Technology') as new_job_id,
       email,
       REPLACE(email, '@example.com', '@newdomain.com') as new_email
FROM employees
WHERE department_id = 60;

2. 숫자형 함수

숫자를 처리하는 함수들입니다.

2.1 기본 숫자 함수

-- ROUND, TRUNC, CEIL, FLOOR 예제
SELECT salary,
       ROUND(salary/7) as round_result,
       ROUND(salary/7, 2) as round_decimal,
       TRUNC(salary/7) as trunc_result,
       TRUNC(salary/7, 2) as trunc_decimal,
       CEIL(salary/7) as ceil_result,
       FLOOR(salary/7) as floor_result
FROM employees
WHERE department_id = 60;

-- MOD, ABS, SIGN 예제
SELECT salary,
       MOD(salary, 1000) as salary_remainder,
       ABS(salary - 10000) as absolute_diff,
       SIGN(salary - 10000) as salary_sign
FROM employees
WHERE department_id = 60;

-- POWER, SQRT 예제
SELECT salary,
       POWER(salary, 2) as salary_squared,
       SQRT(salary) as salary_square_root,
       POWER(salary, 1/3) as salary_cube_root
FROM employees
WHERE department_id = 60;

3. 날짜형 함수

날짜와 시간을 처리하는 함수들입니다.

3.1 기본 날짜 함수

-- SYSDATE, CURRENT_DATE, SYSTIMESTAMP 예제
SELECT SYSDATE as system_date,
       CURRENT_DATE as current_date,
       SYSTIMESTAMP as system_timestamp
FROM dual;

-- ADD_MONTHS, MONTHS_BETWEEN 예제
SELECT hire_date,
       ADD_MONTHS(hire_date, 6) as after_6months,
       ADD_MONTHS(hire_date, -6) as before_6months,
       MONTHS_BETWEEN(SYSDATE, hire_date) as months_worked
FROM employees
WHERE department_id = 60;

-- NEXT_DAY, LAST_DAY 예제
SELECT VALUE 
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_TERRITORY';
--KOREA 이면 한글표기법 사용
--ENGLISH 이면 영어표기법 사용
--언어와 상관없이 숫자사용
--1 일요일(Sunday)
--2 월요일(Monday)
--3 화요일(Tuesday)
--4 수요일(Wednesday)
--5 목요일(Thursday)
--6 금요일(Friday)
--7 토요일(Saturday)
alter session set NLS_LANGUAGE='ENGLISH';
SELECT sysdate,
       NEXT_DAY(sysdate, 6) as next_friday,
       LAST_DAY(sysdate) as month_end,
       NEXT_DAY(LAST_DAY(sysdate), 'SUNDAY') as first_sunday_next_month
FROM dual;

-- ROUND, TRUNC 날짜 예제
SELECT hire_date,
       ROUND(hire_date, 'MONTH') as round_month,
       ROUND(hire_date, 'YEAR') as round_year,
       TRUNC(hire_date, 'MONTH') as trunc_month,
       TRUNC(hire_date, 'YEAR') as trunc_year
FROM employees
WHERE department_id = 60;

4. 변환 함수 (SQLD 중요)