[DB] 함수

Brogod97·2023년 1월 14일
TILdb
0

KH TIL

목록 보기
30/37
post-thumbnail

함수(Function)

하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리하여 작성해 놓은 작은 서브 프로그램

호출하며 값을 전달하면 결과를 리턴하는 방식으로 사용

문자 처리 함수

LENGTH

주어진 컬럼 값/문자열의 길이(문자 개수) 반환

작성법리턴 값 타입
LENGTH (CHARSTRING)
- CHARSTRING : 문자 타입 컬럼 또는 문자열
SELECT EMP_NAME, LENGTH(EMP_NAME), EMAIL, LENGTH(EMAIL)
FROM EMPLOYEE;

INSTR

지정한 위치부터 지정한 숫자 번째로 나타나는 문자의 시작 위치 반환

작성법리턴 값 타입
INSTR(STRING, STR, [POSITION,[OCCURRENCE]])NUMBER
  • STRING : 문자 타입 컬럼 또는 문자열
  • STR : 찾으려는 문자열
  • POSITION : 찾을 위치 시작 값(기본 값 1) POSITION > 0이면 STRING의 시작부터 끝 방향으로 찾고 POSITION < 0이면 STRING의 끝부터 시작 방향으로 찾음
  • OCCURRENCE : SUBSTRING이 반복될 때 지정하는 빈도(기본 값 1), 음수 사용 불가
-- EMAIL 컬럼의 문자열 중 ‘@’의 위치를 구하시오.
SELECT EMAIL, INSTR(EMAIL, ‘@’, -1, 1) 위치
FROM EMPLOYEE;

LTRIM/RTRIM

주어진 컬럼, 문자열의 왼쪽/오른쪽에서 지정한 STR에 포함된 모든 문자를 제거한 나머지 반환

작성법리턴 값 타입
LTRIM(STRING, STR) / RTRIM(STRING, STR)CHARACTER
  • STRING : 문자 타입 컬럼 또는 문자열
  • STR : 제거하려는 문자(열), 생략 시 공백문자
SELECT EMP_NAME, LTRIM(PHONE, '010'), RTRIM(EMAIL, '@kh.or.kr')
FROM EMPLOYEE;

TRIM

주어진 컬럼, 문자열의 앞/뒤/양쪽에 있는 지정한 문자를 제거한 나머지 반환

작성법리턴 값 타입
TRIM( STRING )CHARACTER
TRIM( CHAR FROM STRING)CHARACTER
TRIM( LEADING or TRAILING or BOTH [CHAR] FROM STRING)CHARACTER
  • STRING : 문자 타입 컬럼 또는 문자열
  • CHAR : 제거하려는 문자(열), 생략 시 공백문자
  • LEADING : TRIM할 CHAR의 위치 지정, 앞(LEADING)/뒤(TRAILING)/양쪽(BOTH) 지정 가능(기본 값 양쪽)

SUBSTR

컬럼이나 문자열에서 지정한 위치부터 지정한 개수의 문자열을 잘라내어 반환

작성법리턴 값 타입
SUBSTR( STRING, POSITION, [LENGTH] )CHARACTER
  • STRING : 문자 타입 컬럼 또는 문자열
  • POSITION : 문자열을 잘라낼 위치로 양수면 시작방향에서 지정한 수만큼, 음수면 끝 방향에서 지정한 수만큼의 위치 의미
  • LENGTH : 반환할 문자 개수(생략 시 문자열의 끝까지 의미, 음수면 NULL 리턴)

LPAD/RPAD

주어진 컬럼, 문자열에 임의의 문자열을 왼쪽/오른쪽에 덧붙여 길이 N의 문자열 반환

작성법리턴 값 타입
LPAD(STRING, N, [STR]) / RPAD(STRING, N, [STR])CHARACTER
  • STRING : 문자 타입 컬럼 또는 문자열
  • N : 반환할 문자(열)의 길이(바이트), 원래 STRING의 길이보다 작다면 N만큼 잘라서 표시
  • STR : 덧붙이려는 문자(열), 생략 시 공백문자
SELECT LPAD(EMAIL, 20, ‘#’)
FROM EMPLOYEE;

SELECT RPAD(EMAIL, 20, ‘#’)
FROM EMPLOYEE;

LOWER/UPPER/INITCAP

컬럼의 문자 혹은 문자열을 소문자/대문자/첫 글자만 대문자로 변환하여 반환

작성법리턴 값 타입
LOWER(STRING) / UPPER(STRING) / INITCAP(STRING)CHARACTER
  • STRING : 문자 타입 컬럼 또는 문자열

숫자 처리 함수

ABS

인자로 전달 받은 숫자의 절대값 반환

작성법리턴 값 타입
ABS(NUMBER)NUMBER
  • UMBER: 숫자 혹은 숫자 데이터 컬럼

MOD

인자로 전달 받은 숫자를 나누어 나머지 반환

작성법리턴 값 타입
MOD(NUMBER, DIVISION)NUMBER
  • NUMBER: 숫자 혹은 숫자 데이터 컬럼
  • DIVISION : 나눌 수 혹은 나눌 숫자 데이터 컬럼

ROUND

인자로 전달 받은 숫자 혹은 컬럼에서 지정한 위치부터 반올림하여 값 반환

작성법리턴 값 타입
ROUND(NUMBER)NUMBER
ROUND(NUMBER, POSITION)NUMBER
  • NUMBER : 숫자 혹은 숫자 데이터 컬럼
  • POSITION : 반올림할 위치(생략 시 기본 값 0)

FLOOR

인자로 전달 받은 숫자 혹은 컬럼에서 소수점 자리의 수를 버림 후 반환

작성법리턴 값 타입
FLOOR(NUMBER)NUMBER
  • NUMBER : 숫자 혹은 숫자 데이터 컬럼

TRUNC

인자로 전달 받은 숫자 혹은 컬럼에서 지정한 위치부터의 자리의 수를 버리고(절삭) 반환

작성법리턴 값 타입
TRUNC(NUMBER, POSITION)NUMBER
  • NUMBER : 숫자 혹은 숫자 데이터 컬럼
  • POSITION : 버릴 위치(생략 시 기본 값 0)

FLOOR는 진짜 수학적 ‘버림’을 하는 애고
TRUNC는 그냥 진짜 버리는 애

CEIL

인자로 전달 받은 숫자 혹은 컬럼을 올림 후 반환

작성법리턴 값 타입
CEIL(NUMBER)NUMBER
  • NUMBER : 숫자 혹은 숫자 데이터 컬럼

CEILING

날짜 처리 함수

SYSDATE

시스템에 저장되어 있는 현재 날짜 반환

작성법리턴 값 타입
SYSDATEDATE
SELECT SYSDATE 
FROM DUAL;

MONTHS_BETWEEN

인자로 날짜 두 개를 전달받아 개월 수 차이를 숫자 데이터형으로 반환

작성법리턴 값 타입
MONTHS_BETWEEN(DATE1, DATE2)NUMBER
  • DATE1 : 기준이 되는 날짜
  • DATE2 : 개월 수를 구하려는 날짜
-- EMPLOYEE테이블에서 사원의 이름, 입사일, 근무 개월 수 조회
SELECT EMP_NAME,
       HIRE_DATE,
       MONTHS_BETWEEN(SYSDATE, HIRE_DATE)
FROM EMPLOYEE;

ADD_MONTHS

인자로 전달받은 날짜에 인자로 받은 숫자만큼 개월 수를 더하여 특정 날짜 반환

작성법리턴 값 타입
ADD_MONTHS(DATE, NUMBER)DATE
  • DATE1 : 기준이 되는 날짜
  • DATE2 : 더하려는 개월 수
- EMPLOYEE테이블에서 사원의 이름, 입사일, 입사 후 6개월이 된 날짜 조회
SELECT EMP_NAME,
       HIRE_DATE,
       ADD_MONTHS(HIRE_DATE, 6)
FROM EMPLOYEE;

LAST_DAY

인자로 전달받은 날짜가 속한 달의 마지막 날짜 반환

작성법리턴 값 타입
LAST_DAY (DATE)DATE
  • DATE : 기준이 되는 날짜
- EMPLOYEE테이블에서 사원의 이름, 입사일, 입사한 달의 마지막 날 조회
SELECT EMP_NAME,
       HIRE_DATE,
       LAST_DAY(HIRE_DATE)
FROM EMPLOYEE;

EXTRACT

년, 월, 일 정보 추출하여 반환

작성법리턴 값 타입
EXTRACT(YEAR FROM DATE)DATE
EXTRACT(MONTH FROM DATE)DATE
EXTRACT(DAY FROM DATE)DATE
  • DATE : 기준이 되는 날짜
-- EMPLOYEE테이블에서 사원의 이름, 입사 년, 입사 월, 입사 일 조회
SELECT EMP_NAME,
			 EXTRACT(YEAR FROM HIRE_DATE) YEAR,
			 EXTRACT(MONTH FROM HIRE_DATE) MONTH,
			 EXTRACT(DAY FROM HIRE_DATE) DAY
FROM EMPLOYEE;

형 변환 함수

구분입력 값 타입리턴 값 타입설명
TO_CHARDATE
NUMBERCHARACTER날짜형 혹은 숫자형을 문자형으로 변환
TO_DATECHARACTER
NUMBERDATE문자형 혹은 숫자형을 날짜형으로 변환
TO_NUMBERCHARACTERNUMBER문자형을 숫자형으로 변환

TO_CHAR

날짜 혹은 숫자형 데이터를 문자형 데이터로 변환하여 반환

작성법리턴 값 타입
TO_CHAR(DATE[, FORMAT])CHARACTER
TO_CHAR(NUMBER[, FORMAT])CHARACTER
  • DATE : 문자형으로 변환하려는 날짜형 데이터
  • NUMBER : 문자형으로 변환하려는 숫자형 데이터
  • FORMAT : 문자형으로 변환 시 지정할 출력 형식

FORMAT 형식

형식의미형식의미
YYYY년도 표현(4자리)YY년도 표현(2자리)
MM월을 숫자로 표현MON월을 알파벳으로 표현
DD일 표현Q분기 표현
DAY요일 표현DY요일을 약어로 표현

TO_CHAR 예시1

SELECT EMP_NAME, 
	   TO_CHAR(HIRE_DATE, 'YYYY-MM-DD'), 
	   TO_CHAR(HIRE_DATE, 'YY/MON, DAY, DY')
FROM EMPLOYEE;

TO_CHAR 예시2

SELECT EMP_NAME,
	   TO_CHAR(SALARY, ‘L999,999,999’),
       TO_CHAR(SALARY, ‘000,000,000’)
FROM EMPLOYEE;

TO_DATE

숫자 혹은 문자형 데이터를 날짜형 데이터로 변환하여 반환

작성법리턴 값 타입
TO_DATE(CHARACTER[, FORMAT])DATE
TO_DATE(NUMBER[, FORMAT])DATE
  • CHARACTER : 날짜형으로 변환하려는 문자형 데이터
  • NUMBER : 날짜형으로 변환하려는 숫자형 데이터
  • FORMAT : 날짜형으로 변환 시 지정할 출력 형식
-- EMPLOYEE테이블에서 2000년도 이후에 입사한 사원의 사번, 이름, 입사일 조회
SELECT EMP_NO,
       EMP_NAME,
       HIRE_DATE
FROM EMPLOYEE
WHERE HIRE_DATE > TO_DATE(20000101, ‘YYYYMMDD’);

TO_NUMBER

날짜 혹은 문자형 데이터를 숫자형 데이터로 변환하여 반환

작성법리턴 값 타입
TO_NUMBER (CHARACTER, [FORMAT])NUMBER
  • CHARACTER : 숫자형으로 변환하려는 문자형 데이터
  • FORMAT : 날짜형으로 변환 시 지정할 출력 형식
SELECT TO_NUMBER('1,000,000', '99,999,999') 
		 - TO_NUMBER('550,000', '999,999')
FROM DUAL;

NULL 처리 함수

NVL

NULL로 되어 있는 컬럼의 값을 인자로 지정한 숫자 혹은 문자로 변경하여 반환

작성법리턴 값 타입
NVL(P1, P2)NUMBER
CHARACTER
  • P1 : NULL데이터를 처리할 컬럼명 혹은 값
  • P2 : NULL값을 대체하고자 하는 값
SELECT EMP_NO,
	   EMP_NAME,
	   SALARY,
	   NVL(BONUS, 0),
	   (SALARY + (SALARY * NVL(BONUS, 0)))*12
FROM EMPLOYEE;

DECODE

비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환

작성법리턴 값 타입
DECODE(표현식, 조건1, 결과1, 조건2, 결과2, …, DEFAULT)결과
  • 표현식 : 값에 따라 선택을 다르게 할 컬럼 혹은 값
  • 조건 : 해당 값이 참인지 거짓인지 여부 판단
  • 결과 : 해당 조건과 일치하는 경우 반환할 값
  • DEFAULT : 모든 조건이 불일치 시 반환할 값
SELECT EMP_ID,
	   EMP_NAME,
       EMP_NO,
	   DECODE(SUBSTR(EMP_NO, 8, 1), ‘1’, ‘남’, ‘2’, ‘여‘) AS 성별
FROM EMPLOYEE;

CASE

비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환(조건은 범위 값 가능)

-- 작성법
CASE WHEN 조건1 THEN 결과1
     WHEN 조건2 THEN 결과2
     WHEN 조건3 THEN 결과3
     …
     ELSE 결과N
END

-- 결과값 반환
  • 조건 : 해당 값이 참인지 거짓인지 여부 판단
  • 결과 : 해당 조건과 일치하는 경우 반환할 값
  • DEFAULT : 모든 조건이 불일치 시 반환할 값
SELECT EMP_ID, EMP_NAME, EMP_NO, 
	CASE WHEN SUBSTR(EMP_NO, 8, 1) = 1 THEN ‘남’ 
	ELSE ‘여’
	END AS 성별
FROM EMPLOYEE;
SELECT EMP_NAME, SALARY,
	CASE 
			WHEN SALARY > 5000000 THEN '1등급'
	    WHEN SALARY > 3500000 THEN '2등급'
      WHEN SALARY > 2000000 THEN '3등급'
      ELSE '4등급'
	END 등급
FROM EMPLOYEE;

그룹 함수

하나 이상의 행을 그룹으로 묶어 연산하며 총합, 평균 등을 하나의 컬럼으로 반환하는 함수

구분설명
SUM그룹의 누적 합계 반환
AVG그룹의 평균 반환
COUNT그룹의 총 개수 반환
MAX그룹의 최대 값 반환
MIN그룹의 최소 값 반환

SUM

해당 컬럼 값들의 총합 반환

-- EMPLOYEE테이블에서 남자 사원의 급여 총합 조회
SELECT SUM(SALARY),
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = 1;

-- EMPLOYEE테이블에서 부서코드가 D5인 직원의 보너스 포함 연봉 조회
SELECT SUM(SALARY + (SALARY*NVL(BONUS, 0))*12)
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5';

AVG

해당 컬럼 값들의 평균 반환

-- EMPLOYEE테이블에서 전 사원의 보너스 평균을 소수 셋 째 자리에서 반올림 한 것 조회
SELECT ROUND(AVG(NVL(BONUS, 0)), 2)
FROM EMPLOYEE;
  • NVL을 하지 않을 시 NULL 값을 가진 행은 평균 계산에서 제외되어 계산

MAX/MIN

그룹의 최대값과 최소값 반환

-- EMPLOYEE테이블에서 가장 높은 급여와 가장 낮은 급여 조회
SELECT MAX(SALARY), MIN(SALARY)
FROM EMPLOYEE;

-- EMPLOYEE테이블에서 가장 오래된 입사일과 가장 최근인 입사일 조회
SELECT MAX(HIRE_DATE), MIN(HIRE_DATE)
FROM EMPLOYEE;

COUNT

테이블 조건을 만족하는 행의 개수 반환

-- EMPLOYEE테이블에서 전체 사원 수 조회
SELECT COUNT(*)
FROM EMPLOYEE;

-- EMPLOYEE테이블에서 부서코드가 D5인 직원의 수 조회
SELECT COUNT(DEPT_CODE)
FROM EMPLOYEE
WHERE DEPT_CODE = ‘D5’;

-- EMPLOYEE테이블에서 사원들이 속해있는 부서의 수 조회
SELECT COUNT(DISTINCT DEPT_CODE)
FROM EMPLOYEE;
profile
Brogod97
이전 포스트

[DB] SELECT (DQL)

다음 포스트

[DB] JOIN

0개의 댓글