05.13(Oracle 내장 함수)

Function

오라클에 내장되어 있는 함수 
값(인자=파라미터)전달 호출 → 작업 수행 → 결과값 리턴 (오라클은 무조건 값을 리턴함)

함수 종류

1. 단일행 처리 함수 : 행단위로 실행되는 함수(행마다 한 번 씩 실행되기에 결과값도 행 갯수와 같음)
    a. 문자처리 함수
    b. 숫자처리 함수
    c. 날짜처리 함수
    d. 형변환 함수 *확실히 알아두기*
    e. 기타 함수
2. 그룹함수(다중행 처리 함수) : 여러행을 그룹지어 처리하는 함수

 

 

1. 단일행 처리함수

a. 문자처리 함수

length(문자열)
문자열의 길이를 리턴

select email, 
       length(email),  --가상컬럼으로 매행마다 결과값 리턴(단일행 함수) 
       lengthb(email), 
       emp_name, 
       length(emp_name), 
       lengthb(emp_name) 
from employee; 

select * 
from employee 
where length(email) > 15; 

instr(string, search[, position[, occurence]])  →indexOf와 비슷
stirng에서 search문자열의 인덱스를 리턴
position : 검색 시작위치
1-based index를 사용(자바와 다름!!!!!! 0부터 X)

select instr('바나나맛우유 딸기맛우유 초코맛우유', '우유'),     --여기서 우유라는 글자가 몇번째에 위치하냐 
       instr('바나나맛우유 딸기맛우유 초코맛우유', '우유', 5, 2),  --검색을 시작할 position정해줄 수 있음(5부터 검색해서 2번째 출연한 정보 조회) 
       instr('바나나맛우유 딸기맛우유 초코맛우유', '우유', -1)  --position값이 음수이면 뒤에서부터 검색 lastIndexOf와 비슷 
from dual; 

예제)email의 @의 위치를 표시하기

select email, 
       instr(email, '@')     --email에서 @의 위치가 어디인지 숫자로 출력
from employee; 

lpad(string, width [, padding])  → 대괄호는 옵션으로 생략 가능하다는 의미
주어진 길이의 문자열을 반환. 남는 영역은 padding 문자를 채움
padding 문자 기본값은 공백

select lpad(email, 20, '#'),  --20바이트가 모자라면 padding문자가 모자라는 갯수만큼 왼쪽으로 추가 
        rpad(email, 20, '#'),  --패딩문자 오른쪽으로 채움 
        lpad(email, 20), 
        rpad(email, 20) 
from employee;

rpad 

문자열을 지정된 숫자만큼의 크기로 설정하고, 지정한 문자를 오른쪽부 터 채워서 생성된 문자열을 리턴

 

rtrim

왼쪽부터 지정한 문자를 잘라내고 남은 문자를 리턴

select rtrim(email, '@naver.com')    --네이버 메일의 아이디만 출력
from employee;

 

ltrim

오른쪽부터 지정한 문자를 잘라내고 남은 문자를 리턴

 

trim

왼쪽/오른쪽/양쪽부터 지정한 문자를 잘라내고 남은 문자를 리턴

substr(string, position[, length])
지정문자열에서 시작위치부터 몇개의 문자를 잘라내여 반환 (substring비슷)

select substr('showmethemoney', 5, 2), 
       substr('showmethemoney', 5), 
       substr('showmethemoney', -10, 2)  --뒤에서부터 세어서 뒤로 두글자 
from dual; 

substrb

지정한 위치에서 지정한 바이트만큼 문자를 잘라내어 리턴

 

lower

전달받은 문자/문자열을 소문자로 변환하여 리턴

 

upper

전달받은 문자/문자열을 대문자로 변환하여 리턴 

select upper(email) --메일주소가 oracle.@naver.com 이라면 ORACLE@NAVER.COM출력
from employee;

 

initcap

전달받은 문자/문자열의 첫 글자를 대문자로, 나머지 글자는 소문자로 변환하여 리턴

select initcap(email) --메일주소가 oracle.@naver.com 이라면 Oracle@Naver.Com출력
from employee;

 

 

concat

인자로 전달받은 두 개의 문자/문자열을 합쳐서 리턴

select concat('oracle', '@naver.com')   --oracle@naver.com 출력
from employee;

 

 

replace

전달받은 문자열중에 지정한 문자를 인자로 전달받은 문자로 변환하여 리턴

select replace(emp_no, substr(emp_no,9,6), '******') → 890101-2*******
from employee;

예제)사원테이블에서 사원명중 성만 추출해서 중복없이 오름차순으로 조회하기

select distinct substr(emp_name, 1, 1) 성 
from employee 
order by 성;  --order by문법중 하나 

replace(string, str1, str2) str1 을 str2로 바꿔라 
select email,

        replace(email, 'gmail.com' 'www.naver.com')

from employee;

 

b. 숫자처리함수
mod(number, division)  : 자바의 %연산자 대신 사용

select mod(10,3),      --1출력
       mod(10,2)       --0출력
from dual; 

ceil | floor : 올림버림 함수

select ceil(123.456),              --124
       floor(123.456),             --123
       ceil(123.456*100)/100,      --123.46
       floor(123.456*100)/100      --123.45
from dual; 

round(number, position)  : 반올림

select round(123.456, 2),       --123.46
       round(123.456),          --123
       round(123.456, -1)       --120
from dual;

 
trunc(number, position)
숫자/날짜에 대해 버림기능

select trunc(123.456, 2),    --123.45
       trunc(123.456),       --123
       trunc(123.456, -1)    --120
from dual; 

c. 날짜처리 함수
sysdate, systimestamp

select sysdate,                                         --20/05/13
       to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss'),       --2020/05/13 17:52:51
       to_char(sysdate-1, 'yyyy/mm/dd hh24:mi:ss'),     --2020/05/12 17:52:51
       to_char(sysdate+1, 'yyyy/mm/dd hh24:mi:ss'),     --2020/05/14 17:52:51
       systimestamp                                     --20/05/13 17:52:51.343000000 +09:00
from dual;

add_months(date, number)
number 개월수를 의미함

select sysdate,                     
       add_months(sysdate, 1)       --20/06/13
from dual; 

months_between(date1, date2)  → 2개의 개월수 차이 반환
크리스마스로부터 남은 개월수 확인

select to_date('2020/12/25', 'yyyy/mm/dd') 크리스마스,  -- 날짜타입으로 명시적으로 변환하는 방법 
        months_between(to_date('2020/12/25', 'yyyy/mm/dd'), sysdate) "남은 개월수",  --소수점은 trunc를 이용해서 잘라주기 
        trunc(months_between(to_date('2020/12/25', 'yyyy/mm/dd'), sysdate),1) "남은 개월수" 
from dual; 

extract(type from date)
특정정보만 추출해서 숫자형으로 반환

select extract(year from sysdate) yyyy,  
       extract(month from sysdate) mm, 
       extract(day from sysdate) dd, 
       extract(hour from cast(sysdate as timestamp)) hh,  --sysdate를 timestamp형식으로 변환 
       extract(minute from cast(sysdate as timestamp)) mi, 
       extract(second from cast(sysdate as timestamp)) ss 
from dual;

 
예제)입사일에서 년월일정보 추출

select extract(year from hire_date)||'년'|| 
       extract(month from hire_date)||'월'|| 
       extract(day from hire_date)||'일' 
from employee; 

예제)2001년 입사자의 사원명, 입사일 조회

select emp_name, 
       hire_date 
from employee 
where extract(year from hire_date) = 2001; 

trunc(date[, type])
날짜정보의 시분초를 제외하고 가져올 수 있음

select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss'), 
       to_char(trunc(sysdate), 'yyyy/mm/dd hh24:mi:ss'),  --요걸 제일 자주 사용 
       to_char(trunc(sysdate, 'month'), 'yyyy/mm/dd hh24:mi:ss')  --month값 초기화(해당달의 1일로 초기화) 
from dual; 

*****d. 형변환함수 
/*
        th_char        to_date
        -------->      ------->
    number      string       date
         <-------      <-------
       to_number     to_char
*/

to_char(date | number, format)

날짜형 혹은 숫자형을 문자형으로 반환
format model

참고 사이트 : www.docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00216

fm : 포맷팅으로 생겨난 0, 공백등을 제거함

select to_char(sysdate, 'yyyy/mm/dd(dy) hh24:mi:ss'), 
       to_char(sysdate, 'yyyy/mm/dd(day) hh24:mi:ss'), 
       to_char(sysdate, 'fmyyyy"년" mm"월" dd"일"(day)')  --한글등은 쌍따옴표로 감싸 처리할 것 
from dual; 

select to_char(hire_date, 'yy/mon, day, dy')   -- 01/9월 , 토요일, 토
       to_char(number, [format])
	   to_char(salary, 'fmL999,999,999,999')   --￦8,000,000
	   to_char(salary, '000,000,000,000')      --000,008,000,000
from employeee;

예제)fm을 사용하여 세자리마다 콤마찍기
제공된 숫자보다 충분히 큰 포맷팅을 해야함!!
0 : 해당자릿수에 숫자가 존재하지 않으면 0으로 표시, 소수점 이하는 0으로 표시
9 : 해당자릿수에 숫자가 존재하지 않으면 공백으로 표시, 소숫점 이하는 0으로 표시

select to_char(1234567890, 'fmL999,999,999,999'),  --지역화폐표시 l,L --금액표시 이런식으로 많이 함 
       to_char(1234567890, '000,000,000,000'), 
       to_char(1234567890, '9,999')  --충분히 큰 포맷 모델 자릿수를 제공할 것 
from dual; 

to_number

문자형을 숫자형으로 반환
￦1,234,567,890를 숫자로 변환하기 

select to_number('￦1,234,567,890', 'L999,999,999,999')+10,   --1234567900
       '12345678'+100,                                        --12345778(자동형변환 가능)  
       to_number('10,000', '999,999')                         --10000
from dual;

to_date(string, format)
문자형 혹은 숫자로 쓰인 날짜 데이터를 날짜 타입으로 변환

select to_date('2020/02/02', 'yyyy/mm/dd')+1,     --20/02/02
	   to_date(20200202, 'yyyymmdd')              --20/02/02
from dual; 

2000년도 이후 입사자의 사번, 사원명, 입사일 조회(입사일 내림차순 정렬)

select to_date('2000', 'yyyy'), 
       trunc(to_date('2000', 'yyyy'),'year') 
from dual; 

select emp_id, 
       emp_name, 
       hire_date 
from employee 
where extract(year from hire_date) >= 2000  --hire_date > '2000/01/01'도 가능   => to_date('2000/01/01', 'yyyy/mm/dd') 
order by 3 desc;

 
날짜 연산
1일 2시간 3분 4초뒤를 날짜타입으로 조회

select to_char((sysdate+1) + (1/24*2) + (1/24/60*3) + (1/24/60/60*4), 'yyyy/mm/dd hh24:mi:ss'), 
       to_char((sysdate+1) + (2/24) + (3/24/60) + (4/24/60/60), 'yyyy/mm/dd hh24:mi:ss') 
from dual; 

e.기타함수

null 처리 함수
nvl(col, val) | nvl2(col, val1, val2)
col값이 null이 아니라면, val1을 리턴, null이라면, val2를 리턴 (유무에 따라 출력 필요한 경우 사용)

select nvl2('abc', 0, 1),          --0
       nvl2(null, 0,1)             --1
from dual; 

select emp_name, 
       (salary + (salary * nvl(bonus, 0))) * 12 "연봉계산 예시",  
       nvl2(bonus, '보너스 있음', '보너스 없음') 보너스유무 
from employee; 

조건함수
***decode(표현식, 값1, 결과값1, 값2, 결과값2, .......[, default]) 
성별 구분 

select emp_name, 
       emp_no, 
       substr(emp_no, 8,1), --1은 몇글자 가져올지 
       decode(substr(emp_no, 8,1), '1', '남', '2', '여', '3', '남', '4', '여') 성별,  --첫번째 표현식 검사 
       decode(substr(emp_no, 8,1), '1', '남', '3', '남', '여') 성별  --switch문처럼 생각하기 1과 3에 해당X면 기본값 여 
from employee; 

***case 
사용법1(decode처럼 사용)

case 표현식 
    when 값1 then 결과값1 
    when 값2 then 결과값2 
    else 기본값 
end  --반드시 적어야함

 

사용법2

case 
    when 조건식1 then 결과값1 
    when 조건식2 then 결과값2 
    ... 
    else 기본값 
end 

case 예시

select emp_name, 
       case substr(emp_no, 8,1) 
            when '1' then '남' 
            when '3' then '남' 
            else '여'   --생략시 null 
       end 성별, 
        
       case 
            when substr(emp_no, 8, 1) = '1' then '남' 
            when substr(emp_no, 8, 1) = '3' then '남' 
            else '여' 
        end 성별, 
         
        case 
            when substr(emp_no, 8, 1) in ('1','3') then '남' 
            else '여' 
        end 성별 
from employee;

 

2. 다중행 처리 함수(그룹함수)

하나 이상의 행을 그룹으로 묶어 연산하여 총합, 평균 등을 하나의 컬럼으로 리턴

 

sum

그룹의 누적 합계를 리턴

 

avg

그룹의 평균을 리턴

 

count

그룹의 총 개수를 리턴

 

max

그룹의 최대값을 리턴

 

min

그룹의 최소값을 리턴