K_Digital_Step1_Oracle SQL_03_1

오늘은 OracleDB 지난 포스팅에서 문자관련함수에 이어서 숫자관련함수를 해보도록 하겠습니다.

숫자 관련 함수

round 함수

• 반올림 함수
• round(컬럼 || 숫자, 표시되는 소숫점이하 자리수)

  select round(12.5123) r1, round(12.5123,0) r2, round(12.5123, 1) r3,
  round(12.5123, 2) r4, round(12.5123, -1) r5 from dual;

위 코드 출력값을 보면 r1, r2는 소숫점 0자리까지 출력, r3는 1까지, r4는 2까지 출력한다.
여기서 r5는 소숫점 이하에서 하나 앞으로 앞당겨서 반올림을 진행한다. 즉, 10의 자리까지 출력하기때문에 10이라고 출력된다.

trunc함수

• 버림 함수
• trunc(컬럼 || 숫자, 표시되는 소숫점이하 자리수)

  select trunc(12.5123) r1, trunc(12.5123,0) r2, trunc(12.5123, 1) r3,
  trunc(12.5123, 2) r4, trunc(12.5123, -1) r5 from dual;

위 코드 출력값을 보면 round에서 반올림한 값들을 모두 버림으로 출력한 것이다.

mod함수

• 나머지 함수
• mod(숫자, 숫자)

  select mod(12,10) from dual;

위 코드는 12를 10으로 나눈 값의 나머지를 출력해준다.

근사정수함수

• ceil(숫자 || 컬럼) : 큰 근사정수
• floor(숫자 || 컬럼) : 작은 근사정수

  select ceil(12.345),floor(12.345),ceil(-12.345),floor(-12.345) from dual;

위 코드에서 ceil은 가장 가까운 정수 2가지 중 큰 값을 floor은 작은 값을 출력한다.

power 함수

• 제곱 함수
• power(밑, 제곱)

  select power(2,3) from dual;

위 코드는 값이 8로 2를 3제곱한 값을 출력한다.

abs 함수

• 절대값 함수
• abs(숫자 || 컬럼)

  select abs(-5) a1, abs(5) a2 from dual;

위 코드는 음수 양수를 절대값으로 출력한다.

sign 함수

• 부호 함수
• 1 : 양수, -1 : 음수, 0 : 0

  select sign(10),sign(-10),sign(0) from dual;

날짜 관련 함수

sysdate 함수

• 현재 날짜를 리턴
• 예약어

  select sysdate from dual;

위 코드는 오늘 날짜가 출력될 것이다.

months_between 함수

• 두 날짜 사이의 개월수 리턴
• 소숫점도 표현됨
• months_between(날짜1, 날짜2) : 날짜1 - 날짜2 차이의 개월수

  select months_between(sysdate,'22/01/01') from dual;
  select round(months_between(sysdate,'22/01/01')) from dual;
  select trunc(months_between(sysdate,'22/01/01')/12) from dual;

위 코드는 현재 날짜부터 22년01월01까지 개월수를 구하는 코드이다.
출력값을 보면 소숫점도 출력된다는 것을 확인할 수 있다.
만약 여기서 개월수를 정수로 표현할 경우 round()와 trunc()함수로 출력하고,
월 단위가 아닌 년 단위로 출력할 경우 해당 값을 12로 나누어 출력해준다.

select sysdate-'22/01/01' from dual;

위 코드와 같이 입력하면 개월수가 아닌 두 날짜 사사의 일자를 구할 수도 있다.

add_months 함수

• 개월수를 더한 후의 날짜 리턴
• add_months(날짜, 개월수)

  select sysdate, add_months(sysdate,3) "3개월후", add_months(sysdate,-3) "3개월전" from dual;

위 코드는 현재일 기준 3개월 후, 3개월 전의 날짜를 출력한다.
만약 개월수를 뺄 경우 날짜는 전으로 출력된다.

select sysdate, sysdate+3 "3일후", sysdate-3 "3일전" from dual;

위 코드와 같이 만약 일 단위로 계산할 경우 연산을 통해 계산하여 출력할 수 있다.

round(날짜), trunc(날짜)

• round와 trunc함수의 날짜를 넣어서 기입가능

  select round(sysdate), turn(sysdate) from dual;

위 코드에서 현재 날짜가 12시가 지날경우 round는 다음 날짜, trunc는 현재날짜를 출력할 것이다.

next_day 함수

• 날짜 다음의 지정한 요일에 해당하는 날짜 리턴
• 일:1, 월:2, 화:3 ... 토:7
• next_day(날짜, 요일)

  select sysdate, next_day(stsdate,'토'), next_day(sysdate,2) from dual;

위 코드를 출력하면 next_day(stsdate,'토')에서는 다음주 토요일을 next_day(sysdate,2)는 다음주 월요일을 출력할 것이다.

last_day

• 해당 월의 마지막 날짜 리턴

  select sysdate, last_day(sysdate) from dual;

위 코드는 지금 날짜에 해당하는 달의 마지막 날짜를 출력한다.

형변환함수

자료형을 변환하는 함수

묵시적형변환

• 오라클이 자동으로 변환함
• 성능 취약해짐

  select 1 + '1' from dual;

위 코드는 숫자 1과 문자 1을 더하지만 오라클에서는 묵시적으로 문자를 숫자로 변환하여 연산해 출력해준다. (오라클에서 가능)

to_char()

• 숫자나 날짜형을 지정된 형식에 맞도록 문자열로 변형해주는 함수

1. 날짜 형식 문자
   yyyy : 연도를 4자리로 표시.
   yy : 연도를 2자리로 표시.
   rrrr : 2000년 이후에 Y2K버그로 인해 추가된 연도 4자리로 표시.
   rr : 2000년 이후에 Y2K버그로 인해 추가된 연도 2자리로 표시.
   year : 영문으로 년도 표시 mm : 월을 2자리로 표시
   mon : 영문표시 할때 약자표시. FEB 한글은 상관없다
   month : 영문표시 할때 전체 월 표시. 한글은 상관없다 dd : 일을 2자로 표시
   day : 요일 표시.
   ddth : 몇번째 날짜인지 표시 시간
   hh : 하루 12시간.
   hh24 : 하루 24시간
   mi : 분
   ss : 초
   am : 오전/오후

   select sysdate, to_char(sysdate,'yyyy-mm-dd hh:mi:ss am day') from dual;

위 코드는 년, 월, 일 날짜를 출력하고 시,분,초와 오전, 요일을 출력한다.

select to_char(sysdate, 'yyyy') 년도1,  
	   to_char(sysdate, 'rrrr') 년도2,
       to_char(sysdate, 'yy') 년도3,    
       to_char(sysdate, 'year') 년도4,
       to_char(sysdate, 'rr') 년도5,
       to_char(sysdate, 'mm') 월1,
       to_char(sysdate, 'mon') 월2, --약자표시
       to_char(sysdate, 'month') 월3, 
       to_char(sysdate, 'dd') 일1, -- 일자
       to_char(sysdate, 'day') 일2, --요일
       to_char(sysdate, 'ddth') 일3, --
       to_char(sysdate, 'hh') 시간1, -- 1 ~ 12
       to_char(sysdate, 'hh24') 시간2, -- 0 ~ 23
       to_char(sysdate, 'mi') 분1,   -- 분
       to_char(sysdate, 'ss') 초1,   -- 초
       to_char(sysdate, 'am') AM     -- 오전,오후
    from dual;

to_char(숫자,'형식문자')

select to_char(1234.56,'9999999') 숫자1, --정수 7자리. 반올림
       to_char(1234.56,'0999999') 숫자2, --정수 7자리 빈자리 0으로 채움
       to_char(12.3456,'$9999.99') 숫자3, -- $표시, 소숫점이하 2자리출력 
       to_char(12.3456,'9999.99') 숫자4,  -- 소숫점이하 2자리출력         
       to_char(1234.3456,'9,999.99') 숫자5 -- 세자리마다 , 표시하고 소숫점이하 2자리 출력
from dual

to_number

• 명시적형변환
• 문자 -> 숫자
• 가독성이 좋으며, 성능 취약성 감소

  select '12'+10 from dual; -- 묵시적형변환
  select to_numer('12')+10 from dual -- 명시적형변환

위 코드에서 묵시적형변환과 달리 명시적형변환은 오새라클에서 자동으로 바꿔주는것이 아닌, to_number를 사용하여 명시적으로 형변환을 기입해준다.

to_date

• 문자 -> 날짜형
• '20220816' => 날짜형태로 변환

  select to_date('20220816','yyyymmdd') from dual;

위 코드는 8자리 숫자로 입력한 년월일을 yy/mm/dd로 출력한다.

일반함수

nvl

• null 값을 다른 값으로 변환하는 함수
• nvl(숫자||컬럼, 값)
• 숫자나 컬럼이 null이라면 값으로 변환한다.
• 이때 null이 아니면 숫자||컬럼을 그래도 출력한다.

  select nvl(null,0) from dual;

위 코드에서 nvl에서 null값을 0으로 전환한다
이때 이전에 union을 이용해 사용하던 코드를 nvl을 통해 한줄로 코드를 작성할 수 있다.

조건함수

decode 함수

• decode(컬럼명,값,참,거짓)
• 해당 컬럼에서 값이 참과 거짓일 경우 출력할 값을 변환함

  -- 교수테이블에서 교수이름, 학과번호, 학과명 출력하기
  -- 학과명이 101인 경우, '컴퓨터공학'으로 그외는 공란으로 출력하기
  select name,deptno,decode(deptno, 101,'컴퓨터공학과','') 학과명 from professor;

위 코드는 deptno=101이면 컴퓨터공학과를 아니면 공백을 출력하는 코드이다.

select name,deptno,decode(deptno, 101, '컴퓨터공학', 102,'멀티미디어공학', 201, '기계공학','기타학과')
from professor;

위 코드는 그전과 다르게 참인 경우를 여러가지 넣었다.
학과명이 101인 경우 컴퓨터공학으로 102인 경우 멀티미디어공학 201인 경우 기계공학
그외는 기타학과로 출력하는 조건에 코드였다.
이때 그외 거짓인 값을 넣지 않은 경우도 가능하다.

case 함수

• 조건문
• 범위 지정 가능

select name, tel, case substr(tel,1,instr(tel,')')-1)
when '02' then '서울'
when '051' then '부산'
when '052' then '울산'
else '기타' end 지역명 from student;

위 코드는 학생의 이름, 전화번호, 지역명 출력한다. 이때 지역명은 전화번호의 지역구분에 따라 출력하는 데 이때 case함수를 사용한다.
case문에 끝은 end로 끝난다.
만약 decode만 사용할 경우 아래와 같은 코드가 나온다.

select name, tel, decode(substr(tel,1,3), '02)', '서울', '051', '부산', '052', '울산','기타') from student;

이때!! case문은 범위가 지정 가능하다 하였다!!
범위를 지정할 경우 when 다음에 between, in, 범위연산자 등으로 범위 지정을 하여 조건을 달아 줄 수도 있다.