SQL - 03 함수

함수

컬럼의 값을 읽어서 연산한 결과를 반환

• 단일행(SINGLE ROW) 함수
  • N개의 값을 읽어 N개의 결과 반환.
• 그룹(GROUP) 함수
  • N개의 값을 읽어 1개의 결과 반환.
• 함수는 SELECT절, WHERE절, ORDER BY, GRUPT BY, HAVING 사용 가능.

단일행 함수

LENGTH

LENGTH(문자열 | 칼럼) : 문자열 길이 반환

SELECT LENGTH ('HELLO WORLD')
FROM DUAL;

INSTR

지정한 위치부터 지정한 순번째로 검색되는 문자의 시작 위치를 반환

INSTR('문자열' | 컬럼명, '찾을 문자', [찾을 위치 시작 위치, [순번] ] )

-- 문자열에서 맨 앞에 있는 B 위치 조회
SELECT INSTR('AABAACAABBAA', 'B') FROM DUAL;

-- 문자열에서 5번째 부터 검색해서 맨 앞에 있는 B 위치 조회
SELECT INSTR('AABAACAABBAA', 'B', 5) FROM DUAL;

-- EMPLOYEE 테이블에서 사원명, 이메일, 이메일 중 '@' 위치 조회
SELECT EMP_NAME, EMAIL, INSTR(EMAIL, '@')
FROM EMPLOYEE;

SUBSTR

컬럼이나 문자열에서 지정한 위치부터 지정된 길이만큼 문자열을 잘라내서 반환

SUBSTR('문자열' | 컬럼명, 잘라내기 시작할 위치 , [잘라낼 길이] )

잘라낼 길이 생략 시 끝까지 잘라낸다.

-- EMPLOYEE 테이블에서 사원명, 이메일 중 아이디만 조회
SELECT EMP_NAME, SUBSTR(EMAIL, 1, INSTR(EMAIL, '@') - 1 ) 아이디
FROM EMPLOYEE;

TRIM

주어진 컬럼이나 문자열의 앞, 뒤, 양쪽에 있는 지정된 문자를 제거

TRIM([옵션] '문자열' | 컬럼명 [FROM '문자열'|컬럼명] )

보통 양쪽 공백 제거에 많이 사용한다.

• LEADING (앞쪽)
• TRAILING (뒤쪽)
• BOTH (양쪽, 기본값)

SELECT '      A B      ', TRIM('     A B     ') FROM DUAL; 
-- 양쪽 공백 제거
-- 가운데 공백은 제거되지 않는다

SELECT '---AB---', TRIM( '-' FROM '---AB---') FROM DUAL;
-- BOTH 또는 생략 :  양쪽 '-' 기호 제거
-- LEADING: 앞쪽만 제거
-- TRAILING : 뒤쪽만 제거

숫자 관련 함수

ABS

ABS(숫자 | 컬럼명) : 절대 값

SELECT ABS(10), ABS(-10) FROM DUAL;

MOD

MOD(숫자 | 컬럼명, 숫자 | 컬럼명) : 나머지 값 반환

-- EMPLOYEE 테이블에서 사원의 월급을 100만으로 나눴을 때 나머지 조회
SELECT EMP_NAME, SALARY, MOD(SALARY, 1000000)
FROM EMPLOYEE;

ROUND

ROUND(숫자 | 칼럼명 , [소수점 위치]) : 반올림

SELECT 123.456, ROUND(123.456) FROM DUAL; -- 소수점 첫째 자리에서 반올림
SELECT 123.456, ROUND(123.456, 1) FROM DUAL; -- 소수점 둘째 자리에서 반올림
SELECT 123.456, ROUND(123.456, 2) FROM DUAL; -- 소수점 셋째 자리에서 반올림

SELECT 123.456, ROUND(123.456, 0) FROM DUAL; -- 소수점 첫째 자리에서 반올림
SELECT 123.456, ROUND(123.456, -1) FROM DUAL; 
-- 소수점 -1번째 자리에서 반올림 = 1의 자리 반올림

CEIL

CEIL(숫자 | 컬럼명) : 소수점 첫째 자리 올림

FLOOR

FLOOR(숫자 | 컬럼명) : 소수점 첫째 자리 내림

SELECT 123.5, CEIL(123.5), FLOOR(123.5) FROM DUAL;

TRUNC

TRUNC(숫자 | 칼럼명 [,위치]) : 특정 위치 아래를 버림

SELECT TRUNC(123.456, 1), TRUNC(123.456, -1)
FROM DUAL;

-- 버림, 내림의 차이점
SELECT TRUNC(-123.5), FLOOR(-123.5)
FROM DUAL;
-- FLOOR는 없에는게 아니라 내리는거임

날짜 관련 함수

SYSDATE

SYSDATE : 시스템의 현재 시간(년,원,일,시,분,초)을 반환

SELECT SYSDATE FROM DUAL;

-- SYSIMESTAMP : SYSDATE + MS(밀레세컨드) 단위 추가
SELECT SYSTIMESTAMP FROM DUAL;

-- MONTHS_BETWEEN(날짜, 날짜) : 두 날짜의 개월 수 차이 반환
SELECT ROUND(MONTHS_BETWEEN( SYSDATE, '2022/02/21') ) || '개월' FROM DUAL;

MONTHS_BETWEEN

A와 B 사이 개월수

-- EMPLOYEE 테이블에서
-- 사원의 이름, 입사일, 현재 근무 개월 수, 근무 햇수 조회
SELECT EMP_NAME, HIRE_DATE,
    '근무' || CEIL( MONTHS_BETWEEN(SYSDATE, HIRE_DATE) ) || '개월 차' "근무 개월 수",
    '근무' || CEIL( MONTHS_BETWEEN(SYSDATE, HIRE_DATE) /12) || '년 차' "근무 개월 수"
FROM EMPLOYEE;

ADD_MONTH

날짜에 숫자만큼의 개월 수를 더함

SELECT ADD_MONTHS(SYSDATE,4) +7 FROM DUAL;

LAST_DAY(날짜)

해당 달의 마지막 날짜를 구함.

SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT LAST_DAY('2022/04/01') FROM DUAL;

EXTRACT

년, 월, 일 정보를 추출하여 리턴

• EXTRACT(YEAR FROM 날짜) : 년도만 추출
• EXTRACT(MONTH FROM 날짜) : 월만 추출
• EXTRACT(DAY FROM 날짜) : 일만 추출

-- EMPLOYEE 테이블에서 각 사원의 이름, 입사 년도, 입사 월, 일사 일 조회
SELECT EMP_NAME 이름,
    EXTRACT(YEAR FROM HIRE_DATE) "입사 년도",
    EXTRACT(MONTH FROM HIRE_DATE) "입사 월",
    EXTRACT(DAY FROM HIRE_DATE) "입사 일"
FROM EMPLOYEE

WHERE EXTRACT(MONTH FROM HIRE_DATE) = 1;

형변환 함수

문자'열'(CHAR), 숫자(NUMBER), 날짜(DATE)끼리 서로 형변환이 가능하다

문자열로 변환

• TO_CHAR(날짜, [포맷]) : 날짜형 데이터를 문자형 데이터로 변경
• TO_CHAR(숫자, [포맷]) : 숫자형 데이터를 문자형 데이터로 변경

패턴

• 9 : 숫자 한칸을 의미한다, 여러 개 작성 시 오른쪽 정렬
• 0 : 숫자 한칸을 의미한다, 여러 개 작성 시 오른쪽 정렬, + 빈칸 0 추가
• L : 현재 DB에 설정된 나라의 화폐 기호

SELECT TO_CHAR(1234, '99999') FROM DUAL; -- 숫자 5칸, 오른쪽 정렬
SELECT TO_CHAR(1234, '00000') FROM DUAL; -- 숫자 5칸, 오른쪽 정렬, 빈칸 0 추가

SELECT TO_CHAR(1000000, '9,999,999') FROM DUAL; -- 자릿수 구분
SELECT TO_CHAR(1000000, 'L9,999,999') FROM DUAL;
SELECT TO_CHAR(1000000, '$9,999,999') FROM DUAL;

-- 직원들의 급여를 '\999,999,999' 형식으로 조회
SELECT EMP_NAME, TO_CHAR(SALARY, 'L999,999,999') 급여
FROM EMPLOYEE;

날짜에 TO_CHAR 적용

• YYYY : 년도 / YY : 년도 (짧게)
• RRRR : 년도 / RR : 년도 (짧게)
• MM : 월 / DD : 일
• AM 또는 PM : 오전/오후 표시 (AM이라고 무조건 오전X)
• HH : 시간 / HH24 : 24시간 표기법
• MI : 분
• SS : 초
• DAY : 요일(전체) DY : 요일(요일명만 짧게 표시)

SELECT SYSDATE, TO_CHAR(SYSDATE, 'DY AM HH24:MI:SS') FROM DUAL;

-- 직원들의 입사일을 '2017년 12월 06일 (수)' 형식으로 출력
SELECT EMP_NAME, TO_CHAR(HIRE_DATE,  'YYYY"년" MM"원" DD"일" (DY)')
FROM EMPLOYEE;
--> 년, 월, 일은 오라클에 등록된 날짜 표기 패턴이 아니라서 오류
--> 기존에 없던 패턴 추가시 ""(쌍따옴표)로 감싸워서 문자열 그대로를 출력하게 한다.

날짜로 변환 TO_DATE

지정된 포맷으로 날짜를 인식한다.

• TO_DATE(문자형 데이터, [포맷]) : 문자형 데이터를 날짜로 변경
• TO_DATE(숫자형 데이터, [포맷]) : 숫자형 데이터를 날짜로 변경

SELECT '2022-03-21', TO_DATE('2022-03-21') FROM DUAL;
-- 앞에꺼는 문자형, 뒤에꺼는 날짜형

SELECT TO_DATE('20210103') FROM DUAL;
SELECT TO_DATE(20210103) FROM DUAL; -- 단순 숫자도 똑같이 변환 가능

SELECT TO_CHAR( TO_DATE('041030 143000', 'YYMMDD HH24MISS')
, 'YYYY/MM/DD HH24"시 "MI"분"' ) FROM DUAL;

-- EMPLOYEE 테이블에서 각 직원이 태어난 생년월일 조회
SELECT EMP_NAME, TO_DATE( SUBSTR(EMP_NO, 1, 6), 'RRMMDD')
FROM EMPLOYEE;

• Y : 현재 세기 (21세기)
• R : 1세기 기준으로 절반(50년) 이상이면 이전 세기(1900년대)
  절반(50년) 미만이면 현재 세기(2000년대)

SELECT TO_DATE('490115', 'YYMMDD') FROM DUAL;
SELECT TO_DATE('490115', 'RRMMDD') FROM DUAL;
SELECT TO_DATE('500115', 'YYMMDD') FROM DUAL;
SELECT TO_DATE('500115', 'RRMMDD') FROM DUAL;

SELECT TO_DATE('19490115', 'RRMMDD') FROM DUAL;

숫자 형변환

TO_NUMBER

: 문자형데이터를 숫자 데이터로 변경

TO_NUMBER(문자데이터, [포맷])

SELECT TO_NUMBER('1,000,000', '9,999,999') + 10 FROM DUAL;

NULL처리 함수

NULL인 컬럼값을 다른 값으로 변경한다.

NVL(컬럼명, 컬럼값이 NULL일때 바꿀 값)

NVL2(컬럼명, 바꿀값1, 바꿀값2)

숫자와 NULL 연산시 결과도 NULL이 된다.

-- EMPLOYEE 테이블에서 이름, 급여, 보너스, 급여*보너스 조회
SELECT EMP_NAME, SALARY, BONUS, SALARY * BONUS
FROM EMPLOYEE;

SELECT EMP_NAME, SALARY, NVL(BONUS, 0), SALARY * NVL(BONUS, 0)
FROM EMPLOYEE;

• NVL2
  • 해당 컬럼의 값이 있으면 바꿀값1로 변경
  • 해당 컬럼이 NULL이면 바꿀값2로 변경

-- EMPLOYEE 테이블에서
-- 기존 보너스를 받던 사원의 보너스를 0.8로
-- 보너스를 받지 못했던 사원의 보너스를 0.3 으로 변경하여
-- 이름, 기존 보너스, 변경된 보너스 조회
SELECT EMP_NAME 이름, NVL(BONUS, 0) "기존 보너스"
, NVL2(BONUS, 0.8, 0.3)
FROM EMPLOYEE;

선택 함수

여러 가지 경우에 따라 알맞은 결과를 선택할 수 있음

DECODE

DECODE(계산식 | 컬럼명, 조건값1, 선택값1, 조건값2, 선택값2.....)

• 비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환한다.
• 일치하는 값을 확인한다.(자바의 SWITCH와 비슷함)

-- 직원들의 성별 구분하기
SELECT EMP_NAME, DECODE( SUBSTR(EMP_NO, 8, 1), '1', '남자', '2', '여자' ) 성별
FROM EMPLOYEE;

-- 직원의 급여를 인상하고자 한다
-- 직급코드가 J7인 직원은 급여의 10%를 인상하고
-- 직급코드가 J6인 직원은 급여의 15%를 인상하고
-- 직급코드가 J5인 직원은 급여의 20%를 인상하며
-- 그 외 직급의 직원은 급여의 5%만 인상한다.
-- 직원 테이블에서 직원명, 직급코드, 급여, 인상급여(위 조건)을 조회하세요

SELECT EMP_NAME, JOB_CODE, SALARY,
    DECODE(JOB_CODE, 'J7', SALARY * 1.1, 
                     'J6', SALARY * 1.15,
                     'J5', SALARY * 1.2,
                     SALARY * 1.05) 인상급여
                     --제일 마지막은 ELSE라고 보면 된다.
FROM EMPLOYEE;

CASE

IF ELSE와 유사하게 비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값을 반환한다.

-- CASE WHEN 조건식 THEN 결과값
--      WHEN 조건식 THEN 결과값
--      ELSE 결과값
-- END

SELECT EMP_ID, EMP_NAME, SALARY,
    CASE
        WHEN SALARY >= 5000000 THEN '고급'    
        -- WHEN SALARY >= 3000000 AND SALARY < 5000000 THEN '중급'
        WHEN SALARY >= 3000000 THEN '중급' --IF ELSE와 유사하게 다시 고려 안해도 됨
        ELSE '초급'
    END
FROM EMPLOYEE
WHERE DEPT_CODE = 'D6'
ORDER BY JOB_CODE ASC; -- 정렬 기준이 되는 컬럼이 반드시 SELECT 절에 포함될 필요는 없다

그룹 함수

하나 이상의 행을 그룹으로 묵어 연산하여 총합, 평균 등의 하나의 결과로 행을 반환하는 함수

SUM

합계

--  부서코드가 'D9'인 직원들의 급여 합
SELECT SUM(SALARY) FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';

-- AVG(숫자가 기록된 컬럼명) : 평균
-- 전 직원 급여 평균
SELECT ROUND( AVG(SALARY) ) FROM EMPLOYEE;

MIN, MAX

MIN(컬렴명) : 최소값 MAX(컬럼명) : 최대값

타입 제한 없음 (숫자 : 대/소, 날짜 : 과거/미래, 문자열 : 문자 순서)

SELECT MIN(SALARY), MIN(HIRE_DATE), MIN(EMAIL)
FROM EMPLOYEE;
-- 그룹 함수는 여러 개를 동시 작성 가능한데
-- 이 때 결과는 실제 테이블 한 행이 아니고
-- 각 그룹함수 별 독립된 결과이다.

-- EMPLOYEE 테이블에서 가장 높은 급여
-- 가장 늦은 입사일,
-- 알파벳 순서가 가장 늦은 이메일 
/*3*/SELECT MAX(SALARY), MAX(HIRE_DATE), MAX(EMAIL)
/*1*/FROM EMPLOYEE
/*2*/WHERE EMP_ID != 200;

COUNT

1. COUNT(* | 컬럼명) : 행 개수를 헤아려서 리턴
2. COUNT([DISTINCT] 컬럼명) : 중복을 제거한 행 개수를 헤아려서 리턴
3. COUNT(*) : NULL을 포함한 전체 행 개수를 리턴
4. COUNT(컬럼명) : NULL을 제외한 실제 값이 기록된 행 개수를 리턴함

-- COUNT(칼럼명) : NULL을 제외한 실제 값이 기록된 행 개수를 리턴함
SELECT COUNT(DEPT_CODE) FROM EMPLOYEE;

--EMPLOYEE 테이블의 남자 직원 수 조회
SELECT COUNT(*) FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) =1;

SELECT COUNT(
    CASE
        WHEN SUBSTR(EMP_NO, 8, 1) = '1' THEN '남자'
    END) 남자
FROM EMPLOYEE;

SELECT COUNT(DECODE(SUBSTR(EMP_NO, 8, 1), '1', 1)) FROM EMPLOYEE;