[ SQL ] 오라클 내장함수 2. 단일행함수의 숫자함수, 날짜함수

 

하루입니다.

 

 

---

 

오라클 내장함수란?

SQR 작성에 유용한 기능을 제공하는 함수. DBMS 제품마다 조금씩 차이가 있다. 단일행함수와 다중행함수로 나뉜다. 

 

• 문자함수 : 문자를 입력값으로 받아서 계산한 결과를 반환
• 숫자함수 : 숫자를 입력값으로 받아서 계산한 결과를 반화
• 날짜함수 : Date 타입의 값에 대한 처리를 수행
• 변환함수 : 데이터의 타입을 변환하는 처리를 수행
• 기타함수 : nvl, case, decode 등의 함수가 있다.

 

 오늘은 문자함수를 제외한 나머지 함수들을 배웠다. 숫자 날짜 변환 기타 / 다중행 / JOIN으로 나눠 올려야지. 많이 배웠네 ...

 

 

숫자함수

• ROUND(컬럼 및 표현식), ROUND(컬럼 및 표현식, 자리수) : 반올림
• TRUNC(컬럼 및 표현식), TRUNC(컬럼 및 표현식, 자리수)   : 소숫점 부분을 버림
• FLOOR(컬럼 및 표현식)                                               : 바닥값 반환
• CEIL(컬럼 및 표현식)                                                   : 천정값 반환

 

• ROUND(컬럼 및 표현식), ROUND(컬럼 및 표현식, 자리수) : 반올림
• 1이면 소수점 첫째 자리에서 반올림, -1이면 1의 자리에서 반올림

SELECT ROUND(1234.567),               = 1235
       ROUND(1234.567, 0),            = 1235
       ROUND(1234.567, 1),            = 1234.6
       ROUND(1234.567, 2),            = 1234.57
       ROUND(1234.567, -1),           = 1230
       ROUND(1234.567, -2)            = 1200
FROM DUAL;

 

• TRUNC(컬럼 및 표현식), TRUNC(컬럼 및 표현식, 자리수)  : 소숫점 부분을 버림
• 1이면 소수점 첫째자리까지 남김, -1이면 1의 자리 버림

SELECT TRUNC(1234.567),               = 1234
       TRUNC(1234.123, 0),            = 1234
       TRUNC(1234.567, 1),            = 1234.5   
       TRUNC(1234.567, 2),            = 1234.56
       TRUNC(1234.567, -1),           = 1230
       TRUNC(1234.567, -2)            = 1200
FROM DUAL;

 

• 양수일 경우 : 반올림 / 소수점 버림 / 천장 / 바닥

SELECT ROUND(1.2), ROUND(1.8),        = 1, 2
       TRUNC(1.2), TRUNC(1.8),        = 1, 1
       FLOOR(1.2), FLOOR(1.8),        = 1, 1
       CEIL(1.2),  CEIL(1.8)          = 2, 2
FROM DUAL;

 

• 음수일 경우 : 반올림 / 소수점 버림 / 천장 / 바닥
• 양수일 경우와 값이 다르게 나온다.

SELECT ROUND(-1.2), ROUND(-1.8),      = -1, -2
       TRUNC(-1.2), TRUNC(-1.8),      = -1, -1
       FLOOR(-1.2), FLOOR(-1.8),      = -2, -2
       CEIL(-1.2),  CEIL(-1.8)        = -1, -1
FROM DUAL;

 

SQL FLOOR CEIL

값이 다른 이유 그림으로 정리 (내가 그림)

 

• 숫자함수 활용
• 급여의 백자리는 버리고 출력할 거고 전체 사원수를 셀 거다.
• EMPLOYEES 테이블의 데이터이다.
• TRUNC(SALARY, -3)으로 그룹지으며(얘는 별칭 사용이 안 되나), 급여의 오름차순으로 정렬한다.

SELECT TRUNC(SALARY, -3) 급여, COUNT(*) 사원수
FROM EMPLOYEES
GROUP BY TRUNC(SALARY, - 3)
ORDER BY 급여;

 

 

---

 

날짜함수

• SYSDATE : 현재 날짜를 반환한다.
• 지독한 ROUND ... 날짜까지 반올림하네 ...

SELECT SYSDATE,                 현재 날짜
    ROUND(SYSDATE),             정오가 지나면 다음날로 반올림(...)한다    
    TRUNC(SYSDATE),             시, 분, 초를 0으로 지정한 정보를 반환한다
    TRUNC(SYSDATE) - 3,         현재 날짜 기준으로 3일 전 날짜를 반환한다.  
    TRUNC(SYSDATE) - 7          현재 날짜 기준으로 7일 전 날짜를 반환한다.
FROM DUAL;

 

• SELECT 응용
• 현재 날짜에서 3일 뺀 날짜 이후에 생성된 상품들을 보고 싶다. 
• 여기서 굳이 TRUNC를 사용하는 이유는 '현재 시각' 기준이기 때문이다.
• 이게 무슨 뜻이냐면, 보통은 3일 전 00시부터 생성된 상품을 보고싶은 거지, 3일 전 오후 10시 57분 이후에 생성된 것을 보고싶어하는 것이 아니기 때문.

SELECT *
FROM SAMPLE_PRODUCTS
WHERE PRODUCT_CREATED_DATE >= TRUNC(SYSDATE) - 3;

 

• ADD_MONTHS : S 붙이는 거 빼먹지 말고 ... 

SELECT ADD_MONTHS(SYSDATE, 1),			한 달 후
       ADD_MONTHS(SYSDATE, -1),			힌 달 전
       ADD_MONTHS(SYSDATE, -12)			일 년 전
FROM DUAL;

 

• 같이 수업 듣는 분이 이런 질문을 했다. 만약 3월 29/30/31일에서 한 달을 빼면 오류가 나냐고. 천재적인 생각 ... 
• 분명 수업 당시에는 오류가 났는데 지금은 오류가 안 난다. 알아서 결과를 해당 달의 마지막으로 바꿔 준다.

SELECT ADD_MONTHS(TO_DATE('2022-04-30'), -1),
       ADD_MONTHS(TO_DATE('2022-03-31'), -1)
FROM DUAL;

 

 

날짜관련 연산

• 날짜 + 숫자 : 숫자만큼 이후 날짜를 변환한다.
• 날짜 - 숫자 : 숫자만큼 이전 날짜를 변환한다.
• 날짜 - 날짜 : 두 날짜사이의 일수를 변환한다.
• 날짜 + 날짜 : 오류, 날짜와 날짜를 더하는 연산은 지원하지 않는다.

SELECT SYSDATE + 3,
SYSDATE - 3,
SYSDATE + SYSDATE,			에러 ORA-00975: 날짜와 날짜의 가산은 할 수 없습니다
SYSDATE - SYSDATE
FROM DUAL;

 

• 날짜 연산 활용. 태어난 지 얼마나 됐는지 계산하기.

SELECT TRUNC(SYSDATE) - TO_DATE('2000.01.01')
FROM DUAL;

• TRUNC를 붙이는 이유는 위의 사진처럼 뜨기 때문이다.

 

• 시간 더하기 빼기

SELECT SYSDATE + 1/24, 			현재시간 + 1시간
       SYSDATE - 1/12,			현재시간 - 2시간
       SYSDATE + 1/2			현재시간 + 12시간
FROM DUAL;

 

 

---

 

 

변환함수.

자바의 오토박싱이라는 게 생각났다. 달려 달려

 

• 문자 -> 숫자 : TO_NUMBER(문자, '패턴')
• 숫자 -> 문자 : TO_CHAR(숫자, '패턴')   :   거의 사용 안 함. 비교 못하니까. 별로 할 수 있는 게 없다.
• 문자 -> 날짜 : TO_DATE('문자', '패턴')
• 날짜 -> 문자 : TO_CHAR(날짜, '패턴')

 

• 명시적 변환 : 변환함수를 사용해서 문자를 숫자나 날짜로 변환하는 것
• 묵시적 변환 : 연산자나 연산대상이 되는 컬럼의 타입들을 고려해서 문자를 숫자나 날짜로 자동으로 변환하는 것

SELECT TO_NUMBER('123') + TO_NUMBER('123'),		명시적 변환
       '123' + '123'					묵시적 변환
FROM DUAL;

 

• 9,999는 #,### 형태야. 라고 알려주는 것. 패턴, 명시적 변환이다.
• 다만 SQL에서 숫자를 가공하는 것보단 자바의 표현계층에서 변경하기를 권장한다.

SELECT TO_NUMBER('1,234', '9,999') + TO_NUMBER('1,23', '9,999')
FROM DUAL;

 

• 문자를 날짜로 변환하기
• YYYY/MM/DD, yyyy/mm/dd, YYYY/mm/dd 등 대소문자 상관 없다.

묵시적 변환
WHERE HIRE_DATE >= '2005/01/01';
WHERE HIRE_DATE >= '2005-01-01';
WHERE HIRE_DATE >= '2005.01.01';

명시적 변환
HIRE_DATE >= TO_DATE('2005.01.01', 'YYYY/MM/DD');

문자 두 개가 하나의 패턴이기에, 이건 오류다.
HIRE_DATE >= TO_DATE('2005.01.01', 'YYYY/m/d');

 

• 시, 분, 초 정보가 포함된 형변환

묵시적 변환
product_created_date >= '2022/04/22 00:00:00';

명시적 변환
product_created_date >= TO_DATE('2022/04/22 00:00:00', 'YYYY/MM/DD HH24:MI:SS');

 

• 날짜를 특정 형식의 문자로 변환하기

-- 2022년 4월 26일 오후 10시 30분 기준
SELECT TO_CHAR(SYSDATE, 'YYYY'),    --  년		 2022
        TO_CHAR(SYSDATE, 'MM'),     --  월		 04
        TO_CHAR(SYSDATE, 'DD'),     --  일		 26
        TO_CHAR(SYSDATE, 'AM'),     --  오전/오후        오전
        TO_CHAR(SYSDATE, 'HH'),     --  12시간제		 10
        TO_CHAR(SYSDATE, 'HH24'),   --  24시간제		 23
        TO_CHAR(SYSDATE, 'MI'),     --  분		 40
        TO_CHAR(SYSDATE, 'SS'),     --  초		 11
        TO_CHAR(SYSDATE, 'DAY')     --  요일	 	화요일
FROM DUAL;

 

 

---

 

 

기타함수.

자바의 IF / IF ELSE문과 비슷하다는 생각을 했다.

• NVL, NVL2, DECODE, (함수는 아니지만) CASE WHEN THEN ELSE END 표현식

 

NVL

• NVL(컬럼 혹은 표현식, 값)
• 컬럼 혹은 표현식의 값이 NULL이 아니면 컬럼 혹은 표현식의 값이 반환되고 NULL이면 값이 반환된다.
• 컬럼 혹은 표현식의 타입과 값의 타입이 일치해야 한다.]
• NULL을 처리할 때 쓴다?
• 밑 코드 해설. 첫번째 NVL은 컬럼 혹은 표현식의 값이 NULL이 아니므로 0을 곱하지 않음. 두번째 NVL은 컬럼 혹은 표현식의 값이 NULL므로 0 값을 반환함. 그래서 위는 1100, 아래는 1000의 결과가 나옴. 

SELECT 1000 + 1000*NVL(0.1, 0),
       1000 + 1000*NVL(NULL, 0)
FROM DUAL;

 

• 이걸 어느 경우에 쓰느냐. 직원들의 연봉을 구하고 싶다. 연봉은 월급*12 + 월급*커미션*12의 식을 통해 구하고 싶다. 하지만 SQL의 특징 탓에 커미션이 NULL인 직원들의 연봉이 모두 NULL로 나온다. 이런 경우, NVL을 통해 NVL(COMMISSION_PCT, 0)으로 처리한다면 커미션이 있는 직원들은 커미션이, 없는 직원들은 0이 식으로 들어가며 연봉이 정상적으로 구해질 것이다.
• 주의사항 : 밑의 경우는 ( ) 안의 형식이 다르기에 오류이다.

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT, SALARY*12 + SALARY*NVL(COMMISSION_PCT, 0)*12 ANNUAL_SALARY
FROM EMPLOYEES
WHERE SALARY >= 10000
ORDER BY EMPLOYEE_ID ASC;

-- 이 경우는 형식이 다르기에 오류이다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, NVL(COMMISSION_PCT, '없음')

 

 

NVL2

• NVL2 (컬럼 혹은 표현식, 값1, 값2)
• 컬럼 혹은 표현식의 값이 NULL이 아니면 값1이 반환되고 NULL이면 값이 반환된다.
• 값1과 값2의 타입이 일치해야 한다.
• 급여가 10000이상인 직원의 아이디, 이름, 급여, 커미션을 조회한다. 커미션이 NULL이 아니면 '있음', NULL이라면 '없음'이라고 표시한다.

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, NVL2(COMMISSION_PCT, 'Y', 'N')
FROM EMPLOYEES
WHERE SALARY >= 10000

 

 

DECODE 

• DECODE (컬럼 혹은 표현식, 값1, 값 혹은 표현식, 값2, 값 혹은 표현식)
• 컬럼 혹은 표현식의 값이 값1과 일치하면 값1 다음에 있는 값 혹은 표현식의 결과가 반환된다.
• 값2와 일치하면 값2 다음에 있는 값 혹은 표현식의 결과가 반환된다.
• 값1, 값2와 전부 일치하지 않으면 NULL을 반환한다.

 

• DECODE (컬럼 혹은 표현식, 값1, 값 혹은 표현식, 값2, 값 혹은 표현식, 값 혹은 표현식)
• 컬럼 혹은 표현식의 값이 값1과 일치하면 값1 다음에 있는 값 혹은 표현식의 결과가 반환된다.
• 값2와 일치하면 값2 다음에 있는 값 혹은 표현식의 결과가 반환된다.
• 값1, 값2와 전부 일치하지 않으면 맨 마지막에 있는 값 혹은 표현식의 결과를 반환한다.]

 

• 직원테이블에서 80번 부서에 소속된 사람은 A팀, 50번 부서에 소속된 사람은 B팀, 그 외는 C팀이라고 할 때.

SELECT DECODE(DEPARTMENT_ID, 80, 'ATEAM', 60, 'BTEAM', 'CTEAM') TEAM, DEPARTMENT_ID, FIRST_NAME
FROM EMPLOYEES
ORDER BY TEAM;

 

 

 

CASE WHEN THEN ELSE END 표현식

CASE
WHEN 조건식1 THEN 표현식1
WHEN 조건식2 THEN 표현식2
ELSE 표현식3
END

-- DECODE와 흡사한 방식
CASE 컬럼 혹은 표현식 
WHEN 값1 THEN 표현식1
WHEN 값2 THEN 표현식2
ELSE 표현식3
END

 

• 직원 아이디, 이름, 급여, 인상된 급여를 조회할 것이다. 급여가 10000 이상이면 3% 인상, 급여가 5000 이상이면 5% 인상, 그 외는 10% 인상된 급여를 조회한다.

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,		-- 그래서 여기 , 있다.
CASE	
    WHEN SALARY >= 10000 THEN SALARY*1.03
    WHEN SALARY >= 5000 THEN SALARY*1.05
    ELSE SALARY*1.1
    END INCREASED_SALARY        -- 여기까지가 SELECT문이다.
FROM EMPLOYEES;

 

 

---

 

잘못된 정보에 대한 지적은 언제나 환영입니다. 

 

복습 끝!