SQL funtion

to_date

숫자/문자 -> 날짜 형변환 

select to_date('2022.04.17' , 'yyyy/mm/dd')
from dual;

select to_date('3월 4 02' , 'mon/dd/yyyy')
from dual;

 

 

round(number)
round(date) 

숫자값을 특정 위치에서 반올림하여 리턴한다.

ROUND 함수는 지정한 자리 이하에서 반올림한 결과 값을 반환한다.
ROUND(a,b)는 a를 소수점 이하 b+1자리에서 반올림하여 b자리까지 출력한다.

b가 없으면 소수점 이하를 출력하지 않으며, b값이 음수이면 소수점 왼쪽 b자리에서 반올림하여 출력한다.

select localtimestamp its
,round(sysdate, 'year') year 
,round(sysdate, 'month')month
,round(sysdate,'day') day

--숫자 
,round(3.33, 1)
,round(1932, -2)
from dual;

 

trunc(number) , trunc(date) 

TRUNC 함수는 지정한 소수점 자리수 이하를 절삭한 결과 값을 반환하는 함수이다.

TRUNC(a,b)는 a를 소수점 이하 b+1자리에서 절삭하여 b자리까지 출력한다.

b가 없으면 소수점 이하를 출력하지 않으며, b 값이 음수이면 소수점 왼쪽b 자리에서 절삭한 값을 출력한다.

select localtimestamp, 
    trunc(sysdate,'year')year 
    ,trunc(sysdate,'month')month
    ,trunc(sysdate, 'day')day
--숫자 
    ,trunc(12.2342, 2)
    ,trunc(12312,-3)
from dual;

 

ceil()-소수점 첫째자리에서 올림 
floor()- 소수점 첫째자리에서 버림 

select ceil(12.23423) , floor(18.2)
from dual;

 

months_between()-두개의 날짜간의 차이 

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

 

add_months() 특정 달 더하기 

select ename, hiredate 
    ,hiredate+100
    ,hiredate-100
from emp;

 

LAST_DAY()-해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수
NEXT_DAY()-해당일을 기준으로 명시된 요일의 다음 날짜를 변환하는 함수 

select localtimestamp, 
last_day(sysdate)"4월 마지막날 "
,next_day(sysdate, '월요일')
from dual;

 

 

날짜 추출함수 
extract 
to_char

select sysdate,
extract(year from sysdate)year
,to_char(sysdate,'yyyy')
from dual;

 

오라클은 자동형변환이 가능  문자->숫자
문자를 중심으로 날짜, 숫자로 변환이 가능하다. 

 

• 문자는 좌측정렬
• 숫자는 우측정렬

to_number()

문자->숫자

select '1234' a
,1234 b
,'1234'+1 c
,to_number('1234')+1 d
,'100.00'+1 e
,to_number('10,002','999,999')f
from dual;

 

to_char()

날짜,숫자->문자

select 3
    ,to_char(3)
    ,to_char(1234,'99,999') --  1,234
    ,12345678 a
    ,to_char(12345678 ,'9,999,9999')b -- 1,234,5678
     ,to_char(12345678 ,'l9,999,9999')c --        ￦1,234,5678
from dual;

원하는 형태로 날짜 출력 -to_char

select sysdate 
,to_char(sysdate,'yyyy "월" mm "월" dd "일" (day) am hh24:mi')
,to_char(to_date('2022년 4월 17일','yyyy "년" mm "월" dd "일"'),'day')
from dual;

 

 

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

select name, ssn
,to_date(substr(ssn,3,4),'mmdd')
,sign(trunc(sysdate)-to_date(substr(ssn,3,4),'mmdd'))
from insa;

select 
to_date(substr(ssn,3,4),'mmdd')
,sign(trunc(sysdate)-to_date(substr(ssn,3,4),'mmdd'))
from (select name, ssn from insa);

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

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

 

nullif()

두 개의 값을 비교하여 NULL 또는 두 개 값중 하나를 출력한다.

첫번째 값과 두번째 값을 비교하여 두 값이 같으면 NULL을 출력하고, 같지 않으면 첫번째 값을 출력한다.

【형식】
        NULLIF(expr1, expr2)

 

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

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

 

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

select name, ssn,
    decode(gender,1,'남자','여자')
from (select name, ssn, mod(substr(ssn,8,1),2)gender from insa);


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

 

coalesce()
나열된 값을 순차적으로 null이 아닌값을 출력 

SELECT COALESCE('A','B','C') AS COALESCE_TEST FROM DUAL
UNION ALL
SELECT COALESCE(NULL,'B','C') FROM DUAL
UNION ALL 
SELECT COALESCE(NULL,NULL,NULL) FROM DUAL;