SQL 함수
1. DUAL 테이블과 SQL 함수 분류
1-1. DAUL 테이블
DUAL 테이블은 산술 연산이나 가상 칼럼 등의 값을 한번만 출력하고 싶을 때 많이 사용하는 유용한 테이블로 DUMMY라는 한 개의 칼럼으로 구성되어 있다.
SQL> SELECT 24*60*60 FROM DUAL;
위의 예제처럼 하나의 산술 연산을 계산할때 유용하다.
SQL> DESC DUAL;
1-2. 단일 행 함수와 그룹함수로 SQL 함수 분류
함수를 이용하여 복잡한 질의를 간결하게 표현할 수 있다.
Q) 30번 부서 소속 사원의 급여를 출력
SQL> SELECT DEPTNO, SAL
FROM EMP
WHERE DEPTNO=30;
30번 부서 소속 사원이 6명이기에 결과가 6개의 행으로 나타난다
Q) 그룹함수를 이용해서 30번 부서 소속 사원의 총 급여 출력
SQL> SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING DEPTNO=30;
30번 부서 소속 사원이 6명임에도 결과는 하나의 행으로 나온다
1-3. 단일 행 함수
단일행 함수
| 구 분 | 설 명 |
| 문자 함수 | 문자열을 다른 형태로 변환하여 나타낸다 |
| 숫자 함수 | 숫자 값을 다른형태로 변환하여 나타낸다 |
| 날짜 함수 | 날짜 값을 다른형태로 변환하여 나타낸다 |
| 변환 함수 | 문자, 날짜, 숫자 값을 서로 다른 타입으로 변환하여 나타낸다 |
| 일반 함수 | 기타 함수 |
행마다 함수가 적용되어 결과를 반환한다
1-4. 그룹 함수
그룹 함수
| 구 분 | 설 명 |
| SUM | 그룹의 누적 합계를 반환한다 |
| AVG | 그룹의 평균을 반환한다 |
| COUNT | 그룹의 총 개수를 반환한다 |
| MAX | 그룹의 최댓값을 반환한다 |
| MIN | 그룹의 최솟값을 반환한다 |
| STDDEV | 그룹의 표준편차를 반환한다 |
| CARIANCE | 그룹의 분산을 반환한다 |
하나 이상의 행을 그룹으로 묶어 연산하여 총합, 평균 등의 하나의 결과로 나타낸다
2. 문자 함수
문자형의 값을 조작하여 변환된 문자 값을 반환한다
| 구 분 | 설 명 |
| LOWER | 문자열을 소문자로 변환한다. |
| UPPER | 문자열을 대문자로 변환한다. |
| INITCAP | 첫 문자만 대문자로 변환하고 나머지는 소문자로 변환한다. |
| CONCAT | 두 개의 문자열을 연결한다. (연결 연산자와 비슷한 기능을 수행) |
| SUBSTR | 문자열 내에 지정된 위치의 문자열을 반환한다. |
| LENGTH, LENGTHB | 문자열의 길이나 BYTE 를 반환한다. |
| INSTR, INSTRB | 지정된 문자의 위치를 리턴한다. |
| TRIM | 접두어나 접미어를 잘라낸다. |
| LPAD. RPAD | 지정된 문자열의 길이만큼 빈부분에 문자를 채운다. |
| CONVERT | CHAR SET을 변환한다. |
| CHR | ASCII 코드 값으로 변환한다. |
| ASCII | ASCII 코드 값을 문자로 변경한다. |
| TRANSLATE | 문자단위 치환된 값을 리턴한다. |
| REPLACE | 문자열 단위 치환된 값을 리턴한다. |
2-1. LOWER 함수
입력한 문자 값을 소문자로 변환
Q) 'Database'를 소문자로 변환
SQL> SELECT 'Database', LOWER('Database')
FROM DUAL;
Q) 사원 테이블에서 부서번호가 10번인 사원명을 모두 소문자로 변환
SQL> SELECT ENAME, LOWER(ENAME)
FROM EMP
WHERE DEPTNO=10;
2-2. UPPER 함수
입력한 문자 값을 대문자로 변환
Q) 'Database'를 대문자로 변환
SQL> SELECT 'Database', UPPER('Database')
FROM DUAL;
Q) 직급이 'manager'인 사원을 검색
SQL> SELECT EMPNO, ENAME
FROM EMP
WHERE JOB = UPPER('manager');
SQL> SELECT EMPNO, ENAME
FROM EMP
WHERE LOWER(JOB) = 'manager';
2-3. INITCAP 함수
첫 글자만 대문자로 나머지는 소문자로 변환
Q) 'DATA BASE PROGRAM'의 첫 문자를 대문자로 나머지를 소문자로 변환
SQL> SELECT INITCAP('DATA BASE PROGRAM')
FROM DUAL;
Q) 사원 테이블의 10번 부서 소속의 사원 이름의 첫 글자만 대문자로 변환해서 검색
SQL> SELECT ENAME, INITCAP(ENAME)
FROM EMP
WHERE DEPTNO=10;
Q) 'Smith'란 이름을 갖는 사원의 사번과 이름과 급여와 커미션을 출력
SQL> SELECT EMPNO, ENAME, SAL, COMM
FROM EMP
WHERE INITCAP(ENAME)='Smith';
2-4. CONCAT 함수
두 문자를 연결하는데 사용
Q) 'Data' 와 'Base' 문자를 연결하여 출력
SQL>SELECT CONCAT('Data', 'Base')
FROM DUAL;
2-5. LENGTH / LENGTHB 함수
문자 상수나, 칼럼에 저장된 데이터 값이 몇 개의 문자로 구성되어있는지 길이를 구한다.
B가 붙으면 바이트 수를 구한다. 영문 1바이트 한글 3바이트
Q) 'DataBase'와 '데이터베이스'의 길이를 구한다
SQL> SELECT LENGTH('DataBase'), LENGTH('데이터베이스')
FROM DUAL;
Q) 'DataBase'와 '데이터베이스'의 바이트수를 구한다
SQL> SELECT LENGTHB('DataBase'), LENGTHB('데이터베이스')
FROM DUAL;
Q) 10번 부서 소속 사원들의 이름의 길이를 구한다.
SQL> SELECT DEPTNO, EMPNO, ENAME, LENGTH(ENAME)
FROM EMP
WHERE DEPTNO=10;
Q) 직원 중 이름이 4글자인 직원의 이름을 소문자로 출력
SQL> SELECT EMPNO, LOWER(ENAME)
FROM EMP
WHERE LENGTH(ENAME)=4;
Q) 이름이 6글자 이상인 사원의 사번과 이름과 급여를 출력
SQL> SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE LENGTH(ENAME) >= 6;
2-6. SUBSTR / SUBSTRB 함수
문자열의 일부만 출력할 때 사용
1) SUBSTR(대상, 시작위치, 추출할 개수)
시작위치의 인자 값이 음수이면 뒤에서부터 계산한다
-> 맨 마지막이 -1, 그 앞부터 -2, -3, -4, ...
Q) 'DataBase'에서 첫번째 순서부터 문자 3개를 추출
SQL> SELECT SUBSTR('DataBase', 1, 3)
FROM DUAL;
Q) 'DataBase'의 뒤쪽 4번째부터 문자 3개를 추출
SQL> SELECT SUBSTR('DataBase', -4, 3)
FROM DUAL;
Q) 20번 부서 사원들 중의 입사 년도 알아내기
SQL> SELECT EMPNO, ENAME, SUBSTR(HIREDATE, 1, 2)
FROM EMP
WHERE DEPTNO=20;
Q) 20번 부서 사원들 중 입사 년도가 81년도인 사원을 검색
SQL> SELECT EMPNO, ENAME, SUBSTR(HIREDATE, 1, 2)
FROM EMP
WHERE DEPTNO=20 AND SUBSTR(HIREDATE, 1, 2)='81';
Q) 이름이 K로 끝나는 직원을 검색
SQL> SELECT EMPNO, ENAME
FROM EMP
WHERE ENAME LIKE '%K';
SELECT EMPNO, ENAME
FROM EMP
WHERE SUBSTR(ENAME, -1, 1)='K';
B가 붙으면 바이트 수를 기준으로 문자열 일부만 추출
Q) '데이터베이스'에서 4바이트 수부터 6바이트만큼의 문자를 추출
SQL> SELECT SUBSTRB('데이터베이스', 4, 6)
FROM DUAL;
2-7. INSTR / INSTRB 함수
특정 문자의 위치를 구한다.
1) INSTR (대상, 찾을 글자, 시작위치, 몇번째 발견)
시작위치와 몇번째 발견 인자를 생략할 경우 1번째 위치에서 처음 발견한 문자의 위치를 리턴
Q) 'DataBase'에서 첫번째 'a'의 위치를 구한다
SQL> SELECT INSTR('DataBase', 'a')
FROM DUAL;
Q) 'DataBase'에서 첫번째 'a'의 위치를 3번째 문자부터 찾는다.
SQL> SELECT INSTR('DataBase', 'a', 3, 1)
FROM DUAL;
Q) 'DataBase'에서 두번째 'a'의 위치를 3번째 문자부터 찾는다.
SQL> SELECT INSTR('DataBase', 'a', 3, 2)
FROM DUAL;
Q) 'DataBase'에서 첫번째 'B'의 위치를 찾는다.
SQL> SELECT INSTR('DataBase', 'B')
FROM DUAL;
Q) 30번 부서 소속 사원 이름에 E자가 어디에 위치하는지 찾는다
SQL> SELECT EMPNO, ENAME, INSTR(ENAME, 'E', 1, 1)
FROM EMP
WHERE DEPTNO=30;
B가 붙으면 바이트 수를 기준으로 문자의 위치를 찾는다.
Q) '데이터베이스'에서 '이'문자를 4바이트 위치부터 검색
SQL> SELECT INSTRB('데이터베이스', '이', 4, 1)
FROM DUAL;
Q) 이름의 세번째 자리가 R로 끝나는 직원을 검색
SQL> SELECT EMPNO, ENAME
FROM EMP
WHERE ENAME LIKE '__R%';
SQL> SELECT EMPNO, ENAME
FROM EMP
WHERE INSTR(ENAME, 'R')=3;
Q) 이름의 두번쨰 자리에 A가 있는 사원의 사번, 이름, 직급을 출력
SQL> SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE INSTR(ENAME, 'A')=2;
2-8. LPAD / RPAD 함수
문자열의 왼쪽이나 오른쪽을 특정 기호로 채우고 싶을 때 사용
LPAD(LEFT PADDING) : 문자열을 오른쪽 끝으로 보내고 남은 자리를 특정 기호로 채운다
RPAD(RIGHT PADDING) : 문자열을 왼쪽 끝으로 보내고 남은 자리를 특정 기호로 채운다
LPAD|RPAD(넣을 문자열, 결과 문자열의 길이, 채울문자);
Q) 'DataBase'를 20개 문자열의 맨 오른쪽으로 보내고 나머지를 '$'로 채움
SQL> SELECT LPAD('DataBase', 20, '$')
FROM DUAL;
Q) 'DataBase'를 20개 문자열의 맨 왼쪽으로 보내고 나머지를 '$'로 채움
SQL> SELECT RPAD('DataBase', 20, '$')
FROM DUAL;
2-9. TRIM 함수
특정 문자를 잘라낼 때 사용
특정 문자가 앞부분이거나 뒷부분일 때 잘라내고 남은 문자열을 반환
TRIM([LEADING, TRAILING, BOTH] [trim_character] [FROM] trim_source)
-> LEADING은 왼쪽 공백제거, TRAILING은 오른쪽 공백 제거, BOTH는 둘다 제거
Q) 문자열에서 'a' 앞뒤를 잘라낸다
SQL> SELECT TRIM('a' FROM 'aaaaDataBase programmingaaaa')
FROM DUAL;
Q) 이름이 SMITH인 사원의 이름과 'S'를 잘라낸 이름 'H'를 잘라낸 이름을 출력
SQL> SELECT ENAME, TRIM('S' FROM ENAME), TRIM('H' FROM ENAME)
FROM EMP
WHERE ENAME='SMITH';
Q)
LT = ' ABCD '에서 왼쪽 공백을 제거
RT = ' ABCD '에서 오른쪽 공백 제거
BOTH1 = ' ABCD '에서 양쪽 공백 제거 TRIM(BOTH 문자열) 사용하여
BOTH2 = ' ABCD '에서 양쪽 공백 제거
각각의 문자열과 결과값의 길이를 출력
SQL>
SELECT TRIM(LEADING FROM ' ABCD ') AS LT,
LENGTH(TRIM(LEADING FROM ' ABCD ')) AS LT_LEN,
TRIM(TRAILING FROM ' ABCD ') AS RT,
LENGTH(TRIM(TRAILING FROM ' ABCD ')) AS RT_LEN,
TRIM(BOTH FROM ' ABCD ') AS BOTH1,
LENGTH(TRIM(BOTH FROM ' ABCD ')) AS BOTH1_LEN,
TRIM(' ABCD ') AS BOTH2,
LENGTH(TRIM(' ABCD ')) AS BOTH2_LEN
FROM DUAL;
3. 숫자 함수
3-1. ROUND 함수
특정 자릿수에서 반올림한다.
ROUND(대상, 표시할 자릿수)
표시할 자릿수 바로 아래 자리에서 반올림
두번째 인자값이 생략되면 0으로 간주
ex>
ROUND(35.12, 1) 소수점 이하 2번째 자리에서 반올림해서 1번째 자리까지 구함 35.1
ROUND(21.125, 2) 소수점 이하 3번째 자리에서 반올림해서 2번째 자리까지 구함 21.13
ROUND(47.51) 소수점 이하 1번째 자리에서 반올림해서 구함 48
ROUND(834.12, -1) 소수점 위 1번째 자리에서 반올림해서 구함 830
ROUND(653.54, -2) 소수점 위 2번째 자리에서 반올림해서 구함 700
Q) 12.345을 소수점 이하 3번째자리에서, 34.567을 소수점 이하 1번째 자리에서
56.789를 소수점 이하 1번째 자리에서, 78.901을 소수점 위 1번째 자리에서 반올림
SQL> SELECT ROUND(12.345, 2), ROUND(34.567,0), ROUND(56.789), ROUND(78.901,-1)
FROM DUAL;
3-2. TRUNC 함수
특정 자릿수에서 잘라낸다.(버림)
TRUNC(대상, 표시할 자릿수)
표시할 자릿수 바로 아래 자리에서 버림
두번째 인자값이 생략되면 0으로 간주
Q) 12.345를 소수점 이하 3번재 자리에서, 34.567을 소수점 이하 1번째 자리에서
56.789를 소수점 이하 1번재 자리에서, 78.901을 소수점 위 1번째 자리에서 버림
SQL> SELECT TRUNC(12.345,2), TRUNC(34.567,0), TRUNC(56.789), TRUNC(78.901,-1)
FROM DUAL;
3-3. MOD 함수
입력 받은 수를 나눈 나머지 값을 반환한다.
MOD(입력 받은 수, 나눌 수) -> 입력받은 수 % 나눌 수
Q) 34를 2로 나눈 나머지, 34를 5로 나눈 나머지, 34를 7로 나눈 나머지의 값을 구함
SQL> SELECT MOD(34, 2), MOD(34, 5), MOD(34, 7)
FROM DUAL;
4. 날짜 함수
4-1. SYSDATE 함수
시스템에 저장된 현재 날짜를 반환한다.
날짜형 데이터는 더하기나 빼기 와 같은 연산을 할 수 있다.
Q) 현재 날짜를 출력
SQL> SELECT SYSDATE
FROM DUAL;
Q) 어제 날짜, 현재 날짜, 내일 날짜를 출력
SQL> SELECT SYSDATE-1 AS "어제", SYSDATE AS "오늘", SYSDATE+1 AS "내일"
FROM DUAL;
Q) 부서번호가 10인 사원을 대상으로 입사한 날로부터 오늘까지 며칠이 흘렀는지 근무일수를 구함
SQL> SELECT EMPNO, ENAME, HIREDATE, SYSDATE, FLOOR(SYSDATE-HIREDATE) AS "근무일수"
FROM EMP
WHERE DEPTNO=10;
4-2. MONTHS_BETWEEN 함수
두 날짜 사이의 간격을 계산한다.
MONTHS_BETWEEN(DATE1, DATE2)
-> DATE1-DATE2의 형식, 앞 날짜가 나중 날짜여야 한다.
Q) 부서번호가 10인 사원을 대상으로 오늘을 기준으로 입사한 날로부터 몇개월이 흘렀는지 근무월수를 구함
SQL> SELECT EMPNO, ENAME, HIREDATE, SYSDATE, FLOOR(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS 근무월수
FROM EMP
WHERE DEPTNO=10;
4-3. ADD_MONTHS 함수
특정 개월 수를 더한 날짜를 구하는 함수
ADD_MONTHS(DATE1, 더할 개월수)
Q) 부서번호가 10인 사원을 대상으로 입사일에서 3개월이 지난 날짜를 구하라
SQL> SELECT EMPNO, ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 3) AS "입사후 3개월"
FROM EMP
WHERE DEPTNO=10;
4-4. NEXT_DAY 함수
해당 날짜를 기준으로 지정한 요일이 가장 빨리 다가오는 날짜를 반환
NEXT_DAY(DATE, 요일)
Q) 오늘을 기준으로 최초로 다가오는 수요일은 언제인지 구한다.
SQL> SELECT NEXT_DAY(SYSDATE, '수요일')
FROM DUAL;
-> '수요일' 대신 '수' 가능, 일요일~토요일 기준으로 1, 2, ..., 7로 입력 가능
Q) 오늘을 기준으로 최초로 다가오는 수요일을 구한다. NEXT_DAY의 인자로 '수', 숫자 사용
SQL> SELECT NEXT_DAY(SYSDATE, '수'), NEXT_DAY(SYSDATE, 4)
FROM DUAL;
요일을 영어로 사용할 경우 언어를 AMERICAN으로 지정
SQL> ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
요일을 한글로 사용할 경우 언어를 KOREAN으로 지정
SQL> ALTER SESSION SET NLS_LANGUAGE=KOREAN;
Q) 오늘을 기준으로 최초로 다가오는 수요일을 구한다. NEXT_DAY의 인자로 'WENDSDAY', '수요일' 차례로 사용
SQL> ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
SELECT NEXT_DAY(SYSDATE, 'WEDNSDAY')
FROM DUAL;
SQL> ALTER SESSION SET NLS_LANGUAGE=KOREAN;
SELECT NEXT_DAY(SYSDATE, '수요일')
FROM DUAL;
4-5. LAST_DAY 함수
해당 달의 마지막 날짜를 반환
Q) 오늘 날짜의 마지막 달의 날짜를 구하라
SQL> SELECT LAST_DAY(SYSDATE)
FROM DUAL;
4-6. ROUND 함수
ROUND 함수의 포맷 모델을 지정하여 날짜에 대해서도 반올림을 할 수 있다.
ROUND(DATE, '포맷')
ROUND(DATE, 'MONTH') -> 16일을 기준으로 반올림
-> 가장 가까운 달의 첫 날짜를 반환
-> ROUND('99/06/07', 'MONTH') : 99/06/01
-> ROUND('99/06/20', 'MONTH') : 99/07/01
ROUND(DATE, 'DAY') -> 가장 가까운 한주가 시작되는 날짜를 반환
ROUND(DATE, 'YEAR') -> 가장 가까운 1년이 시작되는 날짜를 반환
Q) 부서번호가 10인 사원을 대상으로 입사일을 달 기준으로 반올림
SQL> SELECT EMPNO, ENAME, HIREDATE, ROUND(HIREDATE, 'MONTH')
FROM EMP
WHERE DEPTNO=10;
4-7. TRUNC 함수
TRUNC 함수로 날짜도 잘라낼 수 있다.
포맷을 기준으로 날짜를 자름
Q) 부서번호가 10인 사원을 대상으로 입사일을 월 기준으로 잘라낸다
SQL> SELECT EMPNO, ENAME, HIREDATE, TRUNC(HIREDATE, 'MONTH')
FROM EMP
WHERE DEPTNO=10;
5. 변환 함수
자료형을 변환시키고자 할 때 사용하는 함수
날짜형 / 숫자형 -> 문자형
문자형 -> 날짜형
문자형 -> 숫자형
5-1. TO_CHAR 함수
숫자나 날짜 형태의 데이터를 문자형으로 변환하는 함수
TO_CHAR(DATE, '출력 형식')
날짜 데이터 출력 형식
| 종 류 | 의 미 |
| YYYY | 년도 표시(4자리) |
| YY | 년도 표시(2자리) |
| MM | 월을 숫자로 표시 |
| MOM | 월을 숫자+월 로 표시 |
| DD | 일 표시 |
| DAY | 요일 표시 |
| DY | 요일을 약어로 표시 |
시간 데이터 출력 형식
| 종 류 | 의 미 |
| AM OR PM | 오전(AM), 오후(PM) 시각 표시 |
| A.M OR P.M | 오전(A.M), 오후(P.M) 시각 표시 |
| HH OR HH12 | 시간(1~12) |
| HH24 | 24(0~23) |
| MI | 분 |
| SS | 초 |
Q) 현재 시간을 년도(4자리)-월-일 의 형식으로 출력
SQL> SELECT TO_CHAR(SYSDATE, "YYYY-MM-DD')
FROM DUAL;
Q) 부서가 10인 사원들의 입사일을 출력하되 요일까지 함께 출력
SQL> SELECT EMPNO, ENAME, HIREDATE, TO_CHAR(HIREDATE, 'YYYY-MM-DD DAY')
FROM EMP
WHERE DEPTNO=10;
SQL> SELECT EMPNO, ENAME, HIREDATE, TO_CHAR(HIREDATE, 'YYYY-MM-DD DY')
FROM EMP
WHERE DEPTNO=10;
Q) 현재 시간을 년/월/일 시(24):분:초 의 형식으로 출력
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD H24:MI:SS')
FROM DUAL;
5-2. TO_DATE 함수
문자를 날짜로 변환하는 함수 (숫자도 됨)
TO_DATE('문자', '출력형식')
Q) 1981년 2월 20일에 입사한 사원을 검색
SQL> SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE HIREDATE = TO_DATE(19810220, 'YYYYMMDD');
Q) 올해 며칠이 지났는지 날짜 계산 // 잘못된 예
SQL>SELECT SYSDATE-'2008/01/01'
FROM DUAL; -> 오류 발생
Q) 올해 며칠이 지났는지 날짜 계산
SQL>SELECT TRUNC(SYSDATE-TO_DATE('2008/01/01', 'YYYY/MM/DD'))
FROM DUAL;
5-3. TO_NUMBER 함수
문자를 숫자로 변환하는 함수
TO_NUMBER('문자', '출력 형식')
Q) '10,000' + '20,000'의 합계 // 잘못된 예
SQL> SELECT '10,000' + '20,000'
FROM DUAL; -> 오류 발생
Q) '10,000' + '20,000'의 합계
SQL> SELECT TO_NUMBER('10,000', '999,999') + TO_NUMBER('20,000', '999,999')
FROM DUAL;