Additional SELECT 함수

vencott·2021년 5월 19일
Databaseoracle
0

sinc 인턴교육

목록 보기
4/18

함수의 종류

반환 결과에 따라 구분

  • 단일행 함수
    • n개 입력에 대한 n개의 결과
  • 그룹 함수
    • n개 입력에 대한 1개의 결과

문자열 함수

LENGTH

문자열의 길이를 반환

// LENGTH
SELECT	  LENGTH(CHARTYPE)
		, LENGTH(VARCHARTYPE)
FROM 	COLUMN_LENGTH;

INSTR

찾는 문자열이 지정한 위치부터 지정한 횟수만큼 나타난 시작 위치를 반환

  • 구문
    • INSTR(string, substring, [position, [occurrence]])
  • 반환
    • NUMBER
// INSTR// email(~~~@vcc.com)에서 . 바로 앞의 문자 'c' 위치SELECT	  EMAIL
		, INSTR(EMAIL, 'c', -1, 2)
FROM	EMPLOYEE;

// same as...SELECT	  EMAIL
		, INSTR(EMAIL, 'c', INSTR(EMAIL, '.')-1) -- 함수의 중첩
FROM	EMPLOYEE;

LPAD / RPAD

주어진 컬럼/문자열에 임의의 문자를 왼쪽/오른쪽에 덧붙여 길이 N의 문자열을 반환

  • 구문
    • LPAD(string, N, [string])
  • 반환
    • CHARACTER
// LPAD/RPAD
SELECT	  EMAIL AS 원본데이터
		, LENGTH(EMAIL) AS 원본길이
		, LPAD(EMAIL, 20, '.') AS 적용결과
		, LENGTH(LPAD(EMAIL, 20, '.')) AS 결과길이
FROM	EMPLOYEE;

LTRIM / RTRIM

주어진 컬럼/문자열의 왼쪽/오른쪽에서 지정한 str에 포함된 모든 문자를 제거한 뒤 str에 해당하지 않는 문자를 만났을 때 남은 문자열을 반환

  • 구문
    • LTRIM(string, str)
  • 반환
    • CHARACTER
// LTRIM/RTRIM
SELECT	LTRIM('123123Tech123', '123')
FROM	DUAL;-- Tech123SELECT	LTRIM('xyzxzyyyTechxyz', 'xyz')
FROM 	DUAL;-- TechxyzSELECT	LTRIM('6372Tech', '0123456789')
FROM	DUAL;-- Tech

TRIM

주어진 컬럼/문자열의 앞/뒤/양쪽에 있는 지정한 문자를 제거한 나머지를 반환하는 함수

  • 구문
    • TRIM([LEADING/TRAILING/BOTH] str FROM string)
      • LEADING
        • 왼쪽
      • TRAILING
        • 오른쪽
      • BOTH
        • 양쪽
  • 반환
    • CHARACTER
// TRIM
SELECT	TRIM(BOTH '1' FROM '123Tech111')
FROM	DUAL;-- 23Tech

SUBSTR

주어진 컬럼/문자열에서 지정한 위치부터 지정한 개수만큼의 문자열을 반환

  • 구문
    • SUBSTR(string, position, [length])
  • 반환
    • CHARACTER
// SUBSTR
SELECT	SUBSTR('TechOnTheNet', 1, 4)
FROM	DUAL;-- TechSELECT	SUBSTR('TechOnTheNet', -6, 3)
FROM	DUAL;-- THE

숫자 함수

ROUND

지정한 자릿수에서 반올림한 값을 반환

  • 구문
    • ROUND(number, [decimal_places])
      • decimal_places

        • 0: 소수점 이하 자리 의미

        • <0: 소수점 이상 자리 의미
  • 반환
    • NUMBER

현업에서는 소수점 이하 자리도 매우 중요하기 때문에 임의로 자리수를 맞추는 경우가 아니면 거의 쓸 일이 없다

// ROUND
SELECT	ROUND(125.315, 0)
FROM	DUAL;-- 125SELECT	ROUND(125.315, 1)
FROM	DUAL;-- 125.3SELECT	ROUND(125.315, -1)
FROM	DUAL;-- 130SELECT	ROUND(-125.315, 2)
FROM	DUAL;-- 125.32

TRUNC

지정한 자릿수에서 버림한 결과를 반환

  • 구문
    • TRUNC(number, [decimal_places])
  • 반환
    • NUMBER
// TRUNC
SELECT	TRUNC(125.315, 1)
FROM	DUAL; -- 125.3

SELECT	TRUNC(125.315, -1)
FROM	DUAL; -- 120

날짜 함수

SYSDATE

현재 시간을 반환

ADD_MONTHS

지정한 만큼의 달 수를 더한 날짜를 반환

  • 구문
    • ADD_MONTS(date ,N)
  • 반환
    • DATE
// MONTHS_BETWEEN
-- 오늘 날짜를 기준으로 근속년수가 20년 이상인-- 사원의 이름, 입사일, 근속년수, 직급, 부서를 조회SELECT	  EMP_NAME AS 이름
		, HIRE_DATE AS 입사일
		, ROUND(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)/12) AS 근속년수
		, JOB_ID AS 직급
		, DEPT_ID AS 부서
FROM	EMPLOYEE
WHERE	MONTHS_BETWEEN(SYSDATE, HIRE_DATE) >= 240;

변환 함수

데이터 타입 변환

  • 묵시적
    • value가 변하는 게 아니라 column이 변하는 것
    • 성능 하락의 원인이 되어 현업에선 잘 쓰이지 않는다
  • 명시적
    • value를 바꿔주는 명시적 변환을 주로 쓴다

NUMBER <--> CHARACTER <--> DATE

TO_CHAR              TO_CHAR

TO_NUMBER            TO_DATE

TO_CHAR

NUMBER/DATE 타입을 CHARACTER 타입으로 변환

  • 입력
    • NUMBER/DATE
  • 구문
    • TO_CHAR(input_type [, format])
  • 반환
    • CHARACTER
// TO_CHAR
SELECT	TO_CHAR(1234, 'L99999')
FROM	DUAL;-- W1234SELECT	TO_CHAR(1234, '09,999')
FROM	DUAL;-- 01,234SELECT	TO_CHAR(SYSDATE, 'MON DY, YYYY')
FROM	DUAL;-- 1월 화, 2020SELECT	TO_CHAR(SYSDATE, 'AM HH:MI:SS')
FROM	DUAL;-- 오전 09:33:39
// DATE to CHARACTER
SELECT	  EMP_NAME AS 이름
		, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일"') AS 입사일
FROM	EMPLOYEE
WHERE	JOB_ID = 'J7';

SELECT	  EMP_NAME AS 이름
		, SUBSTR(HIRE_DATE, 1, 2)||'년 '||
		  SUBSTR(HIRE_DATE, 4, 2)||'월 '||
		  SUBSTR(HIRE_DATE, 7, 2)||'일' AS 입사일
FROM	EMPLOYEE
WHERE	JOB_ID = 'J7';

TO_DATE

CHARACTER 타입을 DATE 타입으로 변환

  • 입력
  • CHARACTER
  • 구문
  • TO_DATE(input_type [, format])
  • 반환
  • DATE
// TO_DATE
SELECT	TO_DATE('041030 143033', 'YYMMDD HH24MISS')
FROM	DUAL;-- 2004-10-30 14:30:33.0SELECT	TO_CHAR(TO_DATE('980603', 'YYMMDD'), 'YYYY.MM.DD')
FROM	DUAL;-- 2098.06.03

TO_NUMBER

CHARACTER 타입을 NUMBER 타입으로 변환

  • 입력
    • CHARACTER
  • 구문
    • TO_NUMBER(input_type [, format])
  • 반환
    • NUMBER
// TO_NUMBER
SELECT	  EMP_NAME
		, EMP_NO
		, SUBSTR(EMP_NO, 1, 6) AS 앞부분
		, SUBSTR(EMP_NO, 8) AS 뒷부분
		, TO_NUMBER(SUBSTR(EMP_NO, 1, 6)) + TO_NUMBER(SUBSTR(EMP_NO, 8)) AS 결과
FROM	EMPLOYEE
WHERE	EMP_ID = '101';

기타 함수

NVL

NULL을 지정한 값으로 변환하는 함수

  • 입력
    • ANY
  • 구문
    • NVL(expr1, expr2)
  • 반환
    • ANY
// NVL
SELECT	  EMP_NAME
		, SALARY
		, NVL(BONUS_PCT, 0)
FROM	EMPLOYEE
WHERE	SALARY > 3500000;

NVL2(expr, result1, result2)

삼항 연산자와 비슷하게 expr이 NULL이면 result1, 아니면 result2

DECODE

SELECT 구문으로 IF-ELSE 논리를 제한적으로 구현한 오라클 DBMS 전용 함수

  • 입력
    • ANY
  • 구문
    • DECODE(expr, search1, result1 [, searchN, resultN, ...][, default])
      • search - result: if
      • searchN - resultN : else if
      • default: else
  • 반환
    • ANY
// DECODE
SELECT	  EMP_NAME
		, EMP_NO
		, SALARY
		, DECODE(SUBSTR(EMP_NO, 8, 1)
			, '1', '남'
			, '3', '남'
			, '여') AS 성별
FROM 	EMPLOYEE
WHERE	JOB_ID = 'J7';
// DECODE & NVL
SELECT	  EMP_ID
		, EMP_NAME
		, MGR_ID
		, DECODE(MGR_ID, NULL, '관리자', '직원') AS 직원구분
FROM 	EMPLOYEE
WHERE	JOB_ID = 'J4';

// same as...
SELECT	  EMP_ID
		, EMP_NAME
		, MGR_ID
		, NVL2(MGR_ID, '관리자', '직원') AS 직원구분
FROM 	EMPLOYEE
WHERE	JOB_ID = 'J4';

CASE

DECODE 함수와 유사한 ANSI 표준 구문

  • 입력
    • ANY
  • 구문
    • CASE expr WHEN search1 THEN result1 [WHEN...THEN...][ELSE default] END
    • CASE WHEN condition1 THEN result1 [WHEN...THEN...][ELSE default] END
  • 반환
    • ANY
// 직급별 인상급여를 확인하고 싶다
// J7 20%, J6 15%, J5 5% 인상, 나머지 직급은 해당 급여를 받는 직원의
// 이름, 직급, 급여, 인상급여를 조회하라

-- DECODESELECT	EMP_NAME AS 이름
		, JOB_ID AS 직급
		, SALARY AS 급여
		, DECODE(JOB_ID, 'J7', SALARY * 1.2
					   , 'J6', SALARY * 1.15
					   , 'J5', SALARY * 1.05
					   , SALARY)
		  AS 인상급여
FROM	EMPLOYEE;

-- CASESELECT	EMP_NAME AS 이름
		, JOB_ID AS 직급
		, SALARY AS 급여
		, CASE JOB_ID WHEN 'J7' THEN SALARY * 1.2
					  WHEN 'J6' THEN SALARY * 1.15
					  WHEN 'J5' THEN SALARY * 1.05
					  ELSE SALARY
		  END AS 인상급여
FROM	EMPLOYEE;

-- CASE WHENSELECT	EMP_NAME AS 이름
		, JOB_ID AS 직급
		, SALARY AS 급여
		, CASE WHEN JOB_ID = 'J7' THEN SALARY * 1.2
			   WHEN JOB_ID = 'J6' THEN SALARY * 1.15
			   WHEN JOB_ID = 'J5' THEN SALARY * 1.05
			   ELSE SALARY
		  END AS 인상급여
FROM	EMPLOYEE;
// 직원들의 급여등급을 확인하고 싶다
// 급여가 300 이하면 초급 400 이하면 중금 400 이상이면 고급으로 간주한다
// 직원들의 이름, 급여, 급여등급을 출력하라
SELECT	  EMP_NAME AS 이름
		, SALARY AS 급여
		, CASE WHEN SALARY < 3000000 THEN '초급'
			   WHEN SALARY BETWEEN 3000000 AND 4000000 THEN '중급'
			   WHEN SALARY > 4000000 THEN '고급'
		  END AS 급여등급
FROM 	EMPLOYEE;

// same as...
SELECT	  EMP_NAME AS 이름
		, SALARY AS 급여
		, CASE WHEN SALARY <= 3000000 THEN '초급'
			   WHEN SALARY <= 4000000 THEN '중급'
			   ELSE '고급'
		  END AS 급여등급
FROM 	EMPLOYEE;
// 함수의 중첩
--  사원 테이블에서 사원의 이름, 메일, 메일아이디를 조회하라SELECT	  EMP_NAME
		, EMAIL
		, SUBSTR(EMAIL, 1, INSTR(EMAIL, '@')-1) AS 메일아이디
FROM	EMPLOYEE;

그룹 함수

주요 그룹 함수

Untitled

그룹 함수는 NULL을 계산하지 않는다 --> SUM, AVG 계산 시 주의

SUM

입력 값의 총합을 계산하여 반환하는 함수

  • 입력
    • NUMBER
  • 구문
    • SUM([DISTINCT] expr)
  • 반환
    • NUMBER

AVG

입력 값의 평균을 계산하여 반환하는 함수

  • 입력
    • NUMBER
  • 구문
    • AVG([DISTINCT] expr)
  • 반환
    • NUMBER

MIN / MAX

최대/최소 값을 반환하는 함수

  • 입력
    • ANY
  • 구문
    • MIN(expr) / MAX(expr)
  • 반환
    • ANY

COUNT

행의 수를 반환

  • 입력
    • ANY
  • 구문
    • COUNT(* | [DISTINCT] expr)
  • 반환
    • NUMBER

출처: SHINSEGAE I&C 인턴십

profile
vencott
Backend Developer
이전 포스트

Basic SELECT

다음 포스트

Additional SELECT Option

0개의 댓글