[DB_SQL활용] 오라클 함수_단일행 함수

예지성준·2024년 5월 8일
db

DB

목록 보기
6/19
post-thumbnail

데이터 처리와 가공을 위한 오라클 함수

오라클 함수의 종류

1. 내장 함수

1) 단일행 함수(single-row function) : 데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수

2) 다중행 함수(multiple-row function): 여러 행이 입력되어 하나의 행의 결과로 반환되는 함수

2. 사용자 정의 함수

문자 데이터를 가공하는 문자함수

1. 대,소문자를 바꿔 주는 UPPER, LOWER, INITCAP 함수

UPPER(): 소문자 -> 대문자
LOWER(): 대문자 -> 소문자
INITCAP: 첫글자만 대문자로

-- 이름을 대소문자 구분없이 조회
SELECT * FROM EMP WHERE UPPER(ENAME) LIKE UPPER('scott');

2. 문자열 길이를 구하는 LENGTH 함수

  • LENGTH(컬럼명): 문자열 길이
    영문자 1자 - 1바이트
    한글 1자 - 2바이트, 3바이트(UTF-8)

  • LENGTHB(컬럼명): 문자열의 바이트 수

참고) MySQL
LENGTH() - 문자열 바이트 수
CHAR_LENGTH() - 문자열 갯수

3. 문자열 일부를 추출하는 SUBSTR 함수

SUBSTR(컬럼, 시작위치, 추출길이)
SUBSTR(컬럼, 시작위치) - 시작 위치 부터 끝까지

  • 시작위치

    • 1부터 시작
    • 양수: 왼쪽부터 시작 위치
    • 음수: 오른쪽부터 시작 위치

4. 문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR 함수

INSTR(컬럼,'찾는 키워드')
INSTR(컬럼,'찾는 키워드',검색 시작 위치)

  • 1번 위치부터 시작

  • 키워드를 못찾은 경우 0을 반환

  • LIKE와 비슷한 효과
    ENAME LIKE %S%
    => INSTR(ENAME,'S') > 0

참고) indexOf()와 비슷

  • 특정 문자열의 위치를 찾는 함수
  • 0번 위치부터 시작

5. 특정 문자를 다른 문자로 바꾸는 REPLACE 함수

REPLACE(컬럼, '찾는 문자열', '치환될 문자열')

6. 데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수

1) LPAD: 왼쪽 패딩
LPAD(컬럼 or 문자열, 자리수, '채워넣을 문자')

🔼비워있는 공간 왼쪽을0으로 채워넣음

2) RPAD: 오른쪽 패딩
RPAD(컬럼 or 문자열, 자리수, '채워넣을 문자')

이름 마스킹 처리할때 주로 사용

7. 두 문자열 데이터를 합치는 CONCAT 함수

  • 문자열 2개를 결합해서 1개로 만듦

CONCAT에 CONCAT에 CONCAT ... 이런식으로 하면 좀 복잡하다.

8. 문자열 데이터를 연결하는 || 연산자

  • 문자열 데이터 여러개를 연결하기 위해 CONCAT을 여러개를 겹쳐써야 할 경우 대신 사용

컬럼1 || ':' || 컬럼2

9. 특정 문자를 지우는 TRIM, LTRIM, RTRIM 함수

  • TRIM: 왼쪽 오른쪽 공백 다 제거
--양쪽 공백 제거 ABCDEF
SELECT TRIM('     ABC     ') || 'DEF' FROM DUAL;

TRIM(LEADING FROM 문자열): 왼쪽 여백 제거

TRIM(TRAILING FROM 문자열): 오른쪽 여백 제거

TRIM(LEADING '*' FROM 문자열): 왼쪽 '*'를 제거

--BOTH 옵션이 기본값 (양쪽 여백 제거)
SELECT TRIM(BOTH FROM'     ABC     ') || 'DEF' FROM DUAL;
--왼쪽 여백 제거 'ABCDEF    '
SELECT TRIM(LEADING FROM'     ABC     ') || 'DEF' FROM DUAL;
--오른쪽 여백 제거'    ABCDEF'
SELECT TRIM(TRAILING FROM'     ABC     ') || 'DEF' FROM DUAL;
-- 오른쪽 '_' 제거 '_____ ABCDEF'
SELECT TRIM(TRAILING '_' FROM'_____ABC_____') || 'DEF' FROM DUAL;
  • LTRIM: 왼쪽 공백 제거
    LTRIM(문자열,'*'): 왼쪽에 있는 *를 제거
-- 왼쪽 * 제거 ABC*****
SELECT LTRIM('*****ABC*****','*') FROM DUAL;
  • RTRIM: 오른쪽 공백 제거

숫자 데이터를 연산하고 수치를 조정하는 숫자 함수

  1. 특정 위치에서 반올림하는 ROUND
    ROUND
-- 소수점 첫째짜리까지 출력 -> 소수점 둘째 자리에서 반올림 함
--123.5
SELECT ROUND(123.4567, 1) FROM DUAL;

-- 123.46
SELECT ROUND(123.4567, 2) FROM DUAL;

👩‍🏫자리수
1234.567 ===> 4자리가 0번, 왼쪽으로 -1 오른쪽으로 1
소수 부분이 양수
정수 부분이 음수

  1. 특정 위치에서 버리는 TRUNC 함수

TRUNC: 절사

-- 0 기본값 생략 가능
SELECT TRUNC(12345.123456, 0) FROM DUAL; --12345

SELECT TRUNC(12345.123456, 1) FROM DUAL; --12345.1

SELECT TRUNC(12345,123456, -3) FROM DUAL --12000
  1. 지정한 숫자와 가까운 정수를 찾는 CEIL, FLOOR 함수

CEIL: 올림

SELECT CEIL(123.4567) FROM DUAL; --124

FLOOR: 버림

SELECT FLOOR(123.678) FROM DUAL; --123
  1. 숫자를 나눈 나머지 값을 구하는 MOD 함수

MOD: 나머지

SELECT MOD(10,3) FROM DUAL; -- 나머지: 1

날짜 데이터를 다루는 날짜 함수

DATE형 데이터

1) 날짜 데이터 + 숫자

  • 숫자만큼 일수가 더해진다.

2) 날짜 데이터 - 숫자

  • 숫자만큼 일수가 빼진다.

3) 날짜 데이터 - 날짜 데이터

  • 날짜 사이 일수 차이

🔽 경과 일수

* SYSDATE: 현재 시스템의 날짜 시간

4) 날짜 데이터 + 날짜 데이터 (불가)

  • 오류발생

SYSDATE 함수를 사용하여 날짜 출력하기

  • SYSDATE: 현재 시스템의 날짜 시간

몇 개월 이후 날짜를 구하는 ADD_MONTHS 함수

  • 월 단위로 가감

두 날짜 간의 개월 수 차이를 구하는 MONTHS_BETWEEN 함수

돌아오는 요일, 달의 마지막 날짜를 구하는 NEXT_DAY, LAST_DAY 함수

NEXT_DAY(날짜, 요일): 다음 요일의 날짜

SELECT NEXT_DAY(SYSDATE,'목') "다음 목요일" FROM DUAL;-- 2024-5-16

참고) DAY: 요일

LAST_DAY(날짜): 날짜에 해당하는 월의 마지막 일자

-- 이번 달의 마지막 일자 2024-05-31
SELECT LAST_DAY(SYSDATE) "마지막 일자" FROM DUAL;

자료형을 변환하는 형 변환 함수

  1. 자동 형 변환, 암시적 형변환

'2024-05-09': 문자 -> DATE형 변환

'1000': 문자 연산시 -> NUMBER 형으로 변환

  • 형식을 오라클이 충분히 파악한 경우 가능
    • ex) '1,000'인 경우 숫자로 형식 파악 불가
-- 문자 연산시 NUMBER형으로 변환
SELECT '1000' + 200 FROM DUAL; --1200
  1. 형변환 함수 - TO_CHAR, TO_NUMBER, TO_DATE

📅📆형식

  • 년도
    YYYY -> 4자리 연도 : 2024
    YY -> 2자리 연도 : 24
    -⏱현재 속해있는 연도로 나옴
    ex) 90-05-09 -> 2090-05-09
    RRRR -> 4자리 연도
    RR -> 2자리 연도
    -⏱가장 가까운 연도로 나옴
    ex) 90-05-09 -> 1990-05-09
  • 월
    MM: 월
  • 일
    DD: 일
  • 시간
    HH24: 24시간 표기 시간
    HH12 / HH: 12시간 표기 시간
  • 분
    MI: 분
  • 초
    SS: 초
  • 날짜, 숫자 데이터를 문자 데이터로 변환하는 TO_CHAR 함수
    TO_CHAR(): 숫자, 날짜 -> 형식화된 문자열
    숫자: TO_CHAR(SAL, '$999,999')
    날짜: TO_CHAR(HIREDATE,'YYYY.MM.DD')

  • 문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수

TO_NUMBER: 문자로 되어있는 형식화된 숫자('1,000')를 숫자형으로 바꿔준다.

SELECT TO_NUMBER('1,000','999,999')+ 200 FROM DUAL; --1200

SELECT TO_NUMBER('1,000,000','999,999,999')+ 200 FROM DUAL; --1000200
  • 문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수

TO_DATE: 문자로 형식화된 날짜를 날짜형식으로 바꿔준다.

TO_DATE(문자열, 날짜, 형식)

SELECT * FROM EMP
WHERE HIREDATE
	BETWEEN TO_DATE('01-01-1981', 'MM-DD-YYYY')
	AND TO_DATE('12/31/1981', 'MM/DD/YYYY');

NULL 처리 함수

  • null일때 연산 불가 -> 수치 연산시 문제 발생

  1. NVL 함수
    NVL(컬럼, 기본값)

  2. NVL2 함수
    삼항조건 연산자와 비슷
    NVL2(컬럼, NULL이 아닐때 값, NULL일때 값)

상황에 따라 다른 데이터를 반환하는 DECODE 함수와 CASE문

  • switch ~ case와 유사한 문법
  1. DECODE 함수: 오라클 전용 함수
SELECT EMPNO, ENAME, JOB, SAL,
	DECODE(JOB,
		'MANAGER', SAL * 1.1,
		'SALESMAN', SAL * 1.05,
		'ANALYST', SAL,
		SAL * 1.03)"내년 급여" 
FROM EMP;-- 해당하지 않으면 SAL * 1.03

  1. CASE 문: 표준 함수

CASE WHEN THEN 구문

profile
예지성준
꽁꽁 얼어붙은 한강 위로 😺
이전 포스트

[DB_SQL활용] WHERE절과 연산자

다음 포스트

[DB_SQL활용] 다중행 함수와 데이터 그룹화

0개의 댓글