[학습목표]
- 형변환함수 [문자,숫자,날짜타입]
- NULL처리 함수
- 선택 함수 [DECODE, CASE WHEN THEN]
- 그룹 합수 [SUM, AVG, MIN, MAX, COUNT]
형변환 함수
- 문자타입 character
- 숫자타입 number
- 날짜타입 date
📝 1. TO_CHAR 📝
📌 숫자타입 또는 날짜타입의 값을 문자타입으로 변환
TO_CHAR(숫자|날짜, [포맷]) => 결과값 CHARACTER타입
✔ 숫자타입 -> 문자타입
SELECT TO_CHAR(1234, '99999') FROM DUAL;
=> 포맷으로 설정한 9가 5개니까 5칸 공간확보
=> 오른쪽 정렬됨
=> 빈칸 1개를 공백으로 채움
SELECT TO_CHAR(1234,'00000') FROM DUAL;
=> 포맷으로 설정한 0이 5개니까 5칸 공간화고
=> 빈칸 1개를 0으로 채움
=> 공백이 있는 이유는 음수일 때 -를 출력해야하니까
SELECT TO_CHAR(1234,'L99999') FROM DUAL;
=> L은 현재 설정된 나라의 화폐 단위 표시
SELECT TO_CHAR(1234,'fm99999') FROM DUAL;
=> 공백을 제거하고 싶다면 포맷 앞에 fm작성
-- 월급과 연봉을 3,000,000 /32,000,000으로 출력
SELECT TO_CHAR(SALARY,'L999,999,999), TO_CHAR(SALARY*12, 'L999,999,999')
FROM EMPLOYEE;✔ 날짜타입 -> 문자타입
SELECT TO_CHAR(SYSDATE, 'PM HH:MI:SS') FROM DUAL;
=> HH는 12시간 형식 HH24로 쓰면 24시간형식
=> AM, PM은 시스템상 시간으로 알아서 출력됨
SELECT TO_CHAR(STSDATE, 'YYYY-MM-DD DAY) FROM DUAL;
=> 2022-11-16 수요일 출력
SELECT TO_CHAR(SYSDATE, 'MON, YYYY') FROM DUAL;
=> 11DNJF, 2022 출력🔴 년도와 관련된 포맷
SELECT TO_CHAR(SYSDATE, 'YYYY') => 2022
TO_CHAR(SYSDATE, 'YY') => 22
TO_CHAR(SYSDATE, 'RRRR') => 2022
TO_CHAR(SYSDATE, 'RR') => 22
TO_CHAR(SYSDATE, 'YEAR') => TWENTY TWENTY-TWO
🔴 월과 관련된 포맷
SELECT TO_CHAR(SYSDATE, 'MM'), => 11
TO_CHAR(SYSDATE, 'MON'), => 11월
TO_CHAR(SYSDATE, 'MONTH'), => 11월
TO_CHAR(SYSDATE, 'RM' =>XI (로마기호)🔴 일과 관련된 포맷
SELECT TO_CHAR(SYSDATE, 'DDD'), -- 년 기준 며칠째인지
TO_CHAR(SYSDATE, 'DD'), -- 월 기준 며칠째인지
TO_CHAR(SYSDATE, 'D') -- 주 기준 며칠째인지 (1~7 일(1) ~ 토(7))
🔴 요일과 관련된 포맷
SELECT TO_CHAR(SYSDATE,'DAY') => 수요일
TO_CHAR(SYSDATE,'DY') => 수
-- XXXX년 XX월 XX일 (x)
SELECT EMP_NAME, TO_CHAR(HIRE_DATE,'YYYY"년" MM"월" DD"일" (DY)')"입사일"
FROM EMPLOYEE;포맷내에서 임의의 문자열값을 쓰고자 할 경우, ""묶어서 표현해야함 !
📝 2. TO_DATE 📝
📌 숫자타입 또는 문자타입 데이터를 날짜타입으로 변환
TO_DATE(숫자|문자, [포맷]) => 결과값 DATE
✔ 숫자타입 -> 날짜타입
SELECT TO_DATE(20100101) FROM DUAL; => 10/01/01
SELECT TO_DATE(100101) FROM DUAL; => 10/01/01
SELECT TO_DATE(070101) FROM DUAL; -- 에러 (첫글자가 0이여서 0을 떼버리고 70101로 인식함)
SELECT TO_DATE('070101') FROM DUAL; -- 0으로 시작하는 년도를 쓰려면 ''로 묶어서 표현해야함 => 07/01/01✔ 문자타입 -> 날짜타입
SELECT TO_DATE('140630','YYMMDD') FROM DUAL; --2014년도
SELECT TO_DATE('980630','YYMMDD') FROM DUAL; --2098년도
-- YY : 무조건 현재세기로 반영
SELECT TO_DATE('140630','RRMMDD') FROM DUAL; --2014년도
SELECT TO_DATE('980630','RRMMDD') FROM DUAL; --1998년도
-- RR : 해당 두자리 년도가 50미만일 경우, 현재세기로 반영 / 50이상일 경우, 이전세기로 반영
📝 3. TO_NUMBER 📝
📌 문자타입의 데이터를 숫자타입으로 변환
TO_NUMBER(문자, [포맷]) => 결과값 NUMBER타입
SELECT TO_NUMBER('05123485') FROM DUAL;
SELECT '1000000' + '550000' FROM DUAL; -- 오라클에서는 자동형변환 잘 돼있음
SELECT '1,000,000' + '550,000' FROM DUAL; -- 오류
SELECT TO_NUMBER ('1,000,000','999,999,999') + TO_NUMBER('550,000','999,999,999') FROM DUAL;<NULL 처리 함수>
📝 4. NVL 📝
📌 NVL(컬럼, 해당컬럼값이NULL일때 반환할값)
SELECT EMP_NAME, NVL(BONUS, '0')
FROM EMPLOYEE;
SELECT EMP_NAME, NVL(BONUS, '없음') -- 오류 BONUS의 반환값이 NUMBER타입이므로 NULL일 때 반환값도 NUMBER값이어야함
FROM EMPLOYEE;
-- 전 사원의 이름, 보너스포함 연봉 조회
SELECT EMP_NAME, (SALARY+SALARY*NVL(BONUS,0))*12, NVL(DEPT_CODE,'부서없음')
FROM EMPLOYEE;📝 4-1. NVL2 📝
📌 NVL2(컬럼, 반환값1, 반환값2)
컬럼값이 존재할 경우 반환값1 반환
컬럼값이 존재하지 않을 경우 반환값2 반환
SELECT EMP_NAME,BONUS, NVL2(BONUS,0.7,0.1)
FROM EMPLOYEE;
SELECT EMP_NAME, NVL2(DEPT_CODE,'부서있음','부서없음')
FROM EMPLOYEE;
<선택 함수>
📝 5. DECODE 📝
📌 DECODE(컬럼|산술연산|함수, 비교값1, 결과값1, 비교값2, 결과값2 ''')
-- 사번, 사원명, 주민번호, 성별 유추하기
SELECT EMP_ID, EMP_NAME, EMP_NO, DECODE(SUBSTR(EMP_NO,8,1),1,'남',3,'남',2,'여','4','여') "성별"
FROM EMPLOYEE;
=> 사원 주민번호에서 SUBSTR로 8번째 자리부터 1자리를 추출해서 그 숫자가 1또는 3이면 남자 2 또는 4면 여자로 추출
📝 5-1. CASE WHEN THEN 📝
📌표현법1
CASE WHEN 조건식1 THEN 결과값 1 => 조건식 1이면 결과값 1 반환
WHEN 조건식2 THEN 결과값 2 '''
ELSE 결과값N
END
📌표현법2
CASE 비교대상컬럼|산술연산식 | 함수식
WHEN 비교값1 THEN 결과값1
WHEN 비교값2 THEN 결과값2
'''
ELSE 결과값 N ==> 위 비교값 둘다 아닐때
END
---------------------------------------
-- 사번, 사원명, 주민번호, 성별 유추하기
SELECT EMP_ID, EMP_NAME, EMP_NO,
CASE WHEN SUBSTR(EMP_NO,8,1) IN ('1','3') THEN '남'
WHEN SUBSTR(EMP_NO,8,1) IN ('2','4') THEN '여'
END "성별"
FROM EMPLOYEE;
SELECT EMP_ID, EMP_NAME, EMP_NO,
CASE SUBSTR(EMP_NO,8,1)
WHEN '1' THEN '남'
WHEN '2' THEN '여'
END "성별"
FROM EMPLOYEE;
-- 사원명, 급여, 등급
-- 급여가 500만원 이상일 경우 '고급'
-- 급여가 350만원 이상일 경우 '중급'
-- 둘 다 아닐 경우 '초급'
SELECT EMP_NAME, SALARY,
CASE WHEN SALARY >= 5000000 THEN '고급'
WHEN SALARY >= 3500000 THEN '중급'
ELSE '초급'
END "등급"
FROM EMPLOYEE;<그룹 함수>
📝 6. SUM 📝
📌 SUM(NUMBER) : 해당 컬럼 값들의 총 합계를 구해서 반환
📝 7. AVG 📝
📌 AVG(NUMBER) : 해당 컬럼값들의 평균값 구해서 반환
📝 8. MIN 📝
📌 MIN(ANY) : 해당 컬럼값 중 가장 작은값 반환
📝 9. MAX 📝
📌 MAX(ANY) : 해당 컬럼값 중 가장 큰 값 반환
📝 10. COUNT 📝
📌 COUNT(*|컬럼|DISTINCT컬럼) : 행 갯수를 세어서 반환해주는 함수
- COUNT(*) : 조회될 결과의 모든 행 갯수 세어서 반환
- COUNT(컬럼) : 제시한 해당 컬럼 값이 NULL이 아닌 것만 행 갯수 세어서 반환
- COUNT(DISTINCT컬럼) : 해당 컬럼값 중복제거한 후, 행 갯수 세어서 반환
-- 전체사원 수
SELECT COUNT(*)
FROM EMPLOYEE;
-- 여자사원 수
SELECT COUNT(*)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1) IN ('2','4');
-- 보너스를 받는 사원 수
SELECT COUNT(*)
FROM EMPLOYEE
WHERE BONUS IS NOT NULL;
SELECT COUNT(BONUS)
FROM EMPLOYEE;
-- 부서배치를 받은 사원 수
SELECT COUNT(DEPT_CODE)
FROM EMPLOYEE;
-- 현재 사원들이 총 몇개의 직급에 분포되어있는지 조회
SELECT COUNT(DISTINCT JOB_CODE)
FROM EMPLOYEE;
-- 현재 사원들이 총 몇개의 부서에 속해있는지 조회
SELECT COUNT (DISTINCT DEPT_CODE) -- NULL은 제외함
FROM EMPLOYEE;
이렇게 행 갯수를 반환해서 출력해줌 !
