SQL-7) 함수(function) - 3

안녕하세요 이승혁 입니다.

 

이번 포스팅에서는 SQL의 함수 중 단일행 함수인 날짜함수, 변환함수에 대한 실습을 진행해보도록 하겠습니다.

 

※ 내가 접속한 세션의 날짜 형식 확인하기

 

* : 모든 칼럼을 선택

nls : national language support

 

select * 
from nls_session_parameters;

NLS_DATE_FORMAT : RR/MM/DD

※ 날짜 형식 변경하기

alter session set nls_date_format='YY/MM/DD';

 

 

※ 날짜를 검색하기 전

RR형식과 YY형식 ?

 

              RR                                    YY

              81                                       81

      1981      2081                         1981     2081

 

기준 :     2020                                    2020

   기준에서 가까운 연도 선택           현재 세기를 선택

 

-> 년도를 입력할 때 2자리로 입력하지 말고 4자리로 입력하자는 암묵적인 규칙이 있다.

하지만 2자리로 입력해도 기술적으로 문제가 없도록 각 데이터베이스 소프트웨어 업체에서 마련한 기술 방안이 바로 RR년도 타입입니다.

 

! 사용자가 날짜를 검색할 때 지켜야할 준수 사항 !

- 날짜 검색 전 형식 확인

 

어느 나라의 DB에서 접속하던 날짜를 검색하려면 다음과 같이 한다.

튜닝전 SQL :

select ename,hiredate

 from emp

 where to_char(hiredate, 'RR/MM/DD') = '81/11/17';

-----> 가공되어 인덱스 사용 불가. 악성 SQL

 

튜닝후 SQL:

select ename,hiredate

 from emp

 where hiredate = to_date( '81/11/17', 'RR/MM/DD' );

 

※ 날짜함수

1. month_between : 날짜와 날짜 사이의 개월수를 출력하는 함수

2. add_months      : 날짜에 개월수를 더한 날짜를 출력하는 함수

3. next_day           : 특정 날짜에서 앞으로 돌아올 특정 요일의 날짜를 출력하는 함수

4. last_day            : 특정 날짜의 달의 말일을 출력하는 함수

5. sysdate             : 오늘 날짜를 보는 함수

select sysdate
from dual;

dual은 따로 만든 테이블이 아닙니다.

임시 테이블로 간단한 계산을 할 빈 노트 정도로 생각하시면 될 것 같습니다. sysdate +1 , +2 등으로 내일, 모레등의 날짜도 확인할 수 있습니다.

 

실습 1. 날짜 계산해보기, 오늘날짜에서 입사일을 빼 총 몇 일 근무했는지 확인해보기

select ename, trunc(sysdate-hiredate) as wrokingday
from emp;

가독성을 위해 버림(trunc) 함수, 칼럼별칭 사용

ㆍ단일행함수 - 날짜함수 add_months ( 날짜함수)

 

     특정 날짜에서 개월 수를 더한 날짜를 출력하는 함수

     add_months(날짜데이터,개월수)

 

실습 2. 오늘부터 10개월 후 날짜 확인해보기

select add_months(sysdate,10)
from dual;

 

ㆍ단일행함수 - 날짜함수 next_day

 

     특정 날짜 앞에서 앞으로 돌아올 요일의 날짜를 출력하는 함수

     next_day(날짜데이터,'x요일')

 

실습 3. 가장 가까운 미래의 수요일 날짜 확인하기

select next_day(sysdate,'수요일')
from dual;

오늘은 25일 일요일입니다.

ㆍ단일행함수 - 날짜함수 last_day

 

     특정 날짜의 달의 말일을 출력하는 함수

     last_day(날짜데이터)

 

실습 4. 10, 11, 12월의 마지막 날짜 확인해보기

select last_day('20/10/1'),last_day('20/11/1'),last_day('20/12/1')
from dual;

---

변환함수 : 데이터의 유형을 다른 데이터 유형으로 변환하는 함수

데이터 유형 : 문자형, 숫자형, 날짜형

 

※ 변환함수

1. to_char      : 문자형으로 변환하는 함수

2. to_number : 숫자형으로 변환하는 함수

3. to_date      : 날짜형으로 변환하는 함수

 

             to_char ->                <- to_char

숫자형  <------------ > 문자형  <-------------- >날짜형

           <- to_number               to_date ->

 

 

ㆍ단일행함수 - 변환함수 to_char

 

     숫자, 날짜형을 문자형으로 바꾸어주는 함수

     to_char( 날짜데이터, '요일의 형식 - yyyyy, year, mon, mm, day, dy, dd, d' )

     to_char( 숫자데이터, '숫자 형식 - 'L999,999,999, L999,999 등')

     L : Local 화폐 단위

     9 : 0~9사이의 숫자 의미, 자리수를 나타내기 위해 사용

select ename, to_char( birth, 'day') as "day" ,     -- ' 월요일'
              to_char( birth, 'YYYY') as "YYYY" ,    -- ' 1995'
              to_char( birth, 'YEAR')as "YEAR" ,    -- ' NINETEEN NINETY FIVE'
              to_char( birth, 'mm') as "mm" ,      -- ' 02 '
              to_char( birth, 'mon') as "mon" ,     -- ' 2월 '
              to_char( birth, 'dd') as "dd" ,      -- ' 28 '
              to_char( birth, 'dy') as "dy",      -- ' 화 '
              to_char( birth, 'd') as "d"        -- 일:1 월:2 화:3 수:4 목:5 금:6 토:7						
from emp11
where ename='이승혁';

실습 5. 월~금 순으로 출력되게 해보기

select replace(ename,substr(ename,-2,1),'*') name,to_char(birth,'dy') as dy
from emp11
order by to_char(birth-1,'d');

정렬 순서의 기준을 'dy'형식이 아닌 'd'로 작성했습니다.

일요일을 1로 시작하기 때문에 숫자 형태로 비교하고 싶어서 'd'로 변경해 구현했습니다.

일요일을 가장 뒤로 보내기 위해 자신의 실제 생일 - 1 을 to_char의 변수로 넣었습니다.

 

실습 6. 숫자형 -> 문자형 변경해보기, 이름, 월급*23450 3자리 콤마(,) 부여해서 출력해보기

select ename 이름, to_char(sal*23450, '999,999,999') 월급
from emp;

L을 붙여서 출력한 결과

 

 

 

ㆍ단일행함수 - 변환함수 to_number

 

      문자형을 숫자형으로 형변환하는 함수

      to_number('문자형데이터')

 

3000과 '3000'은 다르다. 첫 3000은 '삼천'이라는 숫자의 의미를 가진다.

하지만 두번째 '3000'은 그냥 글자 그 자체를 의미한다.

숫자로써의 의미가 아닌 단지 '3' 문자 1개와 '0' 문자 3개를 의미한다.

set autot on
select *
from emp
where sal='3000';

위 쿼리는 원래라면 결과가 출력되지 않아야 합니다.

sal 칼럼의 데이터는 전부 숫자형 데이터입니다.

우측의 조건으로 넣은 '3000'은 문자형 데이터 입니다.

결과가 검색되어 출력된 이유는 옵티마이저가 3000의 싱글 쿼테이션 마크를 알아서 제거했기 때문입니다.

set autot on 코드를 추가로 작성하시면 확인할 수 있습니다.

 

set autot on
select ename, sal
from emp
where sal like '30%';

숫자형의 우선순위가 문자형보다 높지만, %를 숫자형으로 바꿀 수 없기 때문에 숫자형인 sal을 문자형으로 변형시켰다.

겉으로는 가공되지 않았지만, 내부 실행 코드를 확인하면 가공되어 실행되는 것을 확인할 수 있다.

 

위의 결과는 나중에 배울 함수 기반 인덱스로 해결 할 수 있습니다.

 

※ 함수 기반 인덱스 생성

create index emp_sal_fun

on emp( to_char(sal) );

 

※ 좌변을 가공하지 말아야 검색 속도가 빨라진다.

※ 좌변에 있는 칼럼이 인덱스 칼럼이면, 가공 시 인덱스를 사용하지 못하게 되어 검색 속도가 느려진다.

 

※ 형 변환

 

1. 암시적 형변환 : 오라클이 알아서 데이터 유형을 변환하는 것. 모든 경우에 다 변환하는 것은 아니다.

                        아래와 같은 몇 가지 경우에만 변환을 한다.

ex) select ename, sal                             select ename, sal

      from emp                 -------->         from emp

      where sal = '3000';                          where sal = 3000;

             숫자 = 문자                                   숫자 = 숫자

 

select ename, sal                          select ename, sal

 from emp                --------->        from emp

 where sal like '30%'                       where to_char(sal) like '30%'

        숫자     문자                                   문자             문자

 

2. 명시적 형변환 :

    - to_char

    - to_date

    - to_number

 

to_char를 가장 많이 사용한다.

to_char를 사용하는 경우는 to_char(sal,'999,999') / to_char(hiredate,'RRRR')='1981' 과 같은 경우에 많이 사용한다.

 

이번 포스팅에서는 날짜 함수와 변환 함수에 대해서 알아보았습니다.

많은 내용을 다룬 것은 아니지만 조금 헷갈릴 수 있는 내용도 있을 수 있다고 생각합니다.

너무 욕심내지 말고 조금씩 충분히 이해하고 내것으로 만든 후 다음 학습으로 넘어가는 것이 중요합니다.

 

읽어주셔서 감사합니다.