[ORACLE] 오라클의 다양한 함수 (문자열, 날짜, 변환, 숫자, 그룹 함수)

 

 

---

오라클의 다양한 함수 알아보기

(문자열, 날짜, 변환, 숫자, 그룹 함수)

---

 

 

0. 임시 데이터 출력

---

SELECT * FROM DUAL;

 

아래의 예제들에서 함수 적용 값들만을 바로바로 확인하기 위해 임시 데이터를 나타내는 DUAL을 사용할 것이다. DUAL은 테이블에 의존하지 않은 일반데이터의 연산결과를 출력할 때 주로 사용한다.

 

SELECT 1234*1234 FROM DUAL;

 

 

 

1. 문자열 처리 함수

---

LOWER()

문자열을 소문자로 변환하는 함수다.

SELECT LOWER('HONG GIL DONG') AS "소문자" FROM DUAL;

 

 

예제1. emp 테이블에 사원이름(ename)을 "성명"이란 이름을 붙여 소문자로 출력하면?

SELECT LOWER(ename) AS "성명" FROM emp;

 

 

UPPER()

문자열을 대문자로 변환하는 함수다.

SELECT UPPER('Hong Gil Dong') AS "대문자" FROM DUAL;

 

 

예제1. emp 테이블에 사원이름(ename)을 "성명"이란 이름을 붙여 대문자로 출력하면?

SELECT UPPER(ename) AS "성명" FROM emp;

 

 

INITCAP()

문자열의 첫 글자만 대문자로 변환하는 함수다.

SELECT INITCAP('hong gil dong') AS "첫글자만 대문자" FROM DUAL;

 

 

예제1. emp 테이블에 사원이름(ename)을 "성명"이란 이름을 붙여 첫글자만 대문자로 출력하면?

SELECT INITCAP(ename) AS "성명" FROM emp;

 

 

CONCAT()

문자열을 연결하는 이어붙이기 함수다.

SELECT CONCAT('Orcale', 'SQL'), 'Oracle' || 'SQL' FROM DUAL;

 

|| (이어붙이기 연산) 과 동일한 기능을 한다.

 

 

예제1. emp 테이블에서 사원번호(empno)와 사원이름(ename)을 붙여서 출력한다면?

SELECT CONCAT(empno, ename) FROM emp;

 

 

SUBSTR(데이터, 인덱스, 카운트)

인덱스부터, 카운트만큼 문자열 데이터를 추출하는 함수다.

SELECT SUBSTR('즐거운 ORACLE 공부', 2, 4) FROM DUAL;

 

자바의 substring 처럼 시작번째부터 끝번째 +1 이 아닌, 시작번째부터 글자수를 나타낸다. 위의 경우 2번째 글자부터 4글자를 표시한다.

 

 

 

2. 날짜 처리 함수 & 키워드

---

SYSDATE

시스템의 날짜 즉, 오늘 날짜와 현재 시간을 가져온다.

SELECT SYSDATE FROM DUAL;

 

 

MONTHS_BETWEEN()

두 개의 날짜 데이터가 몇 개월 차이나는지 나타낸다.

SELECT MONTHS_BETWEEN('2021-12-31', SYSDATE)) AS "개월수" FROM DUAL;

 

소수점 아래 자리를 버리는 FLOOR 함수를 사용하면 깔끔하게 출력할 수 있다.

 

SELECT FLOOR(MONTHS_BETWEEN('2021-12-31', SYSDATE)) AS "개월수" FROM DUAL;

 

 

ADD_MONTHS()

개월 수를 더한다.

SELECT ADD_MONTHS(SYSDATE, 20) FROM DUAL;

 

 

 

3. 형변환 함수

---

TO_CHAR()

DATE 또는 그 외 형식의 데이터를 문자(VARCHAR2)로 변환한다.

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS "DATE -> String" FROM DUAL;

 

 

예시1. SYSDATE 의 년도를 Y로 나타내보자.

SELECT TO_CHAR(SYSDATE, 'YY"년"') FROM DUAL; -- 24년
SELECT TO_CHAR(SYSDATE, 'Y"년"') FROM DUAL; -- 4년

 

예시2. SYSDATE 의 월을 M으로 나타내보자.

SELECT TO_CHAR(SYSDATE, 'MONTH') FROM DUAL; -- 10월	9월처럼 한 자리수 월이면 한 자리만 출력
SELECT TO_CHAR(SYSDATE, 'MON') FROM DUAL; -- 10월	9월처럼 한 자리수 월이면 한 자리만 출력
SELECT TO_CHAR(SYSDATE, 'MM') FROM DUAL; -- 10
SELECT TO_CHAR(SYSDATE, 'MM"월"') FROM DUAL; -- 10월

 

MONTH 와 MON 은 "월" 문자를 붙여 월을 나타내며, MM 은 숫자만을 나타낸다.

 

 

예시3. SYSDATE 의 일을 D로 나타내보자.

-- DD : 일자를 01~31로 표시
SELECT TO_CHAR(SYSDATE, 'DD') FROM DUAL; -- 31
-- DDD : 일자를 001~365 형태로 표시
SELECT TO_CHAR(SYSDATE, 'DDD') FROM DUAL; -- 305

 

DD 는 01~31 까지, DDD는 한 해를 기준으로 001~365 까지의 일수를 나타낸다.

 

 

예시4. 오늘 날짜(SYSDATE)를 년월일요일까지 나타내보자.

-- DL : 오늘 날짜를 요일까지 표시
SELECT TO_CHAR(SYSDATE, 'DL') FROM DUAL; -- 2024년 10월 31일 목요일

 

DL 은 날짜를 년, 월, 일, 요일을 나타낸다.

 

 

예시5. 숫자 123456789 를 세자리마다 콤마(,)를 넣어 문자로 변환해보자

SELECT TO_CHAR(123456789, '999,999,999') FROM DUAL;

 

TO_CHAR 로 인해 문자로 변환된 숫자 데이터는 명령 실행 이후 숫자로서의 기능이 상실되어 다른 숫자와 계산이 불가능하다.

 

 

TO_DATE()

문자(VARCHAR2)를 날짜(DATE) 데이터 형식으로 변환한다.

INSERT 명령에서 VALUES() 괄호 안에 많이 사용되는 함수다.

SELECT TO_DATE('2019/12/31', 'YYYY-MM-DD') AS "String -> Date" DUAL;

 

 

 

TO_NUMBER()

문자 데이터를 숫자 데이터로 변환한다.

SELECT TO_NUMBER('123456') FROM DUAL;

 

 

 

4. 숫자 처리 함수

---

ROUND(반올림하려는숫자, 표시하고자하는자리수)

반올림 하고자 하는 숫자의 소수점을, 표시하고자 하는 자리수만큼 남기고 반올림한다.

SELECT ROUND(12.9382, 3) FROM DUAL; -- 12.938
SELECT ROUND(12.9382, 2) FROM DUAL; -- 12.94
SELECT ROUND(12.9382, 1) FROM DUAL; -- 12.9
SELECT ROUND(12.9382, 0) FROM DUAL; -- 13

 

 

FLOOR()

소수점 첫째자리에서 버린다.

SELECT FLOOR(12.9382) FROM DUAL; -- 12(버림)

 

 

CEIL()

소수점 첫째자리에서 올린다.

SELECT CEIL(12.9382) FROM DUAL; -- 13(올림)

 

 

 

5. 그룹 함수

---

테이블내의 하나의 필드값들 전체를 대상으로 사용하는 함수를 말한다. SELECT 나 HAVING 절에 사용된다.

 

SUM()

필드값의 합계를 나타낸다.

 

예시1. booklist 테이블의 inprice 필드값의 전체 합계를 "입고가격합계" 이름으로 나타낸다면?

SELECT SUM(inprice) AS "입고가격합계" FROM booklist;

 

예시2. booklist 테이블의 rentprice 필드값 중 inprice가 18000 이상인 값의 전체 합계를 나타낸다면?

SELECT SUM(rentlist) FROM booklist WHERE inprice >= 18000;

 

 

COUNT()

필드값의 개수를 나타낸다.

 

예시1. memberlist 테이블의 필드값의 개수를(레코드값) 전부 구한다면?

SELECT COUNT(*) FROM memberlist;

 

예시2. memberlist 테이블에서 bpoint가 100 이상인 레코드의 개수는?

SELECT COUNT(*) FROM memberlist WHERE bpoint>=100;

 

 

AVG()

필드값의 평균을 나타낸다.

 

예시1. booklist 테이블의 입고가격(inprice) 필드값을 평균내고 소수 첫째자리에서 반올림하여 표현한다면?

SELECT ROUND(AVG(inprice), 0) FROM booklist;

 

 

MAX()

필드값들 중 최고값을 나타낸다.

 

예시1. booklist 테이블에 가장 높은 입고가격(inprice) 값을 구해보자.

SELECT MAX(inprice) FROM booklist;

 

SEQUENCE 에 의해 방금 추가된 자동 증가번호를 조회할 때 많이 사용된다.

 

 

MIN()

필드값들 중 최소값을 나타낸다.

 

예시1. booklist 테이블에 가장 낮은 입고가격(inprice) 값을 구해보자.

SELECT MIN(inprice) FROM booklist;

 

 

GROUP BY()

그룹함수의 결과들을 다른 필드의 그룹으로 재구성한다. 쉽게말해 기준이 되는 필드에서 같은 값을 가진 행끼리 하나의 그룹으로 뭉쳐준다. 보통 앞서 다룬 SUM, COUNT, MAX 와 같은 집계함수와 함께 사용된다.

또한, GROUP BY에 사용되는 필드명은 반드시 SELECT 와 FROM 사이에 나와야 한다.

 

예시1. rentlist 테이블의 도서별(bnum) 대여건수를 나타낸다면?

SELECT bnum, COUNT(*)
FROM rentlist GROUP BY bnum;

 

예시2. 앞선 예제에 적절한 필드명을 붙이고, 할인금액(discount)의 합계도 함께 구해보자.

SELECT bnum AS "도서번호", COUNT(*) AS "도서별 대여건수", SUM(discount) AS "할인금액합계"
FROM rentlist GROUP BY bnum;

 

 

예제3. rentlist 테이블에서 대여일자(rentdate)별 대여건수와 할인금액평균을 내림차순으로 나타낸다면?

SELECT rentdate AS "대여일자", COUNT(*) AS "대여건수", AVG(discount) AS "할인금액평균"
FROM rentlist GROUP BY rentdate ORDER BY rentdate DESC;

 

예제4. 앞선 예제에서, 할인금액의 평균이 150 이상인 경우만 나타낸다면?

SELECT rentdate AS "대여일자", COUNT(*) AS "대여건수", AVG(discount) AS "할인금액평균"
FROM rentlist
GROUP BY rentdate
HAVING AVG(discount) >= 150
ORDER BY rentdate DESC;

 

그룹핑된 내용들에 조건을 붙일 때는 HAVING 절을 사용한다.