[ORACLE] SQL 함수 활용 1

in Study on Oracle
[ORACLE] SQL 함수 활용 1

SQL 함수 활용 1

기본 SQL 이해 - 기본 함수 사용

단일행 함수 : 하나의 행에 대해 하나의 결과를 출력

(다중) 그룹 함수 = 집계 함수 : 여러 행에 대해 하나의 결과를 출력

단일 행 함수부터 알아보자.

대소문자 변환 함수

함수결과
LOWER(’SQL Course’)sql course
UPPER(’SQL Course’)SQL COURSE
INITCAP(’SQL Course’)Sql Course
SELECT  'The job id for '||UPPER(last_name)||' is '||LOWER(job_id) 
    AS  "EMPLOYEE DETAILS"
  FROM  employees;

구문(SELECT, FROM 등)은 대소문자를 구분하지 않지만, Data는 대소문자를 구분해야함을 명심하자.

-- last_name : 첫 글자 대문자
SELECT  employee_id, last_name, department_id
  FROM  employees
 WHERE  last_name = initcap('&lname');

-- 입력이 전부 소문자라는 전제 하에,
-- WHERE  lower(last_name) = '&lname'; 과 같이 사용할 수도 있다.
-- 하지만 이 방법은 추천되지 않는다. 되도록 등호 뒤에서 처리하도록 하자.

위 구문처럼 치환 변수를 통해서 이름을 입력 받고, 이를 INITCAP 을 사용하여 첫 글자만 대문자로 바꾸어 주는 식으로 사용할 수도 있다.

문자 조작 함수

함수결과
CONCAT(’Hello’, ‘World’)HelloWorld
SUBSTR(’HelloWorld’, 1, 5)Hello
LENGTH(’HelloWorld’)10
INSTR(’HelloWorld’, ‘W’)6
LPAD(salary, 10, ‘*’)*****24000
RPAD(salary, 10, ‘*’)24000*****
REPLACE(’JACK and JUE’, ‘J’, ‘BL’)BLACK and BLUE
TRIM(’H’ FROM ‘HelloWorld’)elloWorld

  • CONCAT 은 ‘Concatenation(연결)’의 약자이다.

    글자를 연결시켜준다.

  • SUBSTR 은 ‘Substring(부분 문자열)’의 약자로, 대상은 주로 컬럼이 된다.

    의미는 ‘첫 번째 인자’ 자리부터 ‘두 번째 인자’의 값만큼 글자를 출력한다.

    표의 구문으로 보면 ‘HelloWorld’에서 1자리 부터 5개의 글자를 출력하므로 ‘Hello’가 출력된다.

    두 번째 인자는 생략 가능하다. 생략 시 모든 글자가 출력된다.

      SUBSTR('HelloWorld', 4)
  -- 'loWorld' 출력

  • LENGTH 글자의 수를 출력한다.

  • INSTR 은 ‘Instruction(지시)’의 약자(아닐수도?)이다.

    대상 문자열(주로 컬럼)에서 인자로 들어온 글자를 찾고 그 위치를 출력한다.

  • LPAD 와 RPAD 는 각각 ‘Left Pad’, ‘Right Pad’의 약자이다.

      LPAD(salary, 10, '*') 
  --- '*****24000' 출력

    위 구문의 경우, salary의 값이 24000이라고 생각했을때, 24000을 10(첫 번째 인자)칸의 오른쪽에 채우고 왼쪽 빈 공간을 ‘*’로 채운다.

    Right Pad는 반대이다.

  • REPLACE 는 글자를 바꿔준다.

    문자열에서 첫 번째 인자의 값을 두 번째 인자의 값으로 바꾼다.

  • TRIM 은 문자열에서 글자를 지운다.

    표에 나온 기능은 문자열 제거 기능이고, 해당 문구에서 “’H’ FROM”을 제거하면 양쪽 끝의 공백을 제거한다.

    1. 문자열 제거
    2. 양쪽 끝 공백 제거
      1. LTRIM 왼쪽 공백 제거
      2. RTRIM 오른쪽 공백 제거

    두 개의 기능이 있다고 생각하자.

  • TRANSLATE 는 표에 없지만 추가한다.

    2개의 기능이 있다.

    1. 단순 문자열 치환 : TRANSLATE(’대상 문자열’, ‘비교 문자’, ‘바꿀 문자’)

       SELECT  translate('12345', '1', 'x')
   FROM  dual;
      TRANSLATE(’12345’,’1’,’x’)
      x2345

    2. 숫자 제거 : TRANSLATE(’대상 문자열’, ‘+.0123456789’, ‘ ’)

       SELECT  translate('abc1def2', ' +.0123456789', ' ')
   FROM  dual;
      TRANSLATE(’ABC1DEF2’,’+.0123456789’,‘ ’)
      abcdef

      두 번째 인자(’+.0123456789’)는 수치를 의미한다. 해당 인자에 포함된 숫자는 대상 문자열에서 공백으로 치환된다.

      다음 예를 보고 이해해보자.

       -- 사원의 성(last_name)은 소문자로, 급여(salary)는 한글로 변환하시오.
        
 SELECT  employee_id, 
 			  translate(last_name, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 
 					'abcdefghijklmnopqrstuvwxyz') u_lower,
 				salary,
 				translate(salary, '0123456789', '영일이삼사오육칠팔구') n_h
   FROM  employees;
      EMPLOYEE_IDLAST_NAMEU_LOWERSALARYN_H
      100Kingking24000이사영영영
      101Kochharkochhar17000일칠영영영
      102De Haande haan17000일칠영영영

숫자 함수

  • ROUND

    ‘반올림’을 시켜준다.

      SELECT  ROUND(45.923, 2), ROUND(45.923, 0), ROUND(45.923, -1)
    FROM  DUAL;
     ROUND(45.923, 2)ROUND(45.923, 0)ROUND(45.923, -1)
    145.924650

    소숫점 기준 두 번째 자리 수 까지 반올림 해서 표현해준다고 생각하자.

  • TRUNC

    ‘버림’을 시켜준다.

      SELECT  TRUNC(45.923, 2), TRUNC(45.923, 0), TRUNC(45.923, -1)
    FROM  DUAL;
     TRUNC(45.923, 2)TRUNC(45.923, 0)TRUNC(45.923, -1)
    145.924540

    ROUND 와 동일하나 ‘반올림’이 아닌 ‘버림’을 하는 것만 다르다.

  • MOD

    ‘나머지 처리’를 해준다.

      SELECT  last_name, salary, MOD(salary, 5000)
    FROM  employees
   WHERE  job_id = 'SA_REP';
     LAST_NAMESALARYMOD(SALARY,5000)
    1Abel110001000
    2Taylor86003600
    3Crant70002000

    인자로 나눈 값을 출력한다.

날짜 함수

  • 내부 숫자 형식(세기, 년, 월, 일, 시, 분, 초)으로 날짜를 저장한다.

    소수 이하 초도 최대 9자리 까지 저장 가능하다.

  • 기본 날짜 표시 형식은 DD-MON-RR 이며, SYSDATE, SYSTIMESTAMP 를 통해 조회 가능하다.

      SELECT  sysdate, systimestamp
    FROM  DUAL;
     SYSDATESYSTIMESTAMP
    121/07/1021/07/10 13:34:53.137164000 +09:00
  • 날짜에 숫자를 더하거나 빼서 결과 날짜 값을 구할 수 있다.

  • 시간 수를 24로 나눠 날짜에 시간을 더할 수 있다.

    연산결과설명
    날짜 + 숫자날짜날짜에 일 수를 더한다
    날짜 - 숫자날짜날짜에서 일 수를 뺀다
    날짜 - 날짜일 수한 날짜를 다른 날짜에서 뺀다
    날짜 + 숫자/24날짜날짜에 시간 수를 더한다
      SELECT  last_name, (SYSDATE-hire_date)/7 AS WEEKS
    FROM  employees
   WHERE  department_id = 90;
    
  -- 입사한지 몇 주가 되었는지를 출력한다.
  -- 이 때 WEEKS의 값은 소숫점 값을 포함한다.
  -- ROUND, TRUNC 를 사용하여 깔끔한 값을 얻는 것도 방법일 것이다.

    ‘날짜 + 숫자/24’의 경우를 자세히 보자.

    ‘date+1/24’ 라고 했을 때, 이는 1시간을 더하는 것을 의미한다.

    ‘date+1/1440’ 라고 했을 때, 이는 1분을 더하는 것을 의미한다.

    즉, 날짜에서의 연산은 기본적으로 일 수 라는것을 명심하자.

함수결과
MONTHS_BETWEEN(’01-SEP-95’, ‘11-JAN-94’)19.6774194
ADD_MONTHS(’31-JAN-96’, 1)‘29-FEB-96’
NEXT_DAY(’01-SEP-95’, ‘FRIDAY’)‘08-SEP-95’
LAST_DAY(’01-FEB-95’)‘28-FEB-95’

  • MONTHS_BETWEEN

    인자로 사용된 날짜들 사이의 개월 수 차이를 출력한다.

  • ADD_MONTHS

    날짜에 개월 수를 더한다.

  • NEXT_DAY

    돌아오는 요일의 날짜를 출력한다.

  • LAST_DAY

    해당 월의 마지막 날, 즉 말일을 출력한다.

날짜에도 ROUND 를 사용할 수 있다.

ROUND(SYSDATE, 'MONTH')
-- 7월 10일일 경우 7월 1일이, 7월 20일일 경우 8월 1일이 된다.

ROUND(SYSDATE, 'YEAR')
-- 현재가 22년일 때 3월의 경우 22년, 7월의 경우 23년이 된다.

ROUND(SYSDATE, 'DAY')
-- 일 수가 아니라 주 단위이다.
-- 일월화수목금토 에서 수요일부터 반올림된다.

© 2022. All rights reserved.