2/25 [Database #4] 숫자/ 날짜/ 변환 함수

[단일행 함수]

1. 숫자 함수

    * round 정도를 가장 빈번하게 사용함

1) round ( n, [m] ) : 반올림하여 m자리까지 반환. m은 소수점 아래 자릿수.  (기본값은 0이며 1의 자리임)

    * 결국 m+1의 자리에서 반올림한다는 의미

-- round

select 1234.5438, round(1234.5438) round1, round(1234.5438, 0) round2,

        round(1234.5438, 1) round3 , round(1234.5438, -1) round4,

        round(1234.5438, 3) round5, round(1234.5438, -3) round6

from dual;

-- 사원의 사번, 이름, 급여, 커미션포함급여

-- 커미션 포함 급여는 100의 자리수로 표현(반올림)

select employee_id 사번, first_name 이름, salary 급여,

        round(salary*(1+nvl(commission_pct, 0)), -2) 커미션포함급여

from employees;

    

2) trunc ( n, [m] ) : 버림하여 m의 자리까지 출력. m은 소수점 아래 자릿수. (기본값은 0이며 1의 자리임)

    * 결국 m+1의 자리에서 버림한다는 의미

-- trunc

select 1234.5438, trunc(1234.5438) round1, trunc(1234.5438, 0) round2,

        trunc(1234.5438, 1) round3, trunc(1234.5438, -1) round4,

        trunc(1234.5438, 3) round5, trunc(1234.5438, -3) round6

3) floor ( n ) : 소수점 아래 버림

    * trunc(n)와 동일함

-- floor

select 1234.5438, floor(1234.5438)

from dual;

4) mod ( m, n ) : m을 n으로 나눈 나머지

    * 오라클에서 %는 like 함수의 와일드 카드로만 쓰임

-- mod

select 5 + 3, 5 - 3, 5 * 3, 5 / 3, mod(5, 3)

from dual;

5) abs ( n ) : 절대값

-- abs

select abs(5), abs(-5), abs(+5)

from dual;

2. 문자 함수

    * 함수에서는 문자함수와 변환함수가 가장 중요함

1)  대/소문자, 글자수

▷ lower ( char ) : 소문자

▷ upper ( char ) : 대문자    

▷ initcap ( char ) : 첫단어의 첫글자만 대문자

▷ length ( char ) : 글자수

select 'kiTRi', lower('kiTRi'), upper('kiTRi'), initcap('kiTRi KKK'), length('kiTRi')

from dual;

2) concat ( char1, char2 ) : 두 문자열을 결합

    * 인수 자리에 숫자나 날짜를 쓰더라도 오라클이 문자열로 형식변환하므로 에러는 나지 않음

    * 파이프 연산과 동일함

-- full name

select employee_id, first_name, last_name,

        first_name || ' ' || last_name "full name",

        concat(first_name,concat(' ' , last_name)) concat

from employees;

▷ 중첩함수 가능. 다만, 행의 형식과 수를 고려하여 써야 함

select employee_id, first_name, last_name,

        first_name || ' ' || last_name "full name",

        concat(first_name, concat(' ', last_name)) concat

from employees;

3) substr ( char, m, [n] ) : m번째 자리부터 길이가 n개인 문자열 반환 (m값은 1부터 시작)

    * n의 기본값은 전체 문자수임

    * n은 개수를 의미! (자리수 아님)

    * database의 index는 1부터 시작함!! (프로그램은 0부터 시작)

select 'hello oracle !!!', sustr('hello oracle !!!', 2, 6), sustr('hello oracle !!!', 2)

from dual;

4) instr ( char1, str1, [m], [n] ) : m번째 자리부터 시작하여 지정문자 str1이 n번째 검색된 위치를 반환

    * m, n의 기본값은 1임

select 'hello oracle !!!',

        instr('hello oracle !!!', 'o') instr, instr('hello oracle !!!', 'o', 6) instr1

from dual;

▷ 응용

-- 123-456, 123 zip1, 456 zip2 >>> 1234-56으로 바꾸어도 결과가 나오게

select '123-456' "zipcode",

        substr('123-456', 1,instr('123-456', '-')-1)zip1,

        substr('123-456', instr('123-456', '-')+1)zip2,

        '1234-56' "zipcode",

        substr('1234-56', 1,

        instr('1234-56', '-')-1)zip3,

        substr('1234-56', instr('1234-56', '-')+1)zip4

from dual;

3. 날짜 함수

1) 날짜 연산

    * 날짜 및 시간의 덧셈, 뺄셈은 가능. 

select sysdate, to_char(sysdate, 'yy/dd/mm hh24:mi:ss') "날짜와 시간",

        sysdate -3 "3일전", sysdate +3 "3일후",

        to_char(sysdate+3/24, 'yy/dd/mm hh24:mi:ss') "3시간 후",

        to_char(sysdate+3/24/60, 'yy/dd/mm hh24:mi:ss') "3분 후",

        to_char(sysdate+3/24/60/60, 'yy/dd/mm hh24:mi:ss') "3초 후"

from dual;

▷ months_between ( d1, d2) : d1과 d2사이의 달의 수를 number형 타입으로 반환

    * d1과 d2의 일자부분이 동일하거나, 해당 월의 말일인 때에만 정수로 반환됨

    * 양수: d1 > d2 / 음수: d1 < d2

- 양수 값

select sysdate, sysdate - 31,

        months_between(sysdate, sysdate -31) months_between

from dual;

- 음수 값

select sysdate, sysdate + 10,

        months_between(sysdate, sysdate -31) months_between

from dual;

- months_between은 특정 월의 실제 일수를 고려하지 않으므로, 일자를 계산할 때는 날짜의 연산으로 해결!

-- 두 일자 사이의 개월 수 조회

select months_between( to_date('2010.06.05', 'yyyy.mm.dd'),

                        to_date('2010.05.01', 'yyyy.mm.dd') )

from dual;

-- 두 일자 사이의 일수 조회

select to_date('2010.06.05', 'yyyy.mm.dd')

       - to_date('2010.05.01', 'yyyy.mm.dd')

from dual;

▷ add_months ( d, n ) : d의 날짜에 n개월을 더한 값을 반환

    * 특정 월의 일자 수(28/30/31)까지 고려하여 계산됨. 따라서 단순히 기준일에 30*개월수를 더하는 것과는 차이가 있음

-- 오늘을 기준으로 2개월 후

select sysdate 오늘, sysdate +30 *2 오답, add_months(sysdate, 2) 정답

from dual;

▷ next_day ( d, n ) : d의 날짜를 기준으로 다음 요일(n)가 몇 일인지 반환

    * 1: 일요일, 2: 월요일 ... 7: 토요일

▷ last_day ( d ) : d달의 마지막 날짜를 반환

    * first_day는 없음ㅋ

▷ 예제

select sysdate, months_between(sysdate, sysdate + 70)개월차,

        next_day(sysdate, 1)"다음 일요일", next_day(sysdate, 3)"다음 화요일",

        add_months(sysdate, 2)"2달 후",

        last_day(sysdate)"마지막 날"

from dual;

2) 날짜 형식

▷ 연/ 월/ 주/ 요일/ 일

    - mon : 영어 약자 월 / month : 영어 월 

    - w : 해당월의 주차 / ww : 해당년도의 주차

    - d : 요일_숫자/ dy : 영어 약자 요일 / day : 영어 요일

    - dd : 해당월의 날 수 / ddd : 해당 년도의 날 수

select sysdate, to_char(sysdate, 'yyyy') "4자리", to_char(sysdate, 'yy') "2자리",

        to_char(sysdate, 'mm') "월(숫자)",

        to_char(sysdate, 'mon') "월(문자_약어)", to_char(sysdate, 'month') "월(문자)",

        to_char(sysdate, 'w') 주차, to_char(sysdate, 'ww') 주차_연,

        to_char(sysdate, 'd')"요일_숫자",

        to_char(sysdate, 'dy')"요일_약문자", to_char(sysdate, 'day')"요일_문자",

        to_char(sysdate, 'dd')일자, to_char(sysdate, 'ddd')일자_연

from dual;

▷ 시간

    - hh : 12시간제 (hh12 동일) / am hh : 오전, 오후 표기된 12시간제(pm hh 동일)

    - hh24 : 24시간제

select to_char(sysdate, 'hh')"12시간제", to_char(sysdate, 'hh12')"12시간제",

        to_char(sysdate, 'am hh')"오전/오후 시간", to_char(sysdate, 'pm hh')"오전/오후 시간",

        to_char(sysdate, 'hh24')"24시간제",

        to_char(sysdate, 'mi')분, to_char(sysdate, 'ss')초

from dual;

▷ 응용 (round/trunc)

select to_char(sysdate, 'yyyy.mm.dd hh24:mi:ss')a,

        to_char(round(sysdate), 'yyyy.mm.dd hh24:mi:ss')b, -- 일을 기준으로 해서 시간에서 반올림

        to_char(round(sysdate, 'dd'), 'yyyy.mm.dd hh24:mi:ss')c, -- 위와 동일

        to_char(round(sysdate, 'mm'), 'yyyy.mm.dd hh24:mi:ss')d, -- 월을 기준으로 해서 일에서 반올림

        to_char(round(sysdate, 'yy'), 'yyyy.mm.dd hh24:mi:ss')e, -- 년을 기준으로 해서 월에서 반올림

        to_char(round(sysdate, 'hh'), 'yyyy.mm.dd hh24:mi:ss')f, -- 시를 기준으로 해서 분에서 반올림

        to_char(round(sysdate, 'mi'), 'yyyy.mm.dd hh24:mi:ss')g -- 분을 기준으로 해서 초에서 반올림

from dual

union

select to_char(sysdate, 'yyyy.mm.dd hh24:mi:ss')a,

        to_char(trunc(sysdate), 'yyyy.mm.dd hh24:mi:ss')b, -- 일을 기준으로 해서 시간에서 버림

        to_char(trunc(sysdate, 'dd'), 'yyyy.mm.dd hh24:mi:ss')c, -- 위와 동일

        to_char(trunc(sysdate, 'mm'), 'yyyy.mm.dd hh24:mi:ss')d, -- 월을 기준으로 해서 일에서 버림

        to_char(trunc(sysdate, 'yy'), 'yyyy.mm.dd hh24:mi:ss')e, -- 년을 기준으로 해서 월에서 버림

        to_char(trunc(sysdate, 'hh'), 'yyyy.mm.dd hh24:mi:ss')f, -- 시를 기준으로 해서 분에서 버림

        to_char(trunc(sysdate, 'mi'), 'yyyy.mm.dd hh24:mi:ss')g -- 분을 기준으로 해서 초에서 버림

from dual;

    * 첫번째 select 문을 세미콜른으로 닫지 않고 첫번째 from과 두번째 select 사이에 union을 쓰면 한 화면에 출력됨

4. 변환 함수

- 숫자를 문자로 바로 변환할 수는 없음

- 숫자 > 문자 / 문자 > 숫자 변환시에는 인수1과 인수2의 자릿수와 형태가 동일해야 함

	to_char		to_date
숫자	→ ←	문자	→ ←	날짜
	to_number		to_char

    * 컬럼의 이름은 숫자로 시작할 수 없음

▷ 자동형변환

- 오라클에서 문자열 합치기는 파이프 연산을 사용함. 따라서 연산 기호를 쓴다는 건 산술연산 뿐.

- 문자열 형식의 숫자와 숫자 형식의 숫자를 연산기호로 산술연산 시키면, 오라클에서 자동 형변환 되어 산술연산 결과값이 반환됨.

select 'a', 3, '3', 3 + 5, '3' + 5

from dual;

1) to_char ( n | d , char ) : 숫자 or 날짜를 특정 형식의 문자로 변환 (숫자 >> 문자 / 날짜 >> 문자)

▷ 숫자 형식

    * to_char 에서만 사용 가능

- 9 : 부족한 자릿수를 왼쪽 공란으로 채움

- 0 : 부족한 자릿수를 왼쪽 0으로 채움

- $ : 숫자 앞에 달러 기호 삽입

- L : 숫자 앞에 지역 화폐 기호 삽입

- . : 소수점 삽입

- , : 컴마 삽입

- MI : 음수인 경우 오른쪽에 - 삽입

- PR : 음수인 경우 오른쪽에 () 삽입

    * MI 와 PR 은 '9999mi' 와 같이 숫자 오른쪽에 입력해야 함

▷ 숫자 >> 문자

select 1123456.789,

        to_char(1123456.789, '000,000,000.00')a,

        to_char(1123456.789, '999,999,999.99')b, -- 백만단위 앞에 공간 두개가 있는 것임!

        length(to_char(1123456.789, '000,000,000.00'))c,

        length(to_char(1123456.789, '999,999,999.99'))d,

        to_char(1123456.789, '$999,999,999.99')e

from dual;

-- pr, mi

select to_char(-12, '99pr'), to_char(-12, '99mi')

from dual;

    * 숫자 형식지정을 생략해도 실행은 되나, 형식을 맞추어 작성할 것

▷ 날짜 >> 문자

select sysdate, to_char(sysdate, 'yy.mm.dd')a,

        to_char(sysdate, 'am hh:mi:ss')b,

        to_char(sysdate, 'hh24:mi:ss')c

from dual;

2) to_number ( char, n ) : 숫자 형태의 문자를 숫자로 변환 ( 문자 >> 숫자)

    * 인수 두개의 자릿수와 형태를 맞춰야 함. 숫자를 문자로 변환할 때도 마찬가지임

select '123,456.98', to_number('123,456.98', '000,000.00') + 3 a

from dual;

3) to_date ( char, d ) : 문자를 인수2 형태의 날짜로 변환 (문자 >> 날짜)

- 숫자를 바로 날짜로 변환할 수는 없음. 자동형변환이 적용되기는 하나 추천되지 않음

4) 응용문제

-- 20190225142154 >> 날짜 >> 3일 후 (정답: 2/28)

select to_char( (to_date(to_char(20190225142154, '00000000000000') ,'yyyy.mm.dd hh24:mi:ss'))+3, 'yyyy.mm.dd hh:mi:ss')

from dual;

select to_char( (to_date(to_char(20190225142154, '00000000000000') ,'yyyymmddhh24miss'))+3, 'yyyy.mm.dd hh:mi:ss')

from dual;

5. 일반 함수

1) nvl : null 값을 지정한 인수로 변환

▷ nvl ( char | n | d, n ) : null이면 인수2 값을 반환

▷ nvl2 ( char | n | d, m, n ) : null이 아니면 m 값을, null이면 n 값을 반환

select commission_pct, nvl(commission_pct, 0), nvl2(commission_pct, 1, 0)

from employees;

2) case when then else end : when을 충족하면 then, 해당 없으면 else의 각 인수를 반환

- end로 종결시키지 않으면 에러 발생함

- when then 을 늘려서 조건을 여러개 둘 수 있음

-- 연봉등급

-- 급여가 4000 미만인 사원은 저연봉

-- 급여가 10000 미만인 사원은 평균연봉

-- 급여가 10000 이상인 사원은 고연봉

-- 사번, 이름, 급여, 연봉등급

select employee_id, first_name, salary,

        case

            when salary < 4000

            then '저연봉'

            when salary < 10000

            then '평균연봉'

            else '고연봉'

        end 연봉등급

from employees

order by salary desc;

    > 차례로 걸러지는 형태이기 때문에 아래 예문의 경우 평균연봉의 조건이 4000 이상 10000미만일 필요 없음

▷ 응용

-- 사원구분

-- 1980년도 입사 임원

-- 1990년도 입사 평사원

-- 2000년도 입사 신입사원

-- 사번, 이름, 입사일, 사원구분

select employee_id 사번, first_name 이름, hire_date 입사일,

        case

            when to_char(hire_date, 'yyyy') < 1990  -- 아스키 코드값을 통해 비교연산이 가능함

            then '임원'

            when to_char(hire_date, 'yyyy') < 2000

            then '평사원'

            else '신입사원'

        end 사원구분

from employees;

>> 위 문제의 다른 풀이들

select employee_id 사번, first_name 이름, hire_date 입사일,

        case

            when to_number(to_char(hire_date, 'yyyy'), '0000') < 1990

            then '임원'

            when to_number(to_char(hire_date, 'yyyy'), '0000') < 2000

            then '평사원'

            else '신입사원'

        end 사원구분

from employees;

select employee_id 사번, first_name 이름, hire_date 입사일,

        case

            when substr(to_char(trunc(hire_date, 'yyyy'), 'yyyy'), 1, 3) = '198'

            then '임원'

            when substr(to_char(trunc(hire_date, 'yyyy'), 'yyyy'), 1, 3) = '199'

            then '평사원'

            else '신입사원'

        end 사원구분

from employees;

▷ 문자열의 비교연산도 가능. 문자의 아스키 코드값으로 비교됨

select case when 'a' < 'b' then '작다'

            else '크다'

        end

from dual;

select case when 'abc' < 'abe' then '작다'

            else '크다'

        end

from dual;

▷ 암기해야 할 ascii 코드 값 (아스키 코드값 48보다 작은 것은 없음. -1은 - 기호에 1이 붙은 것임)

- end.