Oracle SQL - 함수 FUNCTION(NULL 처리 함수, COUNT,DECODE,CASE WHEN THEN)

안녕하세요.

#yeoneeblog 여니입니다 :)

 

 

-- NULL : 값이 존재하지 않음을 나타내는 값
-- NULL 처리 함수들 : NVL, NVL2, NULLIF
/*
    <NULL 처리 함수>
    
    NVL(컬럼명, 해당컬럼값이 NULL일 경우 반환할 반환값)
    해당컬럼에 값이 존재할 경우 기존의 칼럼값을 반환
    칼럼에 값이 존재하지 않을경우 내가 제시한 값이 반환됨.
*/

-- 사원명, 보너스, 보너스가 없는 경우 0으로 바꿔서 출력
SELECT EMP_NAME, BONUS, NVL(BONUS, 0)
FROM EMPLOYEE;

-- 보너스 포함 연봉 조회
SELECT EMP_NAME, (SALARY + (SALARY + (SALARY * BONUS))) * 12 AS "보너스 포함 연봉"
FROM EMPLOYEE;

/*
    NVL2(컬럼명, 결과값1, 결과값2)
    해당 컬럼값이 NULL이 아닐경우 결과값1 반환
    해당 컬럼값이 NULL일 경우 결과값2 반환
*/

-- 이름 + 보너스 + 보너스가 있는 사원은 '보너스가 있음', 보너스가 없는 사원은 '보너스 없음'
SELECT EMP_NAME, BONUS, NVL2(BONUS, '보너스가 있음', '보너스 없음') AS 보너스유무
FROM EMPLOYEE;

-- 이름 + 부서코드 + 부서코드가 있는 사원은 '부서배치완료', 없는 사원은 '없음' 조회
SELECT EMP_NAME, DEPT_CODE, NVL2(DEPT_CODE, '부서배치완료', '없음') AS 부서배치유무
FROM EMPLOYEE;

/*
    NULLIF(비교대상1, 비교대상2) : 동등비교
    두값이 동일할 경우 NULL반환
    두값이 동일하지않을경우 비교대상1 반환
*/

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

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

--------------------------------------------------------------------------------
-- 선택함수 : DECODE => SWICH문
--          CASE WHEN THEN 구문 => IF문

/*
    <선택함수>
    - DECODE(비교대상, 조건값1, 결과값1, 조건값2, 결과값2, 조건값3, 결과값,... , 결과값)
    - 자바의 SWITCH문과 유사
    switch(비교대상){
    case 조건값1 : 결과값 1 break;
    case 조건값2 : 결과값 2 break;
    .....
    default : 결과값
    }
    
    비교대상에는 컬럼, 산술연산, 함수가 들어갈수 있다.
*/

-- 사번, 사원명, 주민등록번호, 주민등록번호로부터 성별 자리를 추출해서 1이면 남자, 2면 여자 성별칼럼 만들기
SELECT EMP_ID, EMP_NAME, EMP_NO,
DECODE(SUBSTR(EMP_NO,8,1), 1 , '남자' , 2 , '여자') "성별"
-- DECODE(SUBSTR(EMP_NO,8,1), '1' , '남자' , '2' , '여자') "성별" 
-- (숫자로만 이뤄진 값 넣을때는 '' 안에 숫자 넣어도 안넣어도 상관없음)
FROM EMPLOYEE;

-- 직원들의 급여를 인상시켜서 조회
-- 직급코드가 'J7'인 사원은 급여를 10%인상해서 조회
-- 직급코드가 'J6'인 사원은 급여를 10%인상해서 조회
-- 직급코드가 'J5'인 사원은 급여를 10%인상해서 조회
-- 그 외의 직급코드는 급여를 5%만 인상해서 조회
-- 사원명, 직급코드, 변경전 급여와 변경후급여를 조회

SELECT EMP_NAME, 
       JOB_CODE, 
       SALARY,
       DECODE(JOB_CODE, 'J7' , SALARY * 1.1,
       'J6' , SALARY * 1.15,
       'J5' , SALARY * 1.2, SALARY * 1.05) "변경후급여"
FROM EMPLOYEE;

/*
    CASE WHEN THEN 구문
    - DECODE 선택함수와 비교하면 DECODE는 해당 조건검사시 동등비교만을 수행
    
    CASE WHEN THEN 구문의 경우 특정 조건을 내 마음대로 제시 가능
    [표현법]
    CASE WHEN 조건식1 THEN 결과값1
         WHEN 조건식2 THEN 결과값2
         WHEN 조건식3 THEN 결과값3
         ...
         ELSE 결과값
    END;
*/

SELECT EMP_NAME, 
       JOB_CODE, 
       SALARY,
       CASE WHEN JOB_CODE = 'J7' THEN SALARY * 1.1
            WHEN JOB_CODE = 'J6' THEN SALARY * 1.15
            WHEN JOB_CODE = 'J5' THEN SALARY * 1.2
            ELSE SALARY * 1.05
       END "변경후급여"
FROM EMPLOYEE;

-- 사원명, 급여, 급여등급(SAL_LEVEL칼럼 사용금지)
-- 급여등급 : SALARY 값이 500만원 초과일 경우 '고급'
--                      500만원 이하 350만원 초과 일경우 '중급'
--                      350만원 이하일 경우 '초급
-- CASE WHEN THEN 구문으로 작성해보기
SELECT EMP_NAME, SALARY, 
       CASE WHEN SALARY > 5000000 THEN '고급'
            WHEN SALARY <= 3500000 THEN '초급' -- WHEN SALARY > 3500000 THEN '중급'
            ELSE '중급' -- '초급'
       END "급여등급"
FROM EMPLOYEE;

-----------------------------여기까지 단일행 함수----------------------------------

-- 그룹함수 : 데이터들의 합(SUM), 데이터들의 평균(AVG)
/*
    N개의 값을 읽어서 1개의 결과를 반환(하나의 그룹별로 함수 실행 결과 반환)
*/
-- 1. SUM(숫자타입컬럼) : 해당 칼럼값들의 총 합계를 반환해주는 함수
-- 전체 사원들의 총 급여 합계
SELECT SUM(SALARY)
FROM EMPLOYEE;

-- 부서코드가 'D5'인 사원들의 총 급여 합계
SELECT SUM(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5';

-- 2. AVG(숫자타입컬럼) : 해당칼럼값들의 평균을 구해서 반환
-- 전체 사원들의 평균 급여 
SELECT ROUND(AVG(SALARY))
FROM EMPLOYEE;

-- 3. MIN(ANY타입) : 해당 칼럼값들중 가장 작은값을 반환
-- 전체 사원들중 최저급여, 가장 작은 이름값, 가장 작은 이메일값, 입사일이 가장 예전인 사원
SELECT 
   MIN(SALARY) , MIN(EMP_NAME), MIN(EMAIL), MIN(HIRE_DATE)
FROM EMPLOYEE;
-- 한줄에 출력됐다고 하나의 값이 아니라, 각각의 값이 다 따로인 데이터들이다.

-- 4. MAX(ANY타입컬럼) : 해당칼럼값들중 가장 큰값을 반환
SELECT 
   MAX(SALARY) , MAX(EMP_NAME), MAX(EMAIL), MAX(HIRE_DATE)
FROM EMPLOYEE;

-- 5. COUNT(*/컬럼이름/DISTINCT 컬럼이름) 조회된 행의 갯수를 세서 반환
-- COUNT(*) : 조회결과에 해당하는 모든 행의 갯수를 다 세서 반환
-- COUNT(칼럼이름) : 제시한 해당칼럼의 값이 NULL이 아닌 것만 세서 반환
-- COUNT(DISTINCT 컬럼이름) : 제시한 해당 칼럼값이 중복값이 있을경우 하나로만 세서 반환
--★회사에서 자주 사용 - 게시글 목록 페이지(최하단의 1,2,3... 페이지당 게시글 갯수고 
--몇개가 몇개가 노출될지 만들때 사용(COUNT(*))★★★

-- 전체 사원수에 대해 조회
SELECT COUNT(*)
FROM EMPLOYEE; -- 23

-- 여자인 사원수만 조회
SELECT COUNT(*)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1) = '2'; 

-- 부서배치가 완료된 여자 사원수
SELECT COUNT(DEPT_CODE)
FROM EMPLOYEE
--WHERE SUBSTR(EMP_NO,8,1) = '2' AND DEPT_CODE IS NOT NULL;
WHERE SUBSTR(EMP_NO,8,1) = '2';

-- 현재 사원들이 속해있는 부서의 갯수
SELECT COUNT(DISTINCT DEPT_CODE)
FROM EMPLOYEE;

------------------------------------------------------------------
-- EMPLOYEE 테이블에서 직원명, 부서코드, 생년월일, 나이 조회
-- (단, 생년월일은 주민번호에서 추출해서 00년 00월 00일로 출력되게하며
-- 나이는 주민번호에서 날짜데이터로 변환한 다음 계산)
SELECT EMP_NAME "직원명", 
      JOB_CODE "부서코드", 
--      SUBSTR(EMP_NO,1,2) || '년' || SUBSTR(EMP_NO,3,2) || '월' || SUBSTR(EMP_NO,5,2) || '일'
       TO_CHAR(TO_DATE(SUBSTR(EMP_NO,1,6),'YYMMDD'), 'YY"년" MM"월" DD"일"') AS 생년월일,
       EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO,1,2),'RRRR'))"나이" 
FROM EMPLOYEE;