> For the complete documentation index, see [llms.txt](https://docs.sysout.co.kr/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.sysout.co.kr/database/oracle/structure-query-language/user-query/manage-data/read-data/oracle-standard-function.md).

# 오라클 내장 함수

## 오라클 내장 함수

오라클에는 다양한 내장 함수들이 존재하며, 이를 활용하면 쉽게 계산을 수행할 수 있다.\
함수들은 크게 다음과 같이 구분된다.(해당 링크를 누르면 공식 문서를 확인할 수 있다)

* [Single-row function](https://docs.oracle.com/database/121/SQLRF/functions002.htm#SQLRF51178) : 단일 행 함수. 단일 행에 대한 계산 처리를 수행하는 함수
* [Aggregate function](https://docs.oracle.com/database/121/SQLRF/functions003.htm) : 집계 함수. 데이터를 집계하여 계산 처리를 수행하는 함수
* [Analytic function](https://docs.oracle.com/database/121/SQLRF/functions004.htm#SQLRF06174) : 분석 함수. 행 그룹을 기반으로 집계 값을 계산.

### 단일 행 함수

단일 행 함수는 하나의 데이터를 이용하여 변환 또는 계산을 수행한다.\
결과를 확인하기 위해서 임시 테이블인 `dual` 테이블을 사용한다.

```sql
select * from dual;
```

#### [chr](https://docs.oracle.com/database/121/SQLRF/functions027.htm#SQLRF00616)

chr 함수를 이용하면 `ASCII` 코드 표에 해당하는 숫자를 글자로 변환한다.

```sql
select chr(65) "결과" from dual;
```

| 결과 |
| -- |
| A  |

#### [ascii](https://docs.oracle.com/database/121/SQLRF/functions014.htm#SQLRF00604)

ascii 함수는 `ASCII` 코드표의 글자를 10진수로 변환한다.

```sql
select ascii('A') "결과" from dual;
```

| 결과 |
| -- |
| 65 |

#### [concat](https://docs.oracle.com/database/121/SQLRF/functions040.htm#SQLRF00619)

concat 함수를 이용하면 문자열 두개를 연결한다.

```sql
select concat('안녕', '하세요') "결과" from dual;
```

| 결과    |
| ----- |
| 안녕하세요 |

오라클에서는 `||`연산자로 동일한 작업을 수행할 수 있다.

```sql
select '안녕'||'하세요' from dual;
```

| 결과    |
| ----- |
| 안녕하세요 |

#### [upper](https://docs.oracle.com/database/121/SQLRF/functions242.htm#SQLRF06155)

upper 함수는 알파벳을 대문자로 변환한다.

```sql
select upper('Hello Oracle') "결과" from dual;
```

| 결과           |
| ------------ |
| HELLO ORACLE |

#### [lower](https://docs.oracle.com/database/121/SQLRF/functions106.htm#SQLRF00662)

lower 함수는 알파벳을 소문자로 변환한다.

```sql
select lower('Hello Oracle') "결과" from dual;
```

| 결과           |
| ------------ |
| hello oracle |

#### [initcap](https://docs.oracle.com/database/121/SQLRF/functions083.htm#SQLRF00650)

initcap 함수는 단어의 첫 글자를 대문자로 변환한다.

```sql
select initcap('hello oracle') "결과" from dual;
```

| 결과           |
| ------------ |
| Hello Oracle |

#### [substr](https://docs.oracle.com/database/121/SQLRF/functions196.htm#SQLRF06114)

substr 함수는 문자열을 위치로 잘라낸다.\
오라클에서 문자열은 1번 위치부터 시작함을 주의해야한다.

다음 예문은 `ABCDEFG`를 2번 위치부터 잘라낸 결과를 출력한다.

```sql
select substr('ABCDEFG', 2) "결과" from dual;
```

| 결과     |
| ------ |
| BCDEFG |

시작점과 길이를 지정하여 문자열을 잘라낼 수도 있다.\
다음 예문은 `ABCDEFG`를 2번 위치부터 3글자 잘라낸 결과를 출력한다.

```sql
select substr('ABCDEFG', 2, 3) "결과" from dual;
```

| 결과  |
| --- |
| BCD |

#### [replace](https://docs.oracle.com/database/121/SQLRF/functions167.htm#SQLRF00697)

replace 함수는 특정 문자열을 원하는 문자열로 치환한다.\
다음 예문은 `I hate oracle`에서 `hate`를 `love`로 바꾸어 화면에 출력한다.

```sql
select replace('I hate oracle', 'hate', 'love') "결과" from dual;
```

| 결과            |
| ------------- |
| I love oracle |

#### [to\_date](https://docs.oracle.com/database/121/SQLRF/functions219.htm#SQLRF06132)

to\_date 함수는 문자열 데이터(CHAR,NCHAR,VARCHAR2,NVARCHAR2)를 날짜로 변환하는 명령이다.

다음 예문은 연-월-일 형태의 문자열을 날짜로 변환한다.

```sql
select to_date('2020-01-01', 'YYYY-MM-DD') "결과" from dual;
```

| 결과       |
| -------- |
| 20/01/01 |

to\_date의 첫 번째에는 문자열 데이터를, 두 번째에는 해석할 형식을 지정한다.

* YYYY : 연도 4자리
* MM : 월 2자리
* DD : 일 2자리

더 자세한 형식은 다음 사이트를 방문하여 확인할 수 있다.\
<https://www.techonthenet.com/oracle/functions/to_date.php>

#### to\_char

to\_char 함수는 문자열, 날짜, 숫자 등을 문자열로 변환한다.

* [문자열 → 문자열](https://docs.oracle.com/database/121/SQLRF/functions215.htm#SQLRF06128)
* [날짜 → 문자열](https://docs.oracle.com/database/121/SQLRF/functions216.htm#SQLRF06129)
* [숫자 → 문자열](https://docs.oracle.com/database/121/SQLRF/functions217.htm#SQLRF06130)

다음 구문은 현재 시각을 `OOOO-OO-OO` 형태로 출력한다.

```sql
select to_char(sysdate, 'YYYY-MM-DD') "결과" from dual;
```

| 결과         |
| ---------- |
| 2020-11-19 |

사용하는 형식은 to\_char와 동일하며, 첫 번째 값으로 `날짜` 데이터가 들어가야 한다.

[Oracle DB Format 확인(공식 사이트)](https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm)

다음 구문은 `1234567.890123`이라는 값을 다음 형식에 맞게 출력한다.

* 세 자리마다 콤마 출력
* 소수점 2자리까지만 출력

```sql
select to_char(1234567.890123, '9,999,999.99') "결과" from dual;
```

| 결과           |
| ------------ |
| 1,234,567.89 |

다음 구문은 소수점을 강제로 만들어 출력한다.

```sql
select to_char(1234567, '9,999,999.00') "결과" from dual;
```

| 결과           |
| ------------ |
| 1,234,567.00 |

소수점 이하를 0으로 설정하면, 없을 경우 0으로 출력한다.

하지만 `0.12`와 같은 데이터를 출력하면 문제가 발생한다.

```sql
select to_char(0.12, '9,999,999.00') "결과" from dual;
```

| 결과  |
| --- |
| .12 |

이를 방지하려면 다음과 같이 설정해야 한다.

```sql
select to_char(0.12, '9,999,990.00') "결과" from dual;
```

| 결과   |
| ---- |
| 0.12 |

### 집계 함수

집계 함수는 결과집합의 데이터를 종합하여 어떠한 결과를 만들어내는 함수이다.\
살펴볼 샘플 테이블은 다음과 같다.

> table product

| <p>번호<br>(no)</p> | <p>이름<br>(name)</p> | <p>종류<br>(type)</p> | <p>가격<br>(price)</p> | <p>제조일<br>(made)</p> | <p>유통기한<br>(expire)</p> |
| ----------------- | ------------------- | ------------------- | -------------------- | -------------------- | ----------------------- |
| 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              |

생성 구문은 다음과 같다.

```sql
create table product(
no number primary key,
name varchar2(30) not null,
type varchar2(15) check(type in ('과자','아이스크림','주류','사탕')),
price number,
made date,
expire date
);

insert into product values(1, '스크류바', '아이스크림', 1200, '2020-05-01', '2020-10-01');
insert into product values(2, '마이쮸', '사탕', 900, '2020-01-01', '2021-01-01');
insert into product values(3, '초코파이', '과자', 3000, '2020-01-01', '2021-01-01');
insert into product values(4, '맛동산', '과자', 2200, '2020-02-01', '2020-10-20');
insert into product values(5, '참이슬', '주류', 1000, '2020-01-05', '2020-04-05');
insert into product values(6, '처음처럼', '주류', 1000, '2020-03-15', '2020-08-15');
insert into product values(7, '바나나킥', '과자', 1500, '2020-05-03', '2020-06-03');
insert into product values(8, '빠삐코', '아이스크림', 1000, '2019-12-01', '2020-06-01');
insert into product values(9, '멘토스', '사탕', 1200, '2020-03-20', '2020-12-31');
insert into product values(10, '오레오', '과자', 2100, '2019-06-01', '2020-06-01');

commit;
```

#### [count](https://docs.oracle.com/database/121/SQLRF/functions046.htm#SQLRF00624)

전체 데이터의 개수는 다음과 같이 조회할 수 있다.

```sql
select count(*) from product;
```

데이터 개수를 세어 출력하기 때문에 다음과 같은 결과가 나온다.

| COUNT(\*) |
| --------- |
| 10        |

`COUNT(*)` 대신 항목을 작성해도 되며, 항목으로 작성한 경우는 **NULL**을 세지 않는 것을 주의해야 한다.

별칭 부여가 가능하므로 원하는 이름으로 변경하여 출력할 수 있다.

```sql
select count(*) "상품 개수" from product;
```

| 상품 개수 |
| ----- |
| 10    |

#### [sum](https://docs.oracle.com/database/121/SQLRF/functions197.htm#i89126)

sum 함수를 이용하여 상품 가격들의 합계를 계산하여 출력할 수 있다.

```sql
select sum(price) from product;
```

와일드카드는 사용할 수 없으며, 출력 결과는 다음과 같이 나온다.\
별칭을 사용할 수 있다.

| SUM(PRICE) |
| ---------- |
| 15100      |

#### [avg](https://docs.oracle.com/database/121/SQLRF/functions019.htm#SQLRF00609)

avg 함수를 이용하여 상품 가격들의 평균을 계산하여 출력할 수 있다.

```sql
select avg(price) from product;
```

와일드카드는 사용할 수 없으며, 출력 결과는 다음과 같이 평균이 계산되어 나온다.

| AVG(PRICE) |
| ---------- |
| 1510       |

평균같은 경우 소수점이 나올 수 있으며, 소수점의 개수를 제어하고 싶거나 세 자리마다 콤마를 출력하고 싶다면 다른 명령과 조합하여 사용해야 한다.

```sql
select to_char(avg(price),'fm999,999,999,999,999,999.00') "변환 결과" from product;
```

| 변환 결과    |
| -------- |
| 1,510.00 |

#### [max](https://docs.oracle.com/database/121/SQLRF/functions110.htm#i89072)

max 함수를 이용하여 특정 컬럼의 최대값을 확인할 수 있다.

```sql
select max(price) from product;
```

최대 가격을 조회할 수 있으며 결과는 다음과 같다.\
와일드 카드는 사용할 수 없다.

| MAX(PRICE) |
| ---------- |
| 3000       |

#### [min](https://docs.oracle.com/database/121/SQLRF/functions112.htm#i1280029)

min 함수를 이용하여 특정 컬럼의 최소값을 확인할 수 있다.

```sql
select min(price) from product;
```

최소 가격을 조회할 수 있으며 결과는 다음과 같다.\
와일드 카드는 사용할 수 없다.

| MIN(PRICE) |
| ---------- |
| 900        |

#### [rank](https://docs.oracle.com/database/121/SQLRF/functions155.htm#SQLRF00690)

rank 함수를 이용하면 순위를 구할 수 있다.

```sql
select rank(1000) within group(order by price desc) "순위" from product;
```

| 순위 |
| -- |
| 7  |

1000원은 `price desc`로 조회했을 때 7위라는 것을 확인할 수 있다.\
rank는 단독으로 사용할 수 없으며, within을 조합하여 사용한다.

#### [dense\_rank](https://docs.oracle.com/database/121/SQLRF/functions060.htm#SQLRF00633)

dense\_rank 를 사용하면 건너띄는 숫자 없이 순위를 부여한다.\
만약 공동 2등이 두 개 있을 경우 1, 2, 2, 4가 아닌 1, 2, 2, 3으로 등수를 부여한다.\
rank()와 동일한 예제로 진행한다.

```sql
select dense_rank(1000) within group(order by price desc) "순위" from product;
```

| 순위 |
| -- |
| 6  |

### 분석 함수

분석 함수는 집계 함수와 다르게 각각의 행에 결과를 출력할 수 있다.\
OVER를 사용하여 분류 기준과 정렬 순서를 정하여 출력할 수 있다.

OVER에서 사용할 수 있는 옵션은 다음과 같다.

* partition by : 분류 기준 설정
* order by : 정렬 기준 설정
* windowing : 범위 제한(생략)

#### [rank](https://docs.oracle.com/database/121/SQLRF/functions155.htm#SQLRF00690)

rank와 over를 사용하여 각각의 행에 순위를 부여할 수 있다.

```sql
-- 번호, 이름, 종류, 가격을 조회
select no, name, type, price from product;
-- 위의 결과에 순위를 rank란 별칭으로 추가하여 조회
select
    no, name, type, price,
    rank() over(order by price) "rank"
from product;
```

| 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   |

```sql
select
    no, name, type, price,
    rank() over(partition by type order by price) "rank"
from product;
```

| 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를 계산하도록 설정하였다.

#### [dense\_rank](https://docs.oracle.com/database/121/SQLRF/functions060.htm#SQLRF00633)

```sql
select
    no, name, type, price,
    dense_rank() over(order by price) "rank"
from product;
```

| 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    |

```sql
select
    no, name, type, price,
    dense_rank() over(partition by type order by price) "rank"
from product;
```

| 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 없이 단순한 그룹 합계를 구하면 다음과 같다.

```sql
select type "분류", sum(price) "합계" from product
group by type;
```

| 분류    | 합계   |
| ----- | ---- |
| 과자    | 8800 |
| 주류    | 2000 |
| 사탕    | 2100 |
| 아이스크림 | 2200 |

rollup을 사용하면 다음과 같이 결과가 출력된다

| 분류    | 합계    |
| ----- | ----- |
| 과자    | 8800  |
| 사탕    | 2100  |
| 아이스크림 | 2200  |
| 주류    | 2000  |
|       | 15100 |

누적합계가 추가되는 것을 알 수 있다.\
group조건을 더 세분화시키면 소그룹 합계도 출력 가능하다.

```sql
select 
    extract(year from made) 제조년도,
    type 분류,
    sum(price) 합계
from product
group by rollup(extract(year from made), type);
```

| 제조년도 | 분류    | 합계    |
| ---- | ----- | ----- |
| 2019 | 과자    | 2100  |
| 2019 | 아이스크림 | 1000  |
| 2019 |       | 3100  |
| 2020 | 과자    | 6700  |
| 2020 | 사탕    | 2100  |
| 2020 | 주류    | 2000  |
| 2020 | 아이스크림 | 1200  |
| 2020 |       | 12000 |
|      |       | 15100 |

#### cube

cube를 이용하면 크로스탭에 대한 합계를 볼 수 있다.\
rollup에서는 연도 → 분류로 하면 분류별 합계를 볼 수 없는데, cube에서는 확인할 수 있다.

```sql
select 
    extract(year from made) 제조년도,
    type 분류,
    sum(price) 합계
from product
group by cube(extract(year from made), type);
```

| 제조년도 | 분류         | 합계    |
| ---- | ---------- | ----- |
|      |            | 15100 |
|      | 과자         | 8800  |
|      | 사탕         | 2100  |
|      | 주류         | 2000  |
|      | 아이스크림      | 2200  |
| 2019 |            | 3100  |
| 2019 | 과자         | 2100  |
| 2019 | 아이스크림 1000 |       |
| 2020 |            | 12000 |
| 2020 | 과자         | 6700  |
| 2020 | 사탕         | 2100  |
| 2020 | 주류         | 2000  |
| 2020 | 아이스크림      | 1200  |