[SQL] [oracle] 오라클 내장함수

오라클 내장함수

• SQL 작성이 사용할 수 있는 유용한 기능이 제공되는 함수

  *DBMS 제품마다 조금씩 차이가 있다.

• 종류

  1. 단일행 함수

     조회된 행마다 하나의 결과를 반환한다

     종류

     문자함수 : 문자를 입력값으로 받아서 계산한 결과를 반환

     숫자함수 : 숫자를 입력값으로 받아서 계산한 결과를 반환

     날짜함수 : Date 타입의 값에 대한 처리를 수행

     변환함수 : 데이터의 타입을 변환하는 처리를 수행

     기타함수 : nvl, case, decode 등의 함수가 있다.

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

     조회된 행을 그룹으로 묶고 행 그룹당 하나의 결과를 반환한다 group by 절을 사용해서 조회된 행을 그룹으로 묶고 다중행 함수로 각 그룹당 하나의 결과(합계 , 평균, 분산, 표준편차, 최고값, 최저값) 등을 계산해낸다.
     다중행 함수(그룹함수)

     조회된 행을 그룹으로 묶고 그룹당 하나의 결과를 반환
     count(), sum(), avg(), min(), max(), variance(), stddev()

     (집합그룹이란? 테이블 전체 또는 그룹화된 테이블의 행들을 말한다.)

     집합그룹의 예

     ex)

     사원테이블 전체 혹은 부서별 평균임금, 직종별 전체 급여, 관리자별 관리직원수
     입사년도별 입사한 사원수, 급여등급별 사원수 등과 같은 조회작업에서 그룹화되는 행들의 집합이다

     • select절에 그룹함수를 사용하게 되면 그룹함수 외의 다른 표현식이 올수 없다.

       1. 그룹함수

     • count(*)

       조회된 모든 행의 갯수를 반환한다.

     • count(컬럼 혹은 표현식)

       조회된 행에서 지정된 컬럼의 값이 null이 아닌 행의 갯수를 반환한다.

     • sum(컬럼 혹은 표현식)

       조회된 행에서 지정된 컬럼의 값의 합계를 반환한다.(null값은 무시된다.)

     • avg(컬럼 혹은 표현식)
       조회된 행에서 지정된 컬럼의 값의 평균를 반환한다.(null값은 무시된다.)

     • min(컬럼 혹은 표현식)

     • 조회된 행에서 지정된 컬럼의 값의 최소값을 반환한다.(null값은 무시된다.)

     • max(컬럼 혹은 표현식)

       조회된 행에서 지정된 컬럼의 값의 최대값을 반환한다.(null값은 무시된다.)

     • variance(컬럼 혹은 표현식)
       조회된 행에서 지정된 컬럼의 값의 분산을 반환한다.(null값은 무시된다.)

     • stddev(컬럼 혹은 표현식)
       조회된 행에서 지정된 컬럼의 값의 합계를 반환한다.(null값은 무시된다.)

       2. 행을 그룹화하기

     • group by 절을 사용해서 컬럼의 값이 같은 값을 가지는 행끼리 그룹화 할 수 있다.

         select 그룹함수(), 그룹함수()
         from 테이블명
         [where 조건식]
         [group by 컬럼혹은 표현식]
         [order by 컬럼명]
       

       *group by 절은 행그룹화 기준을 결정하는 값을 가진 컬럼을 지정한다.

       *select에는 group by절에 지정한 컬럼을 사용할 수 있다.

       *select 절에서 사용된 그룹함수는 group by로 그룹화된 각각의 그룹마다 실행된다.

       3. 그룹함수 실행결과를 필터링하기 (having)

     • having절은 group by 절을 사용해서 행을 그룹화하고
       각 그룹에 그룹함수를 실행한 결과를 필터링할 떄 사용한다.

       select column, 그룹함수
       from table
       [where 조건식]
       [group by 컬럼혹은표현식]
       [having 그룹함수적용결과를 필터링하는 조건식]
       [order by 컬럼]
       예) 부서별 사원수를 구했을 때, 사원수가 20명이상인 부서의 아이디와 사원수를 조회하기

       예) 부서별 사원수를 구했을 때, 사원수가 20명이상인 부서의 아이디와 사원수를 조회하기

-   \-**문자함수**

    lower(column or exp)

    upper(column or exp)

    substr(column or exp, beginIndex, length)

    substr(column or exp, beginIndex)

    concat(column or exp, column or exp)

    length(column or exp)

    instr(column or exp, 'string')

    instr(column or exp, 'string',beginIndex)

    instr(column or exp, 'string',beginIndex, count)

    lpad(coloumn or exp, length, 'string')

    rpad(coloumn or exp, length, 'string')

    trin(column or exp)

    replace(column or exp, 'search\_string', 'replacement\_string')

-   \-**숫자함수**

    round(column or exp)

    round(column or exp,n)

    trunc(column or exp)

    trunc(column or exp,n)

    ceil(column or exp)

    floor(column or exp)

    mod(m,n) — 나머지 구하는 연산자.

    [round trunc](https://www.notion.so/round-trunc-d45849f43daa48f6927ddbe30b4d89bf)

-   \-**날짜함수**

    sysdate

    날짜 + 숫자

    날짜 - 숫자

    날짜 - 날짜

    round(날짜)

    trunc(날짜)

    months\_between(날짜, 날짜)

    add\_months(날짜, 숫자)

-   \-데이터 타입변환

    \-묵시적 타입 변환 : 쿼리 실행과정에서 자동으로 데이터타입이 변환됨

    -   문자 —> 숫자 (문자가 숫자로만 구성되어 있을 때)

    -   문자 —> 날짜 (문자가 날짜표기 형식의 문자일 때)

        [타입변환.jpg](https://www.notion.so/jpg-1d027296f0df4b6bae28167044a02b1b)

    -   명시적 타입 변환

        ![nse-809053639893166641-Notes_191010_141405_dcc_1.jpg.jpg](https://s3-us-west-2.amazonaws.com/secure.notion-static.com/fba71128-d050-4c25-ab24-8c4ceb793da7/nse-809053639893166641-Notes_191010_141405_dcc_1.jpg.jpg)

        숫자 —> 문자 : 숫자를 지정된 포맷형식의 문자로 변환한다.

        to\_char(숫자,'포맷형식')

        문자 —> 숫자 : 특정 패턴으로 작성된 숫자형식의 문자를 숫자로 변환한다.

        to\_number('특정패턴으로 구성된 숫자형식의 문자' , '패턴')

        패턴 문자

        9 : 숫자를 나타낸다

        0 : 숫자를 나타낸다

        $ : 달러 기호를 나타낸다.

        . : 소숫점을 나타낸다.

        , : 자릿수를 나타낸다.

        L : 해당지역의 통화로 나오게 한다.

        날짜 —> 문자 : 날짜를 지정된 포맷형식의 문자로 변환한다.

        to\_char(날짜, '포맷형식')

        문자 —> 날짜 : 특정 패턴으로 작성된 날짜형식의 문자를 날짜로 변환한다.

        to\_date('특정 패턴으로 작성된 날짜형식의 문자' , '패턴' )

        패턴 문자

        YYYY

        MM

        DD

        AM 오전 오후를 나타낸다.

        HH, HH12, HH24 시간을 나타낸다.

        MI 분을 나타낸다.

        SS 초를 나타낸다.

• 기타함수

  nvl(null값이 예상되는 컬럼, 대체할 값)

  • nvl은 null값을 지정된 대체값으로 변환한다.

    *컬럼의 값이 null이 아닌 경우에는 그 컬럼의 원래값이 반환된다.

  • nvl에서는 첫번째 항목과 두번째 항목의 데이터 타입이 동일해야 한다.

  • 주로 null값을 포함하고 있는 컬럼이 연산식에 포함되어 있을 때 사용한다.

    예) 커미션포인트가 적용된 직원의 연봉 계산하기

    (급여 + 급여커미션포인트) 12에서 커미션포인트가 null인 직원은 연봉이 null로 계산됨)

    (급여 + 급여_nvl(커미션포인트, 0))_12 와 같은 방식으로 활용된다.

    Case

  • 제시된 조건에 따라서 다른 조회결과를 제공받을 수 있다.

    • java의 switch나 if~else if ~else과 유사하다

      예)

      사용법1 : 제시된 조건식과 만족하면 then에 정의된 반환값이 반환된다.(조건식)

        select column, column,
            case
                when 조건식1 then 반환값1
                when 조건식2 then 반환값2
                when 조건식3 then 반환값3
                else 반환값4
      
            end
            from table;

      사용법2 : 제시된 colum3의 값과 일치하는 비교값을 가진 then에 정의된 반환값이 반환된다.

      (eqaul비교만 가능하다

        select column1, column2,
                    case column3
                                    when 값1 then 반환값1
                                    when 값2 then 반환값2
                                    when 값3 then 반환값3
                                    else 반환값 4
                    end
        from table

      decode

  • 제시된 조건에 따라서 다른 조회결과를 제공받을 수 있다.

  • java의 switch와 유사하다.

    사용법 (equal비교만된다)

    select column1, column2,
              decode(column3, 비교값1, 반환값1,
                                              비교값2, 반환값2,
                                              비교값3, 반환값3
                                              반환값4)
    from talble;