함수

▶ 함수(Function)

• 함수란?
  - 컬럼의 값을 읽어서 연산한 결과를 반환하는 것
• 단일행(Sigle Row) 함수
  - N개의 값을 읽어서 N개의 결과를 반환
• 그룹(Group) 함수
  - N개의 값을 읽어서 1개의 결과를 반환
• 함수는 SELECT절, WHERE절, ORDER BY절, GROUP BY절, HAVING절에서 사용 가능

▶ 단일행 함수(Function)

• 문자열 관련 함수
  - LENGTH (문자열 | 컬럼명) : 문자열의 길이 반환

SELECT 'HELLO WORLD', LENGTH('HELLO WORLD') FROM DUAL;

    - INSTR (문자열 | 컬럼명, '찾을 문자열', [, 찾을 시작 위치 [, 순번])
      : 지정한 위치부터 지정한 순번째로 검색되는 문자의 시작 위치를 반환

EX-1) 문자열에서 맨 앞에 있는 'B'의 위치를 조회
SELECT 'AABAACAABBAA', INSTR('AABAACAABBAA', 'B') FROM DUAL; -- 결과 : 3

EX-2) 문자열 검색을 5번째부터 시작해서 처음 나오는 'B'의 위치를 조회
SELECT 'AABAACAABBAA', INSTR('AABAACAABBAA', 'B', 5) FROM DUAL; -- 결과 : 9

EX-3) 문자열 검색을 5번째부터 시작해서 두 번째에 나오는 'B'의 위치를 조회
SELECT 'AABAACAABBAA', INSTR('AABAACAABBAA', 'B', 5, 2) FROM DUAL; -- 결과 : 10

   - SUBSTR (문자열 | 컬럼명, 시작위치 [, 길이])
   : 문자열을 시작위치부터 지정된 길이만큼 잘라내서 반환 / 길이 미작성 시 시작위치 ~ 끝까지 잘라내서 반환

-- EX) EMPLOYEE 테이블에서 사원명, 이메일 아이디(@ 앞에까지의 문자열)을
-- 이메일 아이디 오름차순으로 조회
SELECT EMP_NAME, EMAIL, SUBSTR(EMAIL, 1, INSTR(EMAIL,'@')-1) "이메일 아이디" FROM EMPLOYEE
ORDER BY "이메일 아이디";

   - TRIM ( [ 옵션 ] '문자열' | 컬럼명 [ FROM '문자열' | 컬럼명 ] )
   : 옵션 ] LEADING(앞쪽), TRAILING(뒤쪽), BOTH(양쪽)
   : 주어진 컬럼이나 문자열의 앞, 뒤, 양쪽에 있는 지정된 문자를 제거

SELECT '     K    H     ', TRIM('     K    H     ') FROM DUAL; -- 양쪽 공백 제거(중간 미포함)

SELECT '---KH---', TRIM('-' FROM '---KH---') FROM DUAL; -- 결과 : KH

SELECT '---KH---', TRIM(LEADING '-' FROM '---KH---') FROM DUAL; -- 결과 : KH---

SELECT '---KH---', TRIM(TRAILING '-' FROM '---KH---') FROM DUAL; -- 결과 : ---KH

SELECT '---KH---', TRIM(BOTH '-' FROM '---KH---') FROM DUAL; -- 결과 : KH

• 숫자 관련 함수
  
  - ABS (숫자 | 컬럼명) : 절대값
  
  

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

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

EX) EMP 테이블에서 사원의 월급을 100만으로 나눴을 때, 나머지 조회

SELECT EMP_NAME, SALARY, MOD(SALARY, 1000000) FROM EMPLOYEE;

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

SELECT 123.456, ROUND(123.456) "소수점 첫째 자리에서 반올림" FROM DUAL; -- 결과 : 123

SELECT 123.456, ROUND(123.456, 1) "소수점 첫째 자리까지 출력" FROM DUAL; -- 결과 : 123.5

SELECT 123.456, ROUND(123.456, 2) "소수점 둘째 자리까지 출력" FROM DUAL; -- 결과 : 123.46


SELECT 123.456, ROUND(123.456, 0) "소수점 첫째 자리에서 반올림" FROM DUAL; -- 결과 : 123

SELECT 123.456, ROUND(123.456, -1) "소수점 0번째 자리에서 반올림" FROM DUAL; -- 결과 : 120

SELECT 123.456, ROUND(123.456, -2) "소수점 -1번째 자리에서 반올림" FROM DUAL; -- 결과 : 100

   - CEIL (숫자 | 컬럼명) : 올림
     FLOOR (숫자 | 컬럼명) : 내림
     → 전부 소수점 첫째 자리에서 올림 / 내림 처리

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

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

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

   - 버림 / 내림 차이점

SELECT TRUNC(-123.5), FLOOR(-123.5) FROM DUAL;

• 날짜(DATE) 관련 함수
  - SYSDAE : 시스템에 현재 시간(년, 월, 일, 시, 분, 초)을 반환
  
  

SELECT SYSDATE FROM DUAL;

   - SYSTIMESTAMP : SYSDATE + MS(1/1000초) 단위 추가

SELECT SYSTIMESTAMP FROM DUAL;

   - MONTHS_BETWEEN(날짜, 날짜) : 두 날짜의 개월 수 차이를 반환

SELECT ROUND(MONTHS_BETWEEN(SYSDATE, '2024/10/16')) || '개월' AS 수강기간 FROM DUAL;

   - ADD_MONTHS(날짜, 숫자) : 날짜에 숫자만큼의 개월 수를 더함

SELECT ADD_MONTHS(SYSDATE, 4) + 7 FROM DUAL; -- 현재일자 + 4개월 + 7일

   - LAST_DAY(날짜) : 해당 월의 마지막 날짜를 구함

SELECT LAST_DAY('25/04/01') FROM DAUL;

SELECT LAST_DAY(SYSDATE) FROM DUAL;

   - EXTRACT : 년, 월, 일 정보를 추출하여 리턴
     EXTRACT(YEAR FROM 날짜) : 연도만 추출
     EXTRACT(MONTH FROM 날짜) : 월(月)만 추출
     EXTRACT(DAY FROM 날짜) : 일(日)만 추출

EX-5) EMP 테이블에서 입사 월이 9월인 사원의 이름, 입사 년도, 입사 월, 입사 일을 조회

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) = 9;

• 형변환 함수
  - 문자열(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; -- 화폐기호(달러)

   - 날짜에 TO_CHAR 적용
   ▷ YYYY : 년도 / YY : 년도 (짧게)
   ▷ RRRR : 년도 / RR : 년도 (짧게)
   ▷ MM : 월 / DD : 일
   ▷ AM 또는 PM : 오전 / 오후 표시
   ▷ HH : 시간 / HH24 : 24시간 표기법
   ▷ MI : 분 / SS : 초
   ▷ DAY : 요일(전체) / DY : 요일(요일 명만 표시)

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

EX-7) 직원들의 입사일을 '0000년 00월 00일 (요일명)' 형식으로 조회
SELECT EMP_NAME, TO_CHAR(HIRE_DATE, 'YYYY"년"MM"월"DD"일" (DY)') AS "입사일" FROM EMPLOYEE;

-- 년, 월, 일은 오라클에 등록된 날짜 표기 패턴이 아니라서 오류 발생 가능
--> 기존에 없던 패턴 추가할 경우 " "(쌍따옴표)로 감싸줘서 문자열 그대로 출력하게 함

   ▷ 날짜로 변환 TO_DATE
   - TO_DATE( 문자형 데이터, [포맷] ) : 문자형 데이터를 날짜로 변경
   - TO_DATE( 숫자형 데이터, [포맷] ) : 숫자형 데이터를 날짜로 변경
   → 지정된 포맷으로 날짜를 인식함

SELECT '2024-12-08', TO_DATE('2024-12-08') FROM DUAL;

SELECT TO_DATE('20241208') FROM DUAL;

SELECT TO_DATE(20241208) FROM DUAL;

SELECT TO_DATE('20241208 114500', 'YYMMDD HH24MISS') FROM DUAL;

-- 2024/12/08 11시 47분

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

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

-- EX) EMP 테이블에서 각 직원이 태어난 생년월일 조회(단, 생년이 1950년대 미만인 직원은 없음)
SELECT EMP_NAME,
TO_DATE(SUBSTR(EMP_NO, 1, 6), 'RRMMDD') AS 생년월일 FROM EMPLOYEE;

SELECT TO_DATE('491119', 'RRMMDD') FROM DUAL; -- 결과 : 2049-11-19 00:00:00.000

SELECT TO_DATE('501119', 'RRMMDD') FROM DUAL; -- 결과 : 1950-11-19 00:00:00.000

   ▷ 숫자 형변환
   - TO_NUMBER( 문자데이터, [포맷] ) : 문자형 데이터를 숫자 데이터로 변경

SELECT '1,000,000' + 10 FROM DUAL; -- 계산 불가(오류 발생)

SELECT TO_NUMBER('1,000,000', '9,999,999') + 10 FROM DUAL; -- 결과 : 1,000,010

   ▷ NULL 처리 함수
   - NVL( 컬럼명, 컬럼값이 NULL일 때 바꿀 값) : NULL인 컬럼 값을 다른 값으로 변경

-- EX) EMP 테이블에서 이름, 급여, 보너스, 급여 * 보너스 조회
-- 보너스가 NULL인 경우, 급여 * 보너스 연산 결과도 NULL로 조회
-- 이를 방지하기 위해 NVL 함수 사용

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

   - NVL2( 컬럼명, 바꿀 값 1, 바꿀 값2)
   : 해당 컬럼의 값이 있으면 바꿀 값1로 변경
   : 해당 컬럼의 값이 NULL이라면 바꿀 값2로 변경

-- EX) EMP 테이블에서
-- 기존 보너스를 받던 사원의 보너스를 0.7로
-- 보너스를 받지 못했던 사원의 보너스를 0.4로 변경하여
-- 이름, 기존 보너스, 변경된 보너스를 조회
-- (단, 기존 보너스는 값이 없을 경우 0으로 나타나게 함)

SELECT EMP_NAME 이름, 
NVL(BONUS, 0) "기존 보너스", 
NVL2(BONUS, 0.7, 0.4) "변경된 보너스"
FROM EMPLOYEE;

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

   - DECODE( 계산식 | 컬럼명, 조건값1, 선택값1, 조건값2, 선택값2, ..., 아무 것도 일치하지 않을 때)
   : 비교하고자 하는 값 또는 컬럼이 조건값과 같으면 선택값(결과값) 반환
   : 일치하는 값을 확인 (자바의 SWITCH 문과 비슷함)

-- EX) 직원들의 성별 구분해서 조회(DECODE)
SELECT EMP_NAME, DECODE( SUBSTR(EMP_NO,8,1), '1', '남자', '2', '여자')) 성별 FROM EMPLOYEE;

   - CASE WHEN 조건식 THEN 결과값
                WHEN 조건식 THEN 결과값
                ELSE 결과값
     END
   - 비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과값 반환
   - 조건은 범위값 가능

-- EX) 직원들의 성별 구분해서 조회(CASE - WHEN)
SELECT EMP_NAME,
   CASE WHEN SUBSTR(EMP_NO, 8, 1) = 1 THEN '남자'
        WHEN SUBSTR(EMP_NO, 8, 1) = 2 THEN '여자'
   END 성별
FROM EMPLOYEE;

   ▷ 그룹 함수
   - 하나 이상의 행을 그룹으로 묶어서 연산하여 총합, 평균 등의 하나의 결과 행으로 변환하는 함수
   - SUM( 숫자가 기록된 컬럼명) : 합계

-- EX) EMP 테이블에서 모든 직원의 급여 합계 조회
SELECT SUM(SALARY) "전 직원 급여 합" FROM EMPLOYEE;

   - AVG( 숫자가 기록된 컬럼명) : 평균

-- EX) 전 직원의 급여 평균 조회
SELECT AVG(SALARY) "전 직원 급여 평균" FROM EMPLOYEE;

   - MIN( 컬럼명 ) : 최소값
   - MAX( 컬럼명 ) : 최대값
   → 타입 제한 없음(숫자 : 대 / 소, 날짜: 과거 / 미래, 문자열: 문자 순서)

-- EX) 전 직원의 정보에서 가장 낮은 급여, 가장 빠른 입사일, 알파벳 순서가 가장 빠른 이메일 주소 조회
SELECT MIN(SALARY), MIN(HIRE_DATE), MIN(EMAIL) FROM EMPLOYEE;

   - 그룹 함수는 여러 항목을 동시에 작성 가능
   - 이때, 결과는 각 그룹 함수별 독립된 결과 조회

-- EX) 전 직원 정보 중에서 가장 높은 급여, 가장 늦은 입사일, 알파벳 순서가 가장 나중인 이메일 주소 조회
SELECT MAX(SALARY), MAX(HIRE_DATE), MAX(EMAIL) FROM EMPLOYEE;

   - ★ COUNT( * | 컬럼명) : 행의 개수를 헤아려서 반환 ★
   - COUNT( [DISTINCT] 컬럼명 ) : 중복을 제거한 행의 개수를 헤아려서 반환
   - COUNT( * ) : NULL을 포함한 전체 행의 개수를 반환
   - COUNT(컬럼명) : NULL을 제외한 실제 값이 기록된 행의 개수를 반환

-- EX) EMP 테이블 전체 행의 개수 조회 ( == 전체 직원 수)
SELECT COUNT(*) FROM EMPLOYEE; -- 결과 : 23

-- EX) 부서코드가 NULL이 아닌 행의 개수( == 직원 수)
SELECT COUNT(*) FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL; -- 결과 : 21

   - COUNT(컬럼명) : NULL을 제외한 실제 값이 기록된 행의 개수를 반환

-- EX) 부서코드가 NULL이 아닌 행의 개수( == 직원 수)
SELECT COUNT(DEPT_CODE) FROM EMPLOYEE -- 결과 : 21 (위와 동일)

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

-- 2) COUNT 함수 + CASE-WHEN 함수
SELECT COUNT(
   CASE WHEN SUBSTR(EMP_NO, 8, 1) = 1 THEN '남자'
   END 남직원)
FROM EMPLOYEE;

-- 3) SUM 함수 + DECODE 함수
SELECT SUM( DECODE( SUBSTR(EMP_NO, 8, 1), 1, 1 ) ) 남직원 FROM EMPLOYEE;

-- EX) EMP 테이블에 있는 부서의 개수 조회(중복 제외)
SELECT COUNT(DISTINCT DEPT_CODE) FROM EMPLOYEE;
--> 7행의 결과가 있지만, 1행이 NULL이기 때문에 COUNT 시 제외되어 6행 조회됨