[DB] Oracle의 내장함수 - 단일행 함수

# 목적

: 오라클 데이터베이스의 내장함수 종류와 단일행 함수에 대한 정리

---

오라클 내장함수의 종류

1. 단일행 함수

- 조회된 행마다 하나의 결과를 반환
- 중첩해서 사용 가능
- 종류
1. 문자함수 : 문자를 입력값으로 받아서 계산한 결과를 반환
2. 숫자함수 : 숫자를 입력값으로 받아서 계산한 결과를 반환
3. 날짜함수 : Date 타입의 값에 대한 처리를 수행
4. 변환함수 : 데이터의 타입을 변환하는 처리를 수행
5. 기타함수 : nvl, case, decode 등의 함수

2. 다중행 함수(그룹함수)

- 조회된 행을 그룹으로 묶고 행 그룹당 하나의 결과를 반환
- group by 절을 사용해서 조회된 행을 그룹으로 묶고 다중행 함수로 각 그룹당 하나의 결과(합계, 평균, 분산, 표준편차, 최고값, 최저값) 등을 계산해냄

---

단일행 함수의 종류

1. 문자 함수

1) LOWER, UPPER
- LOWER(컬럼 혹은 표현식) : 소문자로 변환하는 함수
- UPEER(컬럼 혹은 표현식) : 대문자로 변환하는 함수
- 사용예시

-- 60번 부서에 소속된 직원들의 이름을 소문자, 대문자로 조회하기
SELECT FIRST_NAME, LOWER(FIRST_NAME), UPPER(FIRST_NAME)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

2) CONCAT, ||
- CONCAT(컬럼 혹은 표현식, 컬럼 혹은 표현식) : 두 문자열을 연결하는 함수
- 컬럼 혹은 표현식 || 컬럼 혹은 표현식 || 컬럼 혹은 표현식 ... : 여러 문자열을 연결하는 함수
- 사용예시

-- 60번 부서에 소속된 직원들의 FIRST_NAME과 LAST_NAME을 연결해서 조회하기
-- 1. CONCAT
SELECT CONCAT(FIRST_NAME, LAST_NAME)
FROM EMPLOYEES
WHERE DEPARTMENT ID = 60;

-- 2. ||
SELECT FIRST_NAME || ' ' || LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

3) LENGTH(컬럼 혹은 표현식)
- 문자열의 길이를 반환하는 함수
- 사용예시

-- 60번 부서에 소속된 직원들의 이름과 이름의 길이 조회하기
SELECT FIRST_NAME, LENGTH(FIRST_NAME)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

4) SUBSTR(컬럼 혹은 표현식, 시작위치, 길이)
- 문자열을 시작위치부터 길이만큼 잘라서 반환하는 함수 *시작위치는 1
- 사용예시

-- SAMPLE_BOOKS에서 저자이름 및 이름의 첫번째 글자 조회하기
SELECT BOOK_WRITER, SUBSTR(BOOK_WRITER, 1, 1) LAST_NAME
FROM SAMPLE_BOOKS;

5) INSTR(컬럼 혹은 표현식, 문자열)
- 지정된 문자열의 등장위치를 반환하는 함수
- 사용예시

-- 전화번호에서 국번만 조회하기
SELECT SUBSTR('02)1234-5678', 1, INSTR('031)1234-5678', ')') -1)
FROM DUAL;

-- * DUAL : 오라클에서 제공하는 1행 1열짜리 DUMMY 테이블(실험용)
-- 실제 테이블 조회 없이 간단한 연산을 수행할 때 이용
SELECT *
FROM DUAL;

6) TRIM(컬럼 혹은 표현식)
- 불필요한 좌우 여백을 제거 후 문자열을 반환하는 함수
- 사용예시

-- 불필요한 좌우 여백이 제거된 문자열 조회하기
SELECT TRIM('           HELLO WORLD!     ')
FROM DUAL;

7) LPAD(컬럼 혹은 표현식, 길이, 지정문자)
- 컬럼 혹은 표현식의 길이가 지정된 길이보다 짧으면, 부족한 길이만큼 왼쪽에 지정된 문자가 채워진 문자열을 반환하는 함수
- 사용예시

-- 지정된 길이만큼 문자열의 왼쪽에 '0'을 채워서 조회하기
SELECT LPAD('100', 10, '0')
FROM DUAL;

---

2. 숫자 함수

1) ROUND(컬럼 혹은 표현식, 자리수)
- 지정된 자리수까지 반올림해주는 함수

2) TRUNC(컬럼 혹은 표현식, 자리수)
- 지정된 자리수까지 남기고 전부 버리는 함수
- 사용예시

-- 80번 부서에 소속된 직원의 아이디, 이름, 급여, 급여에 대한 # 출력하기
-- '#' 하나는 1000달러를 나타냄
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, LPAD('#', TRUNC(SALARY/1000), '#')
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;

3) CEIL, FLOOR
- CEIL(컬럼 혹은 표현식) : 지정된 값보다 크거나 같은 정수 중에서 가장 작은 정수를 반환하는 함수 (천장값, 소수점 자리 무조건 올림)
- FLOOR(컬럼 혹은 표현식) : 지정된 값보다 작거나 같은 정수 중에서 가장 큰 정수를 반환하는 함수 (바닥값, 소수점 아래 무조건 무시)

4) MOD, ABS
- MOD(숫자1, 숫자2) : 나머지 값을 반환하는 함수
- ABS(컬럼 혹은 표현식) : 절대값을 반환하는 함수

---

3. 날짜 함수

1) sysdate
- 시스템의 현재 날짜와 시간을 DATE 타입으로 반환하는 함수
* 데이터가 추가/변경될 때 시스템의 현재 날짜와 시간정보를 저장하는 용도로 활용됨

- SYSTIMESTAMP : 시스템의 현재 일자와 시간을 TIMESTAMP 타입으로 반환

2) EXTRACT(포맷 FROM 날짜)
- 날짜에서 형식에 해당하는 값을 조회하는 함수
- 포맷 형식 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

3) LAST_DAY(날짜)
- 지정된 날짜를 기준으로 해당 월의 마지막 일자를 반환하는 함수

4) NEXT_DAY(날짜, 요일)
- 지정된 날짜 이후의 날짜 중에서 지정된 요일에 해당하는 날짜를 반환하는 함수

5) 개월수 관련 함수
- ADD_MONTHS(날짜, 숫자) : 날짜에 지정된 숫자만큼의 월을 더한 날짜를 반환하는 함수
- MONTHS_BETWEEN(날짜, 날짜) : 두 날짜 사이의 개월수를 반환하는 함수

6) 일수 관련 연산
- 날짜 + 숫자 = 숫자만큼 일자가 경과된 날짜를 반환
- 날짜 - 숫자 = 숫자만큼의 이전 날짜 반환
- 날짜 - 날짜 = 두 날짜사이의 일수 반환
- 날짜 + 1/24 = 1시간 이후의 날짜 반환

7) TRUNC(날짜)
- 지정된 날짜의 모든 시간 정보를 0시0분0초로 바꾼 날짜를 반환하는 함수

8) ROUND(날짜)
- 지정된 날짜의 시간정보가 정오를 지나기 전이면 해당 날짜가, 정오를 지났으면 하루 증가된 날짜가 반환되는 함수
* 시간정보는 0시0분0초

---

4. 데이터 타입 변환

1) 묵시적 타입 변환
- 변환함수를 사용하지 않아도 데이터의 타입이 자동으로 변하는 것

- '문자' -> 날짜 : '문자'가 유효한 날짜 형식의 문자인 경우 날짜로 변경됨
- '문자' -> 숫자 : '문자'가 숫자로만 구성되어 있으면 숫자로 변경됨

- 예시

SELECT *
FROM EMPLOYEES
WHERE HIRE_DATE >= '2005/01/01' AND HIRE_DATE <= '2005/07/01';
* 위의 SQL에서 HIRE_DATE가 DATE타입의 컬럼이기 때문에 '2005/01/01'문자가 DATE타입의 값으로 자동 변환됨
* 단, '2005/01/01'이나 '2005-01-01'의 형식에 한해서 자동 변환됨
    
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = '10';
* 위의 SQL문에서 DEPARTMENT_ID가 NUMBER타입의 컬럼이기 때문에 '10'문자가 NUMBER타입의 값 10으로 자동 변환됨

2) 명시적 타입 변환
- 변환함수를 사용해서 '문자' <--> 날짜, '문자' <--> 숫자 간의 데이터변환을 하는 것
- 예시

TO_DATE('날짜형식의 문자열', '패턴')
ex) TO_DATE('2023/01/01', 'YYYYMMDD')
    * 날짜형식의 문자열을 DATE타입으로 자동 변환(패턴 생략 가능)
    
    
TO_CHAR(날짜, '포맷')
	날짜를 지정된 포맷형식의 텍스트로 변환
	날짜 변환 형식
		AM        '오전'
		PM        '오후'
		YYYY, YY  '2022'
		MM        월
		D         1~7 (1:일요일, 7:토요일)
		DAY       요일
		DD        1~31
		HH        12시간제 시간
		HH24      24시간제 시간
		MI        분
		SS        초
    * 날짜 변환 형식은 대소문자 구분하지 않음

❓ 묵시적 타입 변환 / 명시적 타입 변환 비교

-- 직원테이블에서 2005년도에 입사한 직원의 아이디, 이름, 입사일자 조회하기 1
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2005';

-- 직원테이블에서 2005년도에 입사한 직원의 아이디, 이름, 입사일자 조회하기 2 *이 방법을 더 선호
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE >= '2005/01/01' AND HIRE_DATE < '2006/01/01';

/*
* 위에서 1번보다 2번을 더 선호하는 이유
: WHERE절에서 좌변을 가공하면 안됨(Feat.SQL의 칠거지악)
  WHERE절에서 자주 쓰는 걸 색인으로 만들어놓는데, 1번의 경우 색인을 못쓰게 됨
*/

---

5. 기타 함수

1) NVL(컬럼, 대체값)
- NULL값을 다른 값으로 변환하는 함수
- 지정된 컬럼의 값이 NULL이 아닌 경우에는 해당 컬럼의 값을 반환
- 해당 컬럼과 대체값은 데이터 타입이 동일한 타입이어야 함
- 사용예시

-- 모든 직원의 아이디, 이름, 급여, 커미션 조회하기
-- 커미션이 NULL이면 0을 반환
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, NVL(COMMISSION_PCT, 0) COMM
FROM EMPLOYEES;

NVL2(컬럼, 대체값1, 대체값2)
- 지정된 컬럼의 값이 NULL이 아니면 대체값1 반환, NULL이면 대체값2를 반환하는 함수
- 대체값1과 대체값2는 데이터 타입이 동일한 타입이어야 함 (컬럼 타입은 상관X)

2) DECODE(컬럼, 값, 반환값, 값, 반환값, ...)
- 제시된 조건에 따라서 다른 조회결과를 제공해주는 함수
- 사용예시

DECODE(컬럼, 비교값1, 값1,
            비교값2, 값2,
            비교값3, 값3,
            기본값)
- 지정된 컬럼의 값이 비교값1과 같으면 값1 반환,
                  비교값2와 같으면 값2 반환,
                  비교값3과 같으면 값3 반환,
                  일치하는 값이 없으면 기본값을 반환하는 함수(기본값 생략 가능)
- 컬럼의 값과 비교값 간의 equals 비교만 가능


-- 지역 테이블에서 지역아이디별로 지역명 조회하기
-- 1: 유럽 / 2: 아메리카 / 3: 아시아 / 4: 아프리카 및 중동
SELECT REGION_ID,
       DECODE(REGION_ID, 1, '유럽',
                         2, '아메리카',
                         3, '아시아',
                         4, '아프리카 및 중동') AS REGION_NAME
FROM REGIONS;

3) CASE ~ WHEN ~ then ~ end
- 제시된 조건에 따라서 다른 조회결과를 제공해주는 함수
- JAVA의 switch / if ~ else if ~ else 와 유사
- 사용예시

CASE
    WHEN 조건식 THEN 값1
    WHEN 조건식 THEN 값2
    WHEN 조건식 THEN 값3
    ELSE 값4
END
    - 조건식이 TRUE로 판정되면 THEN의 값이 최종값이 됨
    - 모든 조건식이 FALSE로 판정되면 ELSE의 값4이 최종값이 됨
    - =, >, >=, <, <=, != 등의 다양한 연산자를 사용해서 조건식 작성 가능
    - DECODE함수와 비교했을 때 더 다양한 조건 적용 가능
        
CASE 컬럼
    WHEN 비교값1 THEN 값1
    WHEN 비교값2 THEN 값2
    WHEN 비교값3 THEN 값3
    ELSE 값4
END
    - 지정된 컬럼의 값이 비교값들 중 하나와 일치하면 해당 THEN의 값이 최종값이 됨
    - 모든 비교값과 일치하지 않으면 ELSE의 값4가 최종값이 됨
    - DECODE 함수와 기능면에서 동일함
        

-- 모든 직원테이블에서 급여가 5000이하면 보너스 1000 지급,
--                 급여가 10000이하면 보너스 2000 지급,
--                 그 외는 3000 지급하기
-- 모든 직원에 대해서 직원아이디, 이름, 급여, 보너스 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,
       CASE
           WHEN SALARY <= 5000 THEN 1000
           WHEN SALARY <= 10000 THEN 2000
           ELSE 3000
       END BONUS
FROM EMPLOYEES;

 

---

< 해당 글은 velog에서 이전하며 옮겨온 글로, 가독성이 좋지 않을 수 있는 점 양해 부탁드립니다. >

🔗 velog 버전 보기 : https://velog.io/@ryuneng2/DB-Oracle-내장함수-단일행함수