[Oracle/SQL] 오라클 내장 함수 완벽 정리 (숫자, 문자, 날짜, NVL)

SQL 쿼리를 작성할 때 함수를 적재적소에 사용하는 것은 매우 중요합니다. 오늘은 행 하나하나에 적용되는 단일행 함수부터 데이터를 묶어서 처리하는 집계 함수까지, 실무에서 자주 쓰이는 핵심 함수들을 정리해 보겠습니다.

 

1. 숫자 함수 (Numeric Functions)

숫자 데이터의 소수점을 처리하거나 절댓값을 구할 때 사용합니다.

• ABS(n): 절댓값을 반환합니다.
• CEIL(n) / FLOOR(n): 각각 올림과 내림(바닥) 값을 반환합니다.
• ROUND(n, k): 반올림 함수입니다. k가 양수면 소수점 자리, 음수면 정수 자리에서 반올림합니다.

함수	예시	결과	설명
CEIL	CEIL(15.7)	16	무조건 올림
FLOOR	FLOOR(15.7)	15	무조건 내림
ROUND	ROUND(5.36, 1)	5.4	소수점 첫째 자리까지 표시 (둘째에서 반올림)
ROUND	ROUND(232.12, -2)	200	십의 자리에서 반올림하여 백의 자리까지 표시

 

실습 예제: 고객별 평균 주문 금액 구하기 (백 원 단위 반올림)

SELECT custid "고객번호", 
       ROUND(SUM(saleprice)/COUNT(*), -2) "평균금액"
FROM Orders
GROUP BY custid;

 

2. 문자 함수 (Character Functions)

문자열을 자르거나, 합치거나, 길이를 측정할 때 유용합니다.

• REPLACE: 특정 문자열을 찾아 다른 문자로 치환합니다. (실제 데이터 조회 결과가 변경됨)
• SUBSTR: 문자열의 일부만 추출합니다. SUBSTR(문자열, 시작위치, 길이)
• LENGTH / LENGTHB: 문자의 개수와 바이트(Byte) 수를 반환합니다.
• Tip: 오라클 XE 버전에서 한글은 보통 3바이트를 차지합니다.

실습 예제: 성씨별 인원수 파악하기

SQL

 

SELECT SUBSTR(name, 1, 1) "성", COUNT(*) "인원"
FROM customer
GROUP BY SUBSTR(name, 1, 1); -- 첫 번째 글자(성)만 잘라서 그룹화

 

3. 날짜 함수 (Date Functions)

오라클에서 DATE 타입은 산술 연산이 가능합니다. (+10은 10일 후를 의미)

• SYSDATE: 현재 시스템의 날짜와 시간을 반환합니다.
• TO_CHAR: 날짜나 숫자를 원하는 포맷의 문자열로 변환합니다.
• TO_DATE: 문자열을 날짜 형식으로 변환합니다.

자주 쓰는 포맷: yyyy(년), mm(월), dd(일), dy(요일), hh24:mi:ss(시간)

실습 예제: 특정 날짜 주문 조회

SELECT orderid, TO_CHAR(orderdate, 'yyyy-mm-dd dy') "주문일"
FROM orders
WHERE orderdate = TO_DATE('20140707', 'yyyymmdd');

 

4. 일반 함수 (NVL)

데이터베이스의 NULL 값은 연산이 불가능하기 때문에, 이를 처리해주는 함수가 반드시 필요합니다.

• NVL(컬럼, 치환값): 컬럼 값이 NULL일 경우 지정한 '치환값'으로 변환합니다.

실습 예제: 전화번호가 없는 고객 처리

SQL

 

SELECT name "이름", NVL(phone, '연락처없음') "전화번호"
FROM customer;

 

💡 Tip

1. dual 테이블: 오라클에서 제공하는 가상 테이블로, 함수 계산 결과만 확인하고 싶을 때 FROM dual을 사용하면 편리합니다.
2. 대소문자: SQL 예약어나 함수명은 대소문자를 구분하지 않지만, 가독성을 위해 대문자로 쓰는 습관을 들이면 좋습니다.
3. GROUP BY 제약: GROUP BY를 사용할 때는 SELECT 절에 그룹화한 기준 컬럼이나 집계 함수(SUM, AVG 등)만 올 수 있다는 점을 주의하세요!

 

단일행 함수는 행 하나에 함수를 적용해 결과가 나옴, 집계 함수는 여러개의 튜플을 모아서 처리하는 함수

분석함수는 쓸 일이 많지 않음.

 

CEILING (실링) = 천장이라는 뜻으로 올림 함수, 소수점 이하의 숫자가 있을 때만 올림 5.7 -> 6

FLOOR = 마룻바닥이라는 뜻으로 내림 함수 4.0 -> 4 , 4.8 ->4

ROUND = 5.36 , 1 = 1의 자리까지 나타내야 함. 2자리인 6에서 반올림 5.36 , 2 =  3자리에서 반올림 5.360 -> 5.36

round(, ) 소수점이하자리에서 반올림할거면 콤마 뒤 양수, 정수를 반올림할거면 콤마 뒤 음수

1   2   3  2  .12

-4  -3  -2 -1

대문자로 꼭 안쓰고 소문자로 써도 됨

LOG, SQRT, SIGN은 정말 잘 안 씀

 

[실습]

접속하기 위해 sqlplus scott으로 들어가고 비번을 입력하라는 메시지가 나오면 tiger치고 엔터

 

--SELECT 다음에 내가 알고 싶은 함수를 옆에다가 적음

--dual = 칼럼을 하나만 갖고 있는 오라클에서 제공하는 계산식. 테이블 이름

 

round = 반올림함수 콤마 뒤 숫자 소수점 자리까지

 

select round(5.36, 1)from dual;

select round(5.361, 2) from dual;

 

ABS = 절대값함수

--ABS함수 : 절댓값을 구하는 함수

--Q4-1. -78과 +78의 절댓값을 구하시오.

SELECT ABS(-78), ABS(+78)

FROM Dual;

 

--ROUND함수 : 반올림한 값을 구하는 함수

--Q4-2. 4.875를 소수 첫째 자리까지 반올림한 값을 구하시오

SELECT ROUND(4.875, 1)

FROM Dual;

 

--숫자 함수의 연산(음수는 정수 반올림, -1은 일의 자리에서 반올림)

-- ~ 별 : GROUP BY

-- GROUP BY  뒤에 나오는 필드만 SELECT 뒤에 나올 수 있음 다른 필드 SELECT뒤에 나오면 안됨

집계함수인 ROUND, SUM은 와도 상관없음 

 

--Q4-3. 중요! 고객별 평균 주문 금액을 백 원 단위로 반올림한 값을 구하시오.

SELECT custid "고객번호", ROUND(SUM(saleprice)/COUNT(*),-2)"평균금액"

FROM Orders

GROUP BY custid;

-- 주문 금액이므로 Orders테이블에 있음

-- “고객번호”, “평균금액”으로 테이블 헤더에 나올 명 정해줌. 이런거 안 적으면 

그냥 custid, ROUND(SUM(saleprice)/COUNT(*), -2)로 나옴 -> 지저분,,,

-- saleprice는 고객 한 명별로 주문금액이 있음 그래서 다 더한 후 행의 개수인count로 나눠줌

ROUND(232.12, -2) -> 200 

 

자주 사용 : CHR, CONCAT, LOWER, UPPER, LPAD, RPAD, LTRIM, RTRIM, REPLACE, SUBSTR, LENGTH

 

--REPLACE = 문자열을 치환하는 함수 -> 실제 값도 변경됨

--Q4-4. 도서제목에 야구가 포함된 도서를 농구로 변경한 후 도서 목록을 보이시오.

SELECT Bookid, REPLACE(bookname, '야구', '농구'), publisher, price

FROM book;

 

 

테이블 정리 안해서 사진으로 대체,,하하,, 실습하긴 햇다

 

---LENGTH = 글자의 수를 세어주는 함수(단위가 바이트(byte)가 아닌 문자 단위) -> 공백도 글자수에 포함됨

--LENGTHB = 바이트 글자수 세어줌, 띄어쓰기 하나는 1바이트

오라클 11g XE버전은 한글 한개가 3바이트 XE(학생버전)

--Q4-5. 굿스포츠에서 출판한 도서의 제목과 제목의 글자 수를 확인하시오.

--(한글은 2바이트 혹은 UNICODE경우는 3바이트를 차지함)

SELECT bookname"제목", LENGTH(bookname)"글자수", LENGTHB(bookname)"바이트수"

FROM book

 

 

WHERE publisher = '굿스포츠';

 

--SUBSTR = 지정한 길이만큼의 문자열을 반환하는 함수

--Q4-6. 마당서점의 고객 중에서 같은 성을 가진 사람이 몇 명이나 되는지 성별 인원수를 구하시오.

SELECT SUBSTR(name, 1, 1)"성", COUNT(*)"인원"

FROM customer

GROUP BY SUBSTR(name, 1, 1);

1, 1 제일 첫 번째에서 하나만 뽑아옴

 

 

 

 

                                                                 ADD_MONTHS 잘 안씀,,

 

 

 

 

hh, hh12, hh24, day, dd, month, mi, mm, ss, yyyy자주 사용 

 

--Q4-7. 마당서점은 주문일로부터 10일 후 매출을 확정한다. 각 주문의 확정일자를 구하시오.

SELECT orderid"주문번호", orderdate"주문일", orderdate+10"확정"

FROM orders;

orderdate타입은 date이기 때문에 더하기 10하면 자동으로 10일 뒤로 됨

 

 

--TO_DATE = 문자형으로 저장된 날짜를 날짜형으로 변환하는 함수

--TO_CHAR = 날짜형을 문자형으로 변환하는 함수

--Q4-8. 중요 마당서점이 2014년 7월 7일에 주문받은 도서의 주문번호, 주문일, 고객번호, 도서번호를 모두 보이시오. 

단 주문일을 'yyyy-mm-dd 요일' 형태로 표시된다.

SELECT orderid "주문번호", TO_C HAR(orderdate, 'yyyy-mm-dd dy')"주문일", custid "고객번호", bookid "도서번호"

FROM orders

WHERE orderdate=TO_DATE('20140707', 'yyyymmdd'); 

 

 

테이블 열 잘 맞지 않아 사진으로 대체.

--SYSDATETIME = 오라클의 현재 날짜와 시간을 반환하는 함수

--SYSTIMESTAMP = 현재 날짜, 시간과 함께 초 이하의 시간과 서버의 TIMEZONE까지 출력함

--Q4-8. DBMS 서버에 설정된 현재 시간과 오늘 날짜를 확인하시오.

SELECT SYSDATE, TO_CHAR(SYSDATE, 'yyyy/mm/dd dy hh24:mi:ss') "SYSDATE_1"

FROM dual;

 

 

 

 

 

 

 

 

 

  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

--mybook테이블 만들기

 

 

 

create table mybook(

bookid number,

price number);

 

--추가

insert into mybook values(1, 10000);

insert into mybook values(2, 20000);

insert into mybook values(3, NULL);

 

--확인

SELECT* FROM mybook;

 

 

 

--price가 null이 아닌 것 확인

SELECT *

FROM mybook

WHERE price is not null;

 

--NVL ( Null Value ) = NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력

NVL (속성, 값)  //속성 값이 NULL이면 '값'으로 대치한다. 값 - > 치환할 값

 

--Q4-10. 이름, 전화번호가 포함된 고객목록을 보이시오. 단, 전화번호가 없는 고객은 '연락처없음'으로 표시한다.

SELECT name"이름", NVL(phone, '연락처없음')"전화번호"

FROM customer;

 

 

 

 

 

 

 

 

 

 

 

--CEIL(15.7) : CEILING(숫자)함수와 같은 것으로 올림함수

SELECT CEIL(15.7)FROM dual;

 

--FLOOR(15.7) : 15.7 내림함수

SELECT FLOOR(15.7) FROM dual;

 

 

--ROUND(15.7) : 반올림함수

SELECT ROUND(15.7) FROM dual;

 

--CONCAT('HAPPY', 'Birthday') : 두 문자 연결함수

SELECT CONCAT('HAPPY', 'Birthday') FROM dual;

 

--LOWER('Birthday') : 소문자로 변환

SELECT LOWER('Birthday') FROM dual;

 

 

--REPLACE('JACK', 'J', 'BL') : 대상 문자열의 지정한 문자를 원하는 문자로 변경

SELECT REPLACE('JACK', 'J', 'BL') FROM dual;

 

--SUBSTR('ABCDEFG', 3, 4) : 대상 문자열의 지정된 자리에서부터 지정된 길이만큼 잘라서 반환

SELECT SUBSTR('ABCDEFG', 3, 4) FROM dual;

 

--UPPER('Birthday') : 대문자로 변경

SELECT UPPER('Birthday') FROM dual;

 

--LENGTH('Birthday') : 대상 문자열의 글자 수 반환

SELECT LENGTH('Birthday') FROM dual;

 

--SYSDATE : 오늘 날짜 반환하는 인자 없는 함수

SELECT SYSDATE FROM dual;

 

--TO_CHAR(SYSDATE) : 날짜형 데이터를 문자열로 반환

SELECT TO_CHAR(SYSDATE) FROM dual;

 

--TO_DATE('12 05 2014', 'DD MM YYYY') : 문자형 데이터를 날짜형 데이터로 변환

SELECT TO_DATE('12 05 2014', 'DD MM YYYY') FROM dual;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

--SELECT 다음에 내가 알고 싶은 함수를 옆에다가 적음
--dual = 칼럼을 하나만 갖고 있는 오라클에서 제공하는 계산식. 테이블 이름
select round(5.36, 1)from dual;

select round(5.361, 2) from dual;

select abs(-3.74)from dual;

--ABS함수 : 절댓값을 구하는 함수
--Q4-1. -78과 +78의 절댓값을 구하시오.
SELECT ABS(-78), ABS(+78)
FROM Dual;

--ROUND함수 : 반올림한 값을 구하는 함수
--Q4-2. 4.875를 소수 첫째 자리까지 반올림한 값을 구하시오
SELECT ROUND(4.875, 1)
FROM Dual;

--숫자 함수의 연산(음수는 정수 반올림, -1은 일의 자리에서 반올림)
--~ 별 : GROUP BY
--Q4-3. 고객별 평균 주문 금액을 백 원 단위로 반올림한 값을 구하시오.
SELECT custid "고객번호", ROUND(SUM(saleprice)/COUNT(*),-2)"평균금액"
FROM Orders
GROUP BY custid;

--REPLACE = 문자열을 치환하는 함수 -> 실제 값도 변경됨
--Q4-4. 도서제목에 야구가 포함된 도서를 농구로 변경한 후 도서 목록을 보이시오.
SELECT Bookid, REPLACE(bookname, '야구', '농구'), publisher, price
FROM book;

--LENGTH = 글자의 수를 세어주는 함수(단위가 바이트(byte)가 아닌 문자 단위)
--Q4-5. 굿스포츠에서 출판한 도서의 제목과 제목의 글자 수를 확인하시오.
--(한글은 2바이트 혹은 UNICODE경우는 3바이트를 차지함)
SELECT bookname"제목", LENGTH(bookname)"글자수", LENGTHB(bookname)"바이트수"
FROM book
WHERE publisher = '굿스포츠';


--SUBSTR = 지정한 길이만큼의 문자열을 반환하는 함수
--Q4-6. 마당서점의 고객 중에서 같은 성을 가진 사람이 몇 명이나 되는지 성별 인원수를 구하시오.
SELECT SUBSTR(name, 1, 1)"성", COUNT(*)"인원"
FROM customer
GROUP BY SUBSTR(name, 1, 1);

--Q4-7. 마당서점은 주문일로부터 10일 후 매출을 확정한다. 각 주문의 확정일자를 구하시오.
SELECT orderid"주문번호", orderdate"주문일", orderdate+10"확정"
FROM orders;

--TO_DATE = 문자형으로 저장된 날짜를 날짜형으로 변환하는 함수
--TO_CHAR = 날짜형을 문자형으로 변환하는 함수
--Q4-8. 마당서점이 2014년 7월 7일에 주문받은 도서의 주문번호, 주문일, 고객번호, 도서번호를 모두 보이시오. 단 주문일을 'yyyy-mm-dd 요일' 형태로 표시된다.
SELECT orderid "주문번호", TO_CHAR(orderdate, 'yyyy-mm-dd dy')"주문일", custid "고객번호", bookid "도서번호"
FROM orders
WHERE orderdate=TO_DATE('20140707', 'yyyymmdd'); 


--SYSDATETIME = 오라클의 현재 날짜와 시간을 반환하는 함수
--SYSTIMESTAMP = 현재 날짜, 시간과 함께 초 이하의 시간과 서버의 TIMEZONE까지 출력함
--Q4-8. DBMS 서버에 설정된 현재 시간과 오늘 날짜를 확인하시오.
SELECT SYSDATE, TO_CHAR(SYSDATE, 'yyyy/mm/dd dy hh24:mi:ss') "SYSDATE_1"
FROM dual;

--mybook테이블 만들기
create table mybook(
bookid number,
price number);

--추가
insert into mybook values(1, 10000);
insert into mybook values(2, 20000);
insert into mybook values(3, NULL);

--확인
SELECT* FROM mybook;

--price가 null이 아닌 것 확인
SELECT *
FROM mybook
WHERE price is not null;

--NVL = NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력
NVL(속성, 값)  //속성 값이 NULL이면 '값'으로 대치한다.

--Q4-10. 이름, 전화번호가 포함된 고객목록을 보이시오. 단, 전화번호가 없는 고객은 '연락처없음'으로 표시한다.
SELECT name"이름", NVL(phone, '연락처없음')"전화번호"
FROM customer;

--CEIL(15.7) : CEILING(숫자)함수와 같은 것으로 올림함수
SELECT CEIL(15.7)FROM dual;

--FLOOR(15.7) : 15.7 내림함수
SELECT FLOOR(15.7) FROM dual;


--ROUND(15.7) : 반올림함수
SELECT ROUND(15.7) FROM dual;

--CONCAT('HAPPY', 'Birthday') : 두 문자 연결함수
SELECT CONCAT('HAPPY', 'Birthday') FROM dual;

--LOWER('Birthday') : 소문자로 변환
SELECT LOWER('Birthday') FROM dual;


--REPLACE('JACK', 'J', 'BL') : 대상 문자열의 지정한 문자를 원하는 문자로 변경
SELECT REPLACE('JACK', 'J', 'BL') FROM dual;

--SUBSTR('ABCDEFG', 3, 4) : 대상 문자열의 지정된 자리에서부터 지정된 길이만큼 잘라서 반환
SELECT SUBSTR('ABCDEFG', 3, 4) FROM dual;

--UPPER('Birthday') : 대문자로 변경
SELECT UPPER('Birthday') FROM dual;

--LENGTH('Birthday') : 대상 문자열의 글자 수 반환
SELECT LENGTH('Birthday') FROM dual;

--SYSDATE : 오늘 날짜 반환하는 인자 없는 함수
SELECT SYSDATE FROM dual;

--TO_CHAR(SYSDATE) : 날짜형 데이터를 문자열로 반환
SELECT TO_CHAR(SYSDATE) FROM dual;

--TO_DATE('12 05 2014', 'DD MM YYYY') : 문자형 데이터를 날짜형 데이터로 변환
SELECT TO_DATE('12 05 2014', 'DD MM YYYY') FROM dual;