SQL-8) 함수(function) - 4

안녕하세요 이승혁 입니다.

 

이번 시간은 단일행 함수 마지막 시간이 될 것 같습니다.

다음 포스팅에서는 복수행 함수에 대해 다루겠습니다.

 

※ 복습

 

단일행 함수

 

문자 함수

 

length                   - 데이터의 길이                      length ( col )

lpad & rpad           - 데이터 좌, 우 공백 채우기       lpad & rad ( '출력할 문자 / 칼럼 ', '칸 개수', '채울 문자')

trim & rtrim & ltrim - 데이터 좌, 우, 좌우 공백 제거  trim( col ), rtrim( col , ' 문자, 문자 ' )

replace                  - 특정 값 다른 값으로 대체       replace ( col , '문자' , '문자' )   

concat                   - 두 데이터를 연결해서 출력     concat( col, col)

 

숫자 함수

 

round - 반올림                                    round( num, 반올림할 위치 지정 )  , 1의 자리 : -1 , 소수점 첫째 : 0

trunc  - 버림                                       trunc( num, 버림할 위치 지정 )      , 1의 자리 : -1 , 소수점 첫째 : 0

mod   - 나머지                                    mod( 피제수, 제수 )

power - 지수 함수                                power( 상수, 지수 )

log     - 로그 함수                                log( 밑, 진수 )

ln       - 밑수가 자연 상수(e)인 로그 함수   ln(exp(진수))

 

날짜 함수

 

month_between  - 날짜 - 날짜의 개월 수                        month_between( date, date )

add_months       - 날짜 + 개월수                                 add_months( date, num )

next_day           - 특정 날짜에서 가장 가까운 특정 요일    next_day( date, '요일')

last_day            - 특정 날짜의 말일                              last_day( date )

 

변환 함수

 

to_char      - 문자형 변환      to_char( num or date , '요일의 형식 - yyyyy, year, mon, mm, day, dy, dd, d )

to_number  - 숫자형 변환     to_number( char or date )

to_date      - 날짜형 변환     to_date ( char or num )

 

---

※ 일반 함수

 

    1. nvl 함수

    2. decode 함수

    3. case 문

 

ㆍ 단일행함수 - 일반함수 nvl

 

     null값을 처리하는 함수

     null값 대신 다른 값을 출력하는 함수

select ename,nvl(to_char(mgr),'no manager')
from emp;

     null(칼럼,대신할값)

 

실습 1. nvl 사용해보기, null 대신 0 뽑기

select ename,nvl(comm,0) nvl
from emp;

실습 2. nvl - 숫자형 데이터에 문자형 값 출력해보기. mgr이 null인 사원은 'no manager' 출력하기

select ename,nvl(to_char(mgr),'no manager')
from emp;

nvl 함수를 사용할 때는 null을 체크할 칼럼의 데이터유형과, 대체할 값의 유형이 같아야 합니다.

mgr = 숫자형 데이터

'no manager' = 문자형 데이터

따라서 mgr을 to_char함수를 사용해 문자형으로 변경해 사용합니다.

 

ㆍ 단일행함수 - 일반함수 docode

 

     SQL로 if문을 구현하는 함수입니다.

     if문이란 조건을 사용할 수 있게 해주는 함수입니다.

     if(a>10) 처럼 조건을 부여해서 해당 조건에 충족할 때, 아닐 때 수행할 코드를 나누어 작성합니다.

     decode(데이터,조건,맞을때,아닐때)

 

실습 3. decode 사용해보기, 부서번호가 10이면 보너스 5000을 아니면 0을 출력해보기

select ename,deptno,decode(deptno,10,5000,0) as bonus
from emp;

decode(부서번호,10,맞으면5000,아니면0)

실습 4. decode 사용해보기 2, 조건 2개를 사용 부서번호 10이면 5000, 20이면 4000, 아니면 0 보너스를 출력해보기

select ename,deptno,decode(deptno,10,5000,20,4000,0) as bonus
from emp;

실습 4처럼 조건을 하나씩 추가해 갈 수 있습니다.

 

※ decode 단점 !

 

등호비교(=) 만 가능합니다. 따라서 10보다 크다, 작다의 비교는 수행할 수 없습니다.

 

ㆍ 단일행함수 - 일반함수 case 문

 

    등호비교가 가능하게 해주는 함수

    case when 데이터 >= 비교값 and when 데이터 >= 비교값 .... esle 아닐시값 end 

 

실습 5. 나이가 30 이상이면 5000, 26이상이면 4000, 아니면 0 보너스 출력해보기

select replace(ename,substr(ename,-2,1),'*') ename, case when age>=30 then 5000
                                                         when age>=26 then 4000
                                                         else 0 end bonus
from emp11;
--가독성을 위한 case문 작성법

 

※ 실습 데이터

 

1. 테이블 생성

 

create table skin(cusno  number(10),
				  gender varchar2(10),
                  age    varchar2(10),
                  job    varchar2(10),
                  marry  varchar2(10),
                  car    varchar2(10),
                  buy_yn varchar2(10));

2. 데이터 로드

skin.csv

0.00MB

 

 

 

좌측 테이블 - SKIN 테이블 우클릭 - 데이터 임포트 - 파일 : 찾아보기 - skin.csv 로드 - 계속 다음 클릭 - 임포트 확인

 

 

실습 6. case 문 사용해보기, 고객 등급 나누어보기. 직업과 결혼을 했으면 A, 결혼만 했으면 B, 직업만 있으면 C, 둘다 없으면 D 등급으로 고객을 나누어 쿠폰을 발급할 대상을 파악해보기~

 

select cusno, marry, job, case when ( marry = 'YES' and job ='YES' ) then 'A'    
			       when ( marry = 'YES' and job = 'NO' ) then 'B'  
                               when ( marry = 'NO' and job = 'YES' ) then 'C'     
                               else 'D' end 등급
from skin;

등급 글자를 더블클릭하면 order 절을 사용하지 않아도 정렬을 할 수 있습니다.

 

오늘 포스팅은 여기서 마치겠습니다.

 

다음 포스팅에서는 여러개의 행을 받아 하나의 행만을 출력하는 복수행 함수에 대해 실습을 진행하겠습니다.

읽어주셔서 감사합니다.