[SQL/ORACLE] FUNCTION(단일행 함수 - 형변환 함수, NULL처리 함수, 선택 함수 / 그룹 함수)

4. 형변환 함수

4.1 TO_CHAR

      숫자 또는 날짜 타입의 값을  문자로 변환시켜주는 함수
       반환 결과를 특정 형식에 맞게 출력할수 도 있다

 

       [표현식]
       TO_CAHR(숫자|날짜, [포맷]) 

*/
--------------------------------------------------  숫자 => 문자타입
/*
    [포맷]
    * 접두어 : L -> LOCAL(설정된 나라)의 화폐단위
    
    * 9 : 해당 자리의 숫자를 의미한다
         - 해당 자리에 값이 없을 경우 소수점 이상은 공백,   소수점 이하는 0으로 표시
    * 0 :  해당 자리의 숫자를 의미한다 
        -  해당 자리에 값이 없을 경우 0으로 표시하고, 숫자의 길이를 고정적으로 표시할 때 주로 사용
    * FM : 해당 자리에 값이 없을 경우 자리차지를 하지 않음    
*/
SELECT TO_CHAR(1234), 1234 FROM DUAL;       -- 문자는 왼쪽정렬, 숫자는 오른쪽 정렬

SELECT TO_CHAR(1234, '999999') FROM DUAL;
SELECT TO_CHAR(1234, '000000') FROM DUAL;
SELECT TO_CHAR(1234, 'L999999') FROM DUAL;   -- 오른쪽 정렬

SELECT TO_CHAR(1234, 'L99,999') FROM DUAL; 

SELECT EMP_NAME, TO_CHAR(SALARY, 'L999,999,999'), TO_CHAR(SALARY*12, 'L999,999,999')
  FROM EMPLOYEE;

SELECT TO_CHAR(123.456, 'FM99999.999'),
        TO_CHAR(123.456, 'FM90000.99'),
        TO_CHAR(0.1000, 'FM9990.999'),
        TO_CHAR(0.1000, 'FM9999.999')
  FROM DUAL;
  
SELECT TO_CHAR(123.456, '99999.999'),
        TO_CHAR(123.456, '90000.99'),
        TO_CHAR(0.1000, '9990.999'),
        TO_CHAR(0.1000, '9999.999')
  FROM DUAL; 

-------------------------------------------------- 날짜 => 문자타입
-- 시간
SELECT TO_CHAR(SYSDATE, 'PM') "KOREA",
            TO_CHAR(SYSDATE,'AM', 'NLS_DATE_LANGUAGE=AMERICAN') "AMERICAN"
 FROM DUAL;  -- AM,PM 상관없음

ALTER SESSION SET NLS_LANGUAGE = AMERICAN;
SELECT TO_CHAR(SYSDATE, 'PM') "AMERICAN" 
 FROM DUAL;
 
ALTER SESSION SET NLS_LANGUAGE = KOREAN;

-- 12시간 형식, 24시간 형식
SELECT TO_CHAR(SYSDATE) FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM DUAL;  -- 12시간 형식
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL;  -- 24시간 형식

-- 날짜
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'MON, YYYY') FROM DUAL;

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

SELECT TO_CHAR(SYSDATE, 'YY-MM-DD') FROM DUAL;

-- 입사일을 ????년 ?월 ?일 ?요일 로 출력
SELECT TO_CHAR(HIRE_DATE, 'DL')
 FROM EMPLOYEE;
 
------------------ 년도
/*
    YY : 무조건 '20'이 앞에 붙는다
    RR : 50년을 기준으로 작으면 '20'을 크면 '19'
*/
SELECT TO_CHAR(SYSDATE, 'YYYY'),
          TO_CHAR(SYSDATE, 'YY'),
          TO_CHAR(SYSDATE, 'RRRR'),
          TO_CHAR(SYSDATE, 'RR')
FROM DUAL;

----------------- 월
SELECT TO_CHAR(SYSDATE, 'MM'),
          TO_CHAR(SYSDATE, 'MON'),
          TO_CHAR(SYSDATE, 'MONTH'),
          TO_CHAR(SYSDATE, 'RM')   -- 로마기호로
FROM DUAL;

---------------- 일
SELECT TO_CHAR(SYSDATE, 'DDD'), -- 년 기준 몇일째
          TO_CHAR(SYSDATE, 'DD'),   -- 월 기준 몇일째
          TO_CHAR(SYSDATE, 'D')      -- 주 기준(일요일) 몇일째
FROM DUAL;
    
--------------- 요일
SELECT TO_CHAR(SYSDATE, 'DAY'),
          TO_CHAR(SYSDATE, 'DY')
FROM DUAL;

 

4.2 TO_DATE

    숫자나 문자를 날짜타입으로 변환

 

     [표현식]
     TO_DATE(숫자|날짜, [포맷])

SELECT TO_DATE(20240613) FROM DUAL; 
SELECT TO_DATE(240613) FROM DUAL;

SELECT TO_DATE(010610) FROM DUAL;  -- 숫자로 앞이 0일 때 오류
SELECT TO_DATE('010610') FROM DUAL; 

SELECT  TO_CHAR(TO_DATE('070407 020814','YYMMDD HHMISS'), 'YY-MM-DD HH:MI:SS') FROM DUAL;
-- SELECT  TO_CHAR('070407 020811', 'YY-MM-DD HH:MI:SS') FROM DUAL;

-- SELECT TO_DATE('041030 143000','YYMMDD HHMISS') FROM DUAL;  -- 오류 : 오전 오후로 14시는 없음 
SELECT TO_DATE('041030 103000','YYMMDD HHMISS') FROM DUAL;

-- 환경설정 바꾸고 도구 -> 환경설정 -> 데이터베이스 -> NLS -> 날짜 포맷을 RRRR/MM/DD
SELECT TO_DATE('981213','YYMMDD') FROM DUAL;  -- YY : 무조건 현재세기로 반영
SELECT TO_DATE('021213','YYMMDD') FROM DUAL;

SELECT TO_DATE('981213','RRMMDD') FROM DUAL;  -- RR : 50미만 일 때는 현재세기, 50이상이면 이전 세기 반영
SELECT TO_DATE('021213','RRMMDD') FROM DUAL;

 

4.3 TO_NUMBER

      문자를 숫자타입으로 변환

 

      [표현식]
      TO_NUMBER(문자, [포맷])

SELECT TO_NUMBER('0123837310') FROM DUAL;
SELECT '1000' + '500' FROM DUAL;
-- SELECT '1,000' + '5,000' FROM DUAL;  -- 오류 : 숫자에 컴마에 들어가서
SELECT TO_NUMBER('1,000,000', '9,999,999') + TO_NUMBER('50,000','99,999') FROM DUAL;

 

5. NULL처리 함수

5.1 NVL(컬럼, 해당컬럼이 NULL일 경우 반환할 값)

SELECT EMP_NAME, NVL(BONUS, 0)
FROM EMPLOYEE;

-- 전사원의 사원명, 연봉(보너스포함)
SELECT EMP_NAME, (SALARY + SALARY*BONUS)*12
FROM EMPLOYEE;
SELECT EMP_NAME, (SALARY*(1+BONUS))*12
FROM EMPLOYEE;

SELECT EMP_NAME, (SALARY + SALARY*NVL(BONUS,0))*12
FROM EMPLOYEE;

SELECT EMP_NAME, (SALARY*NVL(1+BONUS,1))*12
FROM EMPLOYEE;

-- 전사원의 사원명, 부서코드(부서가 없으면 '부서없음')
SELECT EMP_NAME, NVL(DEPT_CODE, '부서없음')
  FROM EMPLOYEE;

 

5.2 NVL2(컬럼, 반환 값1, 반환 값2)

      - 반환 값1 : 컬럼에 값이 존재할 때 반환되는 값
      - 반환 값2 : 컬럼에 값이 NULL일 때 반환되는 값

-- EMPLOYEE에서 사원명, 급여, 보너스, 성과급(보너스를 받는사람은 50%, 보너스를 못받는 사람은 10%)
SELECT EMP_NAME, SALARY, BONUS, SALARY*NVL2(BONUS, 0.5, 0.1) 성과급
  FROM EMPLOYEE;
  
-- EMPLOYEE에서 사원명, 부서(부서에 속해있으면 '부서있음', 부서에 속해있지 않으면 '부서없음')
SELECT EMP_NAME, NVL2(DEPT_CODE,'부서있음','부서없음') 
  FROM EMPLOYEE;

 

5.3 NULLIF(비교대상1, 비교대상2)

      - 두 개의 값이 일치하면 NULL반환
      - 두 개의 값이 일치하지 않으면 비교대상1 값을 반환

SELECT NULLIF('1234','1234') FROM DUAL;  
SELECT NULLIF('1234','5678') FROM DUAL;

 

6. 선택 함수

6.1 DECODE(비교하고자하는 대상(컬럼|산술연산|함수식), 비교값1, 결과값1, 비교값2, 결과값2, ... ,결과값N)

 

      [자바와 비교]
      SWITCH(비교대상) {
        CASE 비교값1 : 결과값1
        CASE 비교값2 : 결과값2
        ...
        DEFAULT : 결과값N
      }         

-- EMPLOYEE에서 사번, 사원명, 주민번호, 성별(남,여) 조회
SELECT EMP_ID, EMP_NAME, EMP_NO,  DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여','3','남','4','여') 성별
  FROM EMPLOYEE;
  
-- EMPLOYEE에서 사원명, 급여, 직급코드, 인상된 급여(급여 조회시 각 직급별로 인상하여 조회)
    --  J7인 사원은 급여를 10%인상 (SALARY * 1.1)
    --  J6인 사원은 급여를 15%인상 (SALARY * 1.15)
    --  J5인 사원은 급여를 20%인상 (SALARY * 1.2)
    --  그외의 사원은 급여를 5%인상 (SALARY * 1.05)
SELECT EMP_NAME, SALARY, JOB_CODE,
            DECODE(JOB_CODE, 'J7', SALARY*1.1,
                                          'J6', SALARY*1.15,
                                          'J5', SALARY*1.2,
                                                SALARY*1.05) "인상된 급여"
  FROM EMPLOYEE;

 

6.2 CASE WHEN THEN
      END
      
      CASE WHEN 조건식1 THEN 결과값1
               WHEN 조건식2 THEN 결과값2
               ...
               ELSE 결과값N
       END 
       

      [자바와 비교]
       IF(조건식1) 결과값1
       ELSE IF(조건식2) 결과값2
       ...
       ELSE 결과값N

-- EMPLOYEE에서 사원명, 급여, 급수(급여가 5백만원 이상이면 '고급' 그렇지 않고 3백5십만원 이상이면 '중급' 나머지는 '초급')
SELECT EMP_NAME, SALARY,
   CASE WHEN SALARY >= 5000000 THEN '고급'
           WHEN SALARY >= 3500000 THEN '중급'
           ELSE '초급'
   END 급수
FROM EMPLOYEE;

 

7. 그룹 함수

7.1 SUM(컬럼)

      컬럼들의 값의 합계

-- 전 사원의 총급여의 합조회
SELECT SUM(SALARY)
  FROM EMPLOYEE;
  
-- 남자 사원의 총 급여의 합
SELECT SUM(SALARY)
  FROM EMPLOYEE
 WHERE SUBSTR(EMP_NO, 8, 1) IN('1','3');
 
-- 부서코드가 D5인 사원의 연봉(보너스포함)의 합
SELECT SUM(SALARY*12)
FROM EMPLOYEE
WHERE DEPT_CODE='D5';

SELECT SUM(SALARY*NVL(1+BONUS, 1)*12)
FROM EMPLOYEE
WHERE DEPT_CODE='D5';

SELECT TO_CHAR(SUM(SALARY*NVL(1+BONUS, 1)*12), 'L999,999,999') "총 급여액"
FROM EMPLOYEE
WHERE DEPT_CODE='D5';

 

7.2 AVG(컬럼)

      해당 컬럼들의 평균

SELECT AVG(SALARY)
 FROM EMPLOYEE;
 
SELECT ROUND(AVG(SALARY))
 FROM EMPLOYEE;

SELECT ROUND(AVG(SALARY),-1)
 FROM EMPLOYEE;

 

7.3 MIN / MAX

      컬럼값중에서 가장 큰값, 가장 작은값
      MIN(컬럼)

SELECT MIN(SALARY), MIN(EMP_NAME), MIN(HIRE_DATE)
FROM EMPLOYEE;

SELECT MAX(SALARY), MAX(EMP_NAME), MAX(HIRE_DATE)
FROM EMPLOYEE;

 

7.4 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(BONUS)
 FROM EMPLOYEE;

-- 부서배치를 받은 사원 수
SELECT COUNT(DEPT_CODE)
 FROM EMPLOYEE;

-- 현재 사원들이 총 몇개의 부서에 분포되어있는지 조회
SELECT COUNT(DISTINCT DEPT_CODE)
 FROM EMPLOYEE;