ORACLE에서 제공되는 내장 함수

• 목 차

단일 행 함수

숫자 함수

ROUND(NUMBER[, M])

• NUMBER 숫자에 대해서 반올림한 결과를 반환
• M은 반올림할 자리를 의미
• 10^(-M)자리 수 까지 표현
• M 생략 시, 0 > 즉 1의 자리까지 표현 > 소수점 첫째 자리에서 반올림
• 사용 예

  SELECT ROUND(3.141592) --3
      , ROUND(3.141592, 2) --3.14
      , ROUND(123.141592,-1) --120  
  FROM dual;

TRUNC(NUMBER [,M])

• NUMBER 숫자에 대해서 절삭한 결과를 반환
• M은 절삭할 자리를 의미
• 10^(-M)자리 수 까지 표현
• M 생략 시, 0 > 즉 1의 자리까지 표현 > 소수점 첫째 자리부터 절삭
• FLOOR(NUMBER) : 소수점 첫째 자리에서 절삭
• 사용 예

  SELECT TRUNC(3.541592) --3
      , TRUNC(3.146592, 2) --3.14
      , TRUNC(125.141592,-1) --120  
  FROM dual;

CEIL(NUMBER)

• NUMBER 숫자에 대해서 올림한 결과를 반환
• 소수점 첫째자리에서 올림
• 사용 예

  SELECT CEIL(3.141592) --4
      , CEIL(3.141592*100)/100 --3.15
      , CEIL(123.141592/10)*10 --130  
  FROM dual;

MOD(NUM1, NUM2)

• NUM1을 NUM2로 나누었을 때 나머지 값을 반환하는 함수
• NUM1 - (NUM2 * FLOOR(NUM1 / NUM2))계산으로 나머지를 구함
  • REMAINDER()의 경우 버림대신 반올림으로 계산하는 함수

SIGN(NUMBER)

• NUMBER 양수면 1반환
• NUMBER 음수면 -1반환
• NUMBER 0이면 0 반환

ABS(NUMBER)

• NUMBER의 절대값을 반환

문자 함수

UPPER(CHAR)

• 대문자로 변환

LOWER(CHAR)

• 소문자로 변환

INITCAP(CHAR)

• 첫 글자만 대문자로 변환

LENGTH(CHAR)

• 문자열의 길이를 반환

CONCAT(CHAR1, CHAR2)

• 두 문자열을 합쳐 하나의 문자열로 변환
• || 결합 연산자와 비슷한 역할을 함
• 사용 예

  SELECT CONCAT('두부','쿠키') --'두부쿠키'
  FROM dual;

SUBSTR(char, pos, [ length ])

• char문자열에 pos위치부터 length길이만큼 출력
• pos이 음수면 뒤에서 부터

INSTR(char1, char2 [, pos [, occurrence]])

• char1문자열에서 지정한 문자 char2를 찾아서 위치를 숫자로 반환
• pos은 검색 시작 위치 (양수면 좌측부터, 음수면 우측부터)
• occurrence번째 검색된 위치를 반환
• 사용 예

  SELECT INSTR('abcdabcdabcd', 'bc') -- 2
      , INSTR('abcdabcdabcd', 'bc', 3) -- 6
      , INSTR('abcdabcdabcd', 'bc', 3, 2) -- 10
      , INSTR('abcdabcdabcd', 'bc', -1, 1) -- 10
  FROM dual;

RPAD (expr1, n [, expr2] ) / LPAD

• 문자열 길이를 n으로 고정하고, expr1을 출력 후 남는 공간은 expr2로 채운다
• RPAD는 우측, LPAD는 좌측

ASCII 코드값

• ASCII(char) : 문자를 아스키코드값으로 변환
• CHR(n) : n의 아스키코드값을 갖는 문자로 변환

REPLACE(char1, char2, char)

• char1 문자열 중 char2를 char3로 대체하여 문자열로 변환

REGEXP_LIKE(char, pattern, [ match_option ])

• 정규표현식으로 해당되는 문자열 평가

VSIZE()

• 입력된 자료의 크기를 출력하는 함수
  • 한글 1문자 == 3바이트
  • 영문 1문자 == 1바이트
  • 숫자 == 2바이트

날짜 함수

DATETIME 종류

종류	내용
SYSDATE	시스템의 날짜 정보를 가져오는 함수
CURRENT_DATE	시스템의 현재 날짜 정보를 가져오는 함수
CURRENT_TIMESTAMP	시스템의 현재 타임스탬프 날짜 정보를 가져오는 함수
EXTRACT(datetime)	datetime이나 interval 값으로 특정 날짜/시간 정보를 추출

TRUNC(date)

• 날짜 데이터를 특정위치를 절삭하는 함수
• 사용 예

  SELECT CURRENT_TIMESTAMP,
      TRUNC(CURRENT_TIMESTAMP), 
      -- 시간 정보를 절삭 / 00:00:00
      TRUNC(CURRENT_TIMESTAMP, 'DD'), 
      -- 일까지 출력 시 밑으로 절삭 / 00:00:00
      TRUNC(CURRENT_TIMESTAMP, 'MM'), 
      -- 월까지 출력 일 밑으로 절삭 / 22/04/01
      TRUNC(CURRENT_TIMESTAMP, 'YY'), 
      -- 년까지 출력 월 밑으로 절삭 / 22/01/01
      TRUNC(CURRENT_TIMESTAMP, 'DAY') 
      -- 요일 / 그 주의 첫날(일요일) 22/04/03
  FROM dual;

날짜 연산

연산	결과
날짜 - 날짜	= 숫자 (차이 일수)
날짜 ± 숫자	= 날짜 (숫자만큼 전(후) 날짜)
날짜 ± 숫자/24	= 날짜 (숫자만큼의 시간 차이)

MONTHS_BETWEEN(date1,date2)

• 두 날짜에 월의 차이를 반환
• 사용 예

  SELECT MONTHS_BETWEEN(SYSDATE, hiredate), -- 고용일로부터 지난 개월수
    MONTHS_BETWEEN(hiredate , SYSDATE)/12 -- 년수 계산
  FROM emp;

ADD_MONTHS(date, month)

• 날짜의 개월 수를 더한 날짜를 출력
  • 1월 31일, 2월 28일, 4월 30일 같이 월의 마지막 날에서 연산 시 해당 월에 마지막 날로 계산
  • 사용 예

    SELECT ADD_MONTHS(TO_DATE('02-28-2022', 'MM-DD-YYYY'),  1),  -- 3월31일
      ADD_MONTHS(TO_DATE('02-27-2022', 'MM-DD-YYYY'),  1)  -- 3월27일
    FROM dual;

LAST_DAY()

• 해당 월의 마지막 날짜를 반환

NEXT_DAY()

• 해당 요일의 돌아오는 날짜를 반환

형 변환 함수

TO_CHAR(date [, 'fmt' [, 'nlsparam'])

• 날짜를 포맷에 맞춰 문자열 출력

기호	내용	기호	내용
년	Y,YY,YYY,YYYY,RR,RRRR,...	월	MM, MONTH, MON
일	DD(일/월) D(일/주) DDD(일/년)	요일	DY, DAY
시	HH, HH12, HH24	분	MI
초	SS, SSSSS	오전오후	AM, PM

• 사용 예

  SELECT TO_CHAR(SYSDATE, 'YYYY') -- '2022' 문자열로 출력
  FROM dual;

TO_DATE( char [, 'fmt' [, 'nlsparam']])

• 문자열을 포맷에 맞춰 날짜형으로 변환하는 함수
• 사용 예

  SELECT TO_DATE('2022.04.11'), -- 22/02/11(날짜형)
    TO_DATE('04.11.2022', 'MM,DD,YYYY'), --22/02/11(날짜형)
    TO_DATE('2022.04', 'YYYY.MM'), --22/04/01 : 일 입력 안하면 1일로 변환
    TO_DATE('11', 'DD') -- 22/04/11 : 년, 월 입력 안하면 해당년 해당월로 변환
  FROM dual;

TO_CHAR(number [,'fmt' [, 'nlsparam']])

• 숫자를 포맷에 맞춰 문자열로 변환하는 함수

기호	내용	기호	내용
9	숫자	0	숫자, 공백 시 0으로 채움
,	쉼표 표기		.
L	local currency symbol

• 사용 예

  SELECT TO_CHAR(1234567, '9,999,999'), -- '1,234,567' 
    TO_CHAR(1234567, 'L9,999,999.99'), -- '￦1,234,567.00'
    TO_CHAR(12, '0999') -- '0012'
  FROM dual;

TO_NUMBER()

• 문자를 숫자로 변환
• 숫자만 있는 문자열은 묵시적으로 숫자로 취급하기에 잘 사용은 안함

NULL 처리 함수

NVL(exp1, exp2)

• exp1의 값이 널일 때, exp2로 변환

NVL2(exp1, exp2, exp3)

• exp1의 값이 널이 아닐 때 exp2, 널일 때 exp3로 변환

NULLIF(expr1, expr2)

• 두 인자 값을 비교
• 같으면 NULL 반환
• 다르면 expr1 반환

COALESCE(expr [, expr , ...])

• 순차적으로 인자 값에 대하여 NULL 체크
• NULL이 아닌 값 중 가장 처음 순서의 인자 반환

IF문 역할 하는 함수

DECODE(expr, search1, result1 [, search2, result2,...][, default]);

• 다른 언어의 IF문과 같은 역할을 함
  • PL/SQL에서 사용하기 위해 만들어진 함수
• 조건을 =(같다) 비교만 가능
• DEFAULT(IF문으로 따지면 ELSE)를 안주면 NULL
• 사용 예

  SELECT DECODE(x, 1,A, 10,B, 12,C, 14,D, E)
  FROM dual;

CASE

• DECODE보다 더 개선된 함수
• 형식

  SELECT CASE x
      WHEN 1 THEN 'A'
      WHEN 10 THEN 'B'
      WHEN 12 THEN 'C'
      WHEN 14 THEN 'D'
      ELSE 'E'
    END
  FROM dual;

• 비교, 산술, 관계 연산자 사용 가능

  SELECT emp.*,  
      CASE
      WHEN deptno IN (10, 20) THEN 'A팀'
      ELSE 'B팀'
    END AS "팀명"
  FROM emp; 
  -- deptno가 10, 20인 사원은 A팀 나머지는 B팀

순위 함수(TOP_N)

ROWNUM

• 함수 아님
• 오라클 내부에서 사용되는 의사 컬럼(PSEUDO COLUMN)
• SELECT문으로 조회된 행들의 순서번호
• 순번을 1번부터 찾을 수 있고, BETWEEN AND 연산자 같이 중간부터는 못찾음

RANK()

• 그룹 내 순위를 계산하여 NUMBER타입으로 순위를 반환
• 중복 순위 계산
• 사용 예

  RANK ( ) OVER ([PARITION BY 절] ORDER BY절 )

DENSE_RANK()

• 그룹 내 순위를 계산하여 NUMBER타입으로 순위를 반환
• 중복 순위 계산 안함
• 사용 예

  DENSE_RANK ( ) OVER ([PARITION BY 절] ORDER BY절 )

ROW_NUMBER()

• 정렬된 결과에 대해 순번을 NUMBER타입으로 반환
• 같은 순위도 순번이 다름
• 사용 예

  ROW_NUMBER( ) OVER ([PARITION BY 절] ORDER BY절 )

PERCENT_RANK()

• 첫 행을 0 마지막 행을 1로 순번을 비율로 나타냄

FIRST_VALUE()

• 정렬된 값 중 첫 번째 값을 반환하는 함수
• 사용 예

  FIRST_VALUE(expr) OVER ([PARITION BY 절] ORDER BY절 )

LAST_VALUE()

• 정렬된 값 중 마지막 값을 반환하는 함수
  • 조회 중인 행이 마지막이므로 조회중인 행의 값 반환
• 사용 예

  LAST_VALUE(expr) OVER ([PARITION BY 절] ORDER BY절 )

그 외

TRIM(char1 FROM char2)

• char2문자열의 양 끝쪽에 char1문자를 제거하는 함수
• 사용 예

  SELECT 
    TRIM('*' FROM '***AD***MIN***') -- 'AD***MIN' 출력
  FROM dual;

PIVOT()

• 오라클 11g부터 제공
• 행과 열을 뒤집는 함수
• 사용 예

  ELECT *
  FROM (PIVOT할 쿼리문)
  PIVOT (그룹함수(집계컬럼) FOR 피벗컬럼 IN (피벗컬럼값 AS 별칭 ...);

dbms_random

• PL/SQL의 5가지 중 하나인 package
• 난수를 반환하는 함수
• 사용 예

  SELECT 
      ROUND(dbms_random.value,1) "0~1",
      ROUND(dbms_random.value(0, 100),1) "1~100",-- 0 <= 실수 < 100
      TRUNC(dbms_random.value(0, 45)) + 1 lotto,
      dbms_random.string('U', 5) "upper", --대문자
      dbms_random.string('L', 5) "lower", --소문자
      dbms_random.string('A', 5) "eng", --대문자 + 소문자
      dbms_random.string('X', 5) "eng+num", --대문자 + 숫자
      dbms_random.string('P', 5) "eng+특수" -- 알파벳 + 특수문자
  FROM dual;

그룹 함수

• 그룹의 인풋을 하나의 결과로 출력
• SELECT 절이나 HAVING절에 사용
• HAVING절은 그룹을 제한
• GROUP BY절은 행을 그룹
• 그룹 함수와 행이 여러개인 일반 컬럼을 같이 조회할 수 없음

COUNT()

• 컬럼의 갯수를 출력
• DISTINCT : 중복 제거
• ALL (기본 값) : 중복 포함
• • : 널을 포함한 행
• 사용 예

  SELECT  COUNT(*)
  FROM emp
  WHERE deptno =10;
  -- deptno가 10인 레코드 수 / 3
  
  SELECT  COUNT(DISTINCT deptno)
  FROM emp;
  -- 중복을 제외한 deptno컬럼의 수 / 3(10, 20, 30)

SUM()

• (NULL 제외) 합을 출력

AVG()

• (NULL 제외) 평균을 출력

OVER 절

• 그룹함수에서 OVER 절의 사용
• 질의한 행의 누적된 결과 값을 반환
• 사용 예

  COUNT([DISTINCT ¦ ALL] expr1) OVER ([PARTITION BY expr2] ORDER BY expr3)

MAX()

• (NULL 제외) 최대값을 출력

MIN()

• (NULL 제외) 최소값을 출력

STDDEV()

• (NULL 제외) 표준편차를 출력

VARIANCE()

• (NULL 제외) 분산을 출력