[Oracle] 문자열함수, 수식처리, 날짜처리, 그룹함수

-오라클의 함수는 함수의 이름만 사용해서 호출하면 실행되고 결과가 출력되는 건 아님
-select 구문을 형식을 빌려서 select와 from 사이에서 함수를 호출하고 from 뒤에는 함수실행의 임시테이블 역할을 하는 dual을 사용함

샘플 테이블인 dual 테이블

-dual: 테이블이 대상이 아닌 연산을 하려고 할 때 from 다음에 형식적으로 붙이는 실제로는 없는 테이블의 이름

select*from dual;

임시 데이터 출력

-실제테이블 원본을 대상으로 한 연산이 아닌 단순 데이터 연산에는 아래와 같이 사용함

select 1234*1234 as 연산결과 from dual;

 

---

문자열 관련 함수

-현재 사용되고 있고 사용될 모든 함수들은 괄호 안에 쓴 입력값이 필드명일 때 from 뒤에 테이블 이름을 사용하며 연산의 대상은 괄호 안에 쓰인 필드의 실제값이 됨

-다만 위와 같이 select와 from 사이에서 일반 데이터를 대상으로 하여 함수가 쓰인다면 form 뒤에 dual이 쓰여지게 됨

 

1. lower(): 모든 문자를 소문자로 변환

select lower('Hong Kil Dong') as "소문자" from dual;

2. upper(): 모든 문자를 대문자로 변환

select upper('Hong Kil Dong') as "대문자" from dual;

3. initcap(): 첫자만 대문자로 변환

select initcap('hong kil dong') as "첫글자만 대문자" from dual;

4. concat(): 문자열 연결

select concat('DB IT','아카데미') as 문자열연결 from dual;
select 'DB IT'||'아카데미') as 문자열연결 from dual;

5. length(): 문자열의 길이

select length('DB IT 아카데미') as 글자수, length('The DB IT') 
as 글자수 from dual;
select ename, length(ename) from emp;

6. substr(): 문자열 추출(데이터, 인덱스(1), 카운트)
-substr의 경우 자바의 substring처럼 시작번째부터 끝번째 +1 아니라 시작번째부터 글자수를 나타냄.

select substr('홍길동 만세',2,4) from dual;
-- 2번째 글자부터 네글자 표시(공백포함)

-- booklist 테이블에서 grade가 '18' 인 레코드의 도서제목에   
-- '-18+' 이 글자를 붙임
update booklist set subject = concat( subject, '-18+' ) 
where grade='18';
-- booklist 테이블에서 grade가 '18' 인 레코드의 도서제목에서 
'18+'를 '20+'로 바꿈
update booklist set subject = 
substr( subject, 1, length(subject)-3  ) where grade='18';
update booklist set subject = 
concat( subject , '20+' ) where grade='18';
update booklist set subject = 
concat(  substr( subject, 1, length(subject)-3  ) , '20+'  ) 
where grade='18';

7. instr() 포함되어 있는 문자열 시작 위치

select instr('홍길동 만세 동그라미','동') from dual;
-- '홍길동 만세 동그라미' 안에 '동'이 몇번째 글자로 위치하는지 계산
-- 여러개 포함한다면 첫번째 위치가 몇번째 글자인지 계산

 

8. lpad(), rpad(): 자리 채우기

select ipad('Oracle',20,'#') from dual; --##############Oracle
select rpad('Oracle',20,'*') from dual; --Oracle**************

9. trim(): 컬럼이나 대상 문자열에서 특정 문자가 첫번째 글자이거나 마지막 글자이면 잘라내고 남은 문자열만 반환

select trim('a',from'aaaOracleaaaaaaa') as result from dual; 
-- Oracle 출력
select trim('',from' Oracle        ') as result from dual; 
-- Oracle 출력

 

---

수식 처리 관련 함수

 

1. round(): 반올림(음수: 소수점 이상 자리)

select round(12.3456,3) from dual; --12.346
-- 12.3456: 반올림하려는 대상 숫자		
-- 3: 반올림하여 표시하고자하는 마지막 자리수

-- 3: 소수점 넷째자리에서 반올리하여 셋째자리까지 남김
-- 2: 소수점 셋째자리에서 반올리하여 둘째자리까지 남김
-- 1: 소수점 둘째자리에서 반올리하여 첫째자리까지 남김
-- 0: 소수점 첫째자리에서 반올리하여 소수점 자리수 없앰
-- -1: 1의 자리에서 반올림하여 10자리까지 남김
-- -2: 10의 자리에서 반올림하여 100자리까지 남김
-- -3: 100의 자리에서 반올림하여 1000자리까지 남김

select round(1728.9382, 3) from dual; --1728.938
select round(1728.9382, 2) from dual; --1728.94
select round(1728.9382, 1) from dual; --1728.9
select round(1728.9382, 0) from dual; --1729
select round(1728.9382, -1) from dual; --1730
select round(1728.9382, -2) from dual; --1700
select round(1728.9382, -3) from dual; --2000

2. abs(): 절대값

select abs(-10) from dual; --10

3. floor(): 소수점 아래 절사(반올림 없음)

select floor(12.94567) from dual; --12

4. trunc(): 특정 자리 자르기(반올림 없음. 3은 남기고 싶은 소수점 아래 자리수)

select trunc(12.34567,3) from dual; --12.345

5. mod(): 나머지

--8을 5로 나눈 나머지
select mod(8,5) from dual; --3

 

---

날짜 처리 관련 함수

 

1. sysdate: 날짜

select sysdate from dual; --오늘 날짜와 현재 시간

 

---

GroupFunction

-지정된 필드의 값 전체를 대상으로 한번 계산하는 함수

 

1. sum(): 특정 필드의 합계

-- 대여 가격의 합계
select sum(rentprice) as "대여가격 합계" from booklist
-- 입고가격이 18000원 이상인 책들의 대여가격 합계
select sum(rentprice) as "대여가격 합계 " from booklist 
where inprice >=18000

2. count(*): 필드내의 데이터 갯수(레코드 갯수)

-- 전체 회원수
select count(*) as "회원인원수"	 from memberlist;
-- 사은포인트가 2000 이상인 회원의 인원수
select count(*) as "회원인원수" from memberlist where bpoint >=150

3. avg: 평균

select round(avg(필드명),0) from 테이블명;

4. max: 최대값

select max(필드명) from 테이블명;

5. min: 최소값

select min(필드명) from 테이블명;

6. variance(분산), stddev(표준편차)

select variance(필드명), stddev(필드명) from 테이블명;

7. group by: 하나의 필드를 지목해서 같은 값끼리 그룹을 형성한 결과를 도출함
-group by 절에는 select와 from 사이에 쓰여진 함수를 제외한 모든 필드를 반드시 써줘야 함

-- 전체 대여건수
select count(*) from rentlist; 
-- 각 회원별 대여건수(한번도 대여 못한 회원 제외)
select mnum, count(*) from rentlist group by mnum;

-- rentlist 날짜별 할인 금액의 평균
select rentdate, round(avg(discount),0) from rentlist
group by rentdate order by rentdate desc;

-- rentlist 날짜별 대여 건수
select rentdate, count(*) from rentlist
group by rentdate order by rentdate desc;

8. Having절

-- 그룹핑된 내용들에 조건을 붙일 때 날짜별 할인금액의 평균을 출력함. 
-- 다만 그 평균 금액이 100미만인 그룹만 출력
select rentdate as 날짜, avg(discount) as 할인평균 from rentlist
group by rentdate
having avg(discount)<100 --그룹 안에서 실행된 조건