1. 함수(Function)

함수

• 하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리하여 작성해 놓은 작은 서브 프로그램
• 함수의 종류는 무척 많기 때문의 자바의 API처럼 자주 쓰는 함수부터 이해
• 함수는 SELECT절, WHERE절, ORDER BY절, GROUP BY절, HAVING절 등에서 사용 가능

• 1) 단일행 함수:
  N개의 행의 값을 전달하여 N개의 결과를 반환
  2) 그룹 함수:
  N개의 행의 값을 하나의 그룹으로 묶어 그룹 수만큼의 결과를 반환

2. 문자 처리 함수

문자 처리 함수

• LENGTH:
  주어진 컬럼 값/문자열의 길이(문자 개수) 반환

-- EMPLOYEE 테이블에서 이메일이 12글자인 사원의 이름, 이메일 조회
SELECT EMP_NAME, EMAIL
FROM EMPLOYEE
WHERE LENGTH(EMAIL) = 12;

-- EMPLOYEE 테이블에서 이메일 길이 오름차순 조회
SELECT EMP_NAME, EMAIL 
FROM EMPLOYEE
ORDER BY LENGTH(EMAIL) ASC;

• INSTR:
  지정한 위치부터 지정한 숫자 번째로 나타나는 문자의 시작 위치 반환

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

-- 문자열을 5번째부터 검색하여 처음 찾는 B의 위치 조회
SELECT 'AABAACAABBAA', INSTR('AABAACAABBAA', 'B', 5) -- 9
FROM DUAL;

-- 문자열을 5번째부터 검색하여 두번쨰로 찾는 B의 위치 조회
SELECT 'AABAACAABBAA', INSTR('AABAACAABBAA', 'B', 5, 2) -- 10
FROM DUAL;

• SUBSTR:
  컬럼이나 문자열에서 지정한 위치부터 지정한 개수의 문자열을 잘라내어 반환
  (길이 미작성 시 시작 위치부터 끝까지 잘라내서 반환)

SELECT SUBSTR('ABCDEF', 3, 3) -- CDE
FROM DUAL;

SELECT SUBSTR('ABCDEF', 3) -- CDEF
FROM DUAL;

-- EMPLOYEE 테이블에서 사원명과 사원의 이메일 아이디 오름차순 조회
SELECT EMP_NAME, SUBSTR(EMAIL, 1, INSTR(EMAIL, '@')-1) 아이디
FROM EMPLOYEE
ORDER BY 아이디;

• LOWER/UPPER/INITCAP:
  컬럼의 문자 혹은 문자열을 소문자/대문자/첫 글자만 대문자로 변환하여 반환
• LPAD/RPAD:
  주어진 컬럼, 문자열에 임의의 문자열을 왼쪽/오른쪽에 덧붙여 길이 N의 문자열 반환
• TRIM:
  주어진 컬럼, 문자열의 앞/뒤/양쪽에 있는 지정한 문자를 제거한 나머지 반환

SELECT '#####K H#####',
	TRIM(LEADING '#' FROM '#####K H#####') "앞쪽 제거",
	TRIM(TRAILING '#' FROM '#####K H#####') "뒤쪽 제거",
	TRIM(BOTH '#' FROM '#####K H#####') "양쪽 제거"
FROM DUAL;

• REPLACE:
  찾을 문자열을 바꿀 문자열로 변환하여 문자열 반환

-- NATIONAL 테이블에서 한국을 대한민국으로 변경하여 조회
SELECT REPLACE(NATIONAL_NAME, '한국', '대한민국')
FROM NATIONAL;

3. 숫자 처리 함수

숫자 처리 함수

• MOD:
  인자로 전달 받은 숫자를 나누어 나머지 반환

• ABS:
  인자로 전달 받은 숫자의 절대값 반환

• CEIL/FLOOR:
  인자로 전달 받은 숫자 혹은 컬럼을 올림 후/내림 후 반환

SELECT 123.5, -- 123.5
	CEIL(123.5), -- 124
    FLOOR(123.5) -- 123
FROM DUAL;

• ROUND:
  인자로 전달 받은 숫자 혹은 컬럼에서 지정한 위치부터 반올림하여 값 반환
  • 소수점 위치를 지정 X: 첫째 자리에서 반올림(정수)
  • 소수점 위치를 지정 O(양수): 지정된 위치의 소수점까지 표현
  • 소수점 위치를 지정 O(음수): 지정된 위치의 정수 자리수까지 표현

SELECT 123.456, -- 123.456
	ROUND(123.456), -- 123(소수점 첫째 자리에서 반올림)
	ROUND(123.456, 1), -- 123.5(소수점 첫째 자리까지 표현)
	ROUND(123.456, 2), -- 123.46(소수점 둘째 자리까지 표현)
	ROUND(123.456, 0), -- 123(소수점 0번째 자리까지 표현(정수))
	ROUND(123.456, -1), -- 120(소수점 -1번째 자리까지 표현(1의 자리 반올림))
	ROUND(123.456, -2) -- 100(소수점 -2번째 자리까지 표현(10의 자리 반올림))
FROM DUAL;

• TRUNC:
  인자로 전달 받은 숫자 혹은 컬럼에서 지정한 위치부터의 자리의 수를 버리고(절삭) 반환

SELECT EMP_NAME, TRUNC(SALARY, -6) || '이상' 급여
FROM EMPLOYEE;

4. 날짜 처리 함수

날짜 처리 함수

• SYSDATE:
  시스템에 저장되어 있는 현재 날짜 반환
• SYSTIMESTAMP:
  SYSDATE 반환값에 MS 단위, UTC 표준 시차까지 추가해서 반환
• MONTHS_BETWEEN:
  인자로 날짜 두 개를 전달받아 개월 수 차이를 숫자 데이터형으로 반환

SELECT '약'
	|| ROUND(MONTHS_BETWEEN('2023/07/14', '2023/02/06'))
	|| '개월' 훈련기간
FROM DUAL;

-- EMPLOYEE 테이블에서 사원의 이름, 입사일, 근무 연차, 근속 개월 수
SELECT EMP_NAME, HIRE_DATE,
	CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12) "근무 연차",
	FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) "근속 개월 수"
FROM EMPLOYEE;

• ADD_MONTHS:
  인자로 전달받은 날짜에 인자로 받은 숫자만큼 개월 수를 더하여 특정 날짜 반환

SELECT ADD_MONTHS(SYSDATE, -1), -- 한달 전
	SYSDATE,
    ADD_MONTHS(SYSDATE, 1) -- 한달 후
FROM DUAL;

• LAST_DAY:
  인자로 전달받은 날짜가 속한 달의 마지막 날짜 반환
• EXTRACT:
  년, 월, 일 정보 추출하여 반환

5. 형변환 함수

형변환 함수(TO_CHAR)

• TO_CHAR:
  날짜 혹은 숫자형 데이터를 문자형 데이터로 변환하여 반환
• 숫자 -> 문자열 포맷
  • 9: 숫자 한 칸을 의미, 오른쪽 정렬
  • 0: 숫자 한 칸을 의미, 오른쪽 정렬, 빈 칸에 0을 추가
  • L: 현재 시스템이나 DB에 설정된 나라의 화폐 기호
  • ,: 숫자의 자릿수 구분
• 날짜 -> 문자열 포맷
  • YY: 년도(짧게)
  • YYYY: 년도(길게)
  • RR: 년도(짧게)
  • RRRR: 년도(길게)
  • MM: 월
  • DD: 일
  • AM/PM: 오전/오후
  • HH: 시간(12시간)
  • HH24: 시간(24시간)
  • MI: 분
  • SS: 초
  • DAY: 요일(길게)
  • DY: 요일(짧게)

SELECT EMP_NAME,
	TO_CHAR(SALARY, ‘L999,999,999’),
	TO_CHAR(SALARY, ‘000,000,000’)
FROM EMPLOYEE;

SELECT EMP_NAME,
	TO_CHAR(HIRE_DATE, 'YYYY-MM-DD'),
	TO_CHAR(HIRE_DATE, 'YY/MON, DAY, DY')
FROM EMPLOYEE;

• 포맷에 포함되지 않는 글자는 "" 내부에 작성

SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일" DAY') TODAY
FROM DUAL;

• 연도 타입 Y와 R의 차이점:
  연도를 짧게 해석하는 경우에
  1) 50 미만: Y와 R 모두 앞부분에 현재 세기를 적용
  2) 50 이상: Y는 앞부분에 현재 세기를 적용, R은 이전 세기를 적용

SELECT TO_DATE('490115', 'YYMMDD'), -- 2049-01-15
	TO_DATE('490115', 'RRMMDD'), -- 2049-01-15
	TO_DATE('500115', 'YYMMDD'), -- 2050-01-15
	TO_DATE('500115', 'RRMMDD') -- 1950-01-15
FROM DUAL;

기타 형변환 함수

• TO_DATE:
  숫자 혹은 문자형 데이터를 날짜형 데이터로 변환하여 반환

-- EMPLOYEE테이블에서 2000년도 이후에 입사한 사원의 사번, 이름, 입사일 조회
SELECT EMP_NO, EMP_NAME, HIRE_DATE
FROM EMPLOYEE
WHERE HIRE_DATE > TO_DATE(20000101, ‘YYYYMMDD’);

SELECT 20230308,
	TO_DATE(20230308)
FROM DUAL;

• TO_NUMBER:
  날짜 혹은 문자형 데이터를 숫자형 데이터로 변환하여 반환

SELECT TO_NUMBER('$1,500', '$9,999')
FROM DUAL;

6. NULL 처리 함수

NULL 처리 함수

• NVL:
  NULL로 되어 있는 컬럼의 값을 인자로 지정한 숫자 혹은 문자로 변경하여 반환

SELECT EMP_NAME, SALARY,
	NVL(BONUS, 0), -- 보너스가 없으면 0으로 처리
	NVL(SALARY * BONUS, SALARY) -- 보너스가 없으면 급여만 반환
FROM EMPLOYEE

• NVL2:
  NVL과 같지만 NULL인 경우까지 생각하여 반환

NVL(컬럼명, 컬럼 값이 NULL일 경우 바꿀 값)
NVL2(컬럼명, 컬럼 값이 NULL이 아닌 경우 바꿀 값, 컬럼 값이 NULL일 경우 바꿀 값)

SELECT EMP_NAME, BONUS,
	NVL2(BONUS, BONUS + 0.2, 0.3) "변경된 BONUS"
FROM EMPLOYEE;