오라클 내장 함수
오라클 내장 함수
오라클에는 다양한 내장 함수들이 존재하며, 이를 활용하면 쉽게 계산을 수행할 수 있다. 함수들은 크게 다음과 같이 구분된다.(해당 링크를 누르면 공식 문서를 확인할 수 있다)
Single-row function : 단일 행 함수. 단일 행에 대한 계산 처리를 수행하는 함수
Aggregate function : 집계 함수. 데이터를 집계하여 계산 처리를 수행하는 함수
Analytic function : 분석 함수. 행 그룹을 기반으로 집계 값을 계산.
단일 행 함수
단일 행 함수는 하나의 데이터를 이용하여 변환 또는 계산을 수행한다.
결과를 확인하기 위해서 임시 테이블인 dual
테이블을 사용한다.
chr 함수를 이용하면 ASCII
코드 표에 해당하는 숫자를 글자로 변환한다.
결과 |
A |
ascii 함수는 ASCII
코드표의 글자를 10진수로 변환한다.
결과 |
65 |
concat 함수를 이용하면 문자열 두개를 연결한다.
결과 |
안녕하세요 |
오라클에서는 ||
연산자로 동일한 작업을 수행할 수 있다.
결과 |
안녕하세요 |
upper 함수는 알파벳을 대문자로 변환한다.
결과 |
HELLO ORACLE |
lower 함수는 알파벳을 소문자로 변환한다.
결과 |
hello oracle |
initcap 함수는 단어의 첫 글자를 대문자로 변환한다.
결과 |
Hello Oracle |
substr 함수는 문자열을 위치로 잘라낸다. 오라클에서 문자열은 1번 위치부터 시작함을 주의해야한다.
다음 예문은 ABCDEFG
를 2번 위치부터 잘라낸 결과를 출력한다.
결과 |
BCDEFG |
시작점과 길이를 지정하여 문자열을 잘라낼 수도 있다.
다음 예문은 ABCDEFG
를 2번 위치부터 3글자 잘라낸 결과를 출력한다.
결과 |
BCD |
replace 함수는 특정 문자열을 원하는 문자열로 치환한다.
다음 예문은 I hate oracle
에서 hate
를 love
로 바꾸어 화면에 출력한다.
결과 |
I love oracle |
to_date 함수는 문자열 데이터(CHAR,NCHAR,VARCHAR2,NVARCHAR2)를 날짜로 변환하는 명령이다.
다음 예문은 연-월-일 형태의 문자열을 날짜로 변환한다.
결과 |
20/01/01 |
to_date의 첫 번째에는 문자열 데이터를, 두 번째에는 해석할 형식을 지정한다.
YYYY : 연도 4자리
MM : 월 2자리
DD : 일 2자리
더 자세한 형식은 다음 사이트를 방문하여 확인할 수 있다. https://www.techonthenet.com/oracle/functions/to_date.php
to_char
to_char 함수는 문자열, 날짜, 숫자 등을 문자열로 변환한다.
다음 구문은 현재 시각을 OOOO-OO-OO
형태로 출력한다.
결과 |
2020-11-19 |
사용하는 형식은 to_char와 동일하며, 첫 번째 값으로 날짜
데이터가 들어가야 한다.
다음 구문은 1234567.890123
이라는 값을 다음 형식에 맞게 출력한다.
세 자리마다 콤마 출력
소수점 2자리까지만 출력
결과 |
1,234,567.89 |
다음 구문은 소수점을 강제로 만들어 출력한다.
결과 |
1,234,567.00 |
소수점 이하를 0으로 설정하면, 없을 경우 0으로 출력한다.
하지만 0.12
와 같은 데이터를 출력하면 문제가 발생한다.
결과 |
.12 |
이를 방지하려면 다음과 같이 설정해야 한다.
결과 |
0.12 |
집계 함수
집계 함수는 결과집합의 데이터를 종합하여 어떠한 결과를 만들어내는 함수이다. 살펴볼 샘플 테이블은 다음과 같다.
table product
번호 (no) | 이름 (name) | 종류 (type) | 가격 (price) | 제조일 (made) | 유통기한 (expire) |
1 | 스크류바 | 아이스크림 | 1200 | 2020-05-01 | 2020-10-01 |
2 | 마이쮸 | 사탕 | 900 | 2020-01-01 | 2021-01-01 |
3 | 초코파이 | 과자 | 3000 | 2020-02-01 | 2020-10-20 |
4 | 맛동산 | 과자 | 2200 | 2020-01-05 | 2020-04-05 |
5 | 참이슬 | 주류 | 1000 | 2020-01-01 | 2020-07-01 |
6 | 처음처럼 | 주류 | 1000 | 2020-03-15 | 2020-08-15 |
7 | 바나나킥 | 과자 | 1500 | 2020-05-03 | 2020-06-03 |
8 | 빠삐코 | 아이스크림 | 1000 | 2019-12-01 | 2020-06-01 |
9 | 멘토스 | 사탕 | 1200 | 2020-03-20 | 2020-12-31 |
10 | 오레오 | 과자 | 2100 | 2019-06-01 | 2020-06-01 |
생성 구문은 다음과 같다.
전체 데이터의 개수는 다음과 같이 조회할 수 있다.
데이터 개수를 세어 출력하기 때문에 다음과 같은 결과가 나온다.
COUNT(*) |
10 |
COUNT(*)
대신 항목을 작성해도 되며, 항목으로 작성한 경우는 NULL을 세지 않는 것을 주의해야 한다.
별칭 부여가 가능하므로 원하는 이름으로 변경하여 출력할 수 있다.
상품 개수 |
10 |
sum 함수를 이용하여 상품 가격들의 합계를 계산하여 출력할 수 있다.
와일드카드는 사용할 수 없으며, 출력 결과는 다음과 같이 나온다. 별칭을 사용할 수 있다.
SUM(PRICE) |
15100 |
avg 함수를 이용하여 상품 가격들의 평균을 계산하여 출력할 수 있다.
와일드카드는 사용할 수 없으며, 출력 결과는 다음과 같이 평균이 계산되어 나온다.
AVG(PRICE) |
1510 |
평균같은 경우 소수점이 나올 수 있으며, 소수점의 개수를 제어하고 싶거나 세 자리마다 콤마를 출력하고 싶다면 다른 명령과 조합하여 사용해야 한다.
변환 결과 |
1,510.00 |
max 함수를 이용하여 특정 컬럼의 최대값을 확인할 수 있다.
최대 가격을 조회할 수 있으며 결과는 다음과 같다. 와일드 카드는 사용할 수 없다.
MAX(PRICE) |
3000 |
min 함수를 이용하여 특정 컬럼의 최소값을 확인할 수 있다.
최소 가격을 조회할 수 있으며 결과는 다음과 같다. 와일드 카드는 사용할 수 없다.
MIN(PRICE) |
900 |
rank 함수를 이용하면 순위를 구할 수 있다.
순위 |
7 |
1000원은 price desc
로 조회했을 때 7위라는 것을 확인할 수 있다.
rank는 단독으로 사용할 수 없으며, within을 조합하여 사용한다.
dense_rank 를 사용하면 건너띄는 숫자 없이 순위를 부여한다. 만약 공동 2등이 두 개 있을 경우 1, 2, 2, 4가 아닌 1, 2, 2, 3으로 등수를 부여한다. rank()와 동일한 예제로 진행한다.
순위 |
6 |
분석 함수
분석 함수는 집계 함수와 다르게 각각의 행에 결과를 출력할 수 있다. OVER를 사용하여 분류 기준과 정렬 순서를 정하여 출력할 수 있다.
OVER에서 사용할 수 있는 옵션은 다음과 같다.
partition by : 분류 기준 설정
order by : 정렬 기준 설정
windowing : 범위 제한(생략)
rank와 over를 사용하여 각각의 행에 순위를 부여할 수 있다.
no | name | type | price | rank |
2 | 마이쮸 | 사탕 | 900 | 1 |
5 | 참이슬 | 주류 | 1000 | 2 |
8 | 빠삐코 | 아이스크림 | 1000 | 2 |
6 | 처음처럼 | 주류 | 1000 | 2 |
9 | 멘토스 | 사탕 | 1200 | 5 |
1 | 스크류바 | 아이스크림 | 1200 | 5 |
7 | 바나나킥 | 과자 | 1500 | 7 |
10 | 오레오 | 과자 | 2100 | 8 |
4 | 맛동산 | 과자 | 2200 | 9 |
3 | 초코파이 | 과자 | 3000 | 10 |
no | name | type | price | rank |
7 | 바나나킥 | 과자 | 1500 | 1 |
10 | 오레오 | 과자 | 2100 | 2 |
4 | 맛동산 | 과자 | 2200 | 3 |
3 | 초코파이 | 과자 | 3000 | 4 |
2 | 마이쮸 | 사탕 | 900 | 1 |
9 | 멘토스 | 사탕 | 1200 | 2 |
8 | 빠삐코 | 아이스크림 | 1000 | 1 |
1 | 스크류바 | 아이스크림 | 1200 | 2 |
6 | 처음처럼 | 주류 | 1000 | 1 |
partition by type 을 통해 type별로 나누어 rank를 계산하도록 설정하였다.
no | name | type | price | rank |
2 | 마이쮸 | 사탕 | 900 | 1 |
5 | 참이슬 | 주류 | 1000 | 2 |
8 | 빠삐코 | 아이스크림 | 1000 | 2 |
6 | 처음처럼 | 주류 | 1000 | 2 |
9 | 멘토스 | 사탕 | 1200 | 3 |
1 | 스크류바 | 아이스크림 | 1200 | 3 |
7 | 바나나킥 | 과자 | 1500 | 4 |
10 | 오레오 | 과자 | 2100 | 5 |
4 | 맛동산 | 과자 | 2200 | 6 |
3 | 초코파이 | 과자 | 3000 | 7 |
no | name | type | price | rank |
7 | 바나나킥 | 과자 | 1500 | 1 |
10 | 오레오 | 과자 | 2100 | 2 |
4 | 맛동산 | 과자 | 2200 | 3 |
3 | 초코파이 | 과자 | 3000 | 4 |
2 | 마이쮸 | 사탕 | 900 | 1 |
9 | 멘토스 | 사탕 | 1200 | 2 |
8 | 빠삐코 | 아이스크림 | 1000 | 1 |
1 | 스크류바 | 아이스크림 | 1200 | 2 |
6 | 처음처럼 | 주류 | 1000 | 1 |
그룹 함수
그룹 함수는 그룹에 대한 누적 연산을 수행한다. group by 를 통해 사용할 수 있다.
rollup
rollup을 사용하면 누적 계산을 수행할 수 있다. rollup 없이 단순한 그룹 합계를 구하면 다음과 같다.
분류 | 합계 |
과자 | 8800 |
주류 | 2000 |
사탕 | 2100 |
아이스크림 | 2200 |
rollup을 사용하면 다음과 같이 결과가 출력된다
분류 | 합계 |
과자 | 8800 |
사탕 | 2100 |
아이스크림 | 2200 |
주류 | 2000 |
15100 |
누적합계가 추가되는 것을 알 수 있다. group조건을 더 세분화시키면 소그룹 합계도 출력 가능하다.
제조년도 | 분류 | 합계 |
2019 | 과자 | 2100 |
2019 | 아이스크림 | 1000 |
2019 | 3100 | |
2020 | 과자 | 6700 |
2020 | 사탕 | 2100 |
2020 | 주류 | 2000 |
2020 | 아이스크림 | 1200 |
2020 | 12000 | |
15100 |
cube
cube를 이용하면 크로스탭에 대한 합계를 볼 수 있다. rollup에서는 연도 → 분류로 하면 분류별 합계를 볼 수 없는데, cube에서는 확인할 수 있다.
제조년도 | 분류 | 합계 |
15100 | ||
과자 | 8800 | |
사탕 | 2100 | |
주류 | 2000 | |
아이스크림 | 2200 | |
2019 | 3100 | |
2019 | 과자 | 2100 |
2019 | 아이스크림 1000 | |
2020 | 12000 | |
2020 | 과자 | 6700 |
2020 | 사탕 | 2100 |
2020 | 주류 | 2000 |
2020 | 아이스크림 | 1200 |
Last updated