03. SQL 함수

-- 함수는 SELECT 문의 SELECT절, WHERE절, ORDER BY절, GROUP BY절, HAVING절 사용 가능

🔍 단일 행 함수

N개의 값을 읽어 N개의 결과를 반환

==== 문자 관련 함수 ====

📍 LENGTH

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

📍 INSTR

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

컬럼 또는 문자열에서 찾을 문자열을 지정한 위치부터 지정한 순번째로 검색되는 문자의 위치를 반환

SELECT INSTR(EMP_NO, '-', 1) FROM EMPLOYEE;

SELECT INSTR(EMAIL, '@') FROM EMPLOYEE;

📍 SUBSTR

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

컬럼이나 문자열에서 지정한 위치부터 지정된 길이만큼 문자열을 잘라서 반환
--> 잘라낼 길이 생략시 끝까지 잘라냄

SELECT SUBSTR(EMAIL, 1, INSTR(EMAIL,'@')-1,) FROM EMPLOYEE;

📍 TRIM

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

주어진 컬럼이나 문자열의 앞, 뒤, 양쪽에 있는 지정된 문자나 공백을 제거
[옵션] : BOTH(양쪽, 기본값), LEADING(앞), TRAILING(뒤쪽)

SELECT TRIM('    HELLO    ') FROM DUAL; -- HELLO

SELECT TRIM(BOTH '#' FROM '#####안녕######') FROM DUAL; -- 안녕
SELECT TRIM(TRAILING  '#' FROM '#####안녕######') FROM DUAL; -- #####안녕
SELECT TRIM(LEADING  '#' FROM '#####안녕######') FROM DUAL; -- 안녕#####

📍 UPPER

UPPER(컬럼명 | 문자열)

조회한 컬럼이 영문자일 경우 대문자로 바꿔주는 함수

SELECT UPPER('abcderg')
FROM DUAL;

==== 숫자 관련 함수 ====

📍 ABS

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

📍 MOD

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

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

-- EMPLOYEE 테이블에서 사번이 홀수인 사원의 사번, 이름 조회
SELECT EMP_ID , EMP_NAME 
FROM EMPLOYEE
WHERE MOD(EMP_ID, 2) <> 0; -- 같지 않다 ( !=랑 같음)

📍 ROUND

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

📍 CEIL / FLOOR

CEIL(컬럼명|숫자) : 올림
FLOOR(컬럼명|숫자) : 내림
--> 소수점 첫째 자리에서 올림 ==> 반올림 적용!

📍 TRUNC

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

==== 날짜(DATE)관련 함수 ====

📍 SYSDATE

시스템에 현재 시간(년, 월, 일, 시, 분, 초)를 반환

📍 SYSTIMESTAMP

SYSDATE + MS 단위 추가

📍 MONTHS_BETWEEN

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

SELECT MONTH_BETWEEN('2023-07-10', SYSDATE) AS "수강기간(개월)"

📍 ADD_MONTHS

ADD_MONTHS(날짜, 숫자) : 날짜에 숫자만큼의 개월 수를 더함 (음수도 가능)

📍 LAST_DAY

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

📍 EXTRACT

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

SELECT EMP_NAME , 
	EXTRACT(YEAR FROM HIRE_DATE) || '년' ||  
	EXTRACT(MONTH FROM HiRE_DATE) || '월' ||
	EXTRACT(DAY FROM HIRE_DATE) || '일' AS 입사일
FROM EMPLOYEE; 

==== 형변환 함수 ====

📍 TO_CHAR

<문자열로 반환>

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

! 숫자 변환 시 포맷 패턴
-- 9: 숫자 한칸을 의미, 여러개 작성 시 오른쪽 정렬
-- 0: 숫자 한칸을 의미, 여러개 작성 시 오른쪽 정렬 + 빈칸 0 추가
-- L: 현재 DB에 설정된 나라의 화페 기호

! 날짜 변환 시 포맷 패턴
-- YYYY: 년도 / YY: 년도(짧게) --> YY는 모두 2000년대
-- BRRR: 년도 / RR: 년도(짧게) --> RR은 49이하는 2000년대 50이상은 1900년대
-- MM: 월
-- DD: 일
-- AM / PM: 오전 / 오후
-- HH: 시간 / HH24: 24시간 표기법
-- MI: 분 / SS: 초
-- DAY: 요일(전체) / DY: 요일(요일명만 표시 ex 목, 금)

SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일" (DY)')
--2023년 01월 10일 (화)

📍 TO_DATE

<날짜로 반환>

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

-- EMPLOYEE 테이블에서 태어난 생년월일(1990 년 05월 13) 조회 (별칭 생년월일)
SELECT EMP_NAME, 
	TO_CHAR(TO_DATE(SUBSTR(EMP_NO, 1, INSTR(EMP_NO,'-')- 1),'RRMMDD') ,'YYYY"년" MM"월" DD"일"') AS 생년월일
FROM EMPLOYEE;

📍 TO_NUMBER

<숫자 형변환>

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

==== NULL 처리 함수 ====

📍 NVL

NVL(컬럼명, 컬럼값이 NULL일 때 바꿀 값) : NULL인 컬럼값을 다른 값으로 변경

--> DB는 NULL과 산술 연산을 하면 결과는 무조건 NULL!!!

📍 NVL2

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

==== 선택 함수 ====

📍 DECODE

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

-- 직원의 성별 구하기 (남 :1 / 여 :2)
SELECT EMP_NAME 이름, EMP_NO 주민등록번호, DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남성', '2', '여성') AS 성별
FROM EMPLOYEE;

-- 직원의 급여를 인상하고자 한다.
-- 직급 코드가 J7인 직원은 20% 인상
-- 직급 코드가 J6인 직원은 15% 인상
-- 직급 코드가 J5인 직원은 10% 인상
-- 그 외  직원은 5% 인상
-- 이름, 직급코드, 원래 급여, 인상률, 인상된 급여

SELECT EMP_NAME AS 이름, JOB_CODE AS 직급코드, SALARY AS "이전 급여",  
	DECODE(JOB_CODE, 'J7', '20%', 'J6', '15%', 'J5', '10%', '5%') AS 인상률 ,
	DECODE(JOB_CODE, 'J7', SALARY * 1.2 ,'J6',SALARY * 1.15 ,'J5',SALARY * 1.1 ,SALARY * 1.05 ) AS "인상된 급여"
 FROM EMPLOYEE ;

📍 CASE문?

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

--EMPLOYEE 테이블에서 
-- 급여가 500만원 이상이면 '대'
-- 급여가 300만원 이상 500만원 미만이면 '중'
-- 급여가 300만원 미만이면 '소'

SELECT EMP_NAME ,SALARY ,
	CASE 
		WHEN SALARY >= 5000000 THEN '대'
		WHEN SALARY >= 3000000 THEN '중'
		ELSE '소'
	END
FROM EMPLOYEE;

🔍 그룹 함수

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

📍 SUM

SUM(숫자가 기록된 컬럼명) : 합계

📍 AVG

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

📍 MIN / MAX

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

📍 COUNT

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