251021 TIL

오라클 버전별 접미사

i(8i, 9i) : Internet / 인터넷 환경, 웹 애플리케이션 지원을 강조

g(10g, 11g) : Grid / 그리드(분산처리, 클러스터) 환경 지원을 강조

c(12c, 19c) : Cloud / 클라우드 환경 및 컨테이너/서비스 형태 지원을 강조

ai(23ai, 26ai) : Artificial Intelligence / AI/벡터 검색, 자연어처리, 생성형 AI 지원을 강조

날짜

날짜 함수

SELECT
    sysdate,
    systimestamp,
    current_date,
    current_timestamp,
    localtimestamp
FROM dual;

• 서버 시간
  • sysdate : 현재 서버 날짜를 리턴하는 함수
  • systimestamp : 현재 서버 날짜, 시간, 타임존을 리턴하는 함수
• 세션 시간
  • current_date : 현재 클라이언트의 날짜를 리턴하는 함수
  • current_timestamp : 현재 클라이언트의 날짜, 시간, 타임존을 리턴하는 함수
  • localtimestamp : 현재 클라이언트의 날짜, 시간을 리턴하는 함수

-- 세션 타임존 수정
ALTER SESSION SET TIME_ZONE = '-05:00';

SELECT
    sysdate,
    systimestamp,
    current_date,
    current_timestamp,
    localtimestamp
FROM dual;

날짜 계산

• 날짜 + 숫자(일수) = 날짜
• 날짜 - 숫자(일수) = 날짜
• 날짜 - 날짜 = 일수(숫자)
• 날짜 + 날짜 = 오류
• 날짜 + 시간 = 날짜 시간
• 날 짜 - 시간 = 날짜 시간

SELECT
    sysdate + 100,
    sysdate - 100
FROM dual;

SELECT
    employee_id,
    hire_date,
    TRUNC(sysdate - hire_date)
FROM hr.employees;

SELECT sysdate + sysdate
FROM dual;

SELECT
    systimestamp,
    TO_CHAR(systimestamp + 10/24, 'yyyy-mm-dd hh24:mi:ss'),
    TO_CHAR(localtimestamp + 10/24, 'yyyy-mm-dd hh24:mi:ss'),
    TO_CHAR(current_timestamp + 10/24, 'yyyy-mm-dd hh24:mi:ss')
FROM dual;

• 10/24 : 10시간
• 100/(24*60) : 100분
• 100 / (24_60_60) : 100초

months_between

• 두 날짜 감의 달 수(개월 수)를 리턴하는 함수

SELECT
    employee_id,
    hire_date,
    TRUNC(sysdate - hire_date) 근무일수, 
    TRUNC(months_between(sysdate, hire_date)) 근무개월수 
FROM hr.employees;

add_months

• 달 수를 더하거나 빼는 함수

SELECT 
    sysdate,
    add_months(sysdate, 5),
    add_months(sysdate, -5)
FROM dual;

next_day

• 입력한 날짜를 기준으로 찾고자 하는 요일의 첫 번째 날짜를 반환하는 함수
  • 현재 세션 언어에 따라 요일 이름을 맞춰서 작성해야 함(nls_session_parmeters 참고)

SELECT
    sysdate,
    next_day(sysdate, '금요일')
FROM dual;

last_day

• 기준 날짜 달에서 마지막 날짜를 리턴하는 함수

SELECT
    sysdate,
    last_day(sysdate),
    last_day(add_months(sysdate, 1))
FROM dual;

trunc

• trunc(date, ‘형식모델’)
• 날짜의 시간 부분을 형식 모델에 지정된 단위로 잘라서 반환

SELECT
    systimestamp,
    trunc(systimestamp, 'month'), -- 달의 시작일
    trunc(systimestamp, 'year'), -- 년의 시작일
    TO_CHAR(trunc(systimestamp), 'yy/mm/dd hh24:mi:ss.sssss') -- 그 날의 자정
FROM dual;

round

• round(date, ‘형식모델’)
• 형식모델에서 지정된 단위로 반올림하여 반환
• month : 날짜 1 ~ 15일은 현재 월의 시작일, 16~ 31일은 다음 월의 시작일
• year : 1 ~ 6월은 현재 연도의 1월 1일, 7 ~ 12월은 다음 연도의 1월 1일
• 형식 모델을 사용하지 않으면 가장 가까운 날짜로 반올림

SELECT
    systimestamp,
    round(systimestamp, 'month'),
    round(systimestamp, 'year'),
    round(systimestamp)
FROM dual;

문제

-- [문제18] 20년 이상 근무한 사원들의 사원번호(employee_id), 입사날짜(hire_date),  근무개월수를 조회하세요.
SELECT employee_id 사원번호, hire_date 입사날짜, trunc(months_between(sysdate, hire_date)) 근무개월수
FROM hr.employees
WHERE months_between(sysdate, hire_date) >= 240;

-- [문제19] 사원의 last_name,hire_date 및 근무 6 개월 후 월요일에 해당하는 날짜를 조회하세요. 열별칭은 REVIEW 로 지정합니다.
SELECT last_name, hire_date, next_day(add_months(hire_date, 6), '월요일') review
FROM hr.employees;

형 변환 함수

to_char(날짜, ‘날짜 형식 모델 요소’)

• date 형을 char 형으로 변환하는 함수

SELECT
    sysdate,
    to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss.sssss'),
    to_char(sysdate, 'yy yyyy rr rrrr year') 년,
    to_char(sysdate, 'mm mon month') 월,
    to_char(sysdate, 'dd ddd') 일,
    to_char(sysdate, 'hh hh12 hh24') 시,
    to_char(sysdate, 'mi ss sssss') 분초밀리초,
    to_char(sysdate, 'day dy d') 요일,
    to_char(sysdate, 'ww w') 주,
    to_char(sysdate, 'q"분기"') 분기,
    to_char(sysdate, 'ddsp ddth ddthsp') 문자일
FROM dual;

날짜 형식 모델 요소

• yy : 연도 2자리
• yyyy : 연도 4자리
• rr : 연도 2자리
• rrrr : 연도 4자리
• year : 문자 연도
• mm : 숫자달
• mon : 달(월) 이름 약어
• month : 달(월) 이름
• dd : 달의 일수
• ddd : 년의 일수
• hh / hh12 : 12시간으로 환산한 시간
• hh24 : 24시간으로 환산한 시간
• mi : 분
• ss : 초
• sssss : 초 이하 자리수(5자리)
• day : 요일
• dy : 요일 약어
• d : 숫자 요일(일(1) ~ 토(7))
• ww : 일년 중의 주
• w : 그 달의 주
• q : 분기
• ddsp : 문자일
• ddth : 서수단위 일
• ddthsp : 서수단위 문자일

문제

-- [문제20] 사원들의 사원번호, 입사한 요일을 출력하세요. 단 요일을 오름차순 정렬해주세요.
SELECT employee_id 사원번호, to_char(hire_date, 'day') 입사요일
FROM hr.employees
ORDER BY to_char(hire_date, 'd');

-- 월요일부터 정렬
SELECT employee_id 사원번호, to_char(hire_date, 'day') 입사요일
FROM hr.employees
ORDER BY to_char(hire_date-1, 'd');

to_char(숫자, ‘숫자모델요소’)

• number 형을 char 형으로 변환하는 함수
• 오버로딩(overloading) : 함수 이름은 같고 매개변수의 타입과 갯수가 다르도록 하는 기능, 객체지향프로그램의 기능

SELECT
    salary,
    to_char(salary, '$999,999.00'),
    to_char(salary, '$000,999.00'),
    to_char(salary, 'L999G999D00')
FROM hr.employees;

SELECT
    100,
    -100,
    to_char(-100, '999mi'),
    to_char(-100, '999pr'),
    to_char(100, 's999')
FROM dual;

숫자 모델 요소

• 9 : 숫자를 나타냄, 이 위치에 숫자값이 없으면 출력되지는 않음
• 0 : 숫자를 나타냄, 이 위치에 숫자값이 없으면 0으로 출력됨
• $ : 달러 기호
• , : 1000단위 구분자
• . : 소수점 구분자
• G : 천 단위 구분자(지역에 맞게)
• D : 소수점 구분자(지역에 맞게)
• L : 로컬 통화 기호(지역에 맞게)
• mi : 오른쪽 음수 부호
• pr : 음수일 경우 꺽쇠(<>)로 묶음
• s : 음수, 양수 부호를 표시

ALTER SESSION SET NLS_LANGUAGE = american; -- 한국 : korean, 일본 : japanese, 중국 : 'simplified chinese', 프랑스 : french
ALTER SESSION SET NLS_TERRITORY = america; -- 한국 : korea, 일본 : japan, 중국 : china, 프랑스 : france

SELECT
    employee_id,
    salary,
    hire_date,
    to_char(hire_date, 'day month mon'),
    to_char(salary, 'L999G999D00')
FROM hr.employees;

문제

-- [문제21] 2006년도에 홀수달에 입사한 사원들의 정보를 출력해주세요.
SELECT *
FROM hr.employees
WHERE hire_date BETWEEN TO_DATE('20060101', 'yyyymmdd') AND TO_DATE('20070101', 'yyyymmdd') - 1/24/60/60
AND mod(to_number(to_char(hire_date, 'mm')), 2) = 1;

• mod()는 숫자 함수라 문자형이 들어오더라도 자동 형 변환이 일어나지만 수동으로 형 변환하는 습관을 들이는 것이 좋음

to_number(문자숫자, [’숫자모델요소’])

• 문자 숫자를 숫자형(number)으로 변환하는 함수

SELECT 
    '1' + 2, -- 암시적 형 변환
    to_number('1', '9') + 2, -- 명시적 형 변환
    to_number('1') + 2
FROM dual;

YY vs RR

95-10-27

1995-10-25

2095-10-25

• YY : 현재 년도의 세기를 반영, 2095-10-27
• RR : 2000년도부터는 표기법을 자동화로 변경

RR 표기법

	데이터 입력 연도 0 ~ 49	데이터 입력 연도 50 ~ 99
현재연도 0 ~ 49	반환 날짜는 현재 세기를 반영	반환 날짜는 이전 세기를 반영
현재연도 50 ~ 99	반환 날짜는 이후 세기를 반영	반환 날짜는 현재 세기를 반영

 

현재연도	데이터 입력 날짜	YY	RR
1994	95-10-27	1995	1995
1994	17-10-27	1917	2017
2001	17-10-27	2017	2017
2048	52-10-27	2052	1952
2051	47-10-27	2047	2147

SELECT
    to_char(to_date('95-10-27', 'yy-mm-dd'), 'yyyy-mm-dd'),
    to_char(to_date('95-10-27', 'rr-mm-dd'), 'yyyy-mm-dd')
FROM dual;

Null 관련 함수

nvl

• null 값을 실제 값으로 대체하는 함수

SELECT
    employee_id,
    salary,
    commission_pct,
    salary * 12 + salary * 12 * commission_pct ann_sal_1,
    salary * 12 + salary * 12 * nvl(commission_pct, 0) ann_sal_2
FROM hr.employees;

SELECT
    employee_id,
    salary,
    commission_pct,
    nvl(commission_pct, 0),
    nvl(to_char(commission_pct), 'no comm')
FROM hr.employees;

• nvl 함수의 인자의 자료형이 서로 같아야 함
  • 같지 않으면 오류 발생

nvl2(exp1, exp2, exp3)

• 첫 번째 exp1이 null이 아니면 exp2를 수행하고, 첫 번째 exp1이 null 이면 exp3 수행

SELECT
        employee_id,
    salary,
    commission_pct,
    salary * 12 + salary * 12 * commission_pct ann_sal_1,
    salary * 12 + salary * 12 * nvl(commission_pct, 0) ann_sal_2,
    nvl2(commission_pct, salary * 12 + salary * 12 * commission_pct, salary * 12) ann_sal3
FROM hr.employees;

• exp2와 exp3의 자료형이 같아야 함

SELECT
    employee_id,
    salary,
    commission_pct,
    nvl(commission_pct, 0),
    nvl(to_char(commission_pct), 'no comm'),
    nvl2(commission_pct, to_char(salary*12), 'no comm')
FROM hr.employees;

coalesce(exp1, exp2, exp3, …)

• 첫 번째 exp1이 null이면 exp2를 수행하고 exp2도 null이면 exp3 수행 ….
• null이 발생하지 않을 때까지 인수(표현식)을 수행하는 함수
• 모든 인수의 자료형이 일치해야 함

SELECT
    employee_id,
    salary,
    commission_pct,
    salary * 12 + salary * 12 * commission_pct ann_sal_1,
    salary * 12 + salary * 12 * nvl(commission_pct, 0) ann_sal_2,
    nvl2(commission_pct, salary * 12 + salary * 12 * commission_pct, salary * 12) ann_sal3,
    coalesce(salary * 12 + salary * 12 * commission_pct, salary * 12, 0) ann_sal4
--    coalesce(salary * 12 + salary * 12 * commission_pct, 'no comm', 0) 오류
FROM hr.employees;