1127

숫자함수

반올림함수: 반올림하여 지정한 자릿수까지 표현한 숫자를 반환

round(표현, 자릿수)  : round(123.456, 2) -> 1234.46

                      round(1234.567, 0) -> 1235 <- round(12345.567)

                      round(9876.54, -2) -> 9900

                      

버림함수: 버린결과의 데이터를 지정한 자릿수까지 표현하여 반환

trunc(표현, 자릿수) : trunc(123.456, 2) -> 1234.45

                     trunc(1234.567, 0) -> 1234 <- trunc(12345.567) 

                     trunc(9876.54, -2) -> 9800

                    

올림함수: 올림한 정수결과를 반환

ceil(표현) : 표현데이터와 같거나 큰수중에 가장 작은 정수를 반환

            ceil(123.456) -> 124

            

버림함수: 버림한 정수결과를 반환

floor(표현) : 표현데이터와 같거나 작은수중에 가장 큰 정수를 반환

              floor(123.456) -> 123

              

문자함수

문자연결함수: concat(표현1, 표현2)

             concat(표현1, concat(표현2, 표현3))

대/소문자화함수 : upper/lower     java: toUpperCase/toLowerCase

upper('abc') -> ABC

lower('ABC') -> abc

단어단위로 첫문자는 대문자화, 나머지는 소문자화하는 함수: initcap

initcap('abc ABC') -> Abc Abc

성이 king 인 사원의 사번, 성, 명 조회

select employee_id, last_name, first_name

from employees

where upper(last_name) = 'KING';

where lower(last_name) = 'king';

where initcap(last_name) = 'King';

특정문자를 채워져 지정한 길이의 문자결과를 반환: lpad/rpad(표현, 길이, 충전문자)

lpad('abc', 5, '*') -> **abc

특정문자를 제거한 결과를 반환: 제거할 문자는 한개의 문자만 지정가능

trim( 제거할 문자 leading/trailing/both from 표현)

both: default - 생략가능

공백문자:default- 제거할문자 생략가능

trim(leading 'a' from 'abccba') -> bccba

trim(both 'a' from 'abccba') -> bccb <- trim('a' from 'abccba') 

trim(' abccba ') -> abccba

지정한문자외의 문자가 나올때까지 지정한 문자를 제거한 결과를 반환: ltrim/rtrim

ltrim(표현, 제거문자의나열)

ltrim('abccba', 'ba') -> ccba

문자열에서 문자열의 일부를 반환: substr(표현, 시작위치, 반환문자열길이)

반환문자열길이 생략가능 : 문자열의 끝까지 반환

시작위치 음수지정가능: 오른쪽에서부터 시작위치를 찾는다.

substr('abccba', 2, 3) -> bcc

substr('abccba', 2) -> bccba

substr('abccba', -4, 3) -> ccb

substr('abccba', -4) -> ccba

문자열에서 특정문자열의 시작위치를 반환

: instr(표현, 찾는문자열, 찾기시작할위치, 몇번째)

몇번째 생략가능: default 1

찾기시작할위치 생략가능: default 1

찾기시작할위치 음수지정 가능: 오른쪽에서부터 찾기 시작한다.

instr('abccbaabccba', 'ba', 1, 1) -> 5 <- instr('abccbaabccba', 'ba', 1)

<- instr('abccbaabccba', 'ba')        

instr('abccbaabccba', 'ba', 1, 2) -> 11 

instr('abccbaabccba', 'ba', -1, 1) -> 11

instr('abccbaabccba', 'ba', -1, 2) -> 5

이메일에서 아이디를 조회    아이디         서비스제공자 

hong2017@naver.com   -> hong2017(1~8)  ->   naver.com(10~끝)

jeon@chosun.ac.kr    -> jeon(1~4)      ->   chosun.ac.kr(6~끝)

select substr('hong2017@naver.com', 1, 

                instr('hong2017@naver.com','@')-1 ) hong_id, 

       substr('hong2017@naver.com',

                instr('hong2017@naver.com','@')+1 ) hong_servicer,         

       substr('jeon@chosun.ac.kr', 1, 

                instr('jeon@chosun.ac.kr','@')-1 ) jeon_id ,

       substr('jeon@chosun.ac.kr',

                instr('jeon@chosun.ac.kr','@')+1 ) jeon_servicer         

from dual;       

사원에 대해 

사번, 성, 전화번호에서의 국번, 전화번호에서의 개인번호 조회

전화번호에서           국번      전화번호에서의 개인번호  

02.1234.8648          -> 02      -> 8648(9~끝)

061.2452.7412         -> 061     -> 7412(10~끝)

0651.2452.7412.412772 -> 0651     -> 412772(16~끝)

062.2452.7412.412772 -> 0652     -> 412772(15~끝)

select employee_id, last_name , phone_number, 

       substr(phone_number, 1, instr(phone_number, '.')-1 ) local_number,

       substr(phone_number, instr(phone_number, '.', -1)+1 ) private_number

from employees;

* 날짜함수

sysdate: DB가 설치된 시스템의 현재 날짜정보를 반환하는 함수

select sysdate

from dual;

desc v$nls_parameters

select * from v$nls_parameters;

nls_date_format: RR/MM/DD 

날짜 +,- 숫자 -> 날짜

오늘날짜 - 1 -> 어제날짜

오늘날짜 + 1 -> 내일날짜

오늘날짜 + 1/24  시간후

select sysdate+1 tommorrow, sysdate-1 yesterday, sysdate+ 1/24 

from dual;

뒷날짜 - 전날짜 : 일수의 차이

어제날짜 - 오늘날짜 -> 1일 

select sysdate - '17/09/25'

from dual;

오늘로부터 1달전: 오늘 - 30 : 한달전으로 지정하기 힘들다

1. 특정날짜로부터 지정한 개월수 이후/이전에 해당하는 날짜를 반환하는 함수

add_months(날짜, +,-숫자)

select add_months(sysdate, 6) after6 , 

       add_months(sysdate, -6) before6

from dual;

2. 두 날짜사이의 개월수의 차이를 반환하는 함수

months_between(날짜1, 날짜2): 첫파라미터가 뒷 날짜

select months_between(sysdate, '17/09/25') passed, 

       months_between('18/03/06', sysdate) remain

from dual;

사번, 성, 근무개월수 조회

select employee_id, last_name, months_between(sysdate, hire_date) works_months

from employees;

3. 지정한 날짜의 달의 마지막 날짜를 반환하는 함수

: last_day(날짜)

이번달 마지막날, 올 2월(오늘로부터 9개월전)의 마지막 날

select last_day(sysdate), last_day(  add_months(sysdate,-9) ) this2

from dual;

사번, 성, 입사일자, 입사한 달의 마지막날짜 조회

select employee_id, last_name, hire_date, last_day(hire_date) hire_last

from employees;

4. 지정한 날짜 후로 처음 나오는 지정요일의 날짜를 반환하는 함수

next_day(날짜, 요일)

요일: 일요일,......., 토요일 <- sunday, .., saturday

      일, .....,  토       <- sun, .... , sat

      1,  ....  , 7

      

오늘 후로 나오는 첫번째 월요일, 첫번째 수요일

select next_day(sysdate, '월요일') n1, 

       next_day(sysdate, '수') n2, 

       next_day(sysdate, 1) n3

from dual;

5. 반올림함수/버림함수: round/trunc(날짜, 표현할날짜기준)

날짜기준: 년도 - yyyy, yy, rr, rrrr 

               월에서 반올림 1~6 7~12 

         월 - mm, 영문인경우(month, mon)

                일에서 반올림 1~15 16~31 

         요일 - day, dy

                일요일~수요일 정오, 수요일 정오~토요일

         일 - dd

                자정시~정오, 정오~자정

round(12.456, 2) - 0~9 <- 12.46

오늘날짜를 반올림하여 년도까지 표현, 월까지 표현, 요일 표현, 일 표현

select round(sysdate, 'yyyy') r1/*18-01-01*/,

       round(sysdate, 'mm') r2 /*17-12-01*/,

       round(sysdate+3, 'day') r3 /*17-12-03 일요일*/,

       round(sysdate+1/24, 'dd') r4 /*17-11-28*/

from dual;

select trunc(sysdate, 'yyyy') r1/*17-01-01*/,

       trunc(sysdate, 'mm') r2 /*17-11-01*/,

       trunc(sysdate+3, 'day') r3 /*17-11-26 일요일*/,

       trunc(sysdate+1/24, 'dd') r4 /*17-11-27*/

from dual;

* 변환함수: to_number, to_char, to_date

   to_char    to_date

숫자  ->  문자  -> 날짜

number character  date

숫자  <-  문자  <- 날짜

 to_number    to_char

 

select 10+2 r1, 10+to_number('20') r2, '10'+'20' r3

from dual;

to_number: 문자->숫자 : '20', 'abc'불가

to_char: 숫자->문자, 날짜->문자

to_char(표현, 포맷형식): 숫자->문자 

포맷형식: 9(공백채워짐), 0(0채워짐), , .  통화기호L

  

12345000 -> 12,345,000

select to_char(12345000, '9999999999999') c1, 

       to_char(12345000, '0000000000000') c2 ,

       to_char(12345000, 'L9,999,999,999,999') c3       

from dual;

to_char: 날짜->문자

to_char(날짜표현, 포맷형식)

포맷형식: 년도 yyyy, yy, rrrr, rr

         월 mm, 

         요일 day, dy

         일 dd

         시 hh 분 mi 초 ss

select sysdate, to_char(sysdate, 'yyyy-mm-dd day hh:mi:ss') today

from dual;

         

문자, 날짜 : ''

to_date(문자, 포맷형식):문자(날짜형식을가진문자만가능) -> 날짜         

select to_date('16-11-16') d1/*17/11/26*/,

       to_date('12-11-10', 'rr-mm-dd hh:mi:ss') d2 /*12/11/10*/,

       to_date('12-11-10', 'mm-rr-dd') d3/*11/12/10*/,

       to_date('12-11-10', 'dd-mm-rr') d4/*10/11/12*/

from dual;

null: 비교불가, 산술연산불가

사번, 성, 월급여 조회

커미션을 받는 경우 월급여+커미션금액 을 월급여로 볼 수 있다.

커미션금액 = 월급여*커미션%

select employee_id, last_name,salary, commission_pct,

       salary+ salary*nvl(commission_pct,0) tot_sal

from employees;

* null 관련함수

1. nvl(표현, 반환데이터) 

  : 데이터값이 null인 경우 두번째 파라미터로 null을 대체하여 반환하는 함수

    데이터값이 null이 아닌 경우 첫번째 파라미터인 표현이 그대로 반환 

    

2. nvl2(표현, null이아닌경우반환데이터, null인경우반환데이터)

   3번째 파라미터의 데이터타입은 두번째 파라미터의 타입과 일치해야 한다.

 commission_pct가 있는 사원은 총급여=급여+commission금액

 commission_pct가 없는 사원은 총급여=급여

select employee_id, last_name, salary, commission_pct,

       nvl2(commission_pct, '급여+commission금액', '급여') tot_sal

from employees;

사번, 성, 부서코드, 매니저존재여부 조회

매니저존재여부는 사원을 관리하는 관리자가 있으면 관리자있음 으로

                             관리자가 없으면 관리자없음 으로 표현한다.

select employee_id, last_name, department_id, 

       nvl2(manager_id,'ㅠㅠ' , '^^') manager_exists

from employees;       

null, ''

3. coalesce : null 이 아닌 최초의 데이터를 반환하는 함수

coalesce(표현, 표현이 null일때 반환데이터1, 반환데이터2, 반환데이터3, .. )

select coalesce('a' , 'b', null, 'd') c1 /*a*/,

       coalesce('' , 'b', null, 'd') c2 /*b*/,

       coalesce('' , null, null, 'd') c3 /*d*/

 from dual;

 

연락처         홍길동           전우치              심청 

휴대전화    010-2514-4121                         010-2145-4111    

집전화      062-4852-4222     062-4521-4155          

사무실전화                     032-4152-8752      02-8411-1411

기타전화

coalesce(휴대전화, 사무실전화, 집전화) phone

조건문

if( 조건식1 ) {

 ...

}else if( 조건식2 ){

 ... 

}else if( 조건식3 ){

 ... 

}else{

 ...

}

decode(표현, 비교데이터값1, 반환데이터값1,

            비교데이터값2, 반환데이터값2, 

            비교데이터값3, 반환데이터값3,

            ....

            , default반환데이터값)

10부서원에게 급여의 10%를 보너스로 지급

20부서원에게 급여의 20%를 보너스로 지급

30부서원에게 급여의 30%를 보너스로 지급

그 외 부서원에게는 급여의 5%를 보너스로 지급

사번, 성, 급여, 부서코드, 보너스 조회

select employee_id, last_name, salary, department_id, 

       decode(department_id, 10, salary*0.1,

                             20, salary*0.2,

                             30, salary*0.3, salary*0.05) bonus

from employees;                             

10부서원에게 급여의 10%를 보너스로 지급

20부서원에게 급여의 20%를 보너스로 지급

30부서원에게 급여의 30%를 보너스로 지급

select employee_id, last_name, salary, department_id, 

       decode(department_id, 10, salary*0.1,

                             20, salary*0.2,

                             30, salary*0.3) bonus

from employees;                             

case ~ end 

case 표현 when 비교데이터값1 then 반환데이터1

         when 비교데이터값2 then 반환데이터2

         when 비교데이터값3 then 반환데이터3

         ...

         else default반환데이터 

end         

   

select employee_id, last_name, salary, department_id,

       case department_id when 10 then salary*0.1

                          when 20 then salary*0.2

                          when 30 then salary*0.3 

                          else salary*0.05 end bonus

from employees;

case ~ end: 범위판단가능

case when 조건판단식1 then 반환데이터1

     when 조건판단식2 then 반환데이터2

     when 조건판단식3 then 반환데이터3

     ....

     else default반환데이터

end

부서가 10~20 : 급여의 10%

부서가 30~50 : 급여의 20%

부서가 60~80 : 급여의 30%

그 외부서는 급여의 5% 를 보너스로 지급

사번, 성, 급여, 부서코드, 보너스 조회

select employee_id, last_name, salary, department_id,

       case when department_id in (10,20) then salary*0.1

            when department_id between 30 and 50 then salary*0.2

            when department_id between 60 and 80 then salary*0.3

            else salary*0.05 end bonus

from employees;            

            

select employee_id, last_name, salary, department_id,

       case when department_id <=20 then salary*0.1

            when department_id <=50 then salary*0.2

            when department_id <=80 then salary*0.3

            else salary*0.05 end bonus

from employees;            

부서가 10~30 10%

급여가 5000~8000 20%

업무가 IT_PROG 30%

select employee_id, last_name, salary, department_id, job_id, 

       case when department_id between 10 and 30 then salary*0.1

            when salary between 5000 and 8000 then salary*0.2

            when job_id= 'IT_PROG' then salary*0.3 end bonus

from employees

order by department_id asc, salary, job_id;            

            

            

우리회사 사원들이 어느 부서에 속해 있는지 파악하고자 한다

select department_id

from employees;

중복된 데이터행에 대해 대표값 한번만 반환하고 중복행을 제거한 결과를 반환하는 키워드

: distinct - select 바로 다음에 위치한다. - 행에 대해 적용된다.

  null 행을 포함

select distinct commission_pct

from employees;

우리회사에서 

매니저에 해당하는 사원들의 사번을 조회

select distinct manager_id

from employees

where manager_id is not null

order by 1;

  

* 그룹함수: 여러행으로부터 하나의 결과를 반환하는 형태의 함수 - null 제외됨

1. count( * ) : 데이터행의 수를 반환하는 함수

우리회사 사원들이 모두 몇 명인지 파악하고자 한다.

select count(* ) cnt

from employees;

select count(employee_id)

from employees;

select count(department_id)

from employees;

우리회사에서 커미션을 받는 사원들이 모두 몇명인지 조회한다.

select count(commission_pct) cnt

from employees;

select count(*) 

from employees

where commission_pct is not null;

우리회사에서 매니저에 해당하는 사원들이 모두 몇명이지 파악하고자 한다.

매니저에 해당하는 사원의 수를 조회한다.

select count(manager_id)

from employees;

select count(distinct manager_id) cnt 

from employees;

2. sum(표현):합한 결과를 반환하는 함수

우리회사 사원들의 급여 합계, 급여평균을 조회

select sum(salary) tot_sal, sum(salary)/count(*) avg_sal,

       avg(salary) avg_sal       

from employees;

3. avg(표현): 평균값을 반환하는 함수

우리회사 사원들의 평균커미션요율을 파악하고자 한다.

사원들의 평균커미션요율, 커미션을 받는 사원들의 평균커미션요율을 조회

select avg(commission_pct),

       sum(commission_pct)/count(commission_pct) avg_comm,

       sum(commission_pct)/count(*) avg_comm2

from employees;

4. max/min(표현): 가장 큰/작은 데이터값을 반환하는 함수

우리회사 사원들이 받는 급여에서 가장 높은 급여를 조회

select salary

from employees

order by 1 desc;

select max(salary), min(salary)

from employees;

우리회사에서 가장 먼저 나오는 성, 가장 나중에 나오는 성 을 조회

select min(last_name), max(last_name)

from employees;

우리회사에서 사원이 입사한 최근 입사일자, 최초 입사일자 조회

2017-11-20 2010-11-20

select max(hire_date) max_date, min(hire_date) min_date

from employees;

min, max: 숫자, 문자, 날짜

sum, avg: 숫자

우리회사에서 90번 부서원들의 평균급여를 조회.

평균급여는 소수 둘째자리까지 표현한다.

select round(avg(salary), 2) avg_sal

from employees

where department_id=90;

select round(avg(salary), 2) avg_sal

from employees

where department_id=80;

10 ....

.....

90 19333.33

80 8955.88

....

특정조건에 맞는 데이터의 그룹에 대해 그룹당 하나의 결과를 반환하는 형태

: group by 기준1, 기준2

select 절

from 절

where 절

group by 절

order by 절;

부서별로 평균급여를 조회

select department_id, round(avg(salary)) avg_sal

from employees

group by department_id

order by 1;

부서별로 업무별 평균급여 조회

select department_id, job_id, round(avg(salary)) avg_sal

from employees

group by department_id, job_id

order by 1;

select 목록에 그룹함수가 사용된 표현과 함께

            그룹함수가 사용되지 않은 표현이 있다면

그룹함수가 사용되지 않은 표현은   

반.드.시. group by 절의 기준으로 명시되어야만 한다.

우리회사 사원들에 대해 년도별로 입사한 사원수를 파악하고자 한다.

2001  10

2002  3

...

2008  15

년도, 입사한사원수 를 조회