제 3-6절 함수

 

1. 내장함수

 

벤더에서 제공하는 함수 내장함수 vs 사용자가 정의할 수 있는 함수

내장 함수 - 단일행 함수

       - 다중행 함수  - 집계함수

   - 그룹함함수

   - 윈도우함수

입력되는 값이 아누리 많아도 출력은 하나만 된다는 M : 1관계

 

단일행 함수의 종류 (oracle/sqlserver)

종류	내용	함수
문자형	문자를 입력하면 문자나 숫자값 반환	Lower, upper, substr/substiring, length/len, Ltrim, Rtrim, trim, ASCII
숫자형	숫자 입력하면 숫자값 반환	Abs, mod, round, trunc, sign, chr/char, ceil/ceiling floor, exp, log, ln, power, sin, cos, tan
날짜형	DATE 타입의 값을 연산	Sysdate/getdate, extract/datepart, to_number(to_char('YYYY','MM','DD')/YEAR,MONTH,DAY
변환형	문자, 숫자, 날짜형 값의 데이터 타입 변환	To_number, to_char, to_date / cast, convert
NULL관련	NULL처리하기 위한 함수	NVL/ISNULL, NULLIF, COALESCE

 

특징

1. Select, where, order by 절에서 사용가능
2. 행들에 대한 개별적인 작용하여 값 조작
3. 여러 인자를 입력해도 단 하나의 결과만 리턴
4. 인자로 상수, 변수, 표현식 사용 가능하고 하나의 인수를 가지는 경우도 있고 여러개의 인수도 가질 수 있음
5. 함수의 중첩 가능

 

2. 문자형함수

함수	설명
LOWER(문자열)	소문자로 변경
UPPER(문자열)	대문자로 변경
ASCII(문자열)	숫자를 ASCII코드로 변경
CHR/CHAR(ASCII번호)	ASCII코드를 문자나 숫자로 변경
CONCAT(문자열1, 문자열2)	Oracle, mysql에서만, 문자열1과 문자열2을 연결, || 나 + 함수와 동일
SUBSTR/SUBSTRING(문자열, M[, N ])	문자열 중 m위치에서 n개의 문자 길이에 해당하는 문자 추출, n 생략되면 다 추출
LENGTH/LEN(문자열 [, 지정문자])	문자열의 개수를 숫자값으로 변환
LTRIM(문자열 [, 지정문자])	첫문자부터 지정문자가 나타나면 해당 문자 제거, sqlserver 사용 불가
RTRIM(문자열 [, 지정문자])	마지막문자부터 지정문자가 나타는 동안 해당 문자 제거, sqlserver 사용 불가
TRIM ([leading|trailing|both] 지정문자 from 문자열)	머리말, 꼬리말 또는 양쪽 지정문자 제거, sqlserver 사용 불가

 

 

DUAL 테이블의 특성

• 사용자 SYS가 소유, 모든 사용자가 엑세스 가능한 테이블
• DUMMY 테이블
• 'X'라는 값이 들어있는 행 1건을 포함
• SQLserver 에서는 select만 있어도가능하여 DUAL이 필요없음

 

3. 숫자형 함수

함수	설명
ABS(숫자)	절대값
SIGN(숫자)	양수인지, 음수인지, 0인지 구별
MOD(숫자1, 숫자2)	숫자1을 숫자2로 나누어 나머지 값 리턴,  % 함수랑 동일
CEIL/CEILING(숫자)	올려서 정수로 반환
FLOOR(숫자)	내려서 정수로 반환
ROUND(숫자 [, m])	소수점 m 자리에서 반올림
TRUNC(숫자 [, m])	소수점 m 자리에서 버림, sqlserver에서 사용불가
SIN, COS, TAN	삼각함수 값
EXP, POWER, SQRT, LOG, LN	지수, 거듭제곱, 제곱근, 자연로그 값 리턴

 

 

4. 날짜형 함수

 

함수	설명
SYSDATE/GETDATE( )	현재 날짜와 시간
EXTRACT('YEAR'|'MONTH'|'DAY' from d) /DATEPART('YEAR'|'MONTH'|'DAY', d)	날짜데이터에서 년/월/일 데이터 출력 시분초도 가능
TO_NUMBER(TO_CHAR(d,'YYYY')/ YEAR(d) TO_NUMBER(TO_CHAR(d,'MM')/ MONTH(d) TO_NUMBER(TO_CHAR(d,'DD')/ DAY(d)	날짜데이터에서 년/월/일 데이터 출력 TO_NUMBER제외하면 문자열로 출력됨

 

5. 변환형 함수

특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수

종류	설명
명시적 데이터 유형 변환	데이터 변환형 함수로 데이터 유형을 변환하도록 명시해주는 경우
암시적 데이터 유형 변환	테이터베이스가 자동으로 데이터 유형을 변환하여 계산하는 경우

암시적 데이터 변환은 성능저하 발생, 알아서 계산하지 않는 에러 발생할 수 있어서 명시적 데이터 유형 변환 방법 사용 필요

 

Oracle 함수	설명
TO_NUMBER(문자열)	숫자로 변환
TO_CHAR(숫자|날짜 [, FORMAT])	문자열로 변환
TO_DATE(문자열 [, FORMAT])	날짜로 변환

 

SQL server 함수	설명
CAST(expression AS date_type[(length)])	목표 데이터 유형으로 변환
CONVERT (date_type [(length)], expression[, style])	목표 데이터 유형으로 변환

예제  SELECT TEAM_ID, CAST(ZIP_CODE1 AS INT) + CAST(ZIP_CODE2 AS INT) 우편번호합 FROM TEAM;

 

6. CASE 표현

단일행 CASE 표현의 종류

함수	설명
CASE      SIMPLE_CASE_EXPRESSION 조건      ELSE 표현절   END	SIMPLE_CASE_EXPRESSION 조건에 따라서 맞으면 then을 수행 아니면 else 절 수행 ( EQUI = 조건 ) CASE EXPR WHEN COMPARISON_EXPR THEN RETURN_EXPR ELSE 표현절 END
CASE      SEARCHED_CASE_EXPRESSION 조건      ELSE 표현절   END	SEARCHED_CASE_EXPRESSION 조건에 따라서 맞으면 then을 수행 아니면 else 절 수행 ( EQUI 뿐만 아니라 = , >, >= , < , <= 가능) 중첩사용 가능 CASE WHEN CONDITION THEN RETURN_EXPR ELSE 표현절 END
DECODE(표현식, 기준값, 값1, … , default값)	오라클에서만 사용하는 함수 표현식 값이 기준값이면 값1출력, 기준값이 없음 디폴트값 SIMPLE_CASE_EXPRESSION 조건이랑 동일

예시 중첩 : SELECT ENAME, SAL,

CASE WHEN SAL >= 2000

THEN 1000

ELSE (CASE WHEN SAL >= 1000 THEN 500 ELSE 0 END)

END as BONUS FROM EMP;

 

7. NULL 관련 함수

 

가. NVL/ISNULL 함수

 

NULL 특성

• 정의되지 않은 값으로 0 또는 공백과는 다르다
• NOTNULL, PK아니면 널값 포함 가능하다
• 널 값을 포함하는 연산의 경우 결과는 널

 

함수	설명
NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2)	표현식1의 결과값이 NULL이면 표현식 2값이 출력된다. 단, 표현식 1, 2의 데이터 타입이 같아야함
NULLIF(표현식1, 표현식2)	표현식1이 표현식2과 같으면 NULL, 같지 않으면 표현식1 출력
COALESCE(표현식1, 표현식2 …)	임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타냄

 

나. NULL와 공집합

• 일반적인 NULL/ISNULL 함수 사용
• 공집합의 NULL/ISNULL 함수 사용 : 공집합을 대상으로 하지 않음

 

다. NULLIF

라. COALESCE

표현식1, 2의 둘중 1번의 값이 있음 1번을 2번에 값이 있음 2번을 1,2번 둘다 있다면 1번을 둘다 없다면 NULL을 표현

 

 분모에 0이면 무조건 에러, null은 무슨 연산을 하여도 null