[DB] 오라클 내장 함수2

오라클 내장함수

• SQL 작성이 사용할 수 있는 유용한 기능이 제공되는 함수
• DBMS 제품마다 조금씩 차이가 있다.

오라클 내장함수의 종류

• 단일행 함수
  • 조회된 행마다 하나의 결과를 반환한다.
  • 단일행 함수는 중첩해서 사용할 수 있다.
  • 종류
    • 문자함수 : 문자를 입력값으로 받아서 계산한 결과를 반환
    • 숫자함수 : 숫자를 입력값으로 받아서 계산한 결과를 반화
    • 날짜함수 : Date 타입의 값에 대한 처리를 수행
    • 변환함수 : 데이터의 타입을 변환하는 처리를 수행
    • 기타함수 : nvl, case, decode 등의 함수가 있다.
• 다중행 함수(그룹함수)
  • 조회된 행을 그룹으로 묶고 행 그룹당 하나의 결과를 반환한다.
  • group by 절을 사용해서 조회된 행을 그룹으로 묶고 다중행 함수로 각 그룹당 하나의 결과(합계, 평균, 분산, 표준편차, 최고값, 최저값) 등을 계산해 낸다.

문자 함수

• lower(column or exp)
  • 소문자로 변환한다.
• upper(column or exp)
  • 대문자로 변환한다.
• substr(column or exp, beginIndex)
  • 텍스트값을 시작위치부터 끝까지 잘라낸다.
• substr(column or exp, beginIndex, length)
  • 텍스트의 값을 시작위치부터 지정된 길이만큼 잘라낸다.
• concat(column or exp, column or exp)
  • 텍스트 2개를 연결한다.
• length(column or exp)
  • 텍스트의 길이를 반환한다.
• instr(column or exp, ‘string’)
  • 텍스트에서 지정된 문자열이 처음으로 등장하는 위치를 반환한다.
• lpad(column or exp, length, ‘string’)
  • 텍스트의 길이가 지정된 길이보다 부족하면 지정된 문자를 왼쪽에 채운다.
• rpad(column or exp, length, ‘string’)
  • 텍스트의 길이가 지정된 길이보다 부족하면 지정된 문자를 오를쪽에 채운다.
• trim(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)
  • m을 n으로 나눈 나머지값을 반환한다.

    날짜함수

• sysdate
  • 시스템의 현재날짜와 시간을 반환한다.
• 날짜연산
  • 날짜 + 숫자
    • 지정된 날짜에서 숫자만큼 경과된 날짜를 반환한다.
  • 날짜 - 숫자
    • 지정된 날짜에서 숫자만큼 이전 날짜를를 반환한다.
  • 날짜 - 날짜
    • 두 날짜사이의 날짜수를 반환한다.
• round(날짜)
  • 날짜를 반올림한다. 정오가 지나면 하루가 증가된다.
• trunc(날짜)
  • 지정된 날짜에서 시간정보를 전부 0으로 바꾼 값을 반환한다.
• months_between(날짜, 날짜)
  • 두 날짜사이의 월수를 반환한다.
• add_months(날짜, 숫자)
  • 날짜에서 숫자만큼 개월수를 증감시킨 값을 반환한다.

데이터 타입 변환

• 묵시적 타입 변환 : 쿼리 실행과정에서 자동으로 데이터타입이 변환됨
  • 문자를 숫자로 (문자가 숫자로만 구성되어 있을 때)
  • 문자를 날짜러(문자가 날짜표기 형식의 문자일 때)
• 명시적 타입 변환
  • to_char(숫자, ‘포맷형식’)
    • 숫자를 지정된 포맷형식의 문자로 변환한다.
  • to_number(‘특정패턴으로 구성된 숫자형식의 문자’, ‘패턴’)
    • ,가 포함된 문자열을 숫자로 변환한다.
    • 패턴 문자
      • 9 숫자를 나타낸다
      • 0 숫자를 나타낸다.
      • $ 달러 기호를 나타낸다.
      • . 소숫점을 나타낸다.
      • , 자릿수를 나타낸다.

        -- 가격에 3자리마다 ,를 추가한 문자열을 반환한다.
        select book_no, book_title, to_number(book_price, '9,999,999') 
        from sample_books;
        

  • to_char(날짜, ‘포맷형식’)
    • 날짜를 지정된 포맷형식의 문자열로 변환한다.

      -- 날짜를 '년-월-일' 형식으로 변환한다.
      select book_no, book_title, to_char(book_create_date, 'yyyy-mm-dd')
      from sample_books
      

  • to_date(‘특정 패턴으로 작성된 날짜형식의 문자’, ‘패턴’)
    • 문자열을 날짜로 변환한다.

      -- '2020'년에 출간된 도서된 도서를 검색한다.
      select book_no, book_title, book_price, book_create_date
      from sample_books
      where book_create_date >= to_date('2020-01-01 00:00:00', 'yyyy-mm-dd hh:mi:ss') 
      and book_create_date < to_date('2021-01-01 00:00:00', 'yyyy-mm-dd hh:mi:ss')
      

    • 패턴 문자
      • YYYY 년도를 나타낸다.
      • MM 월을 나타낸다.
      • DD 일을 나타낸다.
      • AM 오전 오후를 나타낸다.
      • HH, HH12, HH24 시간을 나타낸다.
      • MI 분을 나타낸다.
      • SS 초를 나타낸다.

        기타함수

• nvl(null값이 예상되는 컬럼, 대체할 값)
  • nvl은 null값을 지정된 대체값으로 변환한다.
  • 컬럼의 값이 null이 아닌 경우에는 그 컬럼의 원래값이 반환된다.
  • nvl에서는 첫번째 항목과 두번째 항목의 데이터 타입이 동일해야 한다.
  • 주로 null값을 포함하고 있는 컬럼이 연산식에 포함되어 있을 때 사용한다.
  • 사용예

    -- 커미션이 null인 경우 0값을 반환한다.
    select no, name, sal, com, sal + nvl(com, 0) real_salary
    from employee
    

• case ~ when ~ then ~ end
  • 제시된 조건에 따라서 다른 조회결과를 제공받을 수 있다.
  • java의 switch나 if ~ else if ~ else과 유사한다.
  • 사용예제

    -- javaa의 if ~ else if ~ else문처럼 사용하기
    -- 급여에 따라서 급여 증가액을 다르게 게산해서 조회하기 
    select no, name, salary,
      case
        when salary >= 1000 then salary * 0.01
        when salary >= 750 then salary * 0.015
        when salary >= 500 then salary * 0.02
        else salary * 0.03
      end as increase_salary
    from employee;
    

  • 사용예제

    -- java의 switch문처럼 사용하기
    -- position 컬럼값에 따라서 보너스를 다르게 계산해서 조회하기
    select no, name, position, salary
      case position
        when '임원' then salary/5
        when '부장' then salary/3
        when '과장' then salary/2
        else salary
      end as bonus
    from employees
    

• decode(컬럼, 값, 반환값, 값 반환갑, …)
  • 제시된 조건에 따라서 다른 조회결과를 제공받을 수 있다.

    decode(컬럼, 값1, 반환값1,
                 값2, 반환값2,
                 값3, 반환값3,
                      반환값4)
    -- 컬럼의 값이 값1과 일치하면 반환값1이 최종값이 된다.
    -- 컬럼의 값이 값2와 일치하면 반환값2가 최종값이 된다.
    -- 컬럼의 값이 값1, 값2, 값3 어느값과도 일치하지 않으면 반환값4가 최종값이 된다.
    

  • 사용예

    -- 소속부서가 100번인 사원들은 A팀, 101번인 사원은 B팀, 102번인 사원은 C팀, 나머지는 D팀으로 조회되게 한다.
    select no, name, deptno,
    decode(deptno, 100, 'A팀',
                   101, 'B팀',
                   102, 'C팀',
                   'D팀')
    from employees