(1) Function
Github: https://github.com/SHIN96BC/KOSMO-2021-2022-Oracle/tree/main/2_SQL2
1) 함수(function)란 ?
<1> 어떤 일을 수행하는 기능으로써 주어진 인수(argument)를 재료로 '처리'하여,
그 결과를 '반환'하는 일
2) 기능 기준 분류
<1> Data 계산
<2> Data 변환
3) 종류
<1> 단일행 함수 (ex: nvl, ... )
1> 하나의 행(row)당, 하나의 결과값을 리턴하는 함수
<2> 복수행 함수 (ex: max, min, avg, .. )
1> 여러개의 행(row)당, 하나의 결과값을 리턴하는 함수
4) 단일행 함수
<1> 문자 함수
1> chr
ex) select chr(65) from DUAL;
2> concat(컬럼명, '붙일문자열')
ex) select ENAME||' is a ' || JOB from EMP;
ex) select concat(ENAME, ' is a '), JOB from EMP;
3> initcap('문자열')
ex) select initcap('the lion') from DUAL;
4> lower('문자열')
ex) select lower('MY NAME IS KHS') from dual;
ex) select lower(ENAME) from EMP;
5> lpad('문자열1', 자리수, '문자열2')
ex) select lpad('khs', 13, '*#') from DUAL;
설명) lpad("대상문자열", "총 문자길이", "채움문자")
6> rpad('문자열1', 자리수, '문자열2')
ex) select rpad('khs', 13, '*#') from DUAL;
설명) rpad("대상문자열", "총 문자길이", "채움문자")
7> ltrim('문자열1', '문자열2')
ex) select ltrim('xyxXxyLAST WORDxy', 'xy') from DUAL;
ex) select ltrim('xyyXxyLAST WORDxy', 'yx') from DUAL;
ex) select ltrim(' LAST WORDxy', ' ') from DUAL;
8> rtrim('문자열1', '문자열2')
ex) select rtrim('xyxXxyLAST WORDxy', 'xy') from DUAL;
ex) select rtrim('xyyXxyLAST WORDxyx', 'xy') from DUAL;
ex) select rtrim('WORDxy ', ' ') from DUAL;
ex) select rtrim('WORDxy ') from DUAL;
ex) select length(rtrim('WORDxy ')) from DUAL;
9> replace('문자열1', '문자열2', '문자열3')
ex) select 'JACK and JUE' from DUAL;
ex) select replace('JACK and JUE', 'J', 'BL') from DUAL;
ex) select DNAME from DEPT;
ex) select replace(DNAME, 'A', 'a') from DEPT;
10> substr('문자열', 자리수, 갯수)
ex) select substr('ABCDEFG', 3, 2) from DUAL;
1. ename의 두번째 자리가 'A'인 사원의 이름을 출력
ex) select ENAME from EMP where ENAME like '_A%';
ex) select ENAME from EMP where substr(ENAME,2,1)='A';
11> ascii('문자')
ex) select ascii('A') from dual;
ex) select ascii('"') from DUAL;
ex) select ascii(' ') from DUAL;
ex) select ascii('#') from DUAL;
ex) select ascii('3') from DUAL;
12> length('문자열')
ex) select length('진달래 꽃') from DUAL;
ex) select length('abc D') from DUAL;
13> greatest('문자열1', '문자열2', '문자열3')
ex) select greatest('CC', 'ABCDE', 'CA') from DUAL;
ex) select greatest('12', '132', '119') from DUAL;
ex) select greatest('가나', '가다', '가자') from DUAL;
14> least('문자열1', '문자열2', '문자열3')
ex) select least('CC', 'ABCDE', 'CA') from DUAL;
ex) select least('12', '132', '119') from DUAL;
ex) select least('가나', '가다', '가자') from DUAL;
15> instr('문자열1', '문자열2', 자리수1, 자리수2)
ex) select instr('CORPORATE FLOOR', 'OR', 3, 2) from DUAL;
ex) select instr('CORPORATE FLOOR', 'OR', 6, 1) from DUAL;
cf) '문자열1'에서 '문자열2'를 찾는데.. '자리수1'부터 '자리수2'번째 것을 찾아 인덱스 리턴
16> nvl(컬럼명, 숫자) **
ex) select ENAME, nvl(COMM, 100) from EMP;
ex) select ENAME, nvl(COMM, 0) from EMP;
<2> 숫자 함수
1> abs(숫자)
ex) select abs(-10) from dual;
2> ceil(숫자)
ex) select ceil(11.012) from dual;
ex) select ceil(-11.012) from dual;
3> floor(숫자)
ex) select floor(12.999) from dual;
ex) select floor(-12.999) from dual;
4> round(숫자)
ex) select round(12.5) from dual;
ex) select round(-12.5) from dual;
5> cos(숫자[rad])
ex) select cos(90 * 3.141592/180) from dual;
6> sin(숫자[rad])
ex) select sin(90* 3.141592/180) from dual;
7> tan(숫자[rad])
ex) select tan(45* 3.141592/180) from dual;
8> exp(숫자)
ex) select exp(2) from dual;
9> log(숫자1, 숫자2)
ex) select log(10, 100) from dual;
10> mod(숫자1, 숫자2)
ex) select mod(11, 4) from dual;
11> power(숫자1, 숫자2)
ex) select power(3, 3) from dual;
12> trunc(숫자1, 숫자2)
ex) select trunc(15.789, 2) from DUAL;
ex) select trunc(15.789, 0) from DUAL;
ex) select trunc(15.789) from DUAL;
ex) select trunc(-15.789, 2) from DUAL;
<3> 날짜 함수 ( *** )
1> SYSDATE
ex) select SYSDATE from DUAL;
2> add_months(날짜컬럼 or 날짜데이터, 숫자)
ex) select HIREDATE from EMP where EMPNO=7782;
ex) select HIREDATE, add_months(HIREDATE, 7) from EMP where EMPNO=7782;
3> last_day(날짜컬럼 or 날짜데이터)
ex) select last_day('21/12/21') from dual;
ex) select HIREDATE, last_day(HIREDATE) from EMP;
4> months_between(날짜컬럼or날짜데이터1, 날짜컬럼or날짜데이터2)
ex) select MONTHS_BETWEEN('80/01/02', '81/02/04') from DUAL;
1. empno가 7782인 사원의 현재까지의 근무월수
ex) select MONTHS_BETWEEN(SYSDATE, HIREDATE) from EMP where EMPNO=7782;
5> next_day(날짜컬럼or날짜데이터, 숫자)
ex) select SYSDATE, next_day(SYSDATE,4) from DUAL;
ex) select SYSDATE, next_day(SYSDATE,1) from DUAL;
ex) select SYSDATE, next_day(SYSDATE,3) from DUAL;
1. 일(1) 월(2) 화(3) 수(4) 목(5) 금(6) 토(7)
4) 문자 변환 함수 ( ***** )
ex) to_char(날짜컬럼or날짜데이터, '변환포멧')
<1> 'D'
ex) select SYSDATE, to_char(SYSDATE, 'D') from DUAL; --요일의 index
<2> 'DAY'
ex) select SYSDATE, to_char(SYSDATE, 'DAY') from DUAL;
<3> 'DY'
ex) select SYSDATE, to_char(SYSDATE, 'DY') from DUAL;
<4> 'DD'
ex) select SYSDATE, to_char(SYSDATE, 'DD') from DUAL; --날짜
<5> 'MM'
ex) select SYSDATE, to_char(SYSDATE, 'MM') from DUAL;
<6> 'MON'
ex) select SYSDATE, to_char(SYSDATE, 'MON') from DUAL;
ex) select SYSDATE, to_char(SYSDATE, 'MONTH') from DUAL;
<7> 'YY'
ex) select SYSDATE, to_char(SYSDATE, 'YY') from DUAL;
<8> 'YYYY'
ex) select SYSDATE, to_char(SYSDATE, 'YYYY') from DUAL;
<9> 'YY-MM-DD'
ex) select SYSDATE, to_char(SYSDATE, 'YY-MM-DD') from DUAL;
<10> 'HH' or 'HH12'
ex) select SYSDATE, to_char(SYSDATE, 'HH') from DUAL;
<11> 'HH24'
ex) select SYSDATE, to_char(SYSDATE, 'HH24') from DUAL;
<12> 'MI'
ex) select SYSDATE, to_char(SYSDATE, 'MI') from DUAL;
<13> 'SS'
ex) select SYSDATE, to_char(SYSDATE, 'SS') from DUAL;
<14> 'AM' or 'PM'
ex) select SYSDATE, to_char(SYSDATE, 'AM HH:MI:SS') from DUAL; --선호
ex) select SYSDATE, to_char(SYSDATE, 'fmAM HH:MI:SS') from DUAL;
<15> 현재날짜를 '2021-12-21 오전 11:43:10 화요일' 식으로 출력
1> 'YYYY-MM-DD AM HH:MI:SS DAY'
ex) select to_char(SYSDATE, 'YYYY-MM-DD AM HH:MI:SS DAY') from DUAL;
Tip1) alter session set NLS_LANGUAGE='ENGLISH';
Tip2) select SYSDATE, to_char(SYSDATE, 'DDSP') from DUAL; -- one two three ..
select SYSDATE, to_char(SYSDATE, 'DDTH') from DUAL; -- 1st 2nd 3rd..
select SYSDATE, to_char(SYSDATE, 'DDSPTH') from DUAL; -- first second third..
5) 복수행 함수 ( group function ***** )
<1> count(컬럼명)
1> 사원의 수를 출력
ex) select count(EMPNO) from EMP; --PK컬럼 사용하면 안전
ex) select count(COMM) from EMP; --null은 세지 않음
ex) select count(*) from EMP;
<2> sum(컬럼명)
2> 모든 사원의 급여의 합 출력
ex) select sum(SAL) from EMP;
ex) select sum(COMM) from EMP;
<3> avg(컬럼명)
ex) select avg(SAL) from EMP;
ex) select avg(COMM) from EMP; --null은 세지 않음
ex) select avg(nvl(COMM,0)) from EMP;
<4> max(컬럼명) 과 min(컬럼명)
ex) select max(SAL) from EMP;
ex) select min(SAL) from EMP;
ex) select max(SAL)-min(SAL) from EMP;
1> 최저 급여자의 사원이름과 급여를 출력
ex) select ENAME, SAL from EMP where SAL=min(SAL);--(X) 그룹함수는 where절X
ex) select ENAME, SAL from EMP where SAL=(select min(SAL) from EMP);
<5> variance(컬럼명)
ex) select variance(COMM) from EMP;
<6> stddev(컬럼명)
ex) select stddev(COMM) from EMP;
6) Question
<1> 부서별 평균연봉을 출력(*)
ex) select avg(SAL) from EMP where DEPTNO=10
union
select avg(SAL) from EMP where DEPTNO=20
union
select avg(SAL) from EMP where DEPTNO=30;
Err) select DEPTNO, avg(SAL) from EMP;
ex) select DEPTNO, avg(SAL) from EMP group by DEPTNO;
ex) select DEPTNO, round(avg(SAL)) from EMP group by DEPTNO;
<2> 급여 2000 이상인 사원들의 부서별 평균급여의 반올림값
ex) select DEPTNO, round(avg(SAL)) from EMP
where SAL>=2000 group by DEPTNO;
<3> 급여 2000 이상인 사원들의 부서별 평균급여의 반올림값 ( 부서번호의 오름차순 정렬 )
ex) select DEPTNO, round(avg(SAL)) from EMP
where SAL>=2000 group by DEPTNO order by DEPTNO;
<4> 급여 2000 이상인 사원들의 부서별 평균급여의 반올림값 (급여가 높은 순 정렬)
ex) select DEPTNO, round(avg(SAL)) from EMP
where SAL>=2000
group by DEPTNO order by round(avg(SAL)) desc;
ex) select DEPTNO, round(avg(SAL)) aa from EMP
where SAL>=2000
group by DEPTNO order by aa desc;
ex) select DEPTNO, round(avg(SAL)) from EMP
where SAL>=2000
group by DEPTNO order by 2 desc;
<5> 급여 1000이상인 사원들의 부서별 평균급여의 반올림값을 부서번호로 내림차순 정렬하라
( 단, 부서별 평균급여가 2000 이상인 값만 출력! )
ex) select DEPTNO, round(avg(SAL)) from EMP
where SAL>=1000
group by DEPTNO
having round(avg(SAL))>=2000
order by DEPTNO desc;
Err) select DEPTNO, round(avg(SAL)) aa from EMP
where SAL>=1000
group by DEPTNO
having aa>=2000
order by DEPTNO desc; --주의: having절에서는 alias 사용불가!
<6> 급여 1000이상인 사원들의 부서별 평균급여의 반올림값을 부서번호로 내림차순 정렬하라
( 단, 부서번호가 20이상인 값만 출력! )
ex) select DEPTNO, round(avg(SAL)) from EMP
where SAL>=1000
group by DEPTNO
having DEPTNO>=20
order by DEPTNO desc;
ex) select DEPTNO, round(avg(SAL)) from EMP
where SAL>=1000
group by DEPTNO
having DEPTNO>20 or DEPTNO=20 --여러개의 조건이 가능
order by DEPTNO desc;
ex) select DEPTNO, round(avg(SAL)) from EMP
where SAL>=1000 and DEPTNO>=20
group by DEPTNO
order by DEPTNO desc;
<7> 각 부서별 같은 업무를 하는 사원의 인원수를 구하여 부서번호, 업무명, 인원수를 출력하라
(단, 부서번호과 업무명으로 각각 내림차순 정렬!)
ex) select DEPTNO, JOB, count(*) from EMP
group by DEPTNO, JOB
order by DEPTNO desc, JOB desc;
7) 일반화
<1> 순서( SF-WGHO )
1> select -> from -> where -> group by -> having -> order by
<2> 그룹함수는 where절에 사용 불가!
<3> having절에서는 alias 사용불가!
'SQL > Oracle' 카테고리의 다른 글
| Oracle 06. DML ( Data Manipulation Language ) (0) | 2022.03.31 |
|---|---|
| Oracle 05. Join & SubQuery (0) | 2022.03.28 |
| Oracle 03. DQL - Base (0) | 2022.03.20 |
| Oracle 02. 개요 (0) | 2022.03.20 |
| Oracle 01. 설치 및 접근 (0) | 2022.03.11 |