[ORACLE] SQL 함수 활용 1

22 Sep 2022 in Study on Oracle

SQL 함수 활용 1

• 기본 SQL 이해 - 기본 함수 사용
  • 대소문자 변환 함수
  • 문자 조작 함수
  • 숫자 함수
  • 날짜 함수

---

기본 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_ID	LAST_NAME	U_LOWER	SALARY	N_H
     100	King	king	24000	이사영영영
     101	Kochhar	kochhar	17000	일칠영영영
     102	De Haan	de haan	17000	일칠영영영

숫자 함수

---

• 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)
  1	45.92	46	50

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

• 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)
  1	45.92	45	40

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

• MOD

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

    SELECT  last_name, salary, MOD(salary, 5000)
      FROM  employees
     WHERE  job_id = 'SA_REP';
  

  	LAST_NAME	SALARY	MOD(SALARY,5000)
  1	Abel	11000	1000
  2	Taylor	8600	3600
  3	Crant	7000	2000

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

날짜 함수

---

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

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

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

    SELECT  sysdate, systimestamp
      FROM  DUAL;
  

  	SYSDATE	SYSTIMESTAMP
  1	21/07/10	21/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')
-- 일 수가 아니라 주 단위이다.
-- 일월화수목금토 에서 수요일부터 반올림된다.