[ 함수 (Function) ]
- 어떠한 일을 수행하는 기능으로써 주어진 인수(argument)를 재료로 처리를 하여 그 결과값을 반환함.
< 함수의 종류 >
- 단일행함수 : 하나의 행(row)당 하나의 결과값을 반환하는 함수
- 복수행함수 : 여러개의 행당 하나의 결과값을 반환하는 함수
< 단일행함수 >
<1> 문자함수
- CHR(아스키코드)
SQL> SELECT CHR(65) FROM DUAL;
[결과]
CH
--
A
- CONCAT(컬럼명,'붙일문자')
SQL> SELECT CONCAT(ENAME,'IS A'),JOB FROM EMP;
[결과]
CONCAT(ENAME,'ISA') JOB
------------------------ -------------
SMITHIS A CLERK
ALLENIS A SALESMAN
WARDIS A SALESMAN
JONESIS A MANAGER
...
- INITCAP('문자열'):시작문자를 대문자로 변환
SQL> SELECT INITCAP('the lion') FROM DUAL;
INITCAP('THELION
----------------
The Lion
- LOWER('문자열') : 소문자로 변환
SQL> SELECT LOWER('THE LION') FROM DUAL;
LOWER('THELION')
----------------
the lion
- LPAD('문자열',자릿수,'채울문자')
SQL> SELECT LPAD(SAL,10,'#') FROM EMP;
LPAD(SAL,10,'#')
------------------------
#######800
######1600
######1250
######2975
######1250
######2850
######2450
- RPAD('문자열',자릿수,'채울문자')
SQL> SELECT RPAD(SAL,10,'#') FROM EMP;
RPAD(SAL,10,'#')
-------------------
800#######
1600######
1250######
2975######
1250######
2850######
2450######
5000######
- LTRIM('문자열1','없앨문자열')
SQL> SELECT LTRIM('THE LION','THE') FROM DUAL;
LTRIM('THE
----------
LION
SQL> SELECT LTRIM('LION THE','THE') FROM DUAL;
LTRIM('LIONTHE',
----------------
LION THE
SQL> SELECT LTRIM(' LION',' ') FROM DUAL;
LTRIM('L
--------
LION
- RTRIM('문자열1','없앨문자열')
SQL> SELECT RTRIM('APPLE IS','IS') FROM DUAL;
RTRIM('APPLE
------------
APPLE
SQL> SELECT RTRIM('APPLE ',' ') FROM DUAL;
RTRIM('APP
----------
APPLE
SQL> SELECT RTRIM('APPLE%%%%%','%') FROM DUAL;
RTRIM('APP
----------
APPLE
- REPLACE(문자열,'원래문자열','바꿀문자열')
SQL> SELECT REPLACE('MY NAME','NAME','JOB') FROM DUAL;
REPLACE('MYN
------------
MY JOB
- SUBSTR('문자열',위치,갯수)
SQL> SELECT SUBSTR('ABCDEF',3,2) FROM DUAL;
SUBS
----
CD
- INSTR('문자열1','위치찾는문자열') : 문자의 위치 찾기 (없으면 0)
SQL> SELECT INSTR('SONG@DAUM.NET','@') FROM DUAL;
INSTR('SONG@DAUM.NET','@')
--------------------------
5
SQL> SELECT INSTR('SONGDAUM.NET','@') FROM DUAL;
INSTR('SONGDAUM.NET','@')
-------------------------
0
SQL> SELECT INSTR('SONGDAUM.NET','NET') FROM DUAL;
INSTR('SONGDAUM.NET','NET')
---------------------------
10
- LENGTH('문자열') : 문자열 길이
SQL> SELECT ENAME,LENGTH(ENAME) FROM EMP;
ENAME LENGTH(ENAME)
---------- -------------
SMITH 5
ALLEN 5
WARD 4
- NVL(컬럼명,값) : 컬럼값이 NULL인경우 값으로 채우기
SQL> SELECT ENAME,NVL(COMM,0) FROM EMP;
ENAME NVL(COMM,0)
-------------------- -----------
SMITH 0
ALLEN 300
WARD 500
JONES 0
MARTIN 1400
BLAKE 0
CLARK 0
<2> 날짜함수
- SYSDATE(*) :현재날짜 얻어오기
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
--------
12/11/20
- ADD_MONTHS(날짜,더할개월수)
SQL> SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;
ADD_MONT
--------
13/02/20
SQL> SELECT ENAME,ADD_MONTHS(HIREDATE,6) FROM EMP;
ENAME ADD_MONT
----------- --------
SMITH 81/06/17
ALLEN 81/08/20
WARD 81/08/22
JONES 81/10/02
MARTIN 82/03/28
- MONTHS_BETWEEN(날짜1,날짜2) : 날짜1과 날짜2의 개월수
SQL> SELECT ENAME,HIREDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE) "근무개월수" 2 FROM EMP;
ENAME HIREDATE 근무개월수
--------- -------- ----------
SMITH 80/12/17 383.111281
ALLEN 81/02/20 381
WARD 81/02/22 380.949991
JONES 81/04/02 379.595152
MARTIN 81/09/28 373.756443
<3> 날짜를 문자로 변환하는 함수(**)
- TO_CHAR(날짜,'형식')
SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL;
TO_CHAR(SYSDATE,'YYY
--------------------
2012-11-20
SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH:MI:SS')
--------------------------------------
2012-11-20 11:04:26
SQL> SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD AM HH:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'YYYY/MM/DDAMHH:MI:SS')
----------------------------------------------------
2012/11/20 오전 11:05:54
SQL> SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') FROM DUAL;--24시간단위
TO_CHAR(SYSDATE,'YYYY/MM/DDHH24:MI:SS'
--------------------------------------
2012/11/20 11:07:22
SQL> SELECT TO_CHAR(SYSDATE,'YYYY"년"MM"월"DD"일" HH"시"MI"분"SS"초"') "현재시간" 2 FROM DUAL;
현재시간
--------------------------------------------
2012년11월20일 11시10분46초
<4> 문자를 날짜로 변환하는 함수
- TO_DATE('날짜에해당하는문자열','해석할서식')
SQL> SELECT TO_DATE('20-11-2012','DD-MM-YYYY') FROM DUAL;
TO_DATE(
--------
12/11/20
SQL> SELECT TO_DATE('1980-11-20','YYYY-MM-DD') FROM DUAL;
TO_DATE(
--------
80/11/20
<5> 수학관련 함수
- ABS(숫자) : 절대값
SQL> SELECT ABS(-10) FROM DUAL;
ABS(-10)
----------
10
- CEIL(숫자) : 올림값구하기
SQL> SELECT CEIL(3.2) FROM DUAL;
CEIL(3.2)
----------
4
- FLOOR(숫자) : 내림값 구하기
SQL> SELECT FLOOR(3.2) FROM DUAL;
FLOOR(3.2)
----------
3
- ROUND(숫자) : 반올림값 구하기
SQL> SELECT ROUND(3.2) FROM DUAL;
ROUND(3.2)
----------
3
SQL> SELECT ROUND(3.7) FROM DUAL;
ROUND(3.7)
----------
4
SQL> SELECT ROUND(3.1256,2) FROM DUAL;--소수점2째자리에서반올림적용
ROUND(3.1256,2)
---------------
3.13
SQL> SELECT ROUND(3.1256,3) FROM DUAL;--소수점3째자리에서반올림적용
ROUND(3.1256,3)
---------------
3.126
- MOD(숫자1,나눌값) : 나머지 구하기
SQL> SELECT MOD(5,3) FROM DUAL;
MOD(5,3)
----------
2
- TRUNC(숫자,절삭할 소숫점자릿수)
SQL> SELECT TRUNC(2.3456,3) FROM DUAL;
TRUNC(2.3456,3)
---------------
2.345
< 복수행 함수(*) >
<1> COUNT(컬럼명) : 갯수 얻어오기
SQL> SELECT COUNT(EMPNO) "사원수" FROM EMP;
사원수
----------
12
SQL> SELECT COUNT(COMM) FROM EMP; --NULL은 COUNT하지 않음
COUNT(COMM)
-----------
4
SQL> SELECT COUNT(*) FROM EMP;
<2> SUM(컬럼명) : 컬럼의 합 구하기
-- 전체사원의 급여의 총합을 구해보세요.
SQL> SELECT SUM(SAL) "급여합" FROM EMP;
<3> AVG(컬럼명) : 컬럼의 평균 구하기
SQL> SELECT AVG(SAL) "급여평균" FROM EMP;
<4> MIN(컬럼명),MAX(컬럼명) : 컬럼의 최소값,컬럼의 최대값 구하기
SQL> SELECT MIN(SAL) "최소급여",MAX(SAL) "최대급여" FROM EMP;
(4) GROUP BY : 컬럼을 기준으로 그룹으로 묶음
-- EMP테이블에서 부서별 급여의 평균을 구하시오.
SELECT DEPTNO,AVG(SAL) 급여평균
FROM EMP
GROUP BY DEPTNO;
-- EMP테이블에서 급여가 2000이상인 사원들의 부서별 급여총합을 구하세요.
SELECT DEPTNO,SUM(SAL) "급여총합"
FROM EMP
WHERE SAL>=2000
GROUP BY DEPTNO;
-- EMP테이블에서 부서별 최대급여,최소급여를 출력해 보세요.(부서번호로 오름차순정렬)
SELECT DEPTNO,MAX(SAL) "최대급여",MIN(SAL) "최소급여"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO ASC
-- 급여가 2000이상인 사원들의 부서별 평균급여을 구하고 평균급여가 높은순으로 정렬하시오.
SQL> SELECT DEPTNO,AVG(SAL) "AVG"
2 FROM EMP
3 WHERE SAL>=2000
4 GROUP BY DEPTNO
5 ORDER BY AVG(SAL) DESC; -- ORDER BY AVG DESC :ALIAS사용가능!
-- 각 직업별 평균급여와 최대급여,최소급여를 구해보세요.
SQL> SELECT JOB,AVG(SAL) "평균급여",MAX(SAL) "최대급여",MIN(SAL) "최소급여" 2 FROM EMP 3 GROUP BY JOB;
JOB 평균급여 최대급여 최소급여
------------- ---------- -------- -------
CLERK 1016.66667 1300 800
SALESMAN 1400 1600 1250
PRESIDENT 5000 5000 5000
-- 각 부서별 같은 업무를 하는 사람의 평균급여를 구하여 부서별,업무별로 오름차순 정렬해서 출력
SELECT DEPTNO,JOB,AVG(SAL) FROM EMP GROUP BY DEPTNO,JOB ORDER BY DEPTNO,JOB ;
DEPTNO JOB AVG(SAL)
--------- ------------ ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 3000
......
-- 사원들의 부서별 평균급여를 출력하시오.단 평균급여가 2000이상인 부서만 출력되도록 하시오.
## WHERE 절에는 그룹함수(AVG,MAX,MIN,SUM,..을 사용할 수 없다.)
SELECT DEPTNO,AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>=2000;
## SELECT 구문의 실행순서(**)
SELECT |
컬럼명,.. |
5 |
FROM |
테이블명 |
1 |
WHERE |
조건절 |
2 |
GROUP BY |
절 |
3 |
HAVING |
절 |
4 |
ORDER BY |
절 |
6 |
SELECT DEPTNO,AVG(SAL) AG FROM EMP GROUP BY DEPTNO HAVING AG>=2000; -- HAVING절에서는 ALIAS를 사용할 수 없다.
SELECT DEPTNO,AVG(SAL) AG
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>=2000
ORDER BY AG DESC; -- ALIAS사용가능!
-- 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수를 출력해보세요.
SQL> SELECT JOB,COUNT(*) 인원수 2 FROM EMP 3 GROUP BY JOB 4 HAVING COUNT(*)>=4;
JOB 인원수
------------------ ----------
SALESMAN 4
Q1) 10번 부서의 급여의 평균,최고,최저,인원수를 구하세요.
SELECT AVG(SAL),MAX(SAL),MIN(SAL),COUNT(*)
FROM EMP
WHERE DEPTNO=10;
Q2) 각 부서별 평균급여,최고급여,최저급여를 구하세요. 단 평균급여가 2000이상인 부서만 출력하세요.
SQL> SELECT DEPTNO,AVG(SAL),MAX(SAL),MIN(SAL) 2 FROM EMP 3 GROUP BY DEPTNO 4 HAVING AVG(SAL)>=2000;
DEPTNO AVG(SAL) MAX(SAL) MIN(SAL)
---------- ---------- ---------- ----------
20 2258.33333 3000 800
10 2916.66667 5000 1300
Q3) 각 부서별 인원수를 구해보세요. 인원수가 3명 이상인 부서만 출력되도록 하세요.
SQL> SELECT DEPTNO,COUNT(*)
2 FROM EMP
3 GROUP BY DEPTNO
4 HAVING COUNT(*)>=3;
DEPTNO COUNT(*)
---------- ----------
30 6
20 3
10 3
Q4) 각 부서별 평균급여,전체월급을 구하여 평균월급이 많은 순으로 출력하세요.
SQL> SELECT DEPTNO,AVG(SAL) "AVG",SUM(SAL)
2 FROM EMP
3 GROUP BY DEPTNO
4 ORDER BY AVG DESC;
DEPTNO AVG SUM(SAL)
---------- ---------- ----------
10 2916.66667 8750
20 2258.33333 6775
30 1566.66667 9400
(5) 기타함수
1)
DECODE(대상컬럼,조건1,결과값1,조건2,결과값2
..., 기본값) 별칭
예)
SELECT JOB,
SAL,
DECODE(JOB, 'ANALYST',SAL*1.1,
'CLERK',SAL*1.2,
'SALESMAN',SAL*1.3,
SAL) 보너스
FROM EMP;
JOB SAL 보너스
------------- ---------- ----------
CLERK 800 960
SALESMAN 1600 2080
SALESMAN 1250 1625
MANAGER 2975 2975
SALESMAN 1250 1625
MANAGER 2850 2850
2)
CASE WHEN 조건1 THEN 결과값1
WHEN 조건2 THEN 결과값2
WHEN 조건3 THEN 결과값3
ELSE 기본값
END 별칭
예)
SELECT JOB,SAL,
CASE WHEN JOB='ANALYST' THEN SAL*1.1
WHEN JOB='CLERK' THEN SAL*1.2
WHEN JOB='SALESMAN' THEN SAL*1.3
ELSE SAL
END BONUS
FROM EMP;
-- 각 부서별 평균급여를 구하고 평균급여가 3000이상이면 높음, 2000이상이면 보통,1000이상이면 낮음을 출력하세요.
SELECT DEPTNO,AVG(SAL), CASE WHEN AVG(SAL)>=3000 THEN '높음' WHEN AVG(SAL)>=2000 THEN '보통' WHEN AVG(SAL)>=1000 THEN '낮음' ELSE '판단불가' END "급여수준" FROM EMP GROUP BY DEPTNO;
DEPTNO AVG(SAL) 급여수준
------ ---------- --------------
30 1566.66667 낮음
20 2258.33333 보통
10 2916.66667 보통
...