05. SQL syntax(3) - Function

- Function

     + Single Row Function : 1개의 Record에서 사용하는 단일 행 함수.

     -> NULL Value : NULL값을 0으로 출력 할 때. 해당 COLUM의 DATATYPE을 준수한다.

1
2
3
4
5
6
7
SELECT NVL(salary,0* 12 [AS] "연봉 (상여금 제외)" FROM table;
 
/*
  OR
*/
 
SELECT NVL(salary*12,0) [AS] "연봉 (상여금 제외)" FROM table;
cs


      -> NULL Value2 : 해당 COLUM의 DATATYPE에 관계 없이 필요에 따른 결과를 출력한다.

1
SELECT NVL2(salary, '있음''없음'* 12 [AS] "연봉 (상여금 제외)" FROM table
cs


      -> DECODE : 조건문. 

1
2
3
4
5
6
7
8
9
10
11
12
/*
  DECODE(해당COLUM, 조건, 참일때 값, 거짓일때 값)
  
  OR 
  DECODE(해당COLUM, 조건, 참일때 값, [다른 조건, 참일때 값,] 거짓일때 값)
*/
 
  SELECT DECODE(title, NULL'직급없음', title) 직급 from table;
 
  SELECT DECODE(title, NULL'직급없음''사장''난사장임', title) 직급 from table;
cs

      -> ROUND(VALUE, 반올림자리수) : 반올림. 2번째 Argument의 Default는 0. 대입하지 않아도 가능.


      -> TRUNC (VALUE,  버림자리수) : 버림. 2번째 Argument의 Default는 0. 대입하지 않아도 가능.


      -> MOD(VALUE, VALUE) : 나머지


      -> ABS(VALUE) : 절대값


      -> CONCAT(VALUE, VALUE) : append


      -> SUBSTR(문자, 시작위치, 잘라낼 개수) : index는 1부터


      -> REPLACE(문자, 치환할 대상, 치환할 문자) : 치환


      -> TRANSLATE(문자, 치환할 대상, 치환할 문자) : 글자 단위로 치환


      -> MONTHS_BETWEEN(끝날짜, 시작날짜) : 개월수 계산


      -> TO_CHAR(value format) : 문자변환


         TO_DATE(value format) : 날자변환  


         TO_NUMBER(value) : 숫자변환  


         -> 날짜 문자 형식


            2077-07-07


            'YEAR'  : 글자형태 출력(twenty seventy-seven)


            'YYYY'  : 숫자형태 출력(2077)


            'YY'    : 숫자형태 출력(77)


            'MONTH' : 글자형태 출력(7월)


            'MM'    : 숫자형태 출력(2077)


            'DAY'   : 요일형태 출력(수요일)


            'DY'    : 요일형태 출력(수)


            'D'     : 요일의 Index 출력(4)


            'DD'    : 일 출력(07)


           -> 조건문을 활용한 예제 


              WHERE TO_CHAR(in_date,'mm')='07'


              WHERE TO_CHAR(in_date, 'D')=2;


         -> Locale 정보 이용(국가에 따른 표시 단위)


            TO_CHAR(NVL(salary,0)*12, 'L999,999.99')


            => ₩119,904.00


      -> LPAD('Oracle DBMS', 13, 'x') : Oracle DBMS의 맨 뒷문자에서 출발.


                  13번째까지 문자 출력.


      공백이 있으면 x로 출력.


      -> RPAD('Oracle DBMS', 13, 'x') : Oracle DBMS의 맨 앞문자에서 출발.


                  13번째까지 문자 출력.


      공백이 있으면 x로 출력.


      -> SIN(VALUE) : sin 삼각함수


      -> SIGN(VALUE) : 양수, 음수 판단.


      -> EXP(VALUE)  : Exponential


      -> LOW(VALUE)  : 소문자 변환


      -> UPPER(VALUE) : 대문자 변환


      -> INITCAP(VALUE) : 공백 분리


     + Multi Row Function(Group) : 1개의 Column에서 사용하는 다중행 함수


  WHERE에 작성할 수 없다.


      -> COUNT(s/n) : 전체 레코드의 수

      Ex : 

1
2
3
4
5
6
select  count(*) 레코드,
        count(salary) 월급수령자, =>NULL은 계산에서 제외
        count(nvl(salary,0)) "월급수령자 (null포함)",
        count(distinct salary) 월급종류,
        count(distinct nvl(salary,0)) "월급종류 (null포함)"
from    emp_test;
cs

1
2
3
4
5
select count(*) 영업부직원,
       count(dept_name) 영업부직원2,
       count(salary) 영업부직원월급수령자
from   emp_test
where  dept_name='영업부';


      -> ROWNUM : 행의 순번을 나타내는 가상 컬럼


      -> SUM, MAX, MIN, AVG 모두 Multi Row Function다.

         Multi Row Function은 모든 연산에서 NULL을 제외한다.


         Ex : 

1
2
3
4
5
6
7
select sum(salary),
       max(salary),
       min(salary),
       avg(salary),
       avg(nvl(salary,0))
from   emp_test
where  dept_name='영업부';
cs


      -> 출력시 주의할 점.

         출력할 값들은 1:1의 관계를 유지해야 된다.

         단일의 값은 Field를 말하며 한개의 자료를 가지고 있다.

         다중의 값은 Column을 말하며, 여러개의 자료를 하나의 묶음으로 가지고 있다.

         단일의 값과 다중의 값을 동시 출력하게 되면 1:N의 관계를 가지므로 같이 출력 할 수 없다.


1
2
3
select name, max(salary) or dept_name, max(salary)
from   emp_test
where  dept_name='영업부';

      2행에 오류:

      ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

      단일의 값을 출력하기 위한 해결책은 GROUP BY다.


    GROUP BY

    특정한 Column의 자료들을 다른 자료들과 비교함으로서, 유일한 값에 따라 단체로 묶는 것이다.

1
2
3
4
select   dept_name, max(salary)
from     emp_test
where    dept_name='영업부'
group by dept_name;

cs

1
2
3
select   nvl(dept_name,'부서배치전'), nvl(avg(salary),0)
from     emp_test
group by dept_name;
cs

     

    HAVING

    Multi Row Function을 이용하여 조건을 부여할 때 사용한다.

1
2
3
4
5
select   dept_name, avg(salary), sum(salary), count(dept_name)
from     emp_test
where    dept_name is not null
group by dept_name
having   count(dept_name)>=5 and avg(salary)>=5000;

cs


+ Recent posts