<Database> 활용 가능한 Oracle 함수
1. DUAL 테이블: 산술 연산 결과를 출력하기 위한 dummy 테이블이다.
select 24*60
from dual;
select *
from dual;
select SYSDATE
from dual;
2. 숫자 관련 함수
1) ABS(n): 절대값을 구하는 함수
SELECT ABS(-1234) FROM DUAL;
---> 12342) 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;
----> 05) MOD(n): 나머지를 구하는 함수
SELECT MOD(27, 2) FROM DUAL;
----> 16) 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;
----> acl5) 특정 문자의 위치값을 반환하는 INSTR(대상, 찾을문자, 시작위치, 몇번째발견)
마찬가지로 INSTRB는 한글을 3바이트로
select INSTR('Welcome to Oracle', 'o')
from dual;
----> 5
select INSTR('Welcome to oracle', 'o' , 6, 2)
from dual;
----> 126) 특정 기호로 채우는 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:072) 날짜형으로 변환하는 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;
----> 22943) 숫자형으로 변환하는 TO_NUMBER
select TO_NUMBER('20,000', '99,999')-TO_NUMBER('10,000', '99,999')
from dual;
----> 100005. 날짜 함수
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/142) 특정 조건을 기준으로 버리는 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/316. Null 값 치환 함수
1)
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 60002) 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 60003) NULLIF(expr1, expr2)
두 표현식을 비교하여 동일한 경우에는 NULL을 반환 두 표현식을 비교하여 동일하지 않을 경우에는 첫 번째 표현식을 반환
select NULLIF('A', 'A'),
NULLIF('A', 'B')
from dual;
=========================결과=============
NU NU
-- --
A7. 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 202) 조건에 따라 서로 다른 처리가 가능한 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 영업부