- DB에서 함수는 반드시 한 개의 리턴값이 있어야 한다.
단일행 함수
- input 하나의 행 → output 하나
- 데이터 종류별로 여러 개의 함수가 있다.
다중행 함수
- input 여러 개의 행 → output 하나
- 계산과 관련된 함수가 주를 이룬다.
- count, max, min, sum
- 계산함수, 그룹함수 라고도 한다.
단일행 함수
1. 수학 함수
반올림 관련 함수
round자릿수 지정 반올림trunc자릿수 지정 버림floor최하 정수 리턴ceil최상 정수 리턴
예제
- 사원명, 급여, 월급(급여/12), 세금(급여의 3.3%)을 추출. 단 월급은 10단위에서 반올림하고, 세금은 원단위에서 절삭
select
saname "사원명",
sapay "급여",
round(sapay / 12, 3)*10000 "월급",
trunc(sapay*0.033, 4)*10000 "세금"
from sawon;
select
saname "사원명",
sapay "급여",
round(sapay / 12, 3) "월급",
trunc(sapay*0.033, 4) "세금"
from sawon;
2. 문자열 함수
길이
lenght문자열 길이lengthb문자열 길이 (바이트 단위)
공백 제거
trim양쪽 공백 제거rtrim오른쪽 공백 제거ltrim왼쪽 공백 제거- 문자열 제거
select trim('k' from 'kibwa kibwa') from dual;
대소문자 전환
lower모든 문자를 소문자로upper모든 문자를 대문자로initcap단어의 첫글자만 대문자로
문자열 대체
select replace('i am a kibwa', 'a', 'x') from dual;
채우기 함수
lpadrpad- *pad(’데이터’, 전체크기, ‘채울 문자열’)
select lpad(saname, 20, '#') a, rpad(saname, 20, '&') a from sawon;예제
- 사원명, 급여, 급여현황(급여가 100단위에 ‘*’ 하나) 추출
col 급여현황 format a50
select
saname,
sapay,
lpad('*', (sapay / 100), '*') "급여현황"
from sawon;
SANAME SAPAY 급여현황
---------- ---------- --------------------------------------------------
홍길동 5000 **************************************************
한국남 3000 ******************************
이순신 3500 ***********************************
이순라 1200 ************
놀기만 2300 ***********************
류별나 1600 ****************
채시라 3400 **********************************
이성계 2803 ****************************
무궁화 3000 ******************************
임꺽정 2200 **********************
깨똥이 4500 *********************************************문자열 찾기
- instr
문자열 추출
- substr(문자열, 시작자릿수, 찾을문자개수) → 시작자릿수 문자부터 n개의 문자열 반환
예제
- 고객명, 전화번호, 주민번호1(**-1234567), 주민번호2(123456-***)
select lpad(substr(gojumin, 7, 14), 14, '*') "주민번호 1" from gogek;
select rpad(substr(gojumin, 1, 7), 14, '*') "주민번호 2" from gogek;
select goname "고객명", gotel "전화번호",
lpad(substr(gojumin, 7, 14), 14, '*') "주민번호 1",
rpad(substr(gojumin, 1, 7), 14, '*') "주민번호 2"
from gogek;decode
decode(deptno, 10, '영업', 20, '관리', 30, '총무', '전산')CASE
select saname, deptno,
case deptno when 10 then '영업'
when 20 then '관리'
when 30 then '총무'
else '전산' end "부서명"
from sawon; 예제
- 고객명, 전화번호, 성별 추출 (decode)
select
goname "고객명",
gotel "전화번호",
decode(substr(gojumin, 8, 1),
1, '남자', 2, '여자', '선택안함') "성별"
from gogek;- 사원명, 급여, 보너스 출력(case)
- 단, 보너스는 급여가 1000미만이면 급여의 10%, 1000~2000이면 급여의 15%, 2000 초과하면 급여의 20%
select
saname,
sapay,
case when sapay < 1000 then floor(sapay * 0.1)
when sapay <= 2000 then floor(sapay * 0.15)
when sapay > 2000 then floor(sapay * 0.2)
else 0
end "보너스"
from sawon;4. 변환 함수
- to_char(’날짜’ | ‘숫자’ , ‘형식’)
예제
select
to_char(sysdate, 'yy') "yy",
to_char(sysdate, 'year') "year", // twenty twenty-two
to_char(sysdate, 'month') "month",
to_char(sysdate, 'mon') "mon",
to_char(sysdate, 'mm') "mm",
to_char(sysdate, 'q') "q", // 분기
to_char(sysdate, 'd') "d", // 요일을 숫자로 일 - 0
to_char(sysdate, 'day') "day", // 수요일
to_char(sysdate, 'dy') "dy", // 수
to_char(sysdate, 'dd') "dd",
to_char(sysdate, 'ddd') "ddd", // 201 - 365일 기준
to_char(sysdate, 'hh') "hh",
to_char(sysdate, 'hh24') "hh24",
to_char(sysdate, 'miss') "miss",
to_char(sysdate, 'am') "am",
to_char(sysdate, 'pm') "pm"
from dual;
select
to_char(sysdate, 'fmyyyy.mm.dd day am hh:mi:ss')
from dual;
// fm - 07월 -> 7월
select
to_char(sysdate, 'fmyyyy.mmsp.dd day am hh:mi:ss')
from dual;
// sp - 영문으로 추출
select
to_char(sysdate, 'fmyyyy.mm.ddth day am hh:mi:ss')
from dual;
// th - th붙이기
- 오늘 날짜를 [2022년 7월 20일 수요일] 형태로 추출
select to_char(sysdate, 'fm yyyy"년" mm"월" dd"일" day') from dual;숫자 관련 형식 종류
- 9는 해당 자리에 데이터가 있는 경우 출력하고, 없으면 출력하지 않는다.
- 0은 해당 자리의 데이터를 무조건 출력한다.
- FM은 9로 치환된 소수점 이상의 공백 및 소수점 이하의 9을 제거한다.
- FM이 없으면 숫자 소수점 이상은 공백으로, 소수점 이하는 0으로 표시된다.
SQL> select to_char(sapay, '$9,990') from sawon;
-- 오른쪽으로 정렬된 상태
TO_CHAR
-------
$5,000
$3,000
$2,000
$400
$1,003
SQL> select to_char(sapay, 'fm$9,990') from sawon;
-- fm 은 불필요한 공백을 제거한다.
-- 왼쪽 정렬을 해 준다
TO_CHAR
-------
$5,000
$3,000
$2,000
$400
$1,0035. 날짜 함수
- last_day(A) - A 월의 마지막 날짜
- next_day(A, ‘월’) - 처음으로 돌아오는 요일의 날짜
- add_months(A, n) - A로부터 n개월 뒤의 날짜
SQL> select
last_day(sysdate),
next_day(sysdate, '월'),
add_months(sysdate, 3)
from dual;
LAST_DAY NEXT_DAY ADD_MONT
-------- -------- --------
22/07/31 22/07/25 22/10/20- months_between(A, B) - A, B의 개월 수 차이
- 소수점 아래는 의미 없는 쓰레기값
SQL> select months_between(sysdate, '97/02/26') from dual;
MONTHS_BETWEEN(SYSDATE,'97/02/26')
----------------------------------
304.826807예제
- 사원명, 입사일, 근무기간 (xx년 xx개월)
- col 근무기간 format a20
select
saname,
sahire,
floor(months_between(sysdate, sahire)/12)||'년 '
||floor(mod(months_between(sysdate, sahire), 12))||'개월'
"근무기간"
from sawon;
SQL> select
2 saname,
3 sahire,
4 floor(months_between(sysdate, sahire)/12)||'년 '
5 ||floor(mod(months_between(sysdate, sahire), 12))||'개월'
6 "근무기간"
7 from sawon;
SANAME SAHIRE 근무기간
---------- -------- --------------------
홍길동 80/01/01 42년 6개월
한국남 88/11/01 33년 8개월
이순신 85/03/01 37년 4개월
이순라 90/05/01 32년 2개월날짜 연산
- to_yminterval(’연-월’)
select
saname,
sahire,
sahire + to_yminterval('01-06') "승급날짜"
from sawon;
SANAME SAHIRE 승급날짜
---------- -------- --------
홍길동 80/01/01 81/07/01
한국남 88/11/01 90/05/01
이순신 85/03/01 86/09/01
이순라 90/05/01 91/11/01- to_dsinterval
select
saname,
sahire,
sahire + to_dsinterval('100 00:00:00') "계약종료"
from sawon;
SANAME SAHIRE 계약종료
---------- -------- --------
홍길동 80/01/01 80/04/10
한국남 88/11/01 89/02/09
이순신 85/03/01 85/06/09
이순라 90/05/01 90/08/09- round, trunc - 지정한 기준의 하위 단계에서 반올림/버림
select
saname,
sahire,
round(sahire, 'year')
from sawon;
SANAME SAHIRE ROUND(SA
---------- -------- --------
홍길동 80/01/01 80/01/01
한국남 88/11/01 89/01/01
이순신 85/03/01 85/01/01
이순라 90/05/01 90/01/01select
trunc(sysdate, 'month')
from dual;
TRUNC(SY
--------
22/07/016. rank 함수 (통계함수)
- rank() over() - 순위 추출
rank() over(**[partition by 컬럼명]** order by 컬럼명 [(asc)|desc]) "석차" - 입사일 순서대로 랭킹 추출
select
saname,
sahire,
rank() over(order by sahire) "근무연차 랭킹"
from sawon;
SANAME SAHIRE 근무연차 랭킹
---------- -------- -------------
홍길동 80/01/01 1
김유신 81/04/01 2
이미라 83/04/01 3
이성계 84/05/01 4
무궁화 84/08/01 5- 부서별 급여를 많이 받는 순서대로 랭킹 추출
- null을 포함하지 않도록 주의
select
deptno,
saname,
sapay,
rank() over(partition by deptno order by sapay desc) "부서별 급여 랭킹"
from sawon
where sapay is not null;
DEPTNO SANAME SAPAY 부서별 급여 랭킹
---------- ---------- ---------- ----------------
10 홍길동 5000 1
10 깨똥이 4500 2
10 무궁화 3000 3
10 최진실 2000 4
10 류명한 1800 5
10 무궁화 1100 6
20 이순신 3500 1
20 채시라 3400 2
20 한국남 3000 3
20 놀기만 2300 4
20 임꺽정 2200 5
20 류별나 1600 6
20 이순라 1200 7
30 공부만 4003 1
30 이성계 2803 2
30 이미라 2503 3
30 채송화 1703 4
30 공부해 1303 5
30 강감찬 1003 6
30 김유신 400 7❓order by 를 제외하면 어떤 기준으로 정렬되어 출력될까? → rowid
- rowid = 행 주소
- 18자리 주소 중 마지막 3자리
- 분산 쿼리 - 100만개의 데이터가 있으면 10만개로 각각 나누어 저장해두고, 데이터를 찾을 때 10만개씩 10개의 묶음을 동시에 찾는다.
- 대량 데이터는 쪼개어 저장해야 한다 → partitioning table
SQL> select rowid, sabun, saname from sawon;
ROWID SABUN SANAME
------------------ ---------- ----------
AAASNVAAEAAAAImAAA 1 홍길동
AAASNVAAEAAAAImAAB 2 한국남
AAASNVAAEAAAAImAAC 3 이순신
AAASNVAAEAAAAImAAD 5 이순라
AAASNVAAEAAAAImAAE 7 놀기만7. Group By
- 계산함수, 집계함수 사용
- sum, avg, count, max, min
- group by 절 : 그룹화 절
- having 절 : group by 에 대한 조건절
select
from
where
group by
having
order by - 직책별 인원수와 급여합계를 추출
select
sajob,
count(*),
sum(sapay)
from sawon
group by sajob;
SAJOB COUNT(*) SUM(SAPAY)
---------- ---------- ----------
과장 5 15903
사원 8 12606
대리 3 6006
회장 1 5000
부장 3 8803- 성별, 직책별로 평균 급여와 인원수를 출력하되, 사원과 대리 직책만 추출, 인원수가 많은 순으로 정렬
select
sajob,
sasex,
count(*) "인원수",
avg(sapay) "평균급여"
from sawon
where sajob = '사원' or sajob = '대리'
group by sasex, sajob
order by 인원수 desc;
SAJOB SASE 인원수 평균급여
---------- ---- ---------- ----------
사원 남자 4 1226.5
사원 여자 4 1925
대리 여자 2 2103
대리 남자 1 1800- 성별, 직책별로 평균 급여와 인원수를 출력하되, 사원을 제외하고 집계 인원수가 2명 이하인 것만 추출
select
sasex,
sajob,
avg(sapay) "평균급여",
count(*) "인원수"
from sawon
where sajob != '사원'
group by sasex, sajob
having count(*) <= 2;
SASE SAJOB 평균급여 인원수
---- ---------- ---------- ----------
남자 회장 5000 1
여자 부장 3000 1
남자 대리 1800 1
남자 부장 2901.5 2
여자 과장 1950 2
여자 대리 2103 2- 입사년도별로 평균급여 추출
select
to_char(sahire, 'yyyy') "입사년도",
avg(sapay) "평균급여 "
from sawon
group by to_char(sahire, 'yyyy');
입사 평균급여
---- ----------
1980 5000
1981 400
1983 2503
1984 1951.5
1985 3500
1986 1003
1988 2167.66667
1989 1600
1990 2500
1991 2000
1992 1703rownum
- rownum : 추출되는 행의 순서를 의미
- 행을 제한할 때 (where 절) 사용
- 무조건 1부터 시작
select rownum, sabun, saname from sawon;
ROWNUM SABUN SANAME
---------- ---------- ----------
1 1 홍길동
2 2 한국남
3 3 이순신
4 5 이순라
5 7 놀기만
6 11 류별나
7 14 채시라
8 17 이성계select rownum, sabun, saname
from sawon
where rownum <= 3;
ROWNUM SABUN SANAME
---------- ---------- ----------
1 1 홍길동
2 2 한국남
3 3 이순신select rownum, sabun, saname
from sawon
where rownum > 3;
선택된 레코드가 없습니다.- 5명씩 평균급여와 최대급여 추출
select
ceil(rownum / 5) "group",
avg(sapay) "avg",
max(sapay) "max"
from sawon
group by ceil(rownum / 5);
group avg max
---------- ---------- ----------
1 3000 5000
2 2600.6 3400
4 1441.8 2503
3 2621.2 4500- 직책별, 성별 급여합계 추출
select
sajob,
sasex,
sum(sapay) "급여합계"
from sawon
group by sajob, sasex;
SAJOB SASE 급여합계
---------- ---- ----------
과장 남자 12003
부장 여자 3000
과장 여자 3900
대리 여자 4206
사원 여자 7700
대리 남자 1800
부장 남자 5803
사원 남자 4906
회장 남자 5000rollup & cube
- group by와 함께 사용
- rollup : 1차 그룹에 대한 집계
select
sajob,
sasex,
sum(sapay) "급여합계"
from sawon
group by rollup(sajob, sasex);
SAJOB SASE 급여합계
---------- ---- ----------
과장 남자 12003
과장 여자 3900
과장 15903
대리 남자 1800
대리 여자 4206
대리 6006
부장 남자 5803
부장 여자 3000
부장 8803
사원 남자 4906
사원 여자 7700
SAJOB SASE 급여합계
---------- ---- ----------
사원 12606
회장 남자 5000
회장 5000
48318- cube : rollup + 2차 그룹에 대한 집계
select
sajob,
sasex,
sum(sapay) "급여합계"
from sawon
group by cube(sajob, sasex);
SAJOB SASE 급여합계
---------- ---- ----------
48318 -- 총합
남자 29512 -- 남자합
여자 18806 -- 여자합
과장 15903 -- 과장합
과장 남자 12003
과장 여자 3900
대리 6006
대리 남자 1800
대리 여자 4206
부장 8803
부장 남자 5803
SAJOB SASE 급여합계
---------- ---- ----------
부장 여자 3000
사원 12606
사원 남자 4906
사원 여자 7700
회장 5000
회장 남자 5000- 단, NULL이 포함되어있는 경우 group by 에 의한 집계결과인지 일반집계결과인지 알 수 없다.
SAJOB SASE 급여합계
---------- ---- ----------
1000 -- sajob이 null인 경우가 따로 나온다.
49318
남자 1000
남자 30512
여자 18806
과장 15903
과장 남자 12003
과장 여자 3900
대리 6006
대리 남자 1800
대리 여자 4206
SAJOB SASE 급여합계
---------- ---- ----------
부장 8803
부장 남자 5803
부장 여자 3000
사원 12606
사원 남자 4906
사원 여자 7700
회장 5000
회장 남자 5000grouping을 사용해 이를 보완할 수 있다.- group by 집계결과이면 1, 일반집계결과이면 0
select
sajob,
sasex,
sum(sapay) "급여합계",
grouping(sajob) JOB,
grouping(sasex) SEX
from sawon
group by cube(sajob, sasex);
SAJOB SASE 급여합계 JOB SEX
---------- ---- ---------- ---------- ----------
1000 0 1
49318 1 1
남자 1000 0 0
남자 30512 1 0
여자 18806 1 0
과장 15903 0 1
과장 남자 12003 0 0
과장 여자 3900 0 0
대리 6006 0 1
대리 남자 1800 0 0
대리 여자 4206 0 0
SAJOB SASE 급여합계 JOB SEX
---------- ---- ---------- ---------- ----------
부장 8803 0 1
부장 남자 5803 0 0
부장 여자 3000 0 0
사원 12606 0 1
사원 남자 4906 0 0
사원 여자 7700 0 0
회장 5000 0 1
회장 남자 5000 0 0컬럼 그룹화
| 사원 | 대리 | 과장 | 부장 |
|---|---|---|---|
| 0000 | 0000 | 0000 | 0000 |
- 컬럼 제한이므로 select column from table 안에서 해결해야 한다.
- 조건명령(case, decode)와 계산함수를 사용한다. group by 사용 안함!
select
sum(decode(sajob, '사원', sapay, 0)) "사원",
sum(decode(sajob, '대리', sapay, 0)) "대리",
sum(decode(sajob, '과장', sapay, 0)) "과장",
sum(decode(sajob, '부장', sapay, 0)) "부장",
sum(decode(sajob, '이사', sapay, 0)) "이사"
from sawon;
사원 대리 과장 부장
---------- ---------- ---------- ----------
12606 6006 15903 8803- 직책별 인원수를 컬럼그룹화로 추출
select
sum(decode(sajob, '과장', 1, 0)) "과장",
sum(decode(sajob, '대리', 1, 0)) "대리",
sum(decode(sajob, '사원', 1, 0)) "사원",
sum(decode(sajob, '이사', 1, 0)) "이사"
from sawon;
과장 대리 사원 이사
---------- ---------- ---------- ----------
5 3 8 0
select
count(decode(sajob, '과장', 1)) "과장",
count(decode(sajob, '대리', 1)) "대리",
count(decode(sajob, '사원', 1)) "사원",
count(decode(sajob, '이사', 1)) "이사"
from sawon;
과장 대리 사원 이사
---------- ---------- ---------- ----------
5 3 8 0- 직책별 부서별 급여합계를 추출
select
sajob,
sum(decode(deptno, 10, sapay, 0)) "부서 10",
sum(decode(deptno, 20, sapay, 0)) "부서 20",
sum(decode(deptno, 30, sapay, 0)) "부서 30",
sum(decode(deptno, 40, sapay, 0)) "부서 40",
sum(sapay) "급여합계"
from sawon
where deptno is not null
group by sajob;
SAJOB 부서 10 부서 20 부서 30 부서 40 급여합계
---------- ---------- ---------- ---------- ---------- ----------
과장 4500 7400 4003 0 15903
사원 3100 6800 2706 0 12606
대리 1800 0 4206 0 6006
회장 5000 0 0 0 5000
부장 3000 3000 2803 0 8803- grouping sets 명령
select
sasex,
sajob,
deptno,
sum(sapay)
from sawon
group by grouping sets ((sasex, sajob), (sajob, deptno));
SASE SAJOB DEPTNO SUM(SAPAY)
---- ---------- ---------- ----------
남자 회장 5000
남자 사원 4906
여자 부장 3000
여자 사원 7700
남자 대리 1800
남자 1000
남자 부장 5803
남자 과장 12003
여자 과장 3900
여자 대리 4206
1000
SASE SAJOB DEPTNO SUM(SAPAY)
---- ---------- ---------- ----------
부장 20 3000
대리 30 4206
부장 10 3000
사원 10 3100
사원 20 6800
대리 10 1800
회장 10 5000
사원 30 2706
부장 30 2803
과장 10 4500
과장 30 4003
SASE SAJOB DEPTNO SUM(SAPAY)
---- ---------- ---------- ----------
과장 20 7400
개발자 헤이린