<Database> 활용 가능한 Oracle 함수

티스토리 뷰

Database

<Database> 활용 가능한 Oracle 함수

춘햄 2021. 4. 13. 16:17

1. DUAL 테이블: 산술 연산 결과를 출력하기 위한 dummy 테이블이다.

select 24*60
from dual;

select *
from dual;

select SYSDATE
from dual;

2. 숫자 관련 함수

1) ABS(n): 절대값을 구하는 함수

SELECT ABS(-1234) FROM DUAL;
---> 1234

2) FLOOR(n): 소수점 이하를 버리는 함수

 SELECT FLOOR(-1234.123) FROM DUAL;
 ----> -1234

3) ROUND(n): 소수점 이하를 기반으로 반올림하는 함수

SELECT ROUND(-1234.123) FROM DUAL;
----> -1234

4) TRUNC(n, 자를 자리수): 특정 자리수에서 잘라내는 함수

SELECT TRUNC(-1234.123,-1) FROM DUAL;
----> -1230
SELECT TRUNC(-1234.123,-2) FROM DUAL;
----> -1200
SELECT TRUNC(-1234.123,-3) FROM DUAL;
----> -1000
SELECT TRUNC(-1234.123,-4) FROM DUAL;
----> 0

5) MOD(n): 나머지를 구하는 함수

SELECT MOD(27, 2) FROM DUAL;
----> 1

6) SIN : SINE 값을 반환

7) COS : COSINE 값을 반환

8) TAN : TANGENT 값을 반환

9) LOG : LOG 값을 반환

10) EXP : e(2.71828183....)의 n승을 반환

11) POWER : POWER(m, n) m의 n승을 반환

12) SIGN : SIGN(n) n<0 이면 -1을 n=0 이면 0을 n>0 이면 1을 반환

3. 문자 처리 함수

1) 대소문자 변환 함수

UPPER : 대문자로 변환

LOWER: 소문자로 변환

INITCAP : 첫글자만 대문자로, 나머지 글자는 소문자로 변환

select 'Welcome to Oracle',
	UPPER('Welcome to Oracle'),
	LOWER('Welcome to Oracle'),
	INITCAP('Welcome to Oracle')
from dual;

2) 문자 길이 구하는 함수

LENGTH : 문자의 길이를 반환 (한글 1byte)

LENGTHB : 문자의 길이를 반환 (한글 3byte)

select LENGTH('Oracle'), LENGTH('오라클')
	from dual;
    
select LENGTHB('Oracle'), LENGTHB('오라클')
	from dual;

3) 문자 조작 함수

CONCAT : 문자의 값을 연결

SELECT CONCAT('HOLY', 'MOLY') FROM DUAL;

4) 문자열의 일부만 추출하는 SUBSTR(대상, 시작, 갯수), 마찬가지로 SUBSTRB는 한글을 3바이트로

select SUBSTR('Welcome to Oracle', 4, 3)
from dual;
----> com
select SUBSTR('Welcome to Oracle', -4, 3)
from dual;
----> acl

5) 특정 문자의 위치값을 반환하는 INSTR(대상, 찾을문자, 시작위치, 몇번째발견)

마찬가지로 INSTRB는 한글을 3바이트로

select INSTR('Welcome to Oracle', 'o')
from dual;
----> 5
select INSTR('Welcome to oracle', 'o' , 6, 2)
from dual;
----> 12

6) 특정 기호로 채우는 LPAD, RPAD

select LPAD('Hi', 5, '#')
from dual;
----> ###Hi
select RPAD('Hi', 5, '#')
from dual;
----> Hi###

4. 형변환 함수

1) 문자형으로 변환하는 TO_CHAR

select SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD')
from dual;
----> 2021-04-13

select SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD, AM HH:MI:SS')
from dual;
----> 2021-04-13, 오후 01:57:07

2) 날짜형으로 변환하는 TO_DATE('문자', '포맷')

select SYSDATE-TO_DATE('2015/01/01', 'YYYY/MM/DD')
from dual;
----> 2294.58206
select TRUNC(SYSDATE-TO_DATE('2015/01/01', 'YYYY/MM/DD'))
from dual;
----> 2294

3) 숫자형으로 변환하는 TO_NUMBER

select TO_NUMBER('20,000', '99,999')-TO_NUMBER('10,000', '99,999')
from dual;
----> 10000

5. 날짜 함수

1) 현재 날짜를 반환하는 SYSDATE

select TO_CHAR(SYSDATE, 'YYYY/MM/DD') from dual;
----> 2021/04/13

select TO_CHAR(SYSDATE-1, 'YYYY/MM/DD') 어제,
	TO_CHAR(SYSDATE, 'YYYY/MM/DD') 오늘,
	TO_CHAR(SYSDATE+1, 'YYYY/MM/DD') 내일
from dual;

======================결과=======================

어제                 오늘                 내일
-------------------- -------------------- --------------------
2021/04/12           2021/04/13           2021/04/14

2) 특정 조건을 기준으로 버리는 TRUNC(date, format)

select TO_CHAR(hiredate, 'YYYY/MM/DD') 입사일,
	TO_CHAR(TRUNC(hiredate, 'MONTH'), 'YYYY/MM/DD') 입사일(TRUNC)
from emp;

======================결과========================

입사일               입사일(TRUNC)
-------------------- --------------------
2007/03/01           2007/03/01
2007/04/02           2007/04/01
2005/02/10           2005/02/01
2003/09/02           2003/09/01
2005/04/07           2005/04/01
2003/10/09           2003/10/01
2004/01/08           2004/01/01
2004/03/08           2004/03/01
1996/10/04           1996/10/01
2005/04/07           2005/04/01
2007/03/01           2007/03/01

3) 개월 수를 더하는 ADD_MONTHS(date, number)

select ename,
	TO_CHAR(hiredate, 'YYYY/MM/DD') 입사일,
	TO_CHAR(ADD_MONTHS(hiredate, 6), 'YYYY/MM/DD') "입사 6개월 후"
from emp;

======================결과======================

ENAME                입사일               입사 6개월 후
-------------------- -------------------- --------------------
김사랑               2007/03/01           2007/09/01
한예슬               2007/04/02           2007/10/02
오지호               2005/02/10           2005/08/10
이병헌               2003/09/02           2004/03/02
신동협               2005/04/07           2005/10/07
장동건               2003/10/09           2004/04/09
이문세               2004/01/08           2004/07/08
감우성               2004/03/08           2004/09/08
안성기               1996/10/04           1997/04/04
이병헌               2005/04/07           2005/10/07
조향기               2007/03/01           2007/09/01

4) 해당 달의 마지막 날짜를 반환하는 LAST_DAY(date)

select ename,
	TO_CHAR(hiredate, 'YYYY/MM/DD') 입사일,
	TO_CHAR(LAST_DAY(hiredate), 'YYYY/MM/DD') "마지막 날짜"
from emp;

===================결과======================

ENAME                입사일               마지막 날짜
-------------------- -------------------- --------------------
김사랑               2007/03/01           2007/03/31
한예슬               2007/04/02           2007/04/30
오지호               2005/02/10           2005/02/28
이병헌               2003/09/02           2003/09/30
신동협               2005/04/07           2005/04/30
장동건               2003/10/09           2003/10/31
이문세               2004/01/08           2004/01/31
감우성               2004/03/08           2004/03/31
안성기               1996/10/04           1996/10/31
이병헌               2005/04/07           2005/04/30
조향기               2007/03/01           2007/03/31

6. Null 값 치환 함수

NULL을 다른 값으로 변환하는 NVL(expr1, expr2)

수치 데이터를 사용한 NVL(comm, 0)

날짜 데이터를 사용한 NVL(hiredate, TO_DATE('2015/03/10', 'YYYY-MM-DD')

문자 데이터를 사용한 NVL(job, '매니저')

select ename, sal, comm, deptno
from emp
order by deptno;

======================결과값====================

ENAME                       SAL       COMM     DEPTNO
-------------------- ---------- ---------- ----------
이문세                      520                    10
이병헌                      500                    10
조인성                      250                    10
이병헌                      600                    20
안성기                     1000                    20
박중훈                      560                    20
김사랑                      300                    20
강혜정                      300                    20
오지호                      500        100         30
한예슬                      250         80         30
감우성                      500          0         30
==================================================

select ename, sal, deptno, NVL(comm, 0), sal*12+NVL(comm, 0)
	from emp
order by deptno;

======================결과값====================

ENAME                       SAL     DEPTNO NVL(COMM,0) SAL*12+NVL(COMM,0)
-------------------- ---------- ---------- ----------- ------------------
이문세                      520         10           0               6240
이병헌                      500         10           0               6000
조인성                      250         10           0               3000
이병헌                      600         20           0               7200
안성기                     1000         20           0              12000
박중훈                      560         20           0               6720
김사랑                      300         20           0               3600
강혜정                      300         20           0               3600
오지호                      500         30         100               6100
한예슬                      250         30          80               3080
감우성                      500         30           0               6000

2) NVL2(expr1, expr2, expr3)

expr1의 결과가 null이 아니면 expr2을 반환, expr1의 결과가 null이면 expr3을 반환

select ename, sal, comm,
NVL2(comm, sal*12+comm, sal*12)
from emp
order by deptno;

========================결과 값========================

ENAME                       SAL       COMM NVL2(COMM,SAL*12+COMM,SAL*12)
-------------------- ---------- ---------- -----------------------------
이문세                      520                                     6240
이병헌                      500                                     6000
조인성                      250                                     3000
이병헌                      600                                     7200
안성기                     1000                                    12000
박중훈                      560                                     6720
김사랑                      300                                     3600
강혜정                      300                                     3600
오지호                      500        100                          6100
한예슬                      250         80                          3080
감우성                      500          0                          6000

3) NULLIF(expr1, expr2)

두 표현식을 비교하여 동일한 경우에는 NULL을 반환 두 표현식을 비교하여 동일하지 않을 경우에는 첫 번째 표현식을 반환

select NULLIF('A', 'A'),
 	NULLIF('A', 'B')
from dual;

=========================결과=============

NU NU
-- --
   A

7. DECODE와 CASE

1) DECODE

선택을 위한 DECODE (일종의 switch case과 같은 기능)

/*
DECODE(표현식, 조건1, 결과1,
 조건2, 결과2,
 조건3, 결과3,
 기본결과,
)
*/

select ename, deptno,
DECODE( 
	deptno, 10, '경리부',
	deptno, 20, '인사과',
	deptno, 30, '영업부',
	deptno, 40, '전산부'
)
AS DNAME
from emp;


=========================================]


ENAME                    DEPTNO  DNAME
-------------------- ---------- --------------------
김사랑                       20    20


한예슬                       30    20

오지호                       30    20

2) 조건에 따라 서로 다른 처리가 가능한 CASE

/*
CASE WHEN 조건1 THEN 결과1
 WHEN 조건2 THEN 결과2
 WHEN 조건3 THEN 결과3
 ELSE 결과1
END
*/

select ename, deptno,
CASE 
	WHEN deptno=10 THEN '경리부'
 	WHEN deptno=20 THEN '인사과'
 	WHEN deptno=30 THEN '영업부'
 	WHEN deptno=40 THEN '전산부'
END AS DNAME
from emp;

====================================================

ENAME                    DEPTNO DNAME
-------------------- ---------- ------------------
김사랑                       20 인사과
한예슬                       30 영업부
오지호                       30 영업부
이병헌                       20 인사과
신동협                       30 영업부
장동건                       30 영업부
이문세                       10 경리부
감우성                       30 영업부
안성기                       20 인사과
이병헌                       10 경리부
조향기                       30 영업부

'Database' 카테고리의 다른 글

[Connection] 2개 이상의 Connection 객체를 관리하기 (0)	2021.05.06
[Error] JDBC 'LIKE' 사용 시 주의 사항 (0)	2021.04.27
<Database> SQL Sequence (0)	2021.04.12
<Database> SQL 집합 연산자 (0)	2021.04.12
<Database> JDBC 활용_3 (문제 풀이) (0)	2021.04.07

공유하기 링크

Comments

Related Articles more

최근에 올라온 글

최근에 달린 댓글

TAG more

Total

Today

Yesterday

Jun's TID

티스토리 뷰

<Database> 활용 가능한 Oracle 함수

'Database' 카테고리의 다른 글

티스토리툴바