FUNCTION

DB에서 제공하는 미리 정의 된 기능.

• 데이터 값을 계산하거나 조작. (단일 행 함수)
• 열의 데이터 타입을 변환.
• 행의 그룹에 대해 계산하거나 요약. (그룹 함수)

DUAL 테이블

쿼리문의 수행 결과가 하나의 ROW로 출력되기 위한 DUMMY 컬럼만 갖고 있음.
함수를 위한 임시 테이블 같은 개념.

단일 행 함수 : 데이터 값을 계산 혹은 조작

• 각 행에 대해 수행.
• 데이터 타입에 맞는 함수를 사용 해야 함.
• 행렬로 하나의 결과를 반환.
• SELECT, WHERE, ORDER BY 절 등에서 사용 가능.
• 함수 속의 함수로 중첩해서 사용 가능.

단일 행 함수의 종류.

문자 처리 함수

숫자 처리 함수

날짜 처리 함수

날짜 연산

SELECT
  TO_CHAR( SYSDATE, 'YY/MM/DD/HH24:MI' ),
  SYSDATE + 1,
  SYSDATE - 1,
  (SYSDATE + 1) - (SYSDATE - 7),
  SYSDATE + 13 / 24
FROM
  DUAL;

날짜 처리 함수

MONTH_BETWEEN 함수 외에는 모두 날짜 타입을 반환함.

MONTHS_BETWEEN

두 날짜 사이의 개월 수 계산

  MONTHS_BETWEEN( 날짜, 날짜 )

SELECT
  SYSDATE, hire_date, MONTHS_BETWEEN( SYSDATE, hire_date )
FROM
  employees
WHERE
  department_id = 100;

ADD_MONTHS

월에 날짜 더하기

  ADD_MONTHS( 날짜, 숫자 )

SELECT
  hire_date,
  ADD_MONTHS( hire_date, 3),
  ADD_MONTHS( hire_date, -3)
FROM
  employees
WHERE
  department_id BETWEEN 100 AND 106; 

NEXT_DAY

돌아오는 요일의 날짜 개산하기

  NEXT_DAY( 날자, '요일' or 숫자 )

SELECT
  hire_date,
  NEXT_DAY( hire_date, 'Friday'),
  NEXT_DAY( hire_date, 6)
FROM
  employees
WHERE
  department_id BETWEEN 100 AND 106; 

LAST_DAY

돌아오는 월의 마지막 날짜 계산하기

  LAST_DAY( 날짜 )

SELECT
  hire_date,
  LAST_DAY( hire_date )
FROM
  employees
WHERE
  department_id BETWEEN 100 AND 106; 

ROUND, TRUNC

날짜를 반올림 혹은 절삭

  ROUND or TRUNC ( 날짜, 지정 값[MONTH, YEAR등] )

SELECT
  hire_date,
  ROUND( hire_date, 'MONTH' ),
  ROUND( hire_date, 'YEAR' ),
  TRUNC( hire_date, 'MONTH' ),
  TRUNC( hire_date, 'YEAR' )
FROM
  employees
WHERE
  department_id BETWEEN 100 AND 106; 

형 변환 함수

TO_DATE

날짜를 나타내는 문자열을 명시도니 날짜 형식으로 변환하는 함수

  TO_DATE( 문자열, '지정형식' )

SELECT
  TO_DATE( '20111217', 'YYYYMMDD')
FROM
  DUAL;

TO_NUMBER

숫자 타입의 문자열을 숫자 데이터 타입으로 변환하는 함수. 데이터 타입만 바뀜.

  TO_NUMBER( '숫자' )

SELECT
  TO_NUMBER('1234')
FROM
  DUAL;

TO_CHAR

숫자 값을 지정한 데이터 형식의 문자열로 변환

  TO_CHAR( 숫자 데이터 타입, '지정 형식' )

TO_CHAR

날자 값을 지정한 형식의 문자열 타입으로 면환

  TO_CHAR( 날짜 데이터 타입, '지정 형식' )

일반 함수

NVL

  NVL( 열이름, '치환 값' )

NULL 값을 치환 값으로 처리함.

SELECT
  salary * NVL( commission_pct, 1 )
FROM
  employees
ORDER BY
  commission_pct;

NVL2

  NVL2( COLUMN1, COLUMN2, COLUMN3 )

COLUMN1의 값이 NULL이면 COLUMN3를 출력 NULL이라면 COLUMN2를 출력.

DECODE

조건 논리 처리.
3항 연산과 같음.

  DECODE( 열 이름, 조건 값, 치환 값, 기본 값 )

SELECT
  first_name,
  last_name,
  department_id,
  salary as 급여,
  DECODE( department_id, 60, salary * 1.1, salary )   as 조정급여,
  DECODE( department_id, 60, '10%인상', '미인상' )    as 인상여부
FROM
  employees;
ORDER BY
  commission_pct;

CASE

복잡한 조건 논리처리 하기

CASE
  WHEN  조건 1  THEN  출력 값 1
  WHEN  조건 3  THEN  출력 값 2
  ...
  ELSE  출력 값 3
END

SELECT
  employee_id, first_name, last_name, salary,
  CASE
    WHEN salary >= 9000 THEN 'TOP'
    WHEN salary BETWEEN 6000 AND 8999 THEN 'MID'
    ELSE 'LOW'
  END AS salary_grade
FROM
  employees
WHERE
  job_id = 'IT_PROG';

RANK, DENSE_RANK, ROW_NUMBER

데이터 값에 순위 매기기

  RANK () OVER ( [PARTITION BY 열 이름] ORDER BY 열 이름)

SELECT
  employee_id, salary,
  RANK()       OVER( ORDER BY salary DESC ) AS RANK_SALARY,
  DENSE_RANK() OVER( ORDER BY salary DESC ) AS DENSE_RANK_SALARY,
  ROW_NUMBER() OVER( ORDER BY salary DESC ) AS ROW_NUMBER_SALARY
FROM
  employees;

SELECT
  A.employee_id, A.department_id, B.department_name, A.salary,
  RANK()       OVER( PARTITION BY A.department_id ORDER BY salary DESC ) AS RANK_SALARY,
  DENSE_RANK() OVER( PARTITION BY A.department_id ORDER BY salary DESC ) AS DENSE_RANK_SALARY,
  ROW_NUMBER() OVER( PARTITION BY A.department_id ORDER BY salary DESC ) AS ROW_NUMBER_SALARY
FROM
  employees A, departments B
WHERE
  A.department_id = B.department_id
ORDER BY B.department_id, A.salary DESC;

그룹 함수

집계 함수라고도 함.
단일 행 함수와 달리 여러 행에 대해 함수가 적용되어 하나의 결과를 나타내는 함수.
기준 열에 대해 같은 데이터 값 기리 그룹으로 묶고,
묶은 행의 집합에 대해 그룹 함수 연산이 필요하다면 GROUP BY 절을 이용하여 처리할 수 있음.
묶은 그룹에 대해 조건이 필요하다면 HAVING 절을 이용함.

그룹 함수의 사용법

  SELECT  그룹함수( 열 이름 )
  FROM    테이블 이름
  [WHERE 조건식]
  [ORDER BY 열 이름]

주요 그룹 함수

예제 생략