[KH정보교육원] 17일차_SELECT 함수

2021.11.3(수)

함수 vs 메서드

• 함수: Built-in Function, 인자로 들어온 데이터에 의해 판별함 e.g. LEN(문자열), LEN(정수), LEN(실수) 등
• 메소드: 참조(객체가 가지고 있는 것(속성, 행위 + '자료형')을 사용하고자 함)할 객체가 필요함 → 특정 자료형(Java에서는 클래스)에만/(내가 만든) 클래스의 객체만 사용할 수 있는 함수 → 자료형/클래스가 명시되지 않으면 사용 불가능, 클래스 안에 없는 메소드는 사용 불가능 e.g. 문자열.length(), Object.toString() 등

함수

• 전달된 값을 읽어서 계산한 결과를 반환
• 단일행 함수 + 그룹 함수

🌿 단일행 함수

• N개의 값을 읽어서 N개의 값을 리턴 ← 각 행마다 반복적으로 적용되어 입력받은 행의 개수만큼 결과 반환
• 예시는 수업 자료 참조

🌱 문자열 관련 함수

✔️ LENGTH(B)(STR): 해당 전달된 문자열의 글자(바이트) 수 반환

✔️ INSTR(STR, '특정문자(열)'[, 찾을위치의 시작값[, 순번]]): 문자열 STR로부터 '특정문자(열)'의 맨앞에서부터의 위치값 반환

✔️ SUBSTR(STR, POSITION[, LENGTH]): 문자열 STR로부터 특정 문자열 추출해서 반환

✔️ L/RPAD(STR, 최종적으로 반환할 문자의 길이(바이트)로써 이 길이가 될 때까지 채워넣음[, 덧붙이고자 하는 문자(열)(생략 시 default는 공백)])

✔️ L/RTRIM(STR[, 제거시키고자 하는 문자]):문자열 STR의 왼/오른쪽에서 제거시키고자/잘라내고자 하는 문자를 찾아서 제거하고 나머지를 반환

✔️ TRIM([BOTH(양쪽)/LEADING(앞쪽)/TRAILING(뒷쪽)] '특정문자' FROM STR): STR 앞/뒤/양쪽에 있는 특정 문자를 제거한 나머지 문자열 반환

✔️ LOWER/UPPER/INTICAP(STR): 문자열을 전부 다 소/대문자로 변경//각 단어 앞글자만 대문자로 변경

✔️ CONCAT(STR1, STR2): 전달된 2개의 문자열을 하나로 합친 결과로 반환

✔️ REPLACE(STR, 찾을문자, 바꿀문자): STR으로부터 찾을 문자를 찾아서 바꿀문자로 변환 후 반환

🌱 숫자 관련 함수

✔️ ABS(NUMBER): 절대값 구해주는 함수

✔️ MOD(NUMBER1, NUMBER2): 2개의 수를 나눈 나머지 값을 반환(Oracle)

✔️ ROUND(NUMBER[, 위치]): 반올림 처리해주는 함수

✔️ CEIL(NUMBER): 소수점 아래의 수를 무조건 올림 처리해주는 함수

✔️ FLOOR(NUMBER): 소수점 아래의 수를 무조건 버림 처리해주는 함수

✔️ TRUNC(NUMBER[, 위치]): 위치 지정가능한(소수점 N번째 아래는), 버림 처리해주는 함수

🌱 날짜 관련 함수

✔️ SYSDATE: 현재 시스템 날짜 반환

✔️ MONTHS_BETWEEN(DATE1, DATE2): 두 날짜 사이의 개월 수 반환

✔️ ADD_MONTHS(DATE, NUMBER): 특정 날짜에 해당 숫자만큼의 개월수를 더한 날짜 반환

✔️ NEXT DAY: 특정 날짜에서 가장 가까운 해당 요일을 찾아 날짜를 반환

-- 1: 일요일, 2: 월요일, 3: 화요일, ... , 6: 금요일, 7: 토요일
SELECT NEXT_DAY(SYSDATE, 6)
FROM DUAL; -- 2021.11.13(토) 복습 시 질의 결과 = 21/11/19

-- 현재 Oracle 설정 언어가 한국어이기 때문에 에러가 남; ORA-01846: not a valid day of the week
SELECT NEXT_DAY(SYSDATE, 'SUNDAY')
FROM DUAL;

-- 영어(AMERICAN)로 언어 변경
ALTER SESSION SET NLS_LANGUAGE = AMERICAN;

SELECT NEXT_DAY(SYSDATE, 'SUNDAY')
FROM DUAL; -- 2021.11.13(토) 복습 시 질의 결과 = 21/11/14

-- 한국어(KOREAN)로 언어 변경
ALTER SESSION SET NLS_LANGUAGE = KOREAN;

SELECT NEXT_DAY(SYSDATE, '목욜')
FROM DUAL; -- 2021.11.13(토) 복습 시 질의 결과 = 21/11/18

✔️ LAST_DAY: 해당/특정 날짜가 속한 달의 마지막 날짜를 구해서 반환

✔️ EXTRACT: 연도, 월, 일 정보 하나씩 추출해서 반환 → NUMBER타입으로 반환

• EXTRACT(YEAR FROM DATE): 특정 날짜로부터 연도만 추출
• EXTRACT(MONTH FROM DATE): 특정 날짜로부터 월만 추출
• EXTRACT(DAY FROM DATE): 특정 날짜로부터 일만 추출

🌱 형 변환 함수

✔️ TO_CHAR(NUMBER/DATE): 숫자/날짜형 데이터를 문자형 타입으로 변환 → CHARACTER타입으로 반환

✔️ TO_DATE(NUMBER/CHARACTER): 숫자/문자형 데이터를 날짜형으로 변환 → DATE타입으로 반환

✔️ TO_NUMBER(CHARACTER): 문자형 데이터를 숫자형으로 변환 → NUMBER타입 반환

🌱 NULL 처리 함수

해당 컬럼의 NULL이 반환할 결과값을 넣어줄 수 있음

✔️ NVL(컬럼명, 반환할 결과값)

SELECT EMP_NAME, BONUS
    , TO_CHAR((SALARY + SALARY * BONUS) * 12, '999,999,999') "연소득(보너스 NULL)"
    , TO_CHAR((SALARY + SALARY * NVL(BONUS, 0)) * 12, '999,999,999') AS "연소득(급여+보너스)"    
FROM EMPLOYEE;
-- BONUS 컬럼에 대해 NVL 함수를 쓰지 않으면 보너스가 없는/NULL값인 사람들의 연소득이 NULL값으로 표시됨 vs 급여소득이라도 표시되도록 하는 게 취지에 맞음 -> BONUS 컬럼에 NVL(BONUS, 0)를 적용하면 BONUS 컬럼의 NULL값이 0으로 반환됨

SELECT EMP_NAME, NVL(DEPT_CODE, '없음')
FROM EMPLOYEE
ORDER BY DEPT_CODE;

✔️ NVL2

-- 사원명, 부서코드, 부서코드가 있는 경우 '부서 배치 완료' VS 없는 경우 '없음'
SELECT EMP_NAME, DEPT_CODE, NVL2(DEPT_CODE, '부서 배치 완료', '없음') "부서 배치 여부"
FROM EMPLOYEE;

✔️ NULLIF

-- NULLIF(비교대상1, 비교대상2): 2개의 값이 동일할 경우 NULL을 반환 VS 동일하지 않을 경우 비교대상 1을 반환
SELECT NULLIF('123', '123')
FROM DUAL; -- (null)(질의 결과 창 더블클릭해보면 아무 값도 들어있지 않은 빈칸 상태)

SELECT NULLIF('123', '456')
FROM DUAL; -- 123

🌱 선택 함수

✔️ DECODE(비교대상(컬럼명, 산술연산, 함수), 조건값, 결과값, ..)

SELECT EMP_ID, EMP_NAME, DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여', 3, '남', 4, '여') "성별" -- 1 또는 3이 '남', 2 또는 4가 '여' 하고 싶으면 조건 이어서 쓰거나, 연산식(2로 나눈 나머지 등) 사용하거나 등등
FROM EMPLOYEE
ORDER BY 성별, 1;

SELECT EMP_ID, EMP_NAME, DECODE(MOD(SUBSTR(EMP_NO, 8, 1), 2), 1, '남', 0, '여') "성별"
FROM EMPLOYEE;

-- 성별 인원 및 평균급여 조회
SELECT DECODE(MOD(SUBSTR(EMP_NO, 8, 1), 2), 1, '남', 0, '여') "성별", COUNT(*) "인원수", TO_CHAR(AVG(SALARY), 'L999,999,999') "평균급여"
FROM EMPLOYEE
GROUP BY MOD(SUBSTR(EMP_NO, 8, 1), 2);

-- 직원들의 급여를 인상시켜서 조회
-- 직급코드가 J7인 사원은 급여를 10% 인상 + J6인 사원은 급여를 15% 인상 + J5인 사원은 급여를 20% 인상 + 그 외의 직급코드인 사원들은 급여를 5% 인상해서 조회
SELECT EMP_NAME
    , JOB_CODE
    , TO_CHAR(SALARY, '999,999,999') "인상 전 급여"
    , TO_CHAR(DECODE(JOB_CODE, 'J7', SALARY * 1.1, 'J6', SALARY * 1.15, 'J5', SALARY * 1.2, SALARY * 1.05), 'L999,999,999') "인상 후 급여"
FROM EMPLOYEE
ORDER BY JOB_CODE DESC, "인상 후 급여";

✔️ CASE WHEN 조건식 1 THEN 결과값1 WHEN 조건식 2 THEN 결과값2 .. ELSE 결과값 END

-- CASE WHEN THEN 구문 -> 가독성 향상
SELECT EMP_ID
    , EMP_NAME
    , CASE WHEN SUBSTR(EMP_NO, 8, 1) = '1' THEN '남'
           ELSE '여'
      END AS "성별" -- 별칭은 컬럼명 뒤에 (AS) "별칭"
FROM EMPLOYEE;

🌿 그룹 함수

• N개의 값을 읽어서 1개의 결과를 리턴 ← 특정 행들의 집합으로 그룹이 형성되어, 적용된 그룹당 1개의 결과 반환

🌱 SUM(숫자타입 컬럼)

해당 컬럼값들의 총 합계를 반환해주는 함수

🌱 AVG(숫자타입 컬럼)

해당 컬럼값들의 평균값을 구해서 반환하는 함수

🌱 MIN(ANY타입 컬럼)

해당 컬럼값들의 가장 작은 값을 반환

🌱 MAX(ANY타입 컬럼)

해당 컬럼값들의 가장 큰 값을 반환

🌱 COUNT

• COUNT(*): 조회 결과에 해당하는 모든 행 갯수를 다 세서 반환
• COUNT(컬럼명): 값이 NULL이 아닌 행의 갯수만 세서 반환
• COUNT(DISTINCT 컬럼명): 중복값이 있을 경우 하나로만 갯수 COUNT, NULL 포함

🌿 연습 문제

1. 직원명과 주민번호 조회; 단, 주민번호 9번째 자리~끝 '*' 문자로 채움/마스킹
2. 직원명, 직급코드, 연봉(보너스 적용(월마다/월 급여에 적용)된 1년치 급여; 표시 형식(formatting): 원(￦) 표시, 3자리마다 쉼표로 나누기) 조회
3. 부서코드가 D5, D6인 직원들 중에서 2004년도에 입사한 직원의 수 조회

📗 소감

• 하루에 많은 함수를 배워서 어질어질하지만, 함수들 자체는 이해하기 어렵지 않았다.
• 함수는 SELECT 할 수 있는 가능성과 다양성을 높여주니 재미있다
• 어떤 함수들이 있는지 기억해두고 필요할 때 유용하게 사용하자!

📗 homework:

-- 1번 문제: EMPLOYEE 테이블에서 사원의 주민번호를 확인하여 생년월일을 각각 조회
-- 이름 | 생년 | 생월 | 생일
-- 홍길 | 00년 | 00월 | 00일
SELECT EMP_NAME "이름"
    , CONCAT(SUBSTR(EMP_NO, 1, 2), '년') "생년" -- EMP_NO = 문자타입
    , CONCAT(SUBSTR(EMP_NO, 3, 2), '월') "생월" -- 주민번호에서 잘라오면 어차피 0X월로 표기되니까 LPAD 필요 없음
    , SUBSTR(EMP_NO, 5, 2) || '일' "생일"
FROM EMPLOYEE;

SELECT EMP_NAME "이름"
    , CONCAT(SUBSTR(HIRE_DATE, 1, 2), '년') "입사년도"
    , SUBSTR(HIRE_DATE, 4, 2) "입사월"
    , SUBSTR(HIRE_DATE, 7, 2) "입사일"
FROM EMPLOYEE;

-- 2번 문제: EMPLOYEE 테이블에서 현재 근무하는 여성 사원의 사번, 사원명, 직급코드를 조회
-- ENT_YN : 현재 근무(N)/퇴사(Y) 여부 파악하는 컬럼 ou 퇴사일이 NULL
-- WHERE 절에서도 함수 사용이 가능
SELECT EMP_ID, EMP_NAME, JOB_CODE
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = 2 AND ENT_YN = 'N';

SELECT EMP_ID 사번, EMP_NAME 사원명, JOB_CODE 직급코드
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = 2 AND ENT_DATE IS NULL;

-- 3번 문제: EMPLOYEE 테이블에서 '해외영업1부'에 근무하는 모든 사원의 평균 급여, 가장 높은 급여, 낮은 급여, 급여 합계 조회하기
-- 단계1) EMPLOYMENT 테이블에서 부서코드(중복 없이) 조회해보기
SELECT DISTINCT DEPT_CODE 
FROM EMPLOYEE
ORDER BY DEPT_CODE; -- 조회 결과 = D1,2,5,6,8,9

-- 단계2) DEPARTMENT 테이블의 전체 데이터 조회해보기
SELECT *
FROM DEPARTMENT
ORDER BY DEPT_ID; -- 조회 결과 = D1~9

-- 단계3) DEPARTMENT 테이블에서 '해외영업1부'의 부서코드 찾기
SELECT DEPT_ID
FROM DEPARTMENT
WHERE DEPT_TITLE LIKE '%해외영업1부%'; -- 조회 결과 = D5

-- 단계4) 문제에서 주어진 데이터 조회하기
SELECT TO_CHAR(AVG(SALARY), 'L999,999,999') "평균 급여" -- TO_CHAR(대상컬럼, 'L999,999') formatting했더니 ROUND(AVG(SALARY)) 필요 없었음
    , TO_CHAR(MAX(SALARY), 'L999,999,999') "최고 급여"
    , TO_CHAR(MIN(SALARY), 'L999,999,999') "최저 급여"
    , TO_CHAR(SUM(SALARY), 'L999,999,999') "급여 합계"
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5';

-- 4번 문제: 부서코드가 D5이면 총무부, D6이면 기획부, D9이면 영업부로 처리(case 사용);
-- 단, 부서코드가 D5, D6, D9인 직원의 사원명, 부서코드, 부서명만 조회함 + 부서코드 기준 오름차순 정렬
SELECT EMP_NAME "사원명" -- 실행순서3: SELECT절 위->아래로
    , DEPT_CODE "부서코드"
    , CASE WHEN DEPT_CODE = 'D5' THEN '총무부'
           WHEN DEPT_CODE = 'D6' THEN '기획부'
           ELSE '영업부' -- WHEN DEPT_CODE = 'D9' THEN '영업부' 명시적으로 써도 됨
        END "부서명"
FROM EMPLOYEE -- 실행순서1
WHERE DEPT_CODE IN ('D5', 'D6', 'D9') -- 실행순서2
ORDER BY 2; -- "부서코드" 컬럼을 의미; Oracle에서는 순서 셀 때 1부터 시작; 실행순서4