[Database/Theory]05. SQL syntax(3) - Function

05. SQL syntax(3) - Function

- Function

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

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

SELECT NVL(salary,0) * 12 [AS] "연봉 (상여금 제외)" FROM table;
 
/*
  OR
*/
 
SELECT NVL(salary*12,0) [AS] "연봉 (상여금 제외)" FROM table;

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

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

      -> DECODE : 조건문. 

/*
  DECODE(해당COLUM, 조건, 참일때 값, 거짓일때 값)
  
  OR 

  DECODE(해당COLUM, 조건, 참일때 값, [다른 조건, 참일때 값,] 거짓일때 값)

*/
 
  SELECT DECODE(title, NULL, '직급없음', title) 직급 from table;
 
  SELECT DECODE(title, NULL, '직급없음', '사장', '난사장임', title) 직급 from table;

      -> 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 : 

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

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 : 

select sum(salary),
       max(salary),
       min(salary),
       avg(salary),
       avg(nvl(salary,0))
from   emp_test
where  dept_name='영업부';

      -> 출력시 주의할 점.

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

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

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

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

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

      2행에 오류:

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

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

    GROUP BY

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

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

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

     

    HAVING

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

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;