단일행 함수
1. RTRIM
- 문자열의 마지막 문자부터 시작해서 지정된 문자와 일치하지 않는 문자가 나올 때까지 계속적으로 해당문자를 제거하는 함수이다.
RTRIM( 컬럼명 | 표현식, 'str')
- str은 문자열에서 삭제할 문자를 의미한다.
SELECT RTRIM('MILLER, 'R')
FROM dual;
2. TRIM
TRIM( LEADING 'str' FROM 컬럼명 | 표현식)
TRIM( TRAILING 'str' FROM 컬럼명 | 표현식)
TRIM( BOTH 'str' FROM 컬럼명 | 표현식)
- str은 삭제할 문자를 의미한다.
- LEADING : 왼쪽 삭제
- TRAILING : 오른쪽 삭제
- BOTH : 양쪽을 모두 삭제
SELECT TRIM ('0' FROM '00001234560000')
FROM dual;
- 키워드를 생략하는 경우에는 자동으로 BOTH로 적용되어 양쪽이 삭제된다.
3. ROUND
- 지정한 자리수 이하에서 반올림한 결과를 반환하는 함수이다.
ROUND (컬럼명 | 표현식, [n])
- n은 반올림하여 출력하기 위한 자리수를 의미하고 n이 양수이면 소수자리를 반올림하고 음수이면 정수 자리를 반올림 한다. 생략하면 기본 값이 0이기 때문에 소수점에서 반올림이 된다.
SELECT ROUND(456.789, 2)
FROM dual;
SELECT ROUND(456.789, -1)
FROM dual;
SELECT ROUND(456.789)
FROM dual;
4. TRUNC
- 지정한 자리수 이하에서 절삭한 결과를 반환하는 함수이다.
TRUNC( 컬럼명 | 표현식, [n])
- n은 절삭을 위한 자리수를 의미한다.
- n > 0 : 소수 자리를 절삭
- n < 0 : 정수 자리를 절삭
- n = 0 : 소수점에서 절삭
SELECT TRUNC(456.769, 2)
FROM dual;
SELECT TRUNC(456.769, -2)
FROM dual;
SELECT TRUNC(456.769)
FROM dual;
5. MOD
- 나누기 연산을 한 후에 나머지를 반환하는 함수이다.
MOD( 컬럼명 | 표현식, n)
- n은 나눌 값을 의미한다. 만약 n이 0이라면 값 자체를 반환한다.
SELECT MOD(10, 3), MOD(10, 0)
FROM dual;
SELECT empno, ename, sal
FROM emp
WHERE MOD(empno, 2) =1;
6. CEIL
- 소수점을 가진 실수값을 정수값으로 반환하는 함수로서, 주어진 숫자보다 크거나 같은 최소 정수값을 반환한다.
CEIL( 컬럼명 | 표현식 )
SELECT CEIL(10.6), CEIL(-10.6)
FROM dual;
7. FLOOR
- 소수점을 가진 실수 값을 반환하는 함수로서, 주어진 숫자보다 작거나 같은 최대 정수값을 반환한다.
FLOOR( 컬럼명 | 표현식 )
SELECT FLOOR(10.6), FLOOR(-10.6)
FROM dual;
8. SIGN
- 지정된 값이 양수인지 음수인지 또는 0인지 판단해주는 함수이다.
SIGN( 컬럼명 | 표현식 )
- 값 > 0 : 1
- 값 < 0 : -1
- 값 = 0 : 0
SELECT SIGN( 100 ), SIGN(-20), SIGN(0)
FROM dual;
오라클은 세기, 년, 월, 일, 시, 분, 초를 내부적으로 7byte 형식의 숫자로 관리한다.
따라서, 날짜 데이터의 산술연산이 가능하다.
연산 | 결과 | 설명 |
---|
날짜 + 숫자 | 날짜 | 날짜에 일수를 더하여 반환한다. |
날짜 - 숫자 | 날짜 | 날짜에 일수를 빼고 반환한다. |
날짜 - 날짜 | 숫자(일수) | 두 날짜의 차이(일수)를 반환한다. |
날짜 + 숫자/24 | 날짜 | 날짜에 시간을 더한다. |
9. SYSDATE
- 오라클이 설치된 시스템의 현재 날짜를 반환하는 함수이다.
SELECT SYSDATE
FROM dual;
SELECT SYSDATE 오늘, SYSDATE + 1 내일, SYSDATE - 1 어제
FROM dual;
SELECT ename, hiredate, TRUNC((SYSDATE - hiredate) / 365) "년"
FROM emp
ORDER BY 3 DESC;
10. MONTHS_BETWEEN
- 날짜와 날짜 사이의 개월 수를 반환하는 함수이다.
MONTHS_BETWEEN(date1, date2)
SELECT MONTHS_BETWEEN(SYSDATE, '00/01/01')
FROM dual;
- 실행결과에서 252는 월을 나타내고 소수점 자리 이하의 수는 월의 일부분을 나타낸다.
11. ADD_MONTHS
- 지정된 날짜에 특정 개월 수를 더하거나 뺀 날짜를 반환하는 함수이다.
ADD_MONTHS(date1, n)
- date1 : 지정된 날짜
- n : 더하거나 뺄 개월 수
SELECT ADD_MONTHS('20/01/07', 5), ADD_MONTHS('20/01/07', -5)
FROM dual;
SELECT sysdate 현재, ADD_MONTHS(sysdate, 1) 다음달,
ADD_MONTHS(sysdate, -1) 이전달,
FROM dual;
12. NEXT_DAY
- 지정된 날짜를 기준으로 돌아오는 가장 가까운 요일에 해당하는 날짜를 반환하는 함수이다.
NETX_DAY(date1, 'string'|n)
- date1은 지정된 날짜를 의미하고, 'string' 값은 돌아오는 요일을 의미한다.
- n 값은 문자 요일값 대신에 숫자 값으로 표현 가능함을 나타낸다.
SELECT ename, hiredate, NEXT_DAY(hiredate, '금'), NEXT_DAY(hiredate, 6)
FROM emp
ORDER BY 3 DESC;
- 지정된 요일은 '금' 또는 '금요일'처럼 한글로 지정해도 되고 숫자 6으로 지정해도 실행결과는 동일하다.
13. LAST_DAY
- 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수로서 윤년 몇 평년 날짜를 자동으로 계산한다.
LAST_DAY(date1)
SELECT ename, hiredate, LAST_DAY(hiredate)
FROM emp
ORDER BY 2 desc;
변환 함수
함수 | 설명 | 반환 값 |
---|
TO_NUMBER | 문자 데이터를 숫자 데이터로 변환한다. | 숫자 |
TO_DATE | 문자 데이터를 날짜 데이터로 변환한다. | 날짜 |
TO_CHAR | 숫자 데이터를 문자데이터로 변환하거나 날짜데이터를 문자 데이터로 변환한다. | 문자 |
SELECT empno, ename
FROM emp
WHERE empno = TO_NUMBER('7900');
SELECT empno, ename, hiredate
FROM emp
WHERE hiredate = TO_DATE('82/01/23');
- 형변환을 할 때 두 번째 인자로 형식을 지정할 수 있다.
SELECT TO_CHAR(sysdate, 'YYYY/MM/DD, (AM) DY HH24:MI:SS')
FROM dual;
SELECT TO_CHAR(sysdate, 'YYYY "년"')
FROM dual;
SELECT TO_CHAR(sysdate, 'YYYY "년" MM"월" DD"일"')
FROM dual;
SELECT empno, ename, TO_CHAR(hiredate, 'YYYY"년" MM"월" DD"일"')
FROM emp
WHERE deptno = 10;
SELECT empno, ename, hiredate, sal
FROM emp
WHERE TO_CHAR(hiredate, 'MM') = '09';
SELECT empno, ename, hiredate, sal
FROM emp
WHERE TO_CHAR(hiredate, 'MM') = '09'
ORDER BY 3;
SELECT ename, TO_CHAR(sal, 'L999,999') 원화, TO_CHAR(sal, '$999,999') 달러
FROM emp;
SELECT TO_NUMBER('123456') + 100
FROM dual;
SELECT TO_DATE(20170802181030, 'YYYYMMDDHH24MISS')
FROM dual;
SELECT SYSDATE, SYSDATE-TO_DATE('20170801', 'YYYYMMDD')
FROM dual;
SELECT TRUNC((SYSDATE-TO_DATE('2000', 'YYYY')) / 365) + 1 as 나이
FROM dual;
조건 함수
함수 | 설명 |
---|
DECODE | 조건이 반드시 일치하는 경우에 사용하는 함수 |
CASE | 조건이 반드시 일치하지 않아도 범위 및 비교가 가능한 경우에 사용하는 함수 |
1. DECODE
DECODE ( 컬럼, 비교값1, 결과값1,
비교값2, 결과값2,
...
비교값n, 결과갑수,
기본결과값)
SELECT empno, ename, sal, job,
DECODE( job, 'ANALYST' , sal * 1.1,
'CLERK', sal * 1.2,
'MANAGER', sal * 1.3,
'PRESIDENT', sal * 1.4, sal) "급여"
FROM emp;
SELECT empno, ename, sal,
DECODE(sal, 5000, sal + 1000,
3000, sal + 1000,
sal) bonus
FROM emp;
2. CASE
CASE 컬럼 WHEN 비교값1 THEN 결과값1
WHEN 비교값2 THEN 결과값2
ELSE 결과값n
END
SELECT empno, ename,
CASE WHEN sal >= 0 AND sal <= 1000 THEN 'E'
WHEN sal > 1000 AND sal <= 2000 THEN 'D'
WHEN sal > 2000 AND sal <= 3000 THEN 'C'
WHEN sal > 3000 AND sal <= 4000 THEN 'B'
WHEN sal > 4000 AND sal <= 5000 THEN 'A'
END "등급"
FROM emp;
SELECT empno, ename,
CASE WHEN sal >= 0 AND sal <= 1000 THEN 'E'
WHEN sal > 1000 AND sal <= 2000 THEN 'D'
WHEN sal > 2000 AND sal <= 3000 THEN 'C'
WHEN sal > 3000 AND sal <= 4000 THEN 'B'
WHEN sal > 4000 AND sal <= 5000 THEN 'A'
END "등급"
FROM emp;
SELECT empno, ename, sal,
CASE WHEN sal = 3000 AND sal = 5000 THEN sal + 1000
ELSE sal
END 보너스
FROM emp;