2-3) 함수

함수 정의

• input value가 있을 경우 그에 맞는 output value를 출력해주는 객체
• input value와 output value의 관계를 정의한 객체
• from 절을 제외한 모든 절에서 사용 가능

 

함수 기능

• 기본적인 쿼리문을 더욱 강력하게 해줌
• 데이터의 계산을 수행
• 개별 데이터의 항목을 수정
• 표시할 날짜 및 숫자 형식을 지정
• 열 데이터의 유형 (data type)을 변환

 

함수의 종류 (입력값의 수에 따라)

• 단일행 함수 / 복수행 함수로 구분
• 단일행 함수 : input과 output의 관계가 1:1
• 복수행 함수 : 여러 건의 데이터를 동시에 입력 받아서 하나의 요약값을 리턴
  • 그룹함수 또는 집계함수라고도 함

 

 

입/출력값의 타입에 따른 함수 분류

 

1. 문자형 함수

• 문자열 결합, 추출, 삭제 등을 수행
• 단일행 함수 형태
• output은 대부분 문자값 (LENGTH, INSTR 제외)

• SUBSTR은 음수 위치에서 시작한 경우에도 정방향으로 n개의 문자열 추출
• INSTR은 음수 위치에서 시작한 경우 반대 방향으로 탐색하며 n번째 발견된 문자열 위치 반환
• LTRIM, RTRIM : 왼쪽, 오른쪽에서부터 삭제하려는 문자열이 나올 때까지 삭제, 다른 문자열 나오면 중단
• TRIM : ORACLE은 특별한 인수 없이 앞뒤로 공백 삭제
• LPAD, RPAD : 문자열 총길이, 추가할 문자열 입력받아 총길이만큼 왼쪽, 오른쪽에 문자열 추가
• CONCAT : 두 개의 인수만 전달 가능, 문자열 결합
• REPLACE는 특정 문자열을 찾으면 바꿀 문자열로 바꿈
• TRANSLATE는 글자 단위로 1대1 치환, 찾을 문자열 전체를 포함하지 않아도 글자 단위로 변경

 

** SQL Server)

• SUBSTR → SUBSTRING
• LENGTH → LEN
• INSTR → CHARINDEX

 

2. 숫자형 함수

• 숫자를 입력하면 숫자 값을 반환
• 단일행 함수 형태의 숫자함수
• ORACLE과 SQL Server 함수 거의 동일

• ROUND, TRUNC : 반올림, 내림 해서 특정 자리가 되도록 (2 → 소수점 셋째자리에서 반올림, 버림)

 

3. 날짜형 함수

• 날짜 연산과 관련된 함수
• ORACLE과 SQL Server 함수 거의 다름

• SYSDATE : 현재 날짜와 시간
• CURRENT_DATE : 현재 날짜 (시간 X)
• CURRENT_TIMESTAMP : 현재 날짜, 시간, 타임스탬프
• ADD_MONTHS : n개월 후 (MONTH만 덧셈)
• MONTHS_BETWEEN : 두 날짜의 개월 수 리턴
• LAST_DAY : 주어진 월의 마지막 날짜
• NEXT_DAY : 주어진 날짜 이후 지정된 요일의 첫 번째 날짜 리턴 (일:1, 월:2, ..., 토: 7)
• ROUND, TRUNC : ROUND(SYSDATE, 'MONTH') → 월 기준으로 이전(일 기준)에서 반올림, 버림 → 15일 기준으로 이상이면 다음 월의 1일 0시 반환 / 미만이면 이전 월의 1일 0시 반환

 

** SQL Server)

• SYSDATE → GETDATE
• ADD_MONTHS → DATEADD (월 뿐만 아니라 모든 단위 날짜 연산 가능)
• MONTHS_BETWEEN → DATEDIFF (두 날짜 사이의 년, 월, 일 추출

 

4. 변환함수

• 값의 데이터 타입을 변환
• 문자를 숫자로, 숫자를 문자로, 날짜를 문자로 변경

• TO_NUMBER('100') → 100으로 숫자 반환
• TO_CHAR : 날짜의 포맷 변경, 숫자의 포맷 변경, TO_CHAR(SYSDATE, 'MM/DD-YYYY'), TO_CHAR(9000, '09999') → 총 5자리수, 앞자리는 0으로 채움
  • 형식을 변경하지만 리턴은 문자 타입
• TO_DATE('2024/01/01', 'YYYY/MM/DD') → 문자열과 문자열의 포맷 형식을 입력하면 날짜로 리턴
• FORMAT(GETDATE(), 'YYYY') 날짜의 포맷 변경 → 날짜를 전달하여 포맷 변경 (아마 문자열로 리턴)
  • SQL SERVER 함수
• CAST : 대상과 데이터 타입 전달, CAST('100' AS int) → 숫자 100으로 리턴

 

** SQL Server)

• TO_NUMBER, TO_DATE, TO_CHAR → CONVERT(포맷 전달 시)
• 단순 변환일 경우 주로 CAST 사용

 

5. 그룹함수

• 다중행 함수
• 여러 값이 input 값으로 들어가서 하나의 요약된 값으로 리턴
• GROUP BY와 함께 자주 사용됨
• ORACLE과 SQL Server 거의 동일

 

** SQL Server)

• VARIANCE → VAR
• STDDEV →  STDEV

 

6. 일반함수

• 기타 함수 (널 치환 함수 등)

• DECODE : 중첩으로 사용 가능
• NVL : 대상이 널이면 치환값으로 치환하여 리턴
• NVL2 : 대상이 널이면 치환할 값, 널이 아니면 치환할 값 설정 → 널이 아니면 치환값1, 널이면 치환값2
• COALESCE : 대상들 중 널이 아닌 값 리턴, 모두가 널이면 그 외 리턴값이 리턴
• ISNULL : 대상이 널이면 치환값이 리턴
• NULLIF : 두 값이 같으면 널 리턴, 다르면 대상1 (첫 번째 대상) 리턴
• CASE문 : 중첩 사용 가능, CASE IF THEN, ..., END로 끝

 

예1) DECODE 사용 예제 1

• DEPTNO가 10이면 '인사부', 20이면 '재무부', 나머지는 NULL 리턴

 

예2) DECODE 사용 예제 2

• DECODE 중첩 사용
• DEPTNO가 10이면서 JOB이 'CLERK'이면 'A', DEPTNO가 10이고 JOB이 'CLERK'가 아니면 'B', DEPTNO가 10이 아니면 'C'

 

예3) NVL, NVL2 사용 예제

• NVL → COMM이 NULL이면 0 반환
• NVL2 → COMM이 NULL이면 500, NULL이 아니면 COMM*1.1 반환
  • NULL이 아닌 경우가 중간!
• NVL2는 NVL과 달리 NULL이 아닌 경우에도 치환값 정의 가능

 

예4) COALESCE 사용 예제

• DEPTNO1과 DEPTNO2 중 NULL이 아닌 값 출력, 둘 다 NULL이 아닐 경우 맨 앞 순서 출력, 둘다 NULL인 경우 0 출력

 

예5) CASE 문 사용 예제 1

• CASE WHEN ~ THEN ~ WHEN ~ THEN ~ ... ELSE ~ 마지막에 END
• ALIAS 붙이는 것이 좋음

 

예6) CASE 문 사용 예제 2

• 동등비교 (=) 시 비교 대상을 CASE와 WHEN 사이에 배치하고 WHEN 뒤에 숫자만 넣어 처리 가능
• WHEN 절 마다의 반복을 줄여줌