SQLD(SQL-Developer) (8)

<함수>

💙 함수 정의

- input value가 있을 경우 그에 맞는 output value를 출력해주는 객체

- input value와 output value의 관계를 정의한 객체

- from절을 제외한 모든 절에서 사용 가능

 

💙 함수 기능

- 기본적인 쿼리문을 더욱 강력하게 해줌

- 데이터의 계산을 수행

- 개별 데이터의 항목을 수정

- 표시할 날짜 및 숫자 형식을 지정

- 열 데이터의 유형(data type)을 변환

 

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

- 단일행 함수와 복수행 함수로 구분

- 단일행 함수 : input과 output의 관계가 1:1

- 복수행 함수 : 여러 건의 데이터를 동시에 입력 받아서 하나의 요약값을 리턴 (그룹함수 또는 집계함수라고도 함)

 

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

1) 문자형 함수

  - 문자열 결합, 추출, 삭제 등을 수행

  - 단일행 함수 형태

  - output은 대부분 문자값(LENGTH, INSTR 제외)

 

※ 문자함수 종류

 

SQL-Server)

• SUBSTR 👉🏻 SUBSTRING
• LENGTH 👉🏻 LEN
• INSTR 👉🏻 CHARINDEX

2) 숫자형 함수

  - 숫자를 입력하면 숫자 값을 반환

  - 단일행 함수 형태의 숫자함수

  - ORACLE과 SQL-Server 함수 거의 동일

 

※ 숫자함수 종류

 

3) 날짜형 함수

  - 날짜 연산과 관련된 함수

  - ORACLE과 SQL-Server 함수 거의 다름

 

※ 날짜함수 종류

함수명	함수기능	사용예시	출력	기타설명
SYSDATE	현재 날짜와 시간 리턴	SYSDATE	2024/02/14 18:44:34	날짜 출력 형식에 따라 다르게 출력됨  (날짜만 출력될 수 있음)
CURRENT_DATE	현재 날짜 리턴	CURRENT_DATE	2024/02/14	날짜 출력 형식에 따라 다르게 출력됨  (시간이 출력될 수 있음)
CURRENT_TIMESTAMP	현재 타임스탬프 리턴	CURRENT_TIMESTAMP	2024/02/14 18:45:29 +09:00
ADD_MONTHS(날짜, n)	날짜에서 n개월 후 날짜 리턴	ADD_MONTHS(SYSDATE, 3)	2024/05/14 18:44:34	n이 음수인 경우 n개월 이전 날짜 리턴
LAST_DAY(날짜)	주어진 월의 마지막 날짜 리턴	LAST_DAY(SYSDATE)	2024/02/29 18:44:34
NEXT_DAY(날짜, n)	주어진 날짜 이후 지정된 요일의 첫 번째 날짜 리턴	NEXT_DAY(SYSDATE, 1)	2024/02/18 18:51:35	1:일요일, 2:월요일, ..., 7:토요일
ROUND(날짜, 자리수)	날짜 반올림	ROUND(SYSDATE, 'MONTH')	2024-02-01 0:00
TRUNC(날짜, 자리수)	날짜 버림	TRUNC(SYSDATE, 'MONTH')	2024-02-01 0:00
MONTHS_BETWEEN(날짜1, 날짜2)	날짜1과 날짜2 사이의 개월 수 리턴	MONTHS_BETWEEN(TO_DATE('2024-11-18', 'YYYY-MM-DD'), TO_DATE('2023-11-18', 'YYYY-MM-DD'))	12

 

SQL-Server)

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

4) 변환함수

  - 값의 데이터 타입을 변환

  - 문자를 숫자로, 숫자를 문자로, 날짜를 문자로 변경

 

※ 변환함수 종류

함수명	함수기능	사용예시	출력	기타설명
TO_NUMBER(대상)	숫자 타입으로 변경하여 리턴	TO_NUMBER('100')	100	문자 100을 숫자 100으로 리턴
TO_CHAR(대상, 포맷)	1) 날짜의 포맷 변경	TO_CHAR(SYSDATE, 'MM/DD-YYYY')	02/14-2024	날짜 형식 변경(리턴은 문자타입)
	2) 숫자의 포맷 변경	TO_CHAR(9000, '9,999')	9,000	천단위 구분기호 생성(리턴은 문자타입)
		TO_CHAR(9000, '09999'	09000	총 5자리로 리턴(앞 자리수 0으로)
TO_DATE(문자, 포맷)	주어진 문자를 포맷 형식에 맞게 읽어 날짜로 리턴	TO_DATE('2024/01/01', 'YYYY/MM/DD')	2024/01/01 00:00:00	날짜로 리턴됨
CAST(대상 AS 데이터타입)	대상을 주어진 데이터타입으로 변환	CAST('100' AS int)	100	문자 100을 숫자 100으로 리턴

 

SQL-Server)

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

5) 그룹함수

  - 다중행 함수

  - 여러 값이 input값으로 들어가서 하나의 요약된 값으로 리턴

  - group by와 함께 자주 사용됨

  - ORACLE과 SQL-Server 거의 동일

 

※ 그룹함수 종류

함수명	함수기능	사용예시	출력	기타설명
COUNT(대상)	행의 수 리턴	TO_NUMBER('100)	각 연산 결과	NULL 무시하고 연산
SUM(대상)	총 합 리턴	SELECT SUM(SAL) FROM EMP;	각 연산 결과	NULL 무시하고 연산
AVG(대상)	평균 리턴	SELECT AVG(SAL) FROM EMP;	각 연산 결과	NULL 무시하고 연산
MIN(대상)	최솟값 리턴	SELECT MIN(SAL) FROM EMP;	각 연산 결과	NULL 무시하고 연산
MAX(대상)	최댓값 리턴	SELECT MAX(SAL) FROM EMP;	각 연산 결과	NULL 무시하고 연산
VARIANCE(대상)	분산 리턴	SELECT VARIANCE(SAL) FROM EMP;	각 연산 결과	NULL 무시하고 연산
STDDEV(대상)	표준편차 리턴	SELECT STDDEV(SAL) FROM EMP;	각 연산 결과	NULL 무시하고 연산

 

SQL-Server)

• VARIANCE 👉🏻 VAR
• STDDEV 👉🏻 STDEV

6) 일반함수

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

 

※ 일반(기타)함수 종류

함수명	함수기능	사용예시	출력	기타설명
DECODE(대상, 값1, 리턴1, 값2, 리턴2, ..., 그외리턴)	대상이 값1이면 리턴1, 값2와 같으면 리턴2, .. 그외에는 그외리턴값 리턴	DECODE(DEPTNO, 10, A, B)	A 또는 B	대소비교에 따른 치환 불가 그외리턴 생략 시 널 리턴
NVL(대상, 치환값)	대상이 널이면 치환값으로 치환하여 리턴	NVL(COMM, 0)	COMM값 또는 0 리턴
NVL2(대상, 치환값1, 치환값2)	대상이 널이면 치환값2로 치환, 널이 아니면 치환값1로 치환하여 리턴	NVL2(COMM, COMM*1.1, 0)	COMM*1.1값 또는 0 리턴
COALESCE(대상1, 대상2, ..., 그외리턴)	대상1이 널이면 대상2, 대상2가 널이면 대상3, ..., 모두가 널이면 그외리턴값이 리턴됨	밑에 참고	밑에 참고	그외리턴값 생략 시 널 리턴
CASE문	조건별 치환 및 연산 수행	밑에 참고	밑에 참고

 

 

예) DECODE 사용 예제1

👉🏻 부서번호가 10번이면 인사부, 20번이면 재무부, 나머지는 널 리턴

 

 

예) DECODE 사용 예제2

👉🏻 DEPTNO가 10이면서 JOB이 CLERK이면 A, DEPTNO가 10이면서 CLERK가 아니면 B, DEPTNO가 10이 아니면 C

 

 

예) NVL, NVL2 사용 예제

👉🏻 NVL2의 경우 NVL이랑 다르게 COMM의 값이 널이 아닐 때도 치환값 정의 가능

👉🏻 NVL2의 경우 COMM이 널이 아니면 10% 인상값, 널이면 500 리턴

 

 

예) COALESCE 사용 예제

👉🏻 DEPTNO1과 DEPTNO2중 널 아닌값 출력(둘 다 널이 아니면 맨 앞 순서대로 출력)

모두 널일 경우 마지막 인수값(0) 출력

 

 

예) CASE문 사용 예제 1

👉🏻 CASE문을 사용하여 여러 조건(대소비교 가능)에 대한 치환 및 연산 가능!

 

 

예) CASE문 사용 예제 2

- CASE와 WHEN사이에 아무것도 없으면 풀 조건이 들어가는 거고, CASE와 WHEN사이에 어떤 대상이 있으면 생략된 조건이 들어간다. 그래서 상수만 이콜비교를 할 수 있다. (⭐매우중요⭐)

👉🏻 동등비교 시 위처럼 비교대상(DEPTNO)를 CASE와 WHEN 사이에 배치하면서 WHEN절마다 반복하지 않아도 됨(이 때, DEPTNO. 데이터 타입과 WHEN절의 명시된 비교대상의 데이터타입이 반드시 일치해야 함)