[Oracle] SQL의 단일행 함수 정리(문자열 관련, 숫자 관련, 날짜 관련, 형변환 관련, NULL 처리, 선택 관련 함수), 그룹 함수 정리

Rachel_ 2022. 3. 23. 15:29

함수 : 자바의 메소드와 같은 의미.

종류

- 단일행 함수 : n개의 데이터 값을 읽어서 n개의 결과 값을 출력

- 그룹함수 : n 개의 데이터 값을 읽어서 1개의 결과를 출력( 따라서 group by 로 묶였을 경우, 그룹 당 함수를 실행 하고 결과를 반환하게 됨)

* 주의사항으로 단일행 함수와 그룹 함수는 함께 사용될 수 없음. 왜냐하면 결과로써 출력되는 행의 갯수 자체가 다르기 때문.

단일행 함수

1. 문자열 관련 함수

- LENGTH(STR) : STR(문자열) 데이터의 글자 수를 반환함

- LEGNTHB(STR) : STR(문자열) 데이터의 바이트 수를 반환함

* 한글의 경우 한 글자 당 3바이트로 취급되며, 영문-숫자-특수문자 등은 한 글자 당 1바이트로 취급됨

- INSTR(STR, '특정 문자', 찾을 위치의 시작값, 순번) : STR(문자열)의 '특정 문자'를 시작 값부터(앞 or 뒤) 시작해 n(순번)째 값의 위치인덱스를 NUMBER 데이터 타입으로 반환한다.

찾을 위치의 시작값 :

1 : 가장 앞 인덱스부터 찾는다. (기본값)

-1 : 가장 뒤 인덱스부터 찾는다.

순번 : 생략 시의 기본값은 1.

- SUBSTR(STR, POSITION, LENGTH) : 문자열로부터 특정 문자열을 추출해서 반환 (자바로 치면 문자열.substring() 메소드와 유사) 결과값은 CHARACTER 타입으로 반환된다. (문자열 형태)

LENGTH 는 생략 가능 (생략 시 끝까지 잘라냄)

    - STR : '문자열 리터럴' / 문자열 타입의 컬럼명
    - POSITION : 문자열 추출을 시작할 위치값, POSITION 번째 문자부터 추출하겠다.
    - LENGTH : 추출할 문자 갯수

- LPAD/RPAD (STR(문자열or 문자열 타입의 컬럼), 최종적으로반환할문자열의길이(바이트), '덧붙이고자하는문자')
    : 제시한 문자열에 임의의 문자를 왼쪽 또는 오른쪽에 덧붙여서 최종 N 길이만큼의 문자열을 반환

    결과값은 CHARACTER 타입으로 반환 (문자열 형태)
덧붙이고자하는문자 : 생략 가능 (DEFAULT 값이 있음)

- LTRIM/RTRIM (STR, '제거시키고자 하는 문자') : STR(문자열)에서 '제거시키고자 하는 문자'를 제거함. 결과값은 CHARACTER 타입으로 반환 (문자열 형태)
제거시키고자하는문자 : 생략 가능 (DEFAULT 값이 있음)

- TRIM ( BOTH/LEADING/TRAILING '제거시키고자 하는 문자' FROM STR)
    : 문자열의 양쪽/앞쪽/뒤쪽에 있는 특정 문자를 제거한 나머지 문자열을 반환

    결과값은 CHARACTER 타입으로 반환 (문자열 형태)
    BOTH : 양쪽에 있는 해당문자를 다 제거하겠다. (생략 시 기본값)
    LEADING : 앞쪽에 있는 해당문자를 다 제거하겠다. (LTRIM 과 동일한 역할)
    TRAILING : 뒤쪽에 있는 해당문자를 다 제거하겠다. (RTRIM 과 동일한 역할)

- CONCAT (STR1, STR2) : 전달된 두개의 문자열을 하나로 합친 결과를 반환. 결과값은 CHARACTER 타입으로 반환

- REPLACE (STR, '찾을문자', '바꿀문자') : STR 로 부터 '찾을문자' 를 찾아서 '바꿀문자' 로 바꾼 문자열을 반환. 결과값은 CHARACTER 타입으로 반환

2. 숫자 관련 함수

- ABS ( NUMBER ) : NUMBER 타입 데이터를 절대값으로 변환

- MOD ( NUMBER1, NUMBER2 ) : NUMBER1 을 NUMBER2 로 나누고 난 뒤의 나머지 값을 반환

- ROUND ( NUMBER, 위치 ) : NUMBER 타입 데이터를 위치 기준으로 반올림해주는 함수

위치

- 0, 1, 2... : 0은 기본값. 값이 커질수록 소수점 첫째 자리, 둘째 자리 순으로 늘어가게 됨.

- -1, -2... : 음수 값으로 갈수록 일의 자리, 십의 자리 순으로 자릿수가 줄어들게 됨

- CEIL (NUMBER) : 소숫점 아래의 수를 무조건 올림처리 해주는 함수

- FLOOR (NUMBER) : 소숫점 아래의 수를 무조건 버림처리 해주는 함수 (소숫점 절삭)

- TRUNC (NUMBER, 위치) : 위치 지정가능한 버림 처리를 해주는 함수
위치

- 0, 1, 2... : 0은 기본값. 값이 커질수록 소수점 첫째 자리, 둘째 자리 순으로 늘어가게 됨.

- -1, -2... : 음수 값으로 갈수록 일의 자리, 십의 자리 순으로 자릿수가 줄어들게 됨

3. 날짜 관련 함수

** DATE 타입 : 년, 월, 일, 시, 분, 초 를 다 포함한 자료형

- SYSDATE : 오늘날짜, 현재 내 컴퓨터의 시스템 날짜

- MONTHS_BETWEEN (DATE1, DATE2) : 두 날짜 사이의 개월수 반환, NUMBER 타입 반환
* 일, 시, 분, 초 가 소숫점으로 나옴.

* DATE1이 DATE2 보다 미래일 경우 양수값이 나오고, 반대로 과거일 경우 음수값이 나옴

- ADD_MONTHS (DATE, NUMBER) : 특정 날짜에 해당 숫자만큼의 개월수를 더한 날짜를 반환, DATE 타입 반환

- NEXT_DAY (DATE, 요일(문자/숫자)) : 특정 날짜에서 가장 가까운 해당 요일을 찾아서 그 날짜를 반환

* 요일의 경우 일요일을 1로 시작하여 월요일은 2... 일요일은 7로 정의된다.

* 현재의 기준 언어가 한글이기 때문에 '일' OR '일요일'이라고 적어도 함수가 작동한다.

만약 영어로 사용하고 싶다면 DDL 의 언어 변경 코드를 작성해주어야 한다.

해당 코드는 => ALTER SESSION SET NLS_LANGUAGE = AMERICAN; 와 같다.

- LAST_DAY (DATE) : 특정 날짜가 속한 달의 마지막 날짜를 구해서 반환, DATE 타입 반환

- EXTRACT : 년도 또는 월 또는 일 정보를 추출해서 반환 (NUMBER 타입 반환)

    - EXTRACT (YEAR FROM DATE) : 특정 날짜로부터 년도만 추출
    - EXTRACT (MONTH FROM DATE) : 특정 날짜로부터 월만 추출
    - EXTRACT (DAY FROM DATE) : 특정 날짜로부터 일만 추출

4. 형 변환 관련 함수

1. NUMBER 타입 데이터를 문자열 데이터로 변환

- TO_CHAR (NUMBER/DATE, '포맷') : 숫자형 또는 날짜형 데이터를 문자형 타입으로 변환 (CHARACTER 타입 반환)

포맷 :

- 포맷을 입력하지 않았을 경우, NUMBER가 그대로 문자열로 변환되어 출력된다.

ex) TO_CHAR(1234) == '1234'

- 포맷에 '0'을 입력할 수 있다. 만약 NUMBER의 자릿수보다 0을 더 적었을 경우, 남는 자릿수만큼 0으로 대체된다.

ex) TO_CHAR(1234, '00000') == '01234'

- 포맷에 '9'을 입력할 수 있다. 만약 NUMBER의 자릿수보다 9을 더 적었더라도, NUMBER은 문자열로 출력된다.

ex) TO_CHAR(1234, '99999999') == '1234'

- 지역 화폐의 단위를 입력하고 싶다면 포맷에 'L'과 함께 0이나 9를 입력하면 된다. 'L00000'의 경우

ex) TO_CHAR(1234, 'L00000') == '\01234'

- 지역 화폐의 단위를 입력하고 싶다면 포맷에 'L'과 함께 0이나 9를 입력하면 된다. 'L99999'의 경우

ex) TO_CHAR(1234, 'L99999') == '\1234'

- 만약 화페의 자릿수를 3자리마다 , 로 구분하여 출력하고 싶다면 'L999,999~~~' 로 입력하면 된다.

ex) TO_CHAR(1234, 'L99,999') == '\1,234'

2. DATE 타입 데이터를 문자열 데이터로 변환

- TO_CHAR (SYSDATE) : 포맷을 지정 안한 경우에는 'YY/MM/DD' 형식으로 나옴

- TO_CHAR (SYSDATE, 'YYYY-MM-DD') : 2022-03-22 와 같이 출력된다.

- TO_CHAR (SYSDATE, 'PM HH:MI:SS' ) : 오전/오후 HH : MI : SS 로 출력

- TO_CHAR (SYSDATE, 'HH24:MI:SS' ) : 오전/오후 구분 없이 24시간 형식으로 HH : MI : SS 로 출력

그 외에도 원하는 대로 포맷을 커스터마이징 할 수 있다.

** 'YYYY' 의 경우 무조건 2000년부터 시작. 반면 'RRRR'의 경우 반올림을 적용해 1950~1999 AND 2000~2049를 적용함.

-- 년도로써 쓸 수 있는 포맷
- TO_CHAR (SYSDATE, 'YYYY') -- 2022
- TO_CHAR (SYSDATE, 'RRRR') -- 2022
- TO_CHAR (SYSDATE, 'YY') - 22
- TO_CHAR (SYSDATE, 'RR') - 22
- TO_CHAR (SYSDATE, 'YEAR') : 영어로 년도수를 출력

-- 월로써 쓸 수 있는 포맷
- TO_CHAR (SYSDATE, 'MM') -- 03
- TO_CHAR (SYSDATE, 'MON') -- 3월 // 세 자리의 약어로 된 월 이름(영문 설정일 경우)
- TO_CHAR (SYSDATE, 'MONTH') -- 3월 // 영문설정에서 아홉 자리를 위해 공백을 추가한 월 이름
- TO_CHAR (SYSDATE, 'RM') -- III 로마 숫자로 나타냄

-- 일로써 쓸 수 있는 포맷
- TO_CHAR (SYSDATE, 'D') -- 4 // D 는 1주일 기준으로 몇일째인지 (일요일부터)
- TO_CHAR (SYSDATE, 'DD') -- 23 // DD 는 1달 기준으로 몇일째인지
- TO_CHAR (SYSDATE, 'DDD') -- 082 // DDD 는 1년 기준으로 몇일째인지 알려주는 포맷

-- 요일로써 쓸 수 있는 포맷
- TO_CHAR (SYSDATE, 'DY') -- 수 // DY 는 '요일' 을 뺀 형태로 출력
- TO_CHAR (SYSDATE, 'DAY') -- 수요일 // DAY 는 '요일' 을 붙여서 출력 (월요일, 화요일, 수요일, ..)

그 외에도, 2022년 03월 21일 (월) 포맷으로 적용시키고 싶다면

- TO_CHAR (SYSDATE, 'YYYY"년" MM"월" DD"일" (DY)') 과 같이 쌍따옴표로 년, 월, 일을 묶어주면 된다.

3. NUMBER/CHARACTER 데이터를 DATE 데이터로 변환

- TO_DATE (NUMBER/CHARACTER, '포맷') : 숫자형 또는 문자형 데이터를 날짜형으로 변환, DATE 타입 반환

'포맷' :

- 값 지정이 없다면 YY/MM/DD 가 기본값으로 출력된다.

** 02/12/12와 같이 0으로 시작하는 년도의 경우 NUMBER 타입의 021212는 불가능하고, '021212'와 같이 반드시 작은 따옴표로 묶어줘야 한다.

- TO_DATE ('20100101', 'YYYYMMDD') -- 10/01/01

- TO_DATE ('041030 143021', 'YYMMDD HH24MISS') -- 04/10/30 14:30:21

- TO_DATE ('140630', 'YYMMDD') -- 14/06/30 (2014년 06월 30일)

- TO_DATE ('980630', 'YYMMDD') -- 98/06/30 ( 그러나 실제로는 2098년 06월 30일) // 두자리 년도에 대해서 YY 포맷을 적용시켰을 경우 현재 세기로 나타남 (98 을 제시했을 때 2098년으로 나온다) 따라서 'RRMMDD' 포맷을 사용해주어야 한다.

- TO_DATE ('980630', 'RRMMDD') -- 98/06/30 (1998년 06월 30일)

4. CHARACTER 데이터를 NUMBER 데이터로 변환

- TO_NUMBER (CHARACTER, '포맷') : 문자형 데이터를 숫자형으로 변환, NUMBER 타입 반환

SELECT '123' + '123'
FROM DUAL; -- 246 : 자동형변환 후 산술연산까지 진행

SELECT '10,000,000' + '550,000'
FROM DUAL; -- 문자(,) 가 포함되어있기 때문에 자동형변환이 진행되지 않음

SELECT TO_NUMBER('10,000,000', '999,999,999') + TO_NUMBER('550,000', '999,999')
FROM DUAL;

SELECT TO_NUMBER('0123')
FROM DUAL; -- 0123 / 123

5. NULL 관련 함수

- NVL (컬럼명, 해당 컬럼값이 NULL일 경우 반환할 반환 값)

: 해당 컬럼값이 존재할 경우 기존의 컬럼값을 반환, 해당 컬럼값이 NULL 인 경우 내가 제시한 특정값을 반환

- NVL2 (컬럼명, 결과값1, 결과값2)

: 해당 컬럼값이 존재할경우 결과값1 반환, 만약 해당 컬럼값이 NULL 일 경우 결과값2 반환

- NULLIF (비교대상1, 비교대상2)
: 비교대상1과 비교대상2가 동일할 경우는 NULL 값 자체를 반환, 두개의 값이 동일하지 않을 경우에는 비교대상1 값을 반환

6. 선택 관련 함수(DECODE, CASE WHEN... )

- DECODE (비교대상, 조건값1, 결과값1,

조건값2, 결과값2,

조건값3, 결과값3, ...,

조건값N, 결과값N,

결과값(아무것도 해당값이 없을 경우) )

* 자바의 SWITCH 구문과 비슷.

- CASE WHEN 조건식1 THEN 결과값1
         WHEN 조건식2 THEN 결과값2
         ...
         WHEN 조건식N THEN 결과값N
         ELSE 결과값
    END

* 자바의 IF - ELSE 구문과 비슷

두 구문의 차이점 : DECODE 선택함수와 비교하면 DECODE 는 해당 조건검사 시 동등비교만을 수행한다면
CASE WHEN THEN 구문으로는 특정 조건 제시시 내 맘대로 조건식을 기술 가능한 구문

그룹 함수

1. SUM (숫자타입의컬럼명) : 해당 컬럼값들의 총 합계를 반환해주는 함수

2. AVG (숫자타입의컬럼명) : 해당 컬럼값들의 평균값을 구해서 반환

3. MIN (아무타입컬럼명) : 해당 컬럼값들 중에 가장 작은값을 반환

4. MAX (아무타입컬럼명) : 해당 컬럼값들 중에 가장 큰값을 반환

5. COUNT (*/컬럼명/DISTINCT 컬럼명) : 조회된 행의 갯수를 세서 반환
-- COUNT (*) : 조회 결과에 해당하는 모든 행의 갯수를 세서 반환하겠다.
-- COUNT (컬럼명) : 내가 제시한 해당 컬럼값이 NULL 이 아닌것만 행의 갯수를 세서 반환하겠다.
-- COUNT (DISTINCT 컬럼명) : 제시한 컬럼값이 중복값이 있을 경우에는 하나로만 세서 행의 갯수를 반환하겠다. 단, NULL 포함 X