서론
- 단일행 함수와 복수행 함수의 차이
- 다양한 문자/숫자/날짜/형변환/일반
단일행 함수 VS 복수행 함수
- 단일행 함수
- 단일 행 함수는 여러 건의 데이터를 한꺼번에 처리하는 것이 아닌
- 한 번에 하나씩 처리하는 함수
- 복수행 함수 ( = 그룹 함수)
- 여러 건의 데이터를 동시에 입력 받아 한 건의 결괏값을 만들어 주는 함수
단일행 함수
- 함수에 어떤 값이 입력되는지 구분하는 것이 중요함
▣ 문자 함수
함수명 | 의미 | 사용 예 |
---|---|---|
INITCAP | 입력 첫 글자만 대문자 변환 공백이 있을 경우 공백 다음을 첫 글자로 판단 |
INITCAP('abcd') => Abcd INITCAP('sky ju') => Sky Ju |
LOWER | 입력 값 전부 소문자 변환 | LOWER('ABCD') => abcd |
UPPER | 입력 값 전부 대문자 변환 | UPPER('abcd') => ABCD |
LENGTH | 입력된 문자열 길이 값 출력 | LENGTH('한글') => 2 |
LENGTHB | 입력된 문자열 길이 바이트 값 출력 | LENGTHB('한글') => 4 |
CONCAT | 두 문자 결합해 출력 ( == || 연산자 ) | CONCAT('A', 'B') => AB |
SUBSTR | 주어진 문자에서 특정 문자만 추출 SUBSTR(문자열, 시작 번호, 출력할 문자 수) |
SUBSTR('ABC', 1, 2) => AB |
SUBSTRB | 주어진 문자에서 특정 바이트만 추출 | SUBSTRB('한글', 1, 2) => 한 |
INSTR | 주어진 문자에서 특정 문자 위치 추출 | INSTR('A*B#', '#') => 4 |
INSTRB | 주어진 문자에서 특정 문자의 위치 바이트 값 추출 | |
LPAD | 문자열의 왼쪽으로 특정 문자 채움 | LPAD('love', 6, '**') => **love |
RPAD | 문자열의 오른쪽으로 특정 문자 채움 | RPAD('love', 6, '**') => love** |
LTRIM | 문자열에서 왼쪽 특정 문자 삭제 | LTRIM('love*','*') => love |
RTRIM | 문자열에서 오른쪽 특정 문자 삭제 | RTRIM('love*','*') => love |
REPLACE | 문자열에서 A를 B로 치환 | REPLACE('AB','A','E') => EB |
REGEXP_REPLACE | 문자열에서 특정 패턴 찾아 치환 | |
REGEXP_INSTR | 문자열에서 특정 패턴의 시작 위치 반환 | |
REGEXP_SUBSTR | 문자열에서 특정 패턴 찾아 반환 | |
REGEXP_LIKE | 문자열에서 특정 패턴 찾아 반환 | |
REGEXP_COUNT | 문자열에서 특정 패턴 횟수 반환 |
- upper, lower
- 실무에서 자주 사용됨
- DB에 데이터를 저장하거나 불러올 때, 대문자나 소문자로 통일해야 할 경우 많이 사용됨
- substr('문자열' 또는 컬럼, 시작 위치, 갯수)
- 즉, 시작 위치에서 갯수만큼 문자열을 잘라냄
- 특정 길이의 문자만 골라내는 함수
- 주민번호에서 생일을 찾아내는 경우에 유용하게 이용됨
- 만약 시작 수를 음수로 줄 경우 뒤에서(오른쪽에서) 자릿수를 계산해 문자를 추출함
- instr('문자열' 또는 컬럼, 시작 위치, 몇 번째인지(기본 값 == 1))
- 시작 위치를 음수로 줄 경우 오른쪽에서 왼쪽으로 결괏값을 검색함
- 시작 위치와 찾으려는 위치 값( == 몇 번째 인지)는 생략 가능함
생략할 경우 자동으로 1로 설정됨
- lpad('문자열' 또는 컬럼, 자릿수, '채울 문자')
- ltrim('문자열' 또는 컬럼, '제거할 문자') / rtrim('문자열' 또는 컬럼, '제거할 문자')
- 맨 오른쪽 혹은 맨 왼쪽에 위치하며 제거할 문자와 동일한 문자를 제거함
- 회원 가입에서 고객 아이디 첫 글자가 공백일 경우 공백 제거에 사용되기도 함
- replace('문자열' 또는 컬럼명, '문자1', '문자2')
- 첫 문자열이나 컬럼에서 문자 1을 문자 2로 바꾸어 출력하는 함수
▣ 숫자 함수
함수명 | 의미 | 사용 예 |
---|---|---|
ROUND | 숫자 반올림 후 출력 - 소숫점 아래에서의 반올림 = 양수 (해당 숫자에서 반올림) - 정수로의 반올림 = 0 - 정수에서의 반올림 = 음수 (해당 자릿수에서 반올림) |
ROUND(12.345, 2) => 12.35 |
TRUNC | 버림 후 출력 | TRUNC(12.345, 2) => 12.34 |
MOD | 나눈 후 나머지 값 출력 | MOD(12, 10) => 2 |
CEIL | 숫자와 가장 근접한 큰 정수 출력 | CEIL(12.345) => 13 |
FLOOR | 숫자와 가장 근접한 작은 정수 출력 | FLOOR(12.345) => 12 |
POWER | 숫자 1의 숫자 2승 출력 | POWER(3, 2) => 9 |
- ceil(숫자)
- 여러 데이터를 하나의 기준으로 묶을 때
- [ex] rownum을 기준으로 세 명씩 한 팀으로 묶을 때,
SELECT rownum AS "r-no", CEIL(rownum/3) AS "team-no", ename
FROM emp; - 거래명세서 출력, 대량 데이터 일정 개수씩 끊어 출력할 경우 요긴하게 사용됨
- 페이징 처리에도 좋을 수 있지 않을까?
- 숫자 함수는 돈과 관련되는 경우가 많아 주의해서 사용해야함
▣ 날짜 함수
함수명 | 의미 | 결과 |
---|---|---|
SYSDATE | 시스템의 현 날짜와 시간 | 날짜 |
MONTHS_BETWEEN | 두 날짜 사이의 개월 수 | 숫자 |
ADD_MONTHS | 날짜에 개월 더하기 | 날짜 |
NEXT_DAY | 날짜를 기준으로 돌아오는 날짜 출력 | 날짜 |
LAST_DAY | 날짜가 속한 달의 마지막 날짜 | 날짜 |
ROUND | 날짜 반올림 | 날짜 |
TRUNC | 날짜 버림 | 날짜 |
- months_between
- 유닉스 오라클과 윈도우 오라클 날짜 형식은 다른데, 이때 날짜 형태가 안 맞을 경우 에러가 발생함
- 그렇기에 유닉스용 오라클에서 윈도우용 날짜 형식을 사용하고 싶다면 NLS_DATE_FORMAT이라는 명령으로 날짜 형태를 알려줘야 함
날짜형태 지정 쿼리
ALTER SESSION SET NLS_DATE_FORMAT = 'YY/MM/DD'; - 날짜를 계산하는 방법은 어떤 방법을 사용하느냐에 따라 결과가 많이 다를 수 있기에 주의해서 사용해야 함
그렇기에 실무에서는 먼저 확인하는 습관 중요!
- next_day(날짜, '알고자 하는 요일')
- 날짜를 기준으로 알고자 하는 요일의 가장 최근 날짜 반환
- 리눅스와 윈도우용은 날짜 표기가 다르기에 요일 작성시 주의해야함
- 월요일일 경우
- 리눅스 : MON
- 윈도우 : 월
- 월요일일 경우
- ADD_MONTHS, NEXT_DAY, LAST_DAY 함수 사용 예시
- 이벤트 진행시 사용됨
- 예를 들어.. '이번 달까지 할인 30% 적용 가격으로 처리'
- 날짜의 ROUND( )와 TRUNC( )
- round: 하루의 반에 해당되는 시간 낮(정오)를 기준으로 넘어갈 경우 다음 날, 아닐 경우 당일 출력
- trunc: 무조건 당일로 출력
- 오전까지 접수된 건 당일 접수 처리, 오후 접수 건 이튿날 처리 ==> ROUND 함수
- 접수 시간 상관없이 당일 처리 ==> TRUNC 함수
▣ 데이터 타입
데이터 타입 | 의미 |
---|---|
CHAR(n) | 고정길이 문자 저장 (최댓값 2,000 바이트) |
VARCHAR2(n) | 가변길이 문자 저장 (최댓값 4,000 바이트) |
NUMBER(p, s) | 숫자 값 저장 [ p = 전체 자릿수 ( 1 ~ 38 ), s = 소수점 이하 자릿수 -84 ~ 127자리 ] |
DATE | 총 7바이트, BC 4712년 1월 1일부터 AD 9999년 12월 31일까지 날짜 저장 가능 |
LONG | 가변 길이 문자 저장 (최대 2GB) |
CLOB | 가변 길이 문자 저장 (최대 4GB) |
BLOB | 가변 길이 바이너리 데이터 저장 (최대 4GB) |
RAW(n) | 원시 이진 데이터 (최대 2,000 바이트) |
LONG RAW(n) | 원시 이진 데이터 (최대 2GB) |
BFILE | 외부 파일에 저장된 데이터 (최대 4GB) |
- 묵시적(자동) 형 변환과 명시적(수동) 형 변환
- SELECT 2 + '2' FROM dual; ==> 결괏값 : 4
- 숫자와 문자를 연산하는 것.
- 숫자와 문자를 연산할 수 없기에 해당 요청 시 오라클은 에러를 발생시킴
- 하지만 위와 같이 숫자처럼 생긴 문자는 오라클이 숫자로 바꾼 후 연산을 수행함
- 내부적
SELECT 2 + TO_NUMBER('2') FROM dual; - 허나, 튜닝에서 속도가 느려지는 주범이기에 주의해야 함
- 왜냐. 해당 컬럼에 인덱스가 생성되어 있을 경우 묵시적 형 변환이 생기면 인덱스를 못 쓰게 되고,
그렇게 되면 Index Suppressing error가 발생해 속도가 느려짐
- SELECT 2 + '2' FROM dual; ==> 결괏값 : 4
- 명시적(강제적)으로 형 변환을 할 때 '형 변환 함수' 사용
▣ 형변환 함수
숫자 -- to_har --> 문자 <-- to_char -- 날짜
숫자<-- to_number -- 문자 -- to_date --> 날짜
- TO_CHAR :: 날짜 -> 문자
- TO_CHAR(원래 날짜, '원하는 모양')
- 포멧
- 연도
- YYYY : 연도 4자리 표현 ( ex. 2014 )
- RRRR : 2000년 이후 Y2K 버그로 등장한 연도 4자리 날짜 표기법
- YY : 연도 끝의 두 자리만 표기 ( ex. 14 )
- RR : 연도 마지막 두 자리 ( ex. 14 )
- YEAR : 연도의 영문 이름 전체 표시
- 월
- MM : 숫자 두 자리로 표현 ( ex. 07 )
- MON : 유닉스용 오라클에서 월을 뜻하는 영어 세 글자로 표시 ( ex. OCT )
( == 윈도우는 MONTH와 동일 ) - MONTH : 월을 뜻하는 이름 전체 표시
- 일
- DD : 일을 숫자 두 자리로 표시 ( ex. 16 )
- DAY : 요일에 해당하는 명칭 표시. 유닉스용 오라클 == 영문, 윈도우용 오라클 == 한글
- DDTH : 몇 번째 날인지 표시
- 시간
- HH24 : 하루를 24시간으로 표시
- HH : 하루를 12시간으로 표시
- MI : 분으로 표시
- SS : 초로 표시
- [참고] WHERE 절에서의 조건
- 연도
- TO_CHAR :: 숫자 -> 문자
- 종류
- 9
- 9개의 개수만큼 자릿수를 가짐
- [ex] TO_CHAR(1234, '99999') => 1234
- 0
- 빈자리를 0으로 채움
- [ex] TO_CHAR(1234, '099999') => 001234
- $
- $를 붙여 표시
- [ex] TO_CHAR(1234, '$9999') => $1234
- .
- 소수점 이하 표시
- [ex] TO_CHAR(1234, '9999.99') => 1234.00
- ,
- 천 단위 구분기호 표시
- [ex] TO_CHAR(12345, '99,999') => 12,345
- 9
- 종류
- TO_NUMBER( )
- 숫자가 아닌 숫자처럼 생긴 문자를 숫자로 전환
- TO_DATE( )
- 날짜가 아닌 날짜처럼 생긴 문자를 날짜로 전환
- 마찬가지로 날짜 형태에 주의해야 함
- 윈도우 오라클에서 유닉스 오라클 날짜 형태의 데이터를 바꾸는 것은 불가함
▣ 일반 함수
- 입력되는 값 구분 없이 모두 사용 가능함
- NVL(컬럼, 치환 값)
- NULL 값을 만날 경우 다른 값으로 치환해 출력
- NVL(sal, 0) => NULL일 경우 0으로 치환
- NVL(sal, 100) => NULL일 경우 100으로 치환
- NVL2( col1, col2, col3 )
- NVL 함수의 확장, NULL 값이 아닐 경우 출력 값 지정 가능
- col1 값이 NULL이 아니면 col2,
col1 값이 NULL이면 col3
- DECODE( ) 함수
- 분기문인 IF문을 오라클 SQL 안으로 가져온 함수
- = (equal) 값 처리
- [유형 1] A가 B일 경우 '1'을 출력하는 경우
- DECODE(컬럼명, 비교 값, TRUE 출력 값, FALSE 출력 값)
- DECODE(A, B, '1', null)
- 마지막 null 생략 가능
- A가 B가 아니라면 null 출력
- [유형 2] A가 B일 경우 '1'을 출력하고, 아닐 경우 '2를 출력하는 경우 (위랑 비슷한거 같은데)
- DECODE(A, B, '1', '2')
- [유형 3] A가 B일 경우 '1', C일 경우 '2', 둘 다 아닐 경우 '3'
- DECODE(A, B, '1', C, '2', '3')
- IF문 쓰듯이 쭉 접근
- [유형 4] A가 B일 경우 중 C가 D를 만족하면 '1', C가 D가 아닐 경우 null (한 마디로 DECODE 함수 안에 DECODE 함수가 중첩되는 경우)
- DECODE(A, B, DECODE(C, D, '1', null))
- [유형 5] A가 B일 경우 중 C가 D를 만족하면 '1', C가 D가 아닐 경우 '2'를 출력하는 경우
- DECODE(A, B, DECODE(C, D, '1', '2'))
- [유형 6] A가 B일 경우 중 C가 D를 만족하면 '1', C가 D가 아닌 경우 '2', A가 B가 아닐 경우 '3'
- DECODE(A, B, DECODE(C, D, '1', '2'), '3')
- CASE 문 [ CASE WHEN ~ THEN ~ END ~ ]
- DECODE는 equal값 처리에 사용
- CASE 문은 크거나 작은 조건을 처리할 때 사용
- DECODE는 ,(comma)로 조건 구분
CASE문은 내부에 콤마가 사용되지 않음 - [유형 1] DECODE와 동일하게 '=(equal)' 조건으로 사용되는 경우
- SELECT CASE 비교 대상 WHEN 조건 THEN 출력 값 END "컬럼명"
- [유형 2] 비교 조건이 '=(equal)'이 아닌 경우
- SELECT CASE WHEN 비교 대상 BETWEEN 시작 값 AND 마지막 값 THEN 출력 값
END "컬럼명"
- SELECT CASE WHEN 비교 대상 BETWEEN 시작 값 AND 마지막 값 THEN 출력 값
- CASE문은 튜닝에서 많이 사용되고 있으며, DECODE보다 더 넓은 범위에서 사용할 수 있기에 반드시 숙지해두면 좋음
여담
- CMD창에서 별칭과 함수가 정상 작동됐지만 DBeaver에서는 .. 같은 쿼리임에도 함수가 제대로 작동하지 않았음
[ 이런거 꽤 많던데.. DBeaver 단점 ]
'DB > Oracle' 카테고리의 다른 글
[Oracle] 단일행 함수 - 정규식(Regular Expression) (2) (0) | 2021.07.27 |
---|---|
[Oracle] 단일행 함수 - 정규식(Regular Expression) (1) (0) | 2021.07.20 |
오라클(Oracle) bin$ 으로 시작하는 테이블 삭제 (0) | 2021.07.15 |
Scott 계정 SQL 파일 (0) | 2021.07.15 |
Oracle 계정 생성 및 사용자 확인 (0) | 2021.07.15 |