[2과목] 2-3. 함수

■ 함수 정의

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

 

■ 함수 기능

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

 

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

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

단수행 함수와 복수행 함수의 input과 ouput의 관계

 

 

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

 

1) 문자형 함수

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

• SQL - Server )
  • SUBSTR => SUBSTRING
  • LENGTH => LEN
  • INSTR => CHARINDEX

 

2) 숫자형 함수

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

 

 

3) 날짜형 함수

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

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

 

4) 변환함수

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

 

• 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 예시1)

select deptno, decode(deptno,10,'인사부',20,'재무부') as dname
from emp;

 

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

 

 

- decode 예시2)

select deptno, job, decode(deptno,10, decode(job,'CLERK','A','B'),'C') as result
from emp;

 

▶ deptno가 10이면서 job이 CLERK이면 A, deptno가 10이면서 CLERK가 아니면 B, deptno가 10이 아니면 C

 

 

- coalesce 예시)

select deptno1, deptno2, coalesce(depno1, deptno2, 0) as result
from student;

 

▶ deptno1과 deptno2 중 널이 아닌 값 출력(둘 다 널이 아니면 맨 앞 순서대로 출력) 모두 널이면 0 출력

 

 

- case문 예시1)

select sal, case when sal<2000 then 'C' when sal<3000 then 'B' else 'A' end as sal_grade
from emp;

 

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

 

 

- case문 예시2)

select deptno, case deptno when 10 then '인사부' when 20 then '총무부' when 30 then '재무부' else '기타' end as dname1,
	case when depno=10 then '인사부' when deptno=20 then '총무부' when deptno = 30 then '재무부' else '기타' end as dname2
from emp;

 

▶ 동등 비교 시 위처럼 비교대상 (deptno)를 case와 when 사이에 배치하면서 when절마다 반복하지 않아도 됨(이때 deptno는 데이터 타입과 when절의 명시된 비교 대상의 데이터 타입이 반드시 일치해야 함)

 

 

 

7) NULL 관련 함수

NVL(표현식1, 표현식2)        // oracle ISNULL(표현식1, 표현식2)  // sql server	표현식1의 결과값이 NULL이면 표현식2의 값을 출력함. 단, 표현식1과 표현식 2의 결과 데이터 타입이 같아야 함. 가장 많이 사용되는 함수임
NULLIF(표현식1, 표현식2)	표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 반환함.
COALESCE(표현식1, 표현식2,...)	임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타냄. 모든 표현식이 NULL이라면 NULL을 리턴함.