[SQLP] SQL함수 - 단일행 함수

SQL함수에는 단일행 함수와 복수행 함수 두가지가 있다.

 

단일행 함수는 데이터값을 조작하는데 주로 사용되며, 행별로 함수를 적용하여 하나의 결과를 반환하는 함수이다.

또한 단일행 함수는 SQL명령문의 SELECT, WHERE, ORDER BY 절에서 사용할 수 있으며, 중첩하여 사용할 수 있다.

 

단일행 함수에는 

- 문자 함수

- 숫자 함수

- 날짜 함수

- 변환 함수

-- 묵시적 데이터 타입변환

-- 명시적 데이터 타입 변환

- 일반 함수

 

이렇게 구분할 수 있다.

 

1. 문자 함수

INITCAP : 문자열의 첫 번째 문자만 대문자로 변환 : INITCAP('student') -> Student

LOWER : 문자열 전체를 소문자로 변환

UPPER : 문자열 전체를 대문자로 변환

 

LENGTH :  문자열의 길이를 반환 :  LENGTH('홍길동') -> 3

LENGTHB : 문자열의 바이트 수를 반환 : LENGTHB('홍길동') -> 6

 

CONCAT : 두 문자열을 결합 , '||' 와 동일 : CONCAT('TE', 'ST') -> 'TEST'

SUBSTR : 특정 문자 또는 문자열 일부를 추출 : SUBSTR('TEST', 2,2) -> 'ES'  : 문자열의 N번째 인덱스부터 M개 짜르기

INSTR : 특정 문자가 출현하는 첫 번째 위치를 반환 : INSTR('TEST', 'ES') ->  2

LPAD : 오른쪽 정렬 후 왼쪽에 지정문자를 삽입 : LPAD('TEST',6,'*') -> '**TEST'

RPAD : 왼쪽 정렬 후 오른쪽에 지정문자를 삽입 : RPAD('TEST',6,'*') -> 'TEST**'

LTRIM : 왼쪽의 지정문자를 삭제 : LTRIM('**TEST', '*') -> 'TEST'

RTRIM : 오른쪽의 지정문자를 삭제 : RTIM('TEST**', '*') -> 'TEST'

 

2. 숫자함수

ROUND : 지정한 자리 이하에서 반올림 : ROUND(123.178, 1) -> 123.2

TRUNC : 지정한 자리 이하에서 절삭 : TRUNC(123.178, 1) -> 123.1

MOD : m을 n으로 나눈 나머지 : MOD(12,10) -> 2

CEIL :  지정한 값보다 큰 수 중에서 가장 작은 정수 : CEIL(123.17) -> 124

FLOOR : 지정한 값보다 작은 수 중에서 가장 큰 정수 : FOOLR(123.178) -> 123

 

3. 날짜함수

SYSDATE : 시스템의 현재 날짜 : '2022/07/27'

MONTHS_BETWEEN : 날짜와 날짜사이의 개월을 계산 : MONTHS_BETWEEN( '2023/01/01', '2022/01/01') -> 12

ADD_MONTHS : 날짜에 개월을 더한 날짜 계산 : ADD_MONTHS('2022/07/27',3) -> '2022/10/27'

NEXT_DAY : 날짜후의 첫 요일의 날짜를 계산 : NEXT_DAY('2022/07/27', '일') -> '2022/07/31'  :: 1 일 / 2 월 .... 7 토 로 매핑됨

LAST_DAY : 월의 마지막 날짜를 계산 : LAST_DAY('2022/08/01') -> '2022/08/31'

ROUND : 날짜를 반올림 :  TO_CHAR(ROUND(SYSDATE) ,'YYYY/MM/DD HH24:MI:SS') -> 2022/07/27 00:00:00 오전이면 날짜내림, 오후면 날짜 올림

TRUNC : 날짜를 절삭 : TO_CHAR(TRUNC(SYSDATE), 'YYYY/MM/DD HH24:MI:SS') -> 2022/07/27 00:00:00 무조건 시간 초기화

 

4. 묵시적 데이터 타입 변환

정확한 연산을 위해 데이터타입을 내부적으로 변환하는 경우이다.

예를들어 WHERE A=B 에서 비교되는 데이터 타입이 다른 경우 A와 B를 동일한 데이터 타입으로 비교하기 위해서 A,B중 하나의 데이터타입을 내부적으로 변환한다.

 

NUMBER = VARCHAR2 / CHAR -> VARCHAR나 CHAR가 NUMBER로 변환

VARCHAR2 / CHAR = NUMBER -> VARCHAR2나 CHAR 가 NUMBER로 변환

 

5. 명시적인 데이터 타입 변환

TO_CHAR : 숫자/날짜 타입을 문자로 변환 : TO_CHAR('22/07' , 'YYYY-MM') -> '2022-07'

TO_NUMBER : 문자열을 숫자 타입으로 변환 : TO_NUMBER('1000') -> 1000 

TO_DATE : 문자열을 날짜 타입으로 변환 : TO_DATE('20220701') -> '2022/07/01'

 

6. 일반함수

NVL : NULL값 변환 함수 : NVL( 'NULL값을 포함하는 컬럼 또는 표현식', NULL을 대체하는 값) : NVL(SAL, 0)

NVL2 : NVL2('NULL을 포함하는 컬럼 또는 표현식', 'NULL이 아닐때 반환하는 값', NULL일때 대체되는 값)  : NVL2(SAL, SAL, 0)

NULLIF : 두개의 표현식을 비교하여 값이 동일하면  NULL을 반환하고, 동일하지 않으면 첫번째 표현식값을 반환 : NULLIF( LENGTHB(NAME), LENGTH(NAME))

COALESCE : 인수중에서 NULL이 아닌 첫 번째 인수를 반환하는 함수이다. N개의 인수중에서 첫 번째 인수 값이 NULL이면 두 번째 값을 출력하고 , 두 번째 인수값이 NULL이면 세번 째 인수 값을 출력한다. : COALESCE(SAL, COMM, 0)

DECODE : 표현식과 찾고자 하는 값이 일치하면 RESULT값 반환 없는 경우에는 기본값 반환 DECODE(DEPT_NO , '101', '컴퓨터공학', '102', '전자공학', '학과없음')

-> DEPT_NO가 101이면 컴퓨터공학, 102이면 전자공학, 일치하지 않으면 학과없음 이라고 출력하는 것이다.

CASE : CASE함수는 DECODE함수의 기능을 확장한 함수이다. \

사용법 : CASE expression WHEN comparison_exp1 THEN return_exp1

                                          [WHEN comparison_exp2 THEN return_exp2

                                          WHEN comparison_exp3 THEN return_exp3

                                           ELSE else_expression]

               END

이렇게 사용하며

CASE WHEN dept_no='101' THEN sal*0.1

          WHEN dept_no='102' THEN sal*0.2

          WHEN dept_no='103' THEN sal*0.3

          ELSE 0

END bonus

위와 같이 예시를 들 수 있다.