DB (2) DML(SELECT) 단일행 함수

▶ 함수 FUNCTION

- 자바로 따지면 메서드와 같은 존재이다.

- 매개변수로 전달된 값들을 읽어서 계산한 결과를 반환해 준다.

- 단일행 함수 :  n개의 값을 읽어서 n개의 결과를 리턴해준다.(매 행마다 함수 실행 후 결과 반환)
- 그룹 함수 : n개의 값을 읽어서 1개의 결과를 리턴해준다.(하나의 그룹별로 함수 실행 후 결과 반환)

※ 단일행 함수와 그룹함수는 함께 사용할 수 없다.(결과 행의 개수가 다르기 때문이다.)

 

◆ 단일행 함수

▶ 문자열과 관련된 함수

■ LENGTH / LENGTHB

- LENGTH(문자열) : 전달된 문자열의 글자 수 반환
- LENGTHB(문자열) : 전달된 문자열의 바이트 수 반환

- 결괏값은 숫자로 반환된다.(NUMBER 타입)

- 문자열 : 문자열 형식의 리터럴, 문자열 정보가 저장된 컬럼
- 한글은 글자당 3BYTE OR 2BYTE이며 영문, 숫자, 특수문자 : 글자당 1BYTE로 취급된다.

ex)

SELECT LENGTH('오라클!'), LENGTHB('오라클!')
FROM DUAL;

 

 

■ DUAL

- 가상 테이블(DUMMY TABLE) : 산술연산이나 가상컬럼 등 값을 한 번만 테스트 용도로 출력하고 싶을 때 사용하는 테이블

 

■ INSTR

- INSTR(문자열, 특정문자, 찾을 위치의 시작값, 순번) : 문자열로부터 특정문자의 위치값 반환

- 찾을 위치의 시작값과 순번은 생략이 가능하다.

- 결괏값은 NUMBER타입으로 반환해 준다.

- 찾을 위치의 시작값(1/-1)

  1 : 앞에서부터 찾겠다.(생략 시 기본값)

  -1 : 뒤에서부터 찾겠다.

 

ex)

SELECT INSTR('AABAACAABBAA', 'B')
FROM DUAL; -- 3

SELECT INSTR('AABAACAABBAA', 'B', 1)
FROM DUAL; -- 3

SELECT INSTR('AABAACAABBAA', 'B', -1)
FROM DUAL; -- 10

SELECT INSTR('AABAACAABBAA', 'B', -1, 2)
FROM DUAL; -- 9

SELECT INSTR('AABAACAABBAA', 'B', -1, 0)
FROM DUAL; -- 범위를 벗어난 순번을 제시했을 경우 오류 발생

※ 인덱스처럼 글자의 위치를 찾는 것은 맞지만 자바처럼 0부터 시작이 아니라 1부터 시작한다.

 

■ SUBSTR

- SUBSTR(문자열, 처음위치, 추출할 문자개수) : 문자열로부터 특정 문자열을 추출하는 함수

- 결괏값은 CHARACTER타입으로 반환(문자열 형태)

- 추출할 문자 개수는 생략이 가능하다.(생략 시 문자열 끝까지 추출하겠다는 의미)

- 처음 위치는 음수로 제시가 가능하다.(뒤에서부터 n번째 위치로부터 문자를 추출하겠다는 의미)

 

ex)

SELECT SUBSTR('SHOWMETHEMONEY', 7)
FROM DUAL; -- THEMONEY

SELECT SUBSTR('SHOWMETHEMONEY', 5, 2)
FROM DUAL; -- ME

SELECT SUBSTR('SHOWMETHEMONEY', 1, 6)
FROM DUAL; -- SHOWME

SELECT SUBSTR('SHOWMETHEMONEY', -8)
FROM DUAL; -- THE

 

■ LPAD / RFAD

- LPAD/RFAD(문자열, 최종적으로 반환할 문자의 길이, 덧붙이고자 하는 문자) 
: 제시한 문자열에 덧붙이고자 하는 문자를 왼쪽, 오른쪽에 덧붙여서 최종적으로 n길이만큼의 문자열을 반환

- 결괏값은 CHARACTER타입으로 반환

- 덧붙이고자 하는 문자는 생략이 가능하다.

 

ex)

SELECT LPAD(EMAIL, 16), EMAIL
FROM EMPLOYEE;

SELECT RPAD(EMAIL, 20, '#')
FROM EMPLOYEE;

-- 주민등록번호 조회 : 621235-1985634 => 621235-1******
-- EMP_NAME, 주민등록번호 조회
SELECT EMP_NAME, RPAD(SUBSTR(EMP_NO, 1, 8), 14, '*') AS "주민등록번호 조회"
FROM EMPLOYEE;

 

■ LTRIM / RTRIM

- LTRIM/RTRIM(문자열, 제거시키고자 하는 문자)
: 문자열의 왼쪽, 오른쪽에서 제거시키고자하는 문자들을 찾아서 제거한 나머지 문자열을 반환

- 결괏값은 CHARACTER형태로 반환

- 제거시키고자 하는 문자는 생략이 가능하다.(기본값은 ' ')

 

ex)

SELECT LTRIM('           K        H           ')
FROM DUAL; -- K      H         

SELECT RTRIM('00012304568100', '0')
FROM DUAL; -- 12304568100

SELECT LTRIM('131322KH123', '123')
FROM DUAL; -- KH123

※ 제거시키고자하는 문자열을 통으로 지워주는 게 아니라 문자 하나하나가 다 존재하면 지워주는 원리이다.

 

■ TRIM

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

- 결괏값은 문자열타입으로 변환해 준다.

- 옵션은 생략이 가능하다.(생략 시 기본값은 BOTH)

 

ex)

SELECT TRIM('    K       H    ')
FROM DUAL; -- K       H

SELECT TRIM('Z' FROM 'ZZZKHZZZZ')
FROM DUAL; -- KH

SELECT TRIM(LEADING 'Z' FROM 'ZZZKHZZZZ')
FROM DUAL; -- KHZZZZ(LTRIM과 비슷하다.)

SELECT TRIM(TRAILING 'Z' FROM 'ZZZKHZZZZ')
FROM DUAL; -- ZZZKH(RTRIM과 비슷하다.)

 

■ LOWER / UPPER / INITCAP

- LOWER(문자열) : 소문자로 변경
- UPPER(문자열) : 대문자로 변경
- INITCAP(문자열) : 각 단어의 첫 번째 글자만 대문자로 변경

ex)

SELECT LOWER('Welcome'), UPPER('Welcome'), INITCAP('Welcome to C class')
FROM DUAL;
-- welcome, WELCOME, Welcome To C Class

 

■ CONCAT

- CONCAT(문자열1, 문자열2) : 전달된 문자열 두 개를 하나의 문자열로 합쳐서 반환해 준다.

ex)

SELECT CONCAT('홍길동', 'HGD')
FROM DUAL; -- 홍길동HGD

SELECT '홍길동' || 'HGD'
FROM DUAL; -- 홍길동HGD

 

■ REPALCE

- REPLACE(문자열, 찾을 문자, 바꿀문자) : 문자열로부터 찾을 문자를 찾아서 바꿀문자로 바꾼 문자열을 반환해 준다.

ex)

SELECT REPLACE('서울시 강남구 역삼동', '역삼동', '삼성동')
FROM DUAL; -- 서울시 강남구 삼성동

 

▶ 숫자와 관련된 함수

■ ABS

- ABS(절댓값을 구할 숫자) : 절대값을 구해주는 함수

- 결괏값은 NUMBER형태로 반환해 준다.

ex)

SELECT ABS(-50)
FROM DUAL; -- 50

 

■ MOD

- MOD(숫자, 나눌 값) : % 두 수를 나눈 나머지 값을 반환해 주는 함수 */

ex)

SELECT MOD(10, 3)
FROM DUAL; -- 1

SELECT MOD(-10, 3)
FROM DUAL; -- -1

SELECT MOD(10.9, 3)
FROM DUAL; -- 1.9

 

■ ROUND

- ROUND(반올림하고자 하는 수, 반올림할 위치) : 반올림처리를 해준다.

- 반올림할 위치 : 소수점 기준으로 아래 N번째 수에서 반올림하겠다.
                            생략이 가능하다.(위치 생략 시 기본값 0, 소수점 첫 번째 자리에서 반올림을 하겠다.)

ex)

SELECT ROUND(123.456)
FROM DUAL; -- 123

SELECT ROUND(123.456, 1)
FROM DUAL; -- 123.5

SELECT ROUND(123.456, -1)
FROM DUAL; -- 120

 

■ CEIL / FLOOR

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

ex)

SELECT CEIL(123.11111)
FROM DUAL; -- 124

SELECT FLOOR(123.999)
FROM DUAL; -- 123

 

■ TRUNC

- TRUNC(버림처리할숫자, 위치) : 위치지정이 가능한 버림처리 함수

- 위치 생략 시 기본값은 0이다.(FLOOR 함수와 동일하다.)

ex)

SELECT TRUNC(123.786)
FROM DUAL; -- 123

SELECT TRUNC(123.786, 1)
FROM DUAL; -- 123.7

SELECT TRUNC(123.786, -1)
FROM DUAL; -- 120

 

▶ 날짜 관련 함수

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

■ MONTHS_BETWEEN

1. MONTHS_BETWEEN(DATE1, DATE2) : 두 날짜 사이의 개월수를 반환(반환값은 NUMBER)

- DATE2가 더 미래일 경우 음수가 나온다. 

 

■ ADD_MONTHS

2. ADD_MONTHS(DATE, NUMBER) : 특정 날짜에 해당 숫자만큼 개월수를 더한 날짜를 반환해 준다.(결괏값은 DATE타입)

ex)

-- 오늘 날짜로부터 5개월 이후
SELECT ADD_MONTHS(SYSDATE, 5)
FROM DUAL; -- 23/10/31

 

■ NEXT_DAY

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

ex)

SELECT NEXT_DAY(SYSDATE, '토요일')
FROM DUAL; -- 23/06/03

SELECT NEXT_DAY(SYSDATE, '토')
FROM DUAL; -- 23/06/03

SELECT NEXT_DAY(SYSDATE, 7)
FROM DUAL; -- 23/06/03

※ 1:일, 2:월, 3:화, 4:수, 5:목, 6:금, 7:토

 

■ LAST_DAY

4. LAST_DAY(DATE) : 해당 특정날짜 달의 마지막 날짜를 구해서 반환해 준다.(DATE자료형)

ex)

SELECT LAST_DAY(SYSDATE)
FROM DUAL; -- 23/05/31

 

■ EXTRACT

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

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

ex)

SELECT EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE), EXTRACT(DAY FROM SYSDATE)
FROM DUAL; -- 2023, 5, 31

 

▶ 형변환 함수1(NUMBER/DATE => CHARACTER)

■ TO_CHAR

- TO_CHAR(NUMBER/DATE, 포맷) : 숫자형 또는 날짜형 데이터를 문자열 타입으로 반환(포맷에 맞춰서)

ex)

SELECT TO_CHAR(1234)
FROM DUAL; -- 1234 => '1234'

SELECT TO_CHAR(1234, '00000')
FROM DUAL; -- 1234 => '01234' : 빈 공간을 0으로 채워준다.

SELECT TO_CHAR(1234, '99999')
FROM DUAL; -- 1234 => '1234' : 빈 칸을 ' '으로 채워준다.

SELECT TO_CHAR(1234, 'L00000')
FROM DUAL; -- L : LOCAL => 현재 설정된 나라의 화폐단위를 나타낸다.

SELECT TO_CHAR(1234, 'L99,999')
FROM DUAL; -- ￦1,234

'2023-05-31'

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD')
FROM DUAL; -- 2023-05-31

시 분 초 : 오전(AM)/오후(PM)

SELECT TO_CHAR(SYSDATE, 'PM HH:MI:SS')
FROM DUAL; --오후 03:35:30

시 분 초 : 24시간 형태

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS')
FROM DUAL; -- 13:36:17

년도로 쓸 수 있는 포맷

SELECT TO_CHAR(SYSDATE, 'YYYY'), TO_CHAR(SYSDATE, 'RRRR'), TO_CHAR(SYSDATE, 'YY'), 
TO_CHAR(SYSDATE, 'RR'), TO_CHAR(SYSDATE, 'YEAR')
FROM DUAL; -- 2023, 2023, 23, 23, TWENTY TWENTY-THREE

- YY와 RR의 차이점
- R이 뜻하는 단어 : ROUND(반올림)

- YY : 앞자리에 무조건 20이 붙는다. => (20)21
- RR : 50년 기준으로 작으면 20, 크면 19가 붙는다. => 1989

월로써 쓸 수 있는 포맷

SELECT TO_CHAR(SYSDATE, 'MM'), TO_CHAR(SYSDATE, 'MON'), 
TO_CHAR(SYSDATE, 'MONTH'), TO_CHAR(SYSDATE, 'RM')
FROM DUAL; -- 05, 5월, 5월 ,V

일로써 쓸 수 있는 포맷

SELECT TO_CHAR(SYSDATE, 'D'), TO_CHAR(SYSDATE, 'DD'), TO_CHAR(SYSDATE, 'DDD')
FROM DUAL; -- 4, 31, 151

- D : 1주일 기준으로 일요일부터 며칠째인지 알려주는 포맷
- DD : 1달 기준으로 1일부터 며칠째인지 알려주는 포맷
- DDD : 1년 기준으로 1월 1일부터 며칠째인지 알려주는 포맷

요일로써 쓸 수 있는 포맷

SELECT TO_CHAR(SYSDATE, 'DY'), TO_CHAR(SYSDATE, 'DAY')
FROM DUAL; -- '요일' 이라는 단위가 있냐 없냐의 차이.

 

▶ 형변환 함수2(NUMBER/CHARACTER => DATE)

■ TO_DATE

- TO_DATE(NUMBER/CHARACTER, 포맷) : 숫자형, 문자열 데이터를 날짜형 데이터로 변환

ex)

SELECT TO_DATE(20210101)
FROM DUAL; -- 21/01/01
-- 기본포맷은 YY/MM/DD로 변환이 된다.

SELECT TO_DATE('20210101')
FROM DUAL; -- 21/01/01

SELECT TO_DATE(000101)
FROM DUAL; -- 000101 == 101 : 0으로 시작하는 숫자로 인식하여 에러 발생

SELECT TO_DATE('000101')
FROM DUAL; -- 00/01/01
-- 0으로 시작하는 년도는 반드시 홀따옴표로 묶어서 관리해야 한다.

SELECT TO_DATE('20100101', 'YYYYMMDD')
FROM DUAL; --10/01/01

SELECT TO_DATE('140630', 'YYMMDD')
FROM DUAL; -- 14/06/30

SELECT TO_DATE('880218', 'YYMMDD')
FROM DUAL; -- 88/02/18
-- 20880218로 세팅되었다.

SELECT TO_DATE('880218', 'RRMMDD')
FROM DUAL; -- 88/02/18
-- 두자리 년도에 대해 RR포맷을 적용시켰을 경우 => 50년 이상이며 19, 50미만이면 20이 붙는다.

 

▶ 형변환 함수2(CHARACTER => NUMBER)

■ TO_NUMBER

- TO_NUMBER(CHARACTER, 포맷) : 문자열 데이터를 숫자형으로 변환(결괏값은 NUMBER)

ex)

SELECT '123' + '456'
FROM DUAL; -- 579 : 자동형변환 시킨 후 산술연산 실행

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

 

▶ NULL 처리 함수

- NULL : 값이 존재하지 않음을 나타내는 값

- NULL 처리 함수들 : NVL, NVL2, NULLIF

■ NVL

- NVL(컬럼명, 해당 컬럼값이 NULL일 경우 반환할 반환값)
: 해당 컬럼에 값이 존재할 경우 기존의 컬럼값을 반환되며 해당 컬럼에 값이 존재하지 않을 경우 내가 제시한 값이 반환된다.

ex)

-- 사원명, 보너스, 보너스가 없는 경우 0으로 바꿔서 출력
SELECT EMP_NAME, BONUS, NVL(BONUS, 0)
FROM EMPLOYEE;

 

■ NVL2

NVL2(컬럼명, 결과값1, 결과값2)
: 해당 컬럼값이 NULL이 아닐경우 결과값1을 반환해주며 해당 컬럼값이 NULL일 경우 결과값2를 반환해 준다.

ex)

-- 이름 + 보너스 + 보너스가 있는 사원은 '보너스가 있음', 보너스가 없는 사원은 '보너스 없음'
SELECT EMP_NAME, BONUS, NVL2(BONUS, '보너스가 있음', '보너스 없음')
FROM EMPLOYEE;

 

■ NULLIF

NULLIF(비교대상1, 비교대상2)
:동등비교, 두 값이 동일할 경우 NULL값을 반환해 주고 두 값이 동일하지 않을 경우 비교대상 1을 반환해 준다.

ex)

SELECT NULLIF('123', '123')
FROM DUAL; -- NULL

SELECT NULLIF('123', '456')
FROM DUAL; -- 123

 

▶ 선택 함수

■ DECODE

 - DECODE(비교대상, 조건값1, 결과값1, 조건값2, 결과값2, 조건값3, 결과값3, ..., 결과값)
: 자바의 SWITCH문과 유사하다.

※ 비교대상에는 컬럼, 산술연산, 함수가 들어갈 수 있다.

ex)

-- 사번, 사원명, 주민등록번호, 주민등록번호로부터 성별 자리를 추출해서 1이면 남자, 2면 여자 성별컬럼 만들기
SELECT EMP_ID, EMP_NAME, EMP_NO, DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남자', 2, '여자') AS 성별
FROM EMPLOYEE;

 

■ CASE WHEN THEN

- CASE WHEN THEN
: DECODE 선택함수와 비교하면 DECODE는 해당 조건검사 시 동등비교만을 수행, CASE WHEN THEN 구문은 특정 조건을 내 맘대로 제시 가능

[표현법]
CASE WHEN 조건식1 THEN 결과값1 WHEN 조건식2 THEN 결과값2 ... ELSE 결과값 END;

ex)

-- 사원명, 급여, 급여등급(SAL_LEVEL칼럼 사용금지)
-- 급여등급 : SALARY값이 500만원 초과일경우 '고급' 500만원 이하 350만원 초과일경우 '중급' 350만원 이하일 경우 '초급'
SELECT EMP_NAME, SALARY, CASE WHEN SALARY > 5000000 THEN '고급' WHEN SALARY > 3500000 THEN '중급' ELSE '초급' END 급여등급
FROM EMPLOYEE
ORDER BY SALARY DESC;