[SQL] 2. 단일행함수(숫자, 날짜, 형변환 함수)

숫자 함수

함수명
ROUND	반올림 한 후 출력	ROUND(12.345, 2)    ->  12.35
TRUNC	버림 한 후 출력	TRUNC(12.345, 2)     ->  12.34
MOD	나머지 값 출력	MOD(12, 10)             ->  2
CEIL	가장 근접한 큰 정수 출력	CEIL(12.345)             ->  13
FLOOR	가장 근접한 작은 정수 출력	FLOOR(12.345)         ->  12
POWER	제곱승 출력	POWER(3, 2)            ->  9

ROUND(숫자, 자리수) : 주어진숫자를 반올림 후 출력. 소수점기준(자리수)

TRUNC(숫자, 자리수) : 버림.

MOD() : 나머지값 출력 /CEIL() : 가장 가까운 큰정수 출력 / FLOOR() : 가장 가까운 작은정수

• ex) CEIL 함수의 경우 여러 데이터들을 하나의 기준으로 묶을 때 주로 사용함. 아래의 예는 emp 테이블의 13 건의 데이터를 출력하되 3 줄씩 나누어서 한 조로 만드는 예.

  SQL> SELECT rownum "ROWNO", CEIL(rownum/3) "TEAMNO", ename    //rownum은 출력 줄번호
  2  FROM emp;
  
  ROWNO     TEAMNO ENAME
  ----- --------- ----------
   1          1 SMITH
   2          1 ALLEN
   3          1 WARD
   4          2 JONES
   5          2 MARTIN
   6          2 BLAKE
   7          3 CLARK
   8          3 SCOTT
   9          3 KING
  10          4 TURNER
  11          4 JAMES
  12          4 FORD
  13          5 MILLER 

POWER(2,3) : 2의 3승 값을 출력

날짜 함수

SYSDATE : 현재 시스템의 시간을 출력

• 윈도우와 유닉스(리눅스)의 날짜형식 다름

  SQL> SELECT SYSDATE FROM dual;  //리눅스형식
  SYSDATE
  -----------
  30-AUG-16
  

• 윈도우 날짜형식으로 변환가능

  SQL> ALTER SESSION SET NLS_DATE_FORMAT='YY/MM/DD';
  Session altered. 
  SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS';
  

MONTHS_BETWEEM() : 두날짜 사이의 개월수를 출력

• 두 날짜중 큰 날짜를 먼저 써야 양수가 출력
• 두 날짜가 같은 달에 속해 있으면 특정 규칙으로 계산된 값이 출력(같은 1개월이라도 29일,30일,31일 같은 경우마다 모두 결과값이 다를것)

ADD_MONTHS() : 주어진 날짜에 숫자만큼의 달을 추가

NEXT_DAY() : 주어진날짜 기준으로 돌아오는 가장 최근 요일의 날짜를 반환

LAST_DAY() : 주어진날짜가 속한 달의 가장 마지막 날을 출력

ROUND() : 정오를 넘겼을경우 다음 날짜로 출력하고 안넘겼을 경우는 당일로 출력

TRUNC() : 무조건 당일로 출력

형변환 함수

묵시적(자동) 형 변환 : 오라클이 자동으로 형을 바꿔줌. 튜닝에서 뜻하지 않게 속도가 느려지는 주범이 될 수 있으므로 조심할것 (속도가 느려지는 이유는 해당 컬럼에 인덱스가 생성되어 있을 경우 묵시적 형 변환이 생기면 그 인덱스를 못쓰게 되고 그렇게 되면 Index Suppressing error 발생) / 숫자처럼 생긴 문자만 변환해줌.

명시적(수동) 형 변환 : 형변환 함수를 통해 수동으로 데이터 형을 바꾸는것. 

형변환 함수는 위 그림처럼 TO_CHAR, TO_NUMBER, TO_DATE 세가지로 나뉘고, TO_CHAR 함수는 숫자와 날짜를 문자로 변환해주는 두가지 기능을 한꺼번에 가짐.

데이터 타입
CHAR(n)	고정길이의 문자를 저장. 최대값 2000 bytes
VARCHAR2(n)	변하는 길이의 문자를 저장. 최대값 4000 bytes
NUMBER(p,s)	숫자 값을 저장. p 는 전체 자리수로 1-38 자리까지 가능하고 s는 소수점 이하 자리수로 -84 ~ 127 자리까지 가능
DATE	7Byte로 BC 4712년 1월 1일부터 AD 9999년 12월 31일까지의 날짜를 저장.
LONG	가변 길이의 문자를 저장. 최대 2GB 까지 저장
CLOB	가변 길이의 문자를 저장. 최대 4GB 까지 저장 할 수 있음
BLOB	가변 길이의 바이너리 데이터를 최대 4GB 까지 저장 할 수 있음
RAW(n)	원시 이진 데이터로 최대 2000 bytes 까지 저장
LONG RAW(n)	원시 이진 데이터로 최대 2GB까지 저장
BFILE	외부 파일에 저장된 데이터로 최대 4GB 까지 저장

• TO_CHAR(날짜 or 숫자,'원하는형태')
  • ex) TO_CHAR(sysdate,'RRRR-MM-DD:HH:MI:SS')
  • ex) TO_CHAR(12345, '99,999') : 12,345

    SCOTT>SELECT empno, ename, sal, comm,
    2          TO_CHAR((sal*12)+comm, '999,999') "SALARY"
    3  FROM emp
    4  WHERE ename = 'ALLEN';
    EMPNO ENAME             SAL       COMM SALARY
    ---------- ---------- ---------- ---------- --------
    7499 ALLEN            1600        300   19,500

cf) 날짜와 숫자를 표현하는 방법

[ 년도 ]
‘YYYY’ – 연도를 4자리로 표현.
‘RRRR’ – 2000년 이후에 Y2K 버그로 인해 등장한 날짜 표기법으로 연도 4자리 표기법.
‘YY’ – 연도를 끝의 2 자리만 표시. 예 : 14
'RR' - 연도를 마지막 2 자리만 표시. 예: 14
‘YEAR’ – 연도의 영문 이름 전체를 표시.

[ 월 ]
MM – 월을 숫자 2자리로 표현. 예: 10
MON – 유닉스용 오라클에서 월을 뜻하는 영어 3글자로 표시. 예: OCT 윈도용 오라클일 경우는 MONTH 와 동일.
MONTH – 월을 뜻하는 이름 전체를 표시

[ 일 ]
DD – 일을 숫자 2자리로 표시. 예: 26
DAY – 요일에 해당하는 명칭을 표시하는데 유닉스용 오라클에서는 영문으로 나오고 윈도용 오라클에서는 한글로 나옴.
DDTH – 몇 번째 날인지를 표시.

[ 시간 ]
HH24 – 하루를 24시간으로 표시.
HH – 하루를 12 시간으로 표시.
분 : MI 로 표시.
초 : SS 로 표시.

[숫자]
9 의 개수만큼 자리수 TO_CHAR(1234,’99999’) 1234
빈자리를 0으로 채움 TO_CHAR(1234,’099999’) 001234
$ 표시를 붙여서 표시 TO_CHAR(1234,’$9999’) $1234
소수점 이하를 표시 TO_CHAR(1234,’9999.99’) 1234.00
천 단위 구분기호를 표시 TO_CHAR(12345,’99,999’) 12,345

• TO_NUMBER('숫자처럼 생긴 문자')
• ASCII('문자') : 문자를 아스키 코드 값으로 출력
• TO_DATE('날짜처럼 생긴 문자')

QUIZ1

Student 테이블의 birthday 컬럼을 사용하여 생일이 1월인 학생의 이름과 birthday 를 아래 화면과 같이 출력하시오.

SCOTT>SELECT studno, name, birthday
  2  FROM student
  3  WHERE TO_CHAR(birthday,'MM') = 01;

SCOTT>SELECT studno, name, birthday
  2  FROM student
  3  WHERE SUBSTR(birthday,4,3) = 'JAN';

QUIZ2

emp 테이블의 hiredate 컬럼을 사용하여 입사일이 1,2,3 월인 사람들의 사번과 이름, 입사일을 출력하시오.

SCOTT>SELECT empno, ename, hiredate
  2  FROM emp
  3  WHERE TO_CHAR(hiredate, 'MM') < 04;

QUIZ3

emp 테이블을 조회하여 comm 값을 가지고 있는 사람들의 empno , ename , hiredate , 총연봉,15% 인상 후 연봉을 아래 화면처럼 출력하세요. 단 총연봉은 (sal*12)+comm 으로 계산하고 아래 화면에서는 SAL 로 출력되었으며 15% 인상한 값은 총연봉의 15% 인상 값임. (HIREDATE 컬럼의 날짜 형식과 SAL 컬럼 , 15% UP 컬럼의 $ 표시와 , 기호 나오게)

SCOTT>SELECT empno, ename, TO_CHAR(hiredate,'YYYY-MM-DD') "HIREDATE",
  2                        TO_CHAR((sal*12)+comm,'$99,999') "SAL",
  3                        TO_CHAR((((sal*12)+comm)*1.15),'$99,999') "15% UP"
  4  FROM emp
  5  WHERE comm IS NOT NULL;

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저