SELECT SYSDATE
FROM DUAL;
숫자함수
구분 |
설명 |
ABS |
절댓값을 구합니다. |
COS |
COSINE값을 반환합니다. |
EXP |
e(2.71828183...)의 n승을 반환합니다. |
FLOOR |
소수점 아래를 잘라냅니다.(버림) |
LOG |
LOG값을 반환합니다. |
POWER |
POWER(m,n) m의 n승을 반환합니다. |
SIGN |
SIGN(n) n<0 이면 -1, n=0이면 0, n>0 이면 1을 반환합니다. |
SIN |
SNE값을 반환합니다. |
TAN |
TANGENT값을 반환합니다. |
ROUND |
특정 자릿수에서 반올림합니다. |
TRUNC |
특정 자릿수에서 잘라냅니다(버림). |
MOD |
입력받은 수를 나눈 나머지 값을 반환합니다. |
-10에 대한 절댓값을 구해 보겠습니다.
SELECT -10,ABS(-10)
FROM DUAL;
-20에 대한 절닷값 : 20
SELECT ABS(-20)
FROM DUAL;
소수점 아래를 버리는 FLOOR 함수
34.5678을 FLOOR 함수에 적용하면 34가 구해집니다.
SELECT 34.5678, FLOOR(34.5678)
FROM DUAL;
SELECT FLOOR(34.5678)
FROM DUAL;
34
특정 자릿수에서 반올림하는 ROUND 함수
ROUND(대상, 자릿수)
SELECT 34.5678, ROUND(34.5678)
FROM DUAL;
35
SELECT 34.5678, ROUND(34.5678,2)
FROM DUAL;
34.57
자릿수에 음수를 지정할 수 있는데, 이 경우 소수점 이하가 아니라 반대쪽인 일 단위, 십 단위,
백 단위순으로 거슬러 올라가게 됩니다. 또한 인자값이 양수일 때와는 달리 해당 자리에서 반올림이 일어나게 됩니다.
SELECT 34.5678, ROUND(34.5678, -1)
FROM DUAL;
30
특정 자릿수에서 잘라내는 TRUNC 함수
TRUNC 함수는 지정한 자릿수 이하를 버린 결과를 구하는 함수입니다.
TRUNC 함수의 두 번째 인자값이 2이면 소수점 이하 세 번째 자리에서 버림 연산을 하여 소수점 이하 두 번째 자리까지 표시합니다. 두 번째 인자값이 0인 경우에는 소수점 자리에서 버림 연산을 하고, -1인 경우는 일의 자리에서 버림 연산을 합니다. 또한 두 번째 인자가 주어지지 않은 경우 0으로 간주하여 소수점 자리에서 버림 연산을 수행합니다.
SELECT TRUNC(34.5678,2), TRUNC(34.5678,-1), TRUNC(34.5678)
FROM DUAL;
34.56, 30, 34
나머지 구하는 MOD 함수
MOD 함수는 나누기 연산을 한 후에 구한 몫이 아닌 나머지를 결과로 되돌려주는 함수입니다.
SELECT MOD(27,2) , MOD(27,5), MOD(27,7)
FROM DUAL;
1, 2, 6
EMPNO가 홀수인 사람들을 검색
홀수는 2로 나누어 나머지가 1인 수를 의미합니다.
SELECT * FROM EMP
WHERE MOD(EMPNO,2)=1;
문자 치리 함수
구분 |
설명 |
LOWER |
소문자로 변환합니다. |
UPPER |
대문자로 변환합니다. |
INITCAP |
첫 글자만 대문자로 변환하고 나머지 글자는 소문자로 변환합니다. |
CONCAT |
문자의 값을 연결합니다. |
SUBSTR |
문자를 잘라 추출(한글 1Byte)합니다. |
SUBSTRB |
문자를 잘라 추출(한글 2Byte)합니다. |
LENGTH |
문자의 길이를 반환(한글 1Byte)합니다. |
LENGTHB |
문자의 길이를 반환(한글 2 Byte)합니다. |
INSTR |
특정 문자의 위치값을 반환(한글 1Byte)합니다. |
INSTRB |
특정 문자의 위치값을 반환(한글 2Byte)합니다. |
LPAD, RPAD |
입력받은 문자열과 기호를 정렬하여 특정 길이의 문자열로 반환합니다. |
TRIM |
잘라내고 남은 문자를 표시합니다. |
CONVERT |
CHAR SET 을 변환합니다. |
CHR |
ASCII 코드값으로 변환합니다. |
ASCII |
ASCII 코드값을 문자로 변환합니다. |
REPLACE |
문자열에서 특정 문자를 변경합니다. |
UPPER 함수는 입력한 문자값을 대문자로 변환하는 함수입니다.
SELECT 'Welcome to Oracle', UPPER('Welcome to Oracle')
FROM DUAL;
소문자로 변환하는 LOWER 함수
SELECT LOWER('Welcome to Oracle')
FROM DUAL;
INITCAP 함수는 문자열의 이니셜만 대문자로 변경합니다.
SELECT 'WELCOME TO ORACLE', INITCAP('WELCOME TO ORACLE')
FROM DUAL;
답 : Welcome To Oracle;
컬럼의 값이 대문자라 검색이 않됨
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE JOB='manager';
검색이 가능하게 컬럼을 모두 소문자로 변경 후 검색하면 검색이 된다.
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE LOWER(JOB) = 'manager';
LENGTH 함수는 컬럼에 저장된 데이터값이 몇 개의 문자로 구성되었는지 길이를 알려주는 함수이다.
영문과 한글의 길이를 구한다.
SELECT LENGTH('Oracle'), LENGTH('오라클')
FROM DUAL;
6,3
LENGTH 함수는 바이트의 수를 알려주는 함수입니다.
메모리를 차지하는 바이트 수를 구합니다.
SELECT LENGTH('Oracle'), LENGTHB('오라클')
FROM DUAL;
SUBSTR함수와 SUBSTRB 함수는 대상 문자열이나 컬럼의 자료에서 시작 위치부터 선택 개수만큼의 문자를 추출합니다. SUBSTRB 함수도 같은 형식이지만 명시된 개수만큼의 문자가 아닌 바이트 수를 잘라 낸다.
[형식]
SUBSTR(대상, 시작 위치, 추출할 개수)
다음은 4부터 시작해서 문자 3개를 추출합니다.
SELECT SUBSTR('Welcome to Oracle',4,3)
FROM DUAL;
com
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
17 |
W |
e |
l |
c |
o |
m |
e |
|
t |
o |
|
O |
r |
a |
c |
l |
e |
시작 위치의 인자값을 음수값으로 줄 수 있는데, 이때는 문자열의 앞쪽이 아닌 뒤쪽에서부터
세어서 시작 위치를 잡습니다.
SELECT SUBSTR('Welcome to Oracle', -4, 3)
FROM DUAL;
acl
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
17 |
W |
e |
l |
c |
o |
m |
e |
|
t |
o |
|
O |
r |
a |
c |
l |
e |
EMP 테이블 사원들의 연도 , 달만 출력
HIREDATE
80/12/17
SELECT SUBSTR(HIREDATE,1,2) AS 년도, SUBSTR(HIREDATE,4,2) AS 달
FROM EMP;
SELECT SUBSTR(HIREDATE,1,2) 년도, SUBSTR(HIREDATE,4,2) 달
FROM EMP;
9월에 입사한 사원을 출력해 보겠습니다.
SELECT *
FROM EMP
WHERE SUBSTR(HIREDATE, 4, 2) = '09';
EMP 테이블에서 87년도에 입사한 직원을 알아내기 위한 쿼리문
SELECT * FROM EMP
WHERE HIREDATE >= '1978/01/01' AND HIREDATE <= '1978/12/31';
SELECT * FROM EMP
WHERE HIREDATE BETWEEN '1978/01/01' AND '1978/12/31';
substr 함수를 이용하여 hiredate 컬럼에서 첫 글자 2개 추출하여 그 값이 87
SELECT * FROM EMP
WHERE SUBSTR(HIREDATE,1,2) = '87';
emp 테이블에서 이름이 E로 끝나는 사원 LIKE 검색
SELECT * FROM EMP
WHERE ENAME LIKE '%E';
SUBSTR 함수를 이용해서 ENAME 컬럼의 마지막 문자 한 개만 추출해서 이름이 E로 끝나는 사원 검색
SELECT * FROM EMP
WHERE SUBSTR(ENAME,-1,1) = 'E';
SUBSTRB 함수는 문자열을 추출하기 위해 문자를
셀 때 문자의 개수가 아닌 문자가 메모리에 저장되는 바이트 수를 셉니다.
영문 한 글자는 메모리에 1바이트로 저장되기 때문에
SUBSTR 함수와 SUBSTRB 함수 중 어떤 함수를 사용해도 결과가 동일합니다.
SELECT SUBSTR('Welcome To Oracle',3,4)
SUBSTRB('Welcome To Oracle',3,4)
FROM dual;
Icom
SUBSTRB 바이트로 자른다.
SELECT
SUBSTR('웰컴투오라클',3,4),
SUBSTRB('웰컴투오라클',3,4)
FROM DUAL;
투오라클, 컴투
특정 문자의 위치를 구하는 INSTR 함수
INSTR 함수는 대상 문자열이나 컬럼에서 특정 문자가 나타나는 위치를 알려 줍니다.
첫 번째 발견된 O의 위치 반환
SELECT INSTR('WELCOME TO ORACLE', 'O')
FROM DUAL;
5
형식
INSTR( 대상 , 찾을 글자, 시작 위치, 몇 번째 발견 )
시작위치와 몇 번째 발견을 생략하면 모두 1로 간주되므로 시작 위치가 1이고,
첫 번째 발견된 위치를 반환합니다.
SELECT INSTR('WELCOME TO ORACLE', 'O' ,6, 2)
FROM DUAL;
12
대상 문자열이 6번째부터 두번째로 발견되는 O의 위치를 반환합니다.
바이트 수를 기준으로 문자의 위치를 구하는 INSTRB 함수
INSTRB 함수 역시 SUBSTRB 함수와 마찬가지로 문자의 위치를 알아내기 위해 바이트를 기준으로 합니다.
INSTR 한글 한 글자를 1바이트로 봅니다.
INSTRB 함수는 한글 한 글자를 2바이트로 봅니다.
SELECT INSTR('데이터베이스','이',3,1), INSTRB('데이터베이스','이',3,1)
FROM DUAL;
5, 3
이름의 세 번째 자리가 R로 끝나는 사원을 검색하기 위해서 와일드카드 _와 LIKE 연산자를 사용하여 다음과 같이 표현할 수 있습니다.
SELECT EMPNO,ENAME
FROM EMP
WHERE ENAME LIKE '__R%';
결과
WARD,MARTIN, ...
INSTR 함수와 SUBSTR 함수 사용하여 이름의 세 번째 자리가 R로 끝나는 사원을 검색하는 쿼리문을 작성하시오
SELECT *
FROM EMP
WHERE INSTR(ENAME, 'R', 3, 1) = 3;
SELECT *
FROM EMP
WHERE SUBSTR(ENAME,3,1) = 'R';
특정 기호로 채우는 LPAD/RPAD 함수
LPAD( LEFT PADDING) 함수는 컬럼이나 대상 문자열을 명시된 자릿수에서 오른쪽에 나타내고, 남은 왼쪽 자리를 특정 기호로 채웁니다.
PRAD(RIGHT PADDING) 함수는 반대로 컬럼이나 대상 문자열을 명시된 자릿수에서 왼쪽으로 나타내고, 남은 오른쪽 자리를 특정 기호로 채웁니다.
SELECT LPAD('Oracle',20,'#')
FROM DUAL;
ex>
###Oracle
SELECT RPAD('Oracle', 20 , '#')
FROM DUAL;
공백 문자를 삭제하는 LTRIM와 RTRIM 함수
LTRIM 함수는 문자열의 왼쪽(앞)의 공백 문자들을 삭제하고 RTRIM 함수 오른쪽(뒤) 공백 문자들을 삭제합니다.
SELECT LTRIM(' Oracle ')
FROM DUAL;
SELECT RTRIM('ORACLE ')
FROM DUAL;
특정 문자를 잘라내는 TRIM 함수
TRIM 함수는 컬럼이나 대상 문자열에서 특정 문자가 첫 번째 글자이거나 마지막 글자이면 잘라내고 남은 문자열을 반환합니다.
select trim('a' from 'aaaaOracleaaaa')
from dual;
Oracle
다음과 같이 기술하면 공백 문자를 잘라냅니다.
select trim(' Oracle ')
from dual;
날짜 함수
날짜 함수는 DATE(날짜)형에 사용하는 함수이며, 결과값으로 날짜 또는 기간을 얻습니다.
기간은 주로 일 단위로 계산되지만, 월 단위로 계산되는 경우도 있습니다.
구분 |
설명 |
SYSDATE |
시스템 저장된 현재 날짜를 반환합니다. |
MONTHS_BETWEEN |
두 날짜 사이가 몇 개월인지 반환합니다. |
ADD_MONTHS |
특정 날짜에 개월 수를 더합니다. |
NEXT_DAY |
특정 날짜에서 최초로 도래하는 인자로 받은 요일의 날짜를 반환합니다. |
LAST_DAY |
해당 달의 마지막 날짜를 반환합니다. |
ROUND |
인자로 받은 날짜를 특정 기준으로 반올림합니다. |
TRUNC |
인자로 받은 날짜를 특정 기준으로 버립니다. |
시스템에서 현재 날짜를 얻어 와서 출력
sysdate 함수는 시스템에 저장된 현재 날짜를 반환하는 함수입니다.
select sysdate
from dual;
날짜 연산
SYSDATE+1 |
내일 날짜를 구합니다. |
SYSDATE-1 |
어제 날짜를 구합니다. |
SELECT SYSDATE-1 어제, SYSDATE 오늘, SYSDATE+1 내일
FROM DUAL;
SELECT SYSDATE-1 어제, SYSDATE 오늘, SYSDATE+1 AS 내일
FROM DUAL;
근무일수 구하기
근무 일수란 입사한 날로부터 오늘이 며칠이 흘렀는지 알아내는 것입니다.
SELECT SYSDATE-HIREDATE
FROM EMP;
ex>
13405.2255......
특정 기준으로 반올림하는 ROUND 함수
round 함수는 숫자를 반올림하는 함수라고 학습하였습니다. 하지만 이 함수에 포맷 모델을 지정하면 숫자 이외의 날짜도 반올림 할 수 있습니다.
[형식]
ROUND( date, format )
포맷 모델 |
단위 |
CC, SCC |
4자리 연도 끝의 두 글자를 기준으로 반올림 |
SYYY, YYYY, YEAR, SYEAR, YYY,YY, Y |
년(7월 1일부터 반올림) |
DDD, D, J |
일을 기준 |
HH, HH12, HH24 |
시를 기준 |
Q |
한 분기의 두 번째 달의 16일을 기준으로 반올림 |
MONTH, MON,MM, RM |
월(16일을 기준으로 반올림) |
DAY,DY,D |
한 주가 시작되는 날짜 |
MI |
분을 기준 |
round 함수의 포맷 모델로 month를 지정하였으므로 특정 날짜(date)를 달(month)을 기준으로 반올림한 날짜를 구합니다.
select
hiredate,
round(hiredate,'month')
from emp;
80/12/17 --> 81/01/01
특정 기준으로 버리는 TRUNC 함수
TRUNC 함수 역시 숫자를 잘라내는 것뿐만 아니라 날짜를 잘라낼 수 있습니다.
ROUND 함수와 마찬가지로 포맷 형식을 주어 다양한 기준으로 날짜를 잘라낼 수 있습니다.
형식
TRUNC(date, format)
특정 날짜를 달을 기준으로 잘라낸 날짜를 구하기
SELECT
HIREDATE,
TRUNC( HIREDATE, 'MONTH')
FROM EMP;
80/12/17 --> 81/01/01
두 날짜 사이의 날수를 구하는 MONTHS_BETWEEN 함수
[형식]
MONTHS_BETWEEN(date1, date2)
직원들이 근무한 개월 수를 구하기
SELECT
ENAME,
SYSDATE,
HIREDATE,
MONTHS_BETWEEN(SYSDATE,HIREDATE)
FROM EMP;
17/08/30 81/05/01
435.9439......
소수점 이하까지 구해집니다.
소수점 이하 자리는 아직 입사한지 한 달이 안된 날수를 나타냅니다.
이를 TRUNC 함수를 사용하면 소수점 이하를 잘라낼 수 있습니다.
SELECT ENAME, HIREDATE,
TRUNC( MONTHS_BETWEEN(SYSDATE, HIREDATE) )
FROM EMP;
80/12/17
17/09/01
440
개월 수를 더하는 ADD_MONTHS 함수
ADD_MONTHS 함수는 특정 개월 수를 더한 날짜를 구하는 함수
[형식]
ADD_MONTHS(date, number)
다음은 입사 날짜에서 6개월을 추가하는 예제입니다.
SELECT *
FROM EMP;
SELECT
ENAME,
HIREDATE,
ADD_MONTHS(HIREDATE,6) AS ADD_MONTH6
FROM EMP;
해당 요일의 가장 가까운 날짜를 반환하는 NEXT_DAY 함수
NEXT_DAY 함수는 해당 요일을 기준으로 가장 가까운 다음 요일에 해당되는 날짜를 반환하는 함수입니다.
[형식]
NEXT_DAY(day, 요일)
기본 형식에서 두 번째 인자값은 한글인 경우 일, 월, 화, 수 등으로 나타낼 수 있고
영문판인 경우에는 SUN, MON, TUE등으로 나타낼 수 있습니다.
문자 대신 요일을 숫자로 표현할 수 있습니다.
일요일7, 월요일1, 화요일2 ...... 와 같이 1~7까지만 입력할 수 있습니다.
다음은 오늘을 기준으로 가장 가까운 다음 수요일은 언제인지 알아보는 예제입니다.
SELECT
SYSDATE, NEXT_DAY(SYSDATE, '수요일')
FROM DUAL;
Tip
NLS_LANG
오늘 날짜인 2008년 2월 22일 이후의 첫 번째 일요일이 되는 날짜를 반환하기 위해 요일을 영어(WEDNEDAY)로 표현해 보겠습니다. 다음 예를 통해 영어로 표현하면 에러가 발생한다는 것을 알 수 있습니다.
SELECT SYSDATE, NEXT_DAYI(SYSDATE, 'WEDESDAY')
FROM DUAL;
이러한 오류는 오라클 설치할 때 설정되는 윈도우 운영체제가 사용 중인 NLS_LANG라는 변수에 저장된 값에 의해서 결정되기 때문입니다.
오라클 설치 시 한글 윈도우가 설치되어 있다면 NLS_LANG도 한국어로 세팅되기 때문에 요일을 영어로 지정하면 오류가 발생하는 것입니다. 만일 요일을 영어로 지정하여 원하는 결과를 얻고자 한다면 NLS_LANG라는 변수를 AMERICAN로 변경해야 합니다.
ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'WEDESDAY')
FROM DUAL;
만일 NLS_LANG라는 변수를 AMERICAN로 변경하면 반대로 요일을 한글로 표현했을 경우 인식하지 못합니다.
ALTER SESSION SET NLS_LANGUAGE=KOREAN;
SELECT SYSDATE, NEXT_DAY(SYSDATE, '수요일')
FROM DUAL;
SELECT
SYSDATE,
NEXT_DAY(SYSDATE,'수요일')
FROM DUAL;
ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
SELECT SYSDATE, NEXT_DAY(SYSDATE,'WEDESDAY')
FROM DUAL;
ALTER SESSION SET NLS_LANGUAGE=KOREAN;
SELECT
SYSDATE,
NEXT_DAY(SYSDATE,'일요일')
FROM DUAL;
해당 달의 마지막 날짜를 반환하는 LAST_DAY 함수
LAST_DAY함수는 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수입니다.
SELECT
HIREDATE,
LAST_DAY(HIREDATE)
FROM EMP;
ex>
1980-12-17 1980-12-31
형 변환 함수
오라클을 사용하다 보면 숫자, 문자, 날짜의 데이터형을 다른 데이터형으로 변환해야 하는 경우가 생깁니다. 이럴 때 사용하는 함수가 형 변환 함수입니다. 자료형을 변환하고자 할 때 사용하는 형 변환 함수로는 TO_NUMBER, TO_CHAR, TO_DATE가 있습니다.
구분 |
설명 |
TO_CHAR |
날짜형 혹은 숫자형을 문자형으로 변환합니다. |
TO_DATE |
문자형을 날짜형으로 변환합니다. |
TO_NUMBER |
문자형을 숫자형으로 변환합니다. |
날짜형을 문자형으로 변환하기
DATE 형태의 테이터를 지정한 양식에 의해 VARCHAR2형의 문자로 변환합니다.
[형식]
TO_CHAR(날짜 데이터, '출력 형식')
데이터형으로 변환할 때 출력 형식을 지정할 수 있습니다.
다음은 날짜 출력 형식의 종류를 나열한 표입니다.
종류 |
의미 |
YYYY |
년도 표현(4자리) |
YY |
년도 표현(2자리) |
MM |
월을 숫자로 표현 |
MON |
월을 숫자로 표현 |
DAY |
요일 표현 |
DY |
요일을 약어로 표현 |
현재 날짜를 기본 형식과 다른 형태로 출력해 보겠습니다.
SELECT
SYSDATE,
TO_CHAR(SYSDATE, 'YYYY-MM-DD')
FROM DUAL;
ex>
2017-09-05
사원들의 입사일을 출력하되 요일까지 함께 출력해 보겠습니다.
SELECT
HIREDATE,
TO_CHAR( HIREDATE, 'YYYY/MM/DD DAY')
FROM EMP;
1980/12/17 수요일
년도를 2자리(YY)로 출력하고 월은 문자(MON)로 표시하고, 요일을 약어(DY)로 표시한 예입니다.
SELECT
HIREDATE,
TO_CHAR( HIREDATE, 'YY/MON/DD DY')
FROM EMP;
다음은 시간 출력 형식의 종류를 나열한 표입니다.
종류 |
의미 |
AM 또는 PM |
오전(AM), 오후(PM) 시각 표시 |
AM 또는 P.M |
오전(A.M), 오후(P.M) 시각 표시 |
HH24 |
24시간으로 표현(0~23) |
MI |
분 표현 |
SS |
초 표현 |
다음은 현재 날짜와 시간을 출력하는 예제
SELECT
TO_CHAR(SYSDATE, 'YYYY/MM/DD , HH24 : MI : SS')
FROM DUAL;
숫자형을 문자형으로 변환하기
1230000이라는 숫자를 문자 형태로 출력하는 예입니다.
SELECT
TO_CHAR(1230000)
FROM DUAL;
다음 표는 숫자 출력 형식을 나열한 표입니다
구분 |
설명 |
0 |
자릿수를 나타내며 자릿수가 맞지 않을 경우 0으로 채웁니다. |
9 |
자릿수를 나타내며 자릿수가 맞지 않아도 채우지 않습니다. |
L |
각 지역별 통화 기호를 앞에 표시합니다. |
. |
소수점 |
, |
천 단위 자리 구분 |
만일 각 지역별 통화 기호를 앞에 붙이고 천 단위마다 콤마를 붙여서 출력 예 : ₩1,230,00
하려면 다음과 같이 표현해야 합니다.
SELECT
FIRST_NAME,
TO_CHAR(SALARY, 'L999,999')
FROM EMPLOYEES;
₩24,000
₩7,900
9는 자릿수를 나타내며 자릿수가 맞지 않으면 채우지 않습니다. 하지만 0은 자릿수를 나타내며 자릿수가 맞지 않을 경우 0으로 채웁니다.
SELECT
TO_CHAR(123456,'000000000'),
TO_CHAR(123456,'999,999,999')
FROM DUAL;
000123456 123,456
날짜형으로 변환하는 TO_DATE 함수
TO_DATE 함수는 문자열을 날짜형으로 변환합니다.
날짜형으로 변환하기에 앞서 날짜형에 대해서 살펴보겠습니다. 날짜형은 세기, 년도, 월, 일, 시간, 분, 초와 같이 날짜와 시간에 대한 정보를 저장합니다. 오라클에서 기본 날짜형식은
'YY/MM/DD'으로 년/월/일, 예를 들면 '06/03/08'식으로 나타냅니다. 만일 년도를 4자리로 출력하려면 'YYYY/MM/DD' 형식으로 지정합니다.
[형식]
TO_DATE('문자', 'format');
SELECT
FIRST_NAME,
HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE = TO_DATE(19870617,'YYYYMMDD');
다음은 올해 며칠이 지났는지 현재 날짜에서 2008/01/01을 뺀 결과를 출력하는 예제입니다.
SELECT
SYSDATE-TO_DATE('2017/01/01','YYYY/MM/DD')
FROM DUAL;
TRUNC 소수점 버리기
SELECT
TRUNC(SYSDATE - TO_DATE('2017/01/01','YYYY/MM/DD'))
FROM DUAL;
SYSDATE = 20170908
숫자형으로 변환하는 TO_NUMBER 함수
TO_NUMBER 함수는 특정 데이터를 숫자형으로 변환하는 함수입니다.
'10,000'은 문자형이기 때문에 산술 연산(-)을 수행하지 못하고 오류가 발생합니다.
산술 연산을 하려면 문자형을 숫자형으로 변환한 후에 실행해야 합니다.
SELECT
TO_NUMBER('20,000', '99,999')
- TO_NUMBER('10,000','99,999')
FROM DUAL;
NULL을 다른 값으로 변환하는 NVL함수
NVL 함수는 NULL을 0 또는 다른 값으로 변환하기 위해서 사용하는 함수입니다.
연봉 계산을 위해 사원 테이블에서 급여와 커미션 컬럼을 살펴보면, 영업 사원 외의 사원들의 커미션은 NULL로 지정되어 있습니다.
NULL값을 갖는 컬럼은 산술 연산에 사용하면 결과가 NULL값으로 구해집니다.
때문에 커미션이 NULL인 로우는 연봉 역시 NULL로 출력됩니다. 그러므로 연봉을 올바르게 계산하기 위해서는 커미션이 NULL인 경우 0으로 변경해야 합니다.
SELECT
LAST_NAME,
SALARY,
COMMISSION_PCT,
SALARY*12+COMMISSION_PCT,
NVL(COMMISSION_PCT,0),
SALARY*12+NVL(COMMISSION_PCT,0)
FROM EMPLOYEES
ORDER BY JOB_ID;
모든 사원에게는 상관이 있습니다. 하지만 테이블에 유일하게 상관이 없는 로우가 있는데 그 사원의 컬럼이 NULL입니다. 상관이 없는 사원만 출력하되 MGR 컬럼값 NULL 대신 CEO로 출력해 보겠습니다.
SELECT
EMPLOYEE_ID,
FIRST_NAME,
NVL(TO_CHAR(MANAGER_ID,'9999'), 'CEO') AS MANAGER
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL;
선택을 위한 DECODE 함수
DECODE 함수는 프로그램 언어에서 가장 많이 사용되는 switch case문과 같이 기능을 합니다. 즉, 여러 가지 경우에 선택할 수 있는 기능을 합니다.
DECODE 함수의 기본 형식
[형식]
DECODE (표현식, 조건1,결과1,
조건2,결과2
조건3,결과3
기본결과 n
)
다음은 부서 번호에 대해서 부서 이름을 지정하는 예입니다.
deptno
10
ACCOUNTING
20
RESEARCH
30
SALES
40
OPERATION
다음은 DECODE 함수를 사용하여 사원의 부서 번호로 부서의 이름을 설정하는 예입니다.
SELECT
DEPARTMENT_ID,
DECODE(
DEPARTMENT_ID,
10, 'A',
20, 'B',
'DEFAULT'
) AS GRADE
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID ASC;
직급에 따라 급여를 인상해 보겠습니다.
SELECT
EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
SALARY,
DECODE(
JOB_ID,
'ST_CLERK', SALARY*1.05, /* 5% 인상 */
'PU_CLERK', SALARY*1.10, /* 10% 인상 */
'IP_PROG', SALARY*1.15, /* 15% 인상 */
'SA_REP', SALARY*1.20, /* 20% 인상 */
SALARY*1.01 /* 1% 인상 */
) AS UPSAL
FROM EMPLOYEES
ORDER BY SALARY ASC;
조건에 따라 서로 다른 처리가 가능한 CASE 함수
CASE 함수 역시 여러 가지 경우에서 하나를 선택하는 함수입니다.
CASE 함수는 프로그램 언어의 'if else'와 유사한 구조를 갖습니다.
CASE 함수의 기본 형식
[형식]
CASE WHEN 조건1 THEN 결과1
CASE WHEN 조건2 THEN 결과2
CASE WHEN 조건3 THEN 결과3
ELSE 결과n
END
부서 번호에 해당되는 부서명을 구하는 예제 CASE 함수를 사용하여 작성해 보겠습니다.
/* 부서 번호에 해당되는 부서명을 구한다. */
SELECT
ENAME,DEPTNO,
CASE WHEN DEPTNO=10 THEN 'ACCOUNTING'
WHEN DEPTNO=20 THEN 'RESEARCH'
ELSE 'OPERATIONS'
END AS DNAME
FROM EMP;
그룹 함수
그룹 함수는 하나 이상의 행을 그룹으로 묶어 연산하여 총합, 평균 등을 하나의 결과로 나타냅니다.
사원의 급여를 출력하되 단일행 함수 ROUND로 천 단위에서 반올림해 보겠습니다.
SELECT
DEPTNO, SAL,
ROUND(SAL,3)
FROM EMP;
단일행 함수는 각 행에 대해서 함수의 결과가 구해지기 때문에 결과가 여러 개의 로우로 구해집니다.
이번에는 그룹 함수를 이용해서 사원의 총 급여를 구해 보겠습니다.
SELECT
SUM(SAL)
FROM EMP;
그룹 함수의 종류
구분 |
설명 |
SUM |
그룹의 누적 합계를 반환합니다. |
AVG |
그룹의 평균을 반환합니다. |
COUNT |
그룹의 총 개수를 반환합니다. |
MAX |
그룹의 최대값을 반환합니다. |
MIN |
그룹의 최소값을 반환합니다. |
STDDEV |
그룹의 표준편차를 반환합니다. |
VARIANCE |
그룹의 분산을 반환합니다. |
합계 구하는 SUM 함수
SUM 함수는 해당 컬럼값들에 대한 총합을 구하는 함수입니다.
SELECT
SUM(SAL)
FROM EMP;
TIP 그룹 함수와 NULL
사원 테이블을 살펴보면 커미션 컬럼에 NULL값이 저장된 사원이 존재합니다.
NULL은 블랙홀이므로 NULL을 저장한 컬럼과 연산한 결과도 NULL입니다.
그러나 그룹 함수를 사용하면 커미션의 총합을 구해도 NULL값이 출력되지 않습니다.
그룹 함수는 다른 연산자와는 달리 컬럼값이 NULL인 것을 제외하고 계산하기 때문입니다.
그룹 함수는 NULL값을 제외한다는 점을 꼭 기억합시다.
평균 구하는 AVG 함수
AVG 함수는 해당 컬럼값에 대한 평균을 구하는 함수입니다.
이때 해당 컬럼값이 NULL인 것을 제외하고 계산합니다.
SELECT AVG(SAL)
FROM EMP;
최대값 구하는 MAX, 최소값 구하는 MIN 함수
지정한 컬럼값 중에서 최대값을 구하는 함수는 MAX이고, 최소값을 구하는 함수는 MIN입니다.
다음은 가장 높은 급여와 가장 낮은 급여를 구하는 예제입니다.
SELECT MAX(SAL), MIN(SAL)
FROM EMP;
그룹 함수와 단순 컬럼
SELECT문에 그룹 함수를 사용하는 경우, 그룹 함수를 적용하지 않은 단순 컬럼은 올 수 없습니다.
SELECT
ENAME,
MAX(SAL)
FROM EMP;
에러가 발생하는 이유는 위의 그림처럼 그룹 함수의 결과값은 하나인데 그룹 함수를 적용하지 않은 단순 컬럼의 로우의 개수는 14개이므로, 산출되는 로우의 수가 달라 둘을 매치시킬 수 없기 때문입니다.
최대 급여를 받는 사원의 이름을 출력하려면 서브 쿼리문을 배워야 합니다.
로우 개수 구하기 COUNT 함수
COUNT 함수는 테이블에서 조건을 만족하는 행의 개수를 반환하는 함수입니다.
COUNT 함수에 특정 컬럼을 기술하면 해당 컬럼값을 갖고 있는 로우의 개수를 계산하여 되돌립니다.
다음은 사원 테이블의 사원들 중에서 커미션을 받는 사원의 수를 구하는 예제입니다.
SELECT
COUNT(COMM)
FROM EMP;
COUNT 함수는 NULL값에 대한 개수를 세지 않습니다.
COUNT(*) 테이블의 전체 로우(행) 개수를 출력
SELECT COUNT(*), COUNT(COMM)
FROM EMP;
사원 테이블에서 사원들의 직업의 개수를 카운트해 보겠습니다.
SELECT COUNT(JOB)
FROM EMP;
SELECT
JOB
FROM EMP
ORDER BY JOB ASC;
직업의 종류가 몇 개인지 즉, 중복되지 않은 직업의 개수를 카운트 해보겠습니다.
이때 중복 행 제거 키워드 DISTINCT를 써서 다음과 같이 질의합니다.
SELECT COUNT(DISTINCT JOB) 업무수
FROM EMP;
SELECT COUNT(DISTINCT JOB) AS 업무
FROM EMP;
문제
1. 가장 최근에 입사한 사원의 입사일과 입사한지 가장 오래된 사원의 입사일을 출력하는 쿼리문을 작성하세요.
SELECT
MIN(HIREDATE) AS "최근에 입사한 사원",
MAX(HIREDATE) AS "가장 오래된 사원"
FROM EMP;
2. 10번 부서 소속의 사원 중에서 커미션을 받는 사원의 수를 구해 보십시오.
SELECT
COUNT(COMM) AS "사원수"
FROM EMP
WHERE DEPTNO = 10;
GROUP BY절
어떤 컬럼 값을 기준으로 그룹함수를 적용해 줄지 기술해 주어야 할 경우에는
SELECT 문에 GROUP BY절을 추가하되 GROUP BY절 뒤에 해당 컬럼을 기술합니다.
그룹 함수는 테이블에 아무리 행이 많아도 단 한 개의 결과값만을 산출합니다.
그러나 직원 중 가장 높은 급여값을 구하는 것이 아닌, 부서별 가장 높은 급여값을 구하고 싶을 때가 있습니다. 즉, 최대값이나, 최소값, 합계, 평균 등을 어떤 컬럼을 기준으로 보고자 하는 경우입니다.
이때는 그룹 함수를 쓰되 어떤 컬럼값을 기준으로 그룹 함수를 적용할지 기술해야 합니다.
GROUP BY절 뒤에 해당 컬럼을 기술하면 됩니다.
[형식]
SELECT 컬럼명, 그룹함수
FROM 테이블명
WHERE 조건(연산자)
GROUP BY 컬럼명;
GROUP BY절 다음에는 컬럼의 별칭은 사용할 수 없고, 반드시 컬럼명을 기술해야 한다는 점입니다.
사원 테이블의 부서 번호로 그룹 지어 보겠습니다.
SELECT
DEPTNO
FROM EMP
GROUP BY DEPTNO;
사원 테이블에서 부서별로 평균 급여를 구하려면 우선 전체 사원을 소속 부서별로 그룹 지어 놓어야 합니다. 다음은 소속 부서별 평균 급여를 구하는 예제입니다.
SELECT
DEPTNO,
ROUND(AVG(SAL))
FROM EMP
GROUP BY DEPTNO;
SELECT
DEPTNO,
AVG(SAL)
FROM EMP
GROUP BY DEPTNO;
GROUP BY 절에 명시하지 않은 컬럼을 SELECT절에 사용하면 에러
SELECT DEPTNO, ENAME, AVG(SAL)
FROM EMP
GROUP BY DEPTNO;
SELECT DEPTNO, ENAME, AVG(SAL)
FROM EMP
GROUP BY DEPTNO,ENAME;
다음은 소속 부서별 급여 총액과 평균 급여를 구하는 예제입니다.
SELECT DEPTNO, SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY DEPTNO;
다음은 소속 부서별 최대 급여와 최소 급여를 구하는 예제입니다.
SELECT
DEPTNO,
MAX(SAL),
MIN(SAL)
FROM EMP
GROUP BY DEPTNO;
다음은 부서별 사원의 수와 커미션을 받는 사원의 수를 계산하는 예제입니다.
SELECT
DEPTNO,
COUNT(*),
COUNT(COMM)
FROM EMP
GROUP BY DEPTNO;
부서번호로 그룹지어 총 사원수와
커미션을 받는 사원 수 출력
HAVING 조건
SELECT절에 조건을 사용하여 결과를 제한할 때는 WHERE절을 사용하며, 그룹의 결과를 제한할 때는 HAVING절을 사용합니다.
SELECT
DEPARTMENT_ID,
AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY) >= 2000;
WHERE절은 테이블에서 데이터를 가져올 때 특정 조건에 부합사는 자료만을 검색할 때 사용하는 절입니다. 반면 HAVING절은 그룹 함수 사용 시 그룹 함수를 적용해서 나온 결과값 중에서 원하는 조건에 부합하는 자료만 산출할 때 사용하는 절입니다.
WHERE절에서 조건에 사용되는 컬럼은 단순 컬럼이고,
HAVING절에서 그룹 함수를 적용한 컬럼이 조건으로 온다고 생각하면 이해하기 쉽습니다.