[days05] SQL function2

1. 2020년 09월 15일 개강 후 오늘 날짜까지의 일수 출력

select sysdate - '2020.09.15'
--ORA-01722: invalid number 
--날짜-날짜를 해야하는데 '2020.09.15'를 문자로 인식한다
-- 숫자,문자 -> 날짜 형변환 필요 to_date()
from dual;

 

2. round(number) / round(Date)
- 정오를 기준으로 날짜를 반올림하여 리턴한다
- 형식 ROUND( date [,fmt] )

select localtimestamp lts 
        ,round(sysdate, 'year') year --21/01/01 연도 (11월이 6월보다 지났으니 연도 반올림)

         ,round(sysdate, 'month') month --20/11/01

         ,round(sysdate, 'day') day --20/11/01 (오후에는 20/11/08) 
        ,round(sysdate) r --20/11/04 일 밑에꺼 반올림(오후에는  20/11/05) 
from dual;

출력값 (오후6시)

- 일을 반올림할 때 정오를 넘으면 다음날 자정을 출력하고, 넘지 않으면 그 날 자정을 출력한다. 
- 월을 반올림하는 경우는 15일 이상이면 다음 달 1일을 출력하고, 넘지 않으면 현재 달 1일을 출력한다. 
- 년을 반올림하는 경우에는 6월을 넘으면 다음해 1월1일을 출력하고, 넘지 않으면 그 해 1월1일을 출력한다.

 

 

3. trunc(number) / trunc(Date)

- 날짜에서 시간 부분을 절삭해 00:00으로 바꿔줌
- ex) 이벤트 기간 2020.1.1 00:00:00~ 2020.12.12 00:00:00처럼 시간은 중요하지 않을때 절삭 많이함 
- 형식 : TRUNC (date, [.fmt] )

select hiredate --81/11/17 
        ,trunc(hiredate, 'year') y --81/01/01 
        ,trunc(hiredate, 'month') m --81/11/01 
        ,trunc(hiredate, 'day') d --81/11/15 위의 3개는 잘 쓰진 않음 
        ,trunc(hiredate) n --81/11/17 (시간이 절삭됐다) 
from emp 
where ename = 'KING';

- 날을 절삭하면 그날 자정을 출력하고,
- 월을 절삭하면 그 달 1일을 출력하고, 
- 년을 절삭하면, 금년 1월1일을 출력한다.
- TRUNC 함수는 시간 정보가 중요하지 않은 경우에 시간 정보를 생략하고 입력할 때 편리하다.

 

ROUND 함수를 이용한 반올림과 TRUNC함수를 이용한 버림이 날짜 데이터에도 적용 가능하다는 것 꼭 기억

 

 

4. months_between()

- 두개의 날짜간의 달 차이를 리턴하는 함수

select ename, hiredate 
        ,sysdate 
        ,ceil(sysdate - hiredate) 근무일수 
        ,round( months_between(sysdate, hiredate))근무개월수 
        ,floor(round( months_between(sysdate, hiredate))/12) 근무년수 
from emp 
where deptno=10;

5. add_months() 

- 특정수의 달을 더한다

select ename, hiredate 
        ,hiredate + 100 --입사일로부터 100일 후의 날짜 
        ,hiredate - 100 
        ,add_months(hiredate, 3)--입사후 3개월 
from emp;

 

6. last_day()

- 특정 날짜가 속한 달의 가장 마지막 날짜 반환

select sysdate 
        ,last_day(sysdate) november --2020 11월의 마지막 날짜 객체 가져오기 
        ,last_day(sysdate) one -- 이번달의 마지막 날
        ,to_char(last_day(Sysdate),'d') two --2 주중일 (30일이 그 주의 2번째 일이다)
        ,to_char(last_day(Sysdate),'dd') three --30 월중일 
        ,to_char(last_day(Sysdate),'ddd') four --335일 연중일     
from dual; 

7. next_day()

- 명시된 요일이 돌아오는 가장 최근의 날짜를 리턴
- 풀네임이나 약어요일이름 다 사용 가능

select sysdate 
        ,next_day(sysdate, '토요일') -- 20/11/07 
         ,next_day(sysdate, '수') --20/11/11 
from dual;

8. extract()

- 날짜에서 원하는 정보를 얻어오는 함수

select sysdate 
        ,extract( year from sysdate ) A --2020 
        ,to_char(sysdate, 'yyyy') A2 
        ,extract( month from sysdate ) B --11 
        ,to_char(sysdate, 'MM') B2 
        ,extract( day from sysdate ) C --4 
from dual;

 

형변환 함수

오라클의 경우, EMPNO+'500'를 실행하면 숫자로 출력된다.

'500'은 문자 데이터지만 숫자 자료형인 사원 변호 열 값과 수치 연산이 가능한 것은 자동 형변환 즉, 

'암시적 형변환' 이 발생했기 때문이다. 그러나 'ABCD'+EMPNO를 하게되면 오류가 발생하는데

이 이유는 숫자처럼 생긴 문자 데이터는 숫자로 바꿔주지만 그 외의 경우는 잘 동작하지 않기 때문이다.

오라클에서 자료형이 자동으로 변환되는 방식이 아닌 사용자가 자료형을 직접 지정해주는 방식을 '

명시적 형변환' 이라고 한다. (to_char, to_number, to_date)

 

숫자데이터(NUMBER) <-> 문자데이터(CHARACTER) <-> 날짜데이터(DATE)

문자를 중심으로 숫자 또는 날짜 데이터의 변환이 가능하다.

 

9. to_number

- 문자->숫자

- 오라클은 자동 형변환되니까 to_char() 별로 쓸 필요 없다

- '1,300' - '1,500' 처럼 숫자 데이터가 가공된 문자 데이터로 저장되어 있고 이를 산술 연산에 사용하고자 할 경우 사용

    to_number('1,300' , '999,999') -  to_number('1,500' , '999,999')

select '1234' A --문자 좌측정렬 
        ,1234 B --숫자 우측정렬 
        ,'1234'+1 C -- 우측정렬, 숫자 
        ,to_number('1234')+1 D --숫자 
        ,'100.00'+1 E --숫자 
        ,to_number('100.00', '9G999D99')+1 F --101 --똑같으니 굳이 G, D 할필요 없겠네 
from dual;

10.  to_char(number)

- 날짜, 숫자->문자 형변환
- TO_CHAR( n [,'fmt' [,'nlsparam']]) []은 생략가능

select 3 --숫자 
        ,to_char(3) --문자 
        ,1234567  
        ,to_char(1234567, '9,999,999') A --천자리마다 , 
        ,to_char(1234567, 'L9,999,999.99') B --천자리마다 , 
        --L : 그 지역에서 사용하는 통화 심볼 우리는 \
        --9: 숫자의 한 자리 의미(빈자리 채우지 않음)
        --0: 빈자리를 0으로 채움
from dual;

select to_char(3,'0009')   --0003 
        , to_char(345,'$9099.999') --  $345.000 
        , to_char(45,'$9099.999') --   $045.000 
from dual;

  1) to_char - 연봉을 원하는 형식으로 출력하는 예제 : $99,999,999

select ename, sal, comm 
        ,to_char( (sal+comm)*12 , '$99,999,999') 
from emp 
where comm is not null;

  1-2)  '2020년 11월 04일(수요일) 오후 14:50' 형태로 출력

select sysdate 
        ,to_char(sysdate, 'yyyy"년" mm"월" dd"일" (DAY)AM hh24:mi') --AM/PM은 노상관 
from dual;

  2) to_char(날짜) - 날짜->내가 원하는 정보만을 문자 형변환

select sysdate 
        ,to_char(sysdate, 'q') --4분기 (10~12월) 
        ,to_char(sysdate, 'cc') --21세기 
from dual;

  3) to_char(시간) - 시간 분 초 출력

select to_char(sysdate, 'hh') hh --12시간 
        ,to_char(sysdate, 'hh24') hh24 --24시간 
         ,to_char(sysdate, 'mi') mi --분 
          ,to_char(sysdate, 'ss') ss --초 
           ,to_char(sysdate, 'ssss') ssss --자정부터 지금까지 지난 초(0∼86399)
from dual;

  4) to_char() - 요일과 일 출력 / 몇째주인지 출력

select to_char(sysdate, 'DY') dy  --수 
        ,to_char(sysdate, 'DAY') day  --수요일 
        ,to_char(sysdate, 'D') d --4 주중의 일 
        ,to_char(sysdate, 'DD') dd  --04 해당 월의 일 
        ,to_char(sysdate, 'DDD') ddd -- 309 해당 연도의 일
from dual;

--(암기) W(월중 몇째주), WW(년중 몇째주) , IW

select to_char(sysdate, 'W') --1 
        ,to_char(sysdate, 'WW') --45 
        ,to_char(sysdate, 'IW') --45 
from dual;

 

11.  to_date

 개강일(20/09/15)로부터 오늘까지 며칠이 흘렀냐 ?

 '2020/09/15' 문자 -> 날짜 형변환 필요
  --to_date(char, [format], [nlsparam])

select  
    sysdate - to_date('2020/09/15')    -- 1
    ,sysdate - to_date('2020.09.15')    -- 2
    ,sysdate - to_date('2020-09-15')   -- 3
    ,sysdate - to_date('20200915')      -- 4
    ,sysdate - to_date('2020년09월15일') --얜 인식불가 
    ,sysdate - to_date('2020년09월15일', 'yyyy"년"mm"월"dd"일"') -- 5
from dual; 

1,2,3,4,5 모두 같은 값 출력

(기억)
- '2020' -> 날짜형 to_date() 함수 사용해서 변환

select to_date('2020', 'yyyy') y  --20/11/01 현재년도의 1일 날짜로 만들어지더라 
        ,to_date('11', 'mm') m --20/11/01  얘도 
        ,to_date('08', 'dd') d --20/11/08 현재연도, 월가지고 08일 출력 
        ,to_char(to_date('201008', 'yyyymm'), 'DAY') dow --일요일   (2010년8월1일) 
from dual;

 

주의

YY와 RR의 차이 : YY는 어떤 두 자리 수가 입력되어도 현 시점의 년도와 동일한 연도로 계산되고 RR은 현 시점의 연도의 끝 두자리수가 00~49, 50~99 그리고 입력된 수치가 00~49, 50~99인 경우를 계산하여 비교적 가까운 날짜 데이터를 계산한다. (현재 20년이기 떄문에 두자리 RR에 50 이상의 연도를 인식시키면 1950년대가 된다.)

 

문제 

1) insa테이블에서 ssn을 통해서 생년월일 얻어오고 오늘날짜 기준으로 지났는지 체크

select name, ssn 
        ,to_date(substr(ssn, 3,4), 'mmdd') 생일 
        ,sign(trunc(sysdate) - to_date(substr(ssn, 3,4), 'mmdd')) 지났는지 
        --시간은 체크할 필요가 없다-> trunc 
from insa;

 

2) emp에서 각 사원들의 입사일자를 기준으로 10년 5개월 20일째 되는 날은?

select ename, hiredate 
        ,add_months(hiredate, 10*12+5)+20 
from emp;

 

11. nullif()

- 첫번째 인자값==두번째 인자값이면 null반환

select nullif('aa', 'aa') -- null    첫번째 인자값==두번째 인자값이면 null반환 
        ,nullif('aa', 'AA') -- aa     다르면 앞의 값 반환 
from dual;

 

 

12. decode()

= 프로그램 언어의 if문
- 비교연산은 '='만 가능하다 (크다작다x 같냐만 물어볼 수 있음)
- JAVA
  if(i==j){
     return 'T';
  }

 decode(i, j, 'T');

- if(A==B){
     return 'T';
  }else if(A==C){
    return 'S';
  }else{
     return 'X';
  }
 decode(A, B, 'T', C, 'S', 'X');

 

ex1 ) emp 사원번호 10(10%) 20(20%)그 외 25%인상

select deptno, ename, sal
        ,decode(deptno, 10, sal*1.1, 20, sal*1.2, sal*1.25)
from emp;

ex 2) insa테이블에서 남자라면 '남자' 아니면 '여자' 칼럼명 gender

select name, ssn
        ,substr(ssn,8,1)
        ,decode(mod(substr(ssn,8,1),2), 1, '남자', '여자') gender
        ,case mod(substr(ssn,8,1),2)
            when 1 then '남'
            else '여'
        end 성별
        ,case 
            when substr(ssn,8,1) in(1,3,5,7,9) then '남'
            else '여'
        end 성별
from insa;

13. case()

- case()함수는 decode()함수와 동일한 함수 + 확장(범위 비교 가능)
- 케이스문 쓸때는 무조건 case -> end alias 적고 시작 !!!

 

ex) emp 사원번호 10(10%) 20(20%)그 외 25%인상

select deptno, ename, sal
        , case deptno
            when 10 then sal*1.1
            when 20 then sal*1.2
            else sal*1.25
        end "인상된sal"
        
        ,case 
            when deptno=10 then sal*1.1
            when deptno=20 then sal*1.2
            else  sal*1.25           
        end "인상된sal-2"
        
       , case 
        when 0<=sal and sal<=1000 then 'C'
        when sal between 1001 and 2000 then 'B'
        --when 1001<=sal and sal<=2000 then 'B'
        when 2001<=sal then 'A'
        end grade
from emp
order by deptno;

14. coalesce()

- 나열된 값을 순차적으로 체크하여 NULL이 아닌 값을 출력한다

select sal+nvl(comm,0) pay
        ,sal+nvl2(comm, comm,0) pay
        ,coalesce(sal+comm, sal, 0) pay
from emp;