국비 3일차, 함수(TRIM), 숫자함수(CEIL, FLOOR, ROUND, TRUNC, MOD, SIGN, CASE, DECODE), 날짜함수(SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY,LAST_DAY), 변환함수(TO_NUM, TO_CHAR, TO_DATE)

2021. 1. 7. 18:14

2021.01.07 국비교육 3일차

[TOC]

함수

LTRIM

문자열의 첫 문자부터 확인해서 지정 문자가 나타나는 동안 해당 문자 제거

SELECT LTRIM('MILLER', 'M') FROM DUAL;
-- M을 제거, M을 제거하지 않으면 I는 제거되지 않음 // 출력값 ILLER

SELECT LENGTH(LTRIM('  MILLER  ')) FROM DUAL;
-- 공백이 포함된 문자를 LTRIM 하면 좌측에 있는 공백은 사라지지만 우측은 안사라짐, 출력값 8

SELECT ENAME, JOB, LTRIM(job, 'A'), LTRIM(SAL, 1) FROM EMP;

RTRIM

문자열의 끝 문자부터확인해서 지정 문자가 나타나는 동안 해당 문자 제거
지정 문자 생략하면 공백이 기본

SELECT RTRIM('MILLER', 'R') FROM DUAL;
-- R을 제거 // 출력값 MILLE

SELECT LENGTH(LTRIM('  MILLER  ')) FROM DUAL;
-- 우측 공백 사라짐

SELECT ENAME, JOB, RTRIM(JOB, 'T'), SAL, RTRIM(SAL, 0) FROM EMP;
-- 직업 끝자리 T삭제, 급여 0자리 다 삭제

TRIM

양쪽 전부 다 삭제, 공백 제거할 때 주로 사용
옵션
- leading 왼쪽만
- trailing 오른쪽만
- both 양쪽 다 ( default값)

SELECT TRIM('0' FROM '000012340000') FROM DUAL;
-- 출력값 1234

SELECT TRIM( TRAILING '0' FROM 000012340000) FROM DUAL;
-- 출력값 00001234

SELECT TRIM( LEADING '0' FROM '000012340000') FROM DUAL;
-- 출력값 12340000

SELECT TRIM('    ABC   '), LENGTH(TRIM('    ABC   ')) FROM DUAL;
-- 출력값 ABC, 3

숫자 함수

CEIL

올림값 // 반올림 아님 !

FLOOR

내림값

SELECT CEIL(10.1) FROM DUAL;
-- 출력값 11
SELECT FLOOR(10.1) FROM DUAL;
-- 출력값 10

ROUND

소수점 N번째 자리 이하에서 반올림
생략하면 기본 N값 0

SELECT ROUND(124.151, 2) FROM DUAL;
-- 출력값 124.15

SELECT ROUND(4567.678), ROUND(4567.678 ,0 ), 
ROUND(4567.678 ,2), ROUND(4567.678, -2) 
FROM DUAL;

--출력값, 4568/ 4568/ 4567.68/ 4600

TRUNC

-숫자를 소주 N자리에서 절삭, 반올림이 아님

SELECT TRUNC(4567.678, 2) FROM DUAL;
--출력값 4567.67

MOD

숫자의 나머지를 구하는 함수
MOD(숫자, 나눌 숫자) / 결과 값 = 나머지
홀수, 짝수 구할때 많이 사용


SELECT EMPNO, ENAME FROM EMP WHERE MOD(EMPNO, 2) = 1;
-- 사원 번호가 홀수인 사람 출력

SIGN

양수,음수, 0인지 확인하는 함수

-- 양수면 1, 음수면 -1, 0이면 0값 리턴

SELECT SIGN(10) FROM DUAL;
-- 1 출력

SELECT SIGN(-15) FROM DUAL;
-- -1 출력

SELECT SIGN(0) FROM DUAL;
-- 0 출력

SELECT SAL FROM EMP WHERE SIGN(SAL - 1500) = 1;
-- 월급이 1500 이상인 사람 출력

SELECT * FROM NLS_SESSION_PARAMETERS; 파라미터 확인 명령어

CASE

다양한 비교연산자를 사용하여 설정 가능

SELECT EMPNO, ENAME, SAL, JOB,
CASE JOB WHEN 'ANALYST' THEN SAL * 1.1
        WHEN 'CLERK' THEN SAL * 1.2
        WHEN 'MANAGER' THEN SAL * 1.3
        WHEN 'PRESIDENT' THEN SAL * 1.4
        WHEN 'SALESMAN' THEN SAL * 1.5
        ELSE SAL
    END 급여
FROM EMP;


SELECT EMPNO, ENAME,
    CASE WHEN SAL >=0 AND SAL <=1000 THEN 'E'
         WHEN SAL >1000 AND SAL <=2000 THEN 'D'
         WHEN SAL >2000 AND SAL <=3000 THEN 'C'
         WHEN SAL >3000 AND SAL <=4000 THEN 'B'
         WHEN SAL >4000 AND SAL <=5000 THEN 'A'
    END 등급
FROM EMP;


SELECT EMPNO, ENAME, SAL,
    CASE SAL WHEN  5000 THEN SAL + 1000
         WHEN  3000 THEN SAL + 1000
         ELSE SAL
    END
FROM EMP;

SELECT EMPNO, ENAME, SAL,
    CASE WHEN  SAL>=2000 THEN 1000
         WHEN  SAL >=1500 THEN 2000
         WHEN  SAL >=1000 THEN 3000
         ELSE 4000
    END 보너스
FROM EMP
ORDER BY 2;

SELECT EMPNO, ENAME, SAL,
    CASE WHEN  SAL BETWEEN 2000 AND 2500 THEN '상'
         WHEN  SAL BETWEEN 1000 AND 1999 THEN '중'
         ELSE  '하'
    END 등급
FROM EMP
ORDER BY 2;

--예제 1. 사원테이블에서 입사일이 12월인 사원의 사번, 사원명, 입사일 검색
SELECT EMPNO, ENAME, HIREDATE FROM EMP
WHERE TO_CHAR(HIREDATE, 'MM') = '12';

--예제 2. EMPNO, ENAME, 급여 앞자리 6개 별표로
SELECT EMPNO, ENAME, LPAD(SAL, 10, '*') 급여 FROM EMP;

--예제 3. 입사일 빠른 순서대로 정렬
SELECT EMPNO, ENAME, TO_CHAR(HIREDATE, 'YYYY-MM-DD') 입사일 FROM EMP
ORDER BY 3;

--예제 4. 영업사원 / 비영업사원 구별
SELECT EMPNO, ENAME, COMM,
    CASE WHEN COMM IS NULL THEN '일반사원'
    ELSE '영업사원'
END 사원종류
FROM EMP;

--예제 5.부서위치 동부, 중부, 서부 규별
SELECT LOC,
    CASE LOC WHEN 'NEW YORK' THEN 'EAST'
              WHEN 'DALLAS' THEN 'CENTER'
              WHEN 'CHICAGO' THEN 'CENTER'
              WHEN 'BOSTON' THEN 'EAST'
    END AREA
FROM DEPT;

OR

SELECT LOC,
    CASE WHEN LOC IN('NEW YORK', 'BOSTON')  THEN 'EAST'
        WHEN LOC IN ('DALLAS', 'CHICAGO') THEN 'CENTER'
    END AREA
FROM DEPT;

--6. 사원 테이블에서 급여가 2000 이상이면 보너스 1000, 1000이상이면 500, 1000미만이면 0

SELECT ENAME, SAL,
    CASE WHEN SAL >=2000 THEN 1000
         WHEN SAL >= 1000 AND SAL < 2000 THEN 500
         ELSE 0
    END BONUS
FROM EMP;

--7. SAL 별 등급 부여
SELECT EMPNO, ENAME, SAL,
    CASE WHEN SAL >=0 AND SAL <=1000 THEN 'E'
         WHEN SAL <=2000 THEN 'D'
         WHEN SAL <=3000 THEN 'C'
         WHEN SAL <=4000 THEN 'B'
         WHEN SAL > 4000 THEN 'A'
    END 등급
FROM EMP;

SELECT EMPNO, ENAME, SAL, 
    DECODE(SIGN(SAL-4000), 1, 'A',
    DECODE(SIGN(SAL-3000), 1, 'B',
    DECODE(SIGN(SAL-2000), 1, 'C',
    DECODE(SIGN(SAL-1000), 1, 'D' , 'E')
    ))) GRADE
    FROM EMP;

DECODE

조건이 반드시 일치하는 경우

SELECT EMPNO, ENAME, SAL, JOB,
DECODE(JOB, 'ANALYST' , SAL * 1.1,
            'CLERK', SAL *1.2,
            'MANAGAER', SAL *1.3,
            'PRESIDENT', SAL*1.4, SAL) 급여
FROM EMP;

-- 직업에 맞게 월급 차등지급

날짜 함수

Default Date Type 은 RR-MM-DD(변경가능)

RR과 YY 차이

YY는 데이터베이스의 현재 년도의 앞의 두자리수와 데이터의 년도 합침

EX) 데이터베이스 : 2021년, 데이터 90/11/11 -> 반환년도 2090

RR은 50단위로 끊어서 DB와 DATA의 수를 합하여 반환

SYSDATE

데이터베이스 서버에 설정되어 있는 날짜를 리턴

SELECT SYSDATE FROM DUAL;
-- 출력값 21/01/07
-- 게시판 작성 날짜 삽입시에 사용

SELECT SYSTIMESTAMP FROM DUAL;
-- 현재 시간까지 함께 출력

SELECT SYSDATE "오늘", SYSDATE +1 "내일", SYSDATE -1 "어제" FROM DUAL;
-- 연산 가능

STEP
SELECT SYSDATE - HIREDATE FROM EMP;
-- DB날짜에서 DATA날짜 빼면 일 수로 계산됨 (근무 일 수)

SELECT TRUNC((SYSDATE - HIREDATE)/7) FROM EMP;
-- 근무한 주

SELECT ENAME, HIREDATE "입사 일", TRUNC((SYSDATE - HIREDATE)/365) "근무 년수" FROM EMP;
-- 근무 년수 계산

SELECT ENAME, TRUNC((SYSDATE-HIREDATE)/7) "WEEK", TRUNC((SYSDATE-HIREDATE)/365) "DAY"
FROM EMP ORDER BY DAY DESC;
--EMP 테이블에서 현재까지 근무일 수가 몇주 며칠 인가를 출력. 단 근무 일수가 많은 사람 순으로 출력

?SYSDATE에서 HIREDATE를 빼면 왜 소수점이 나오는걸까

-> 시간부분도 같이 계산이 되기 때문에

MONTHS_BETWEEN

날짜와 날짜 사이의 월수를 계산
MONTHS_BETWEEN(SYSDATE, HIREDATE)

SELECT MONTHS_BETWEEN(SYSDATE, HIREDATE) FROM EMP;


SELECT DEPTNO, ENAME, HIREDATE, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) "근무 월수"
FROM EMP WHERE DEPTNO = 10 ORDER BY "근무 월수" DESC; 
--EMP 테이블내에서 10번 부서중 현재가지의 근무 월수 계산

ADD_MONTHS

날짜에 월을 더하고 뺀다
ADD_MONTHS(날짜, N) N = 더할날짜 (음수도 가능)

SELECT DEPTNO, ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 5) FROM EMP WHERE DEPTNO = 10
ORDER BY HIREDATE DESC;
-- 부서 번호가 10번인 사원들의 입사일자로 부터 5개월이 지난 후의 날짜는?

NEXT_DAY

지정된 날짜를 기준으로 가장 가까운 요일 찾기
일요일을 숫자 1로인식(토요일 = 7)
NEXT_DAY(기준날짜, 요일)

SELECT NEXT_DAY(SYSDATE, '일') FROM DUAL;
-- 위 아래 두개는 같은 출력값
SELECT NEXT_DAY(SYSDATE, 1) FROM DUAL;

LAST_DAY

월의 마지막 날짜를 계산
윤년, 평년은 자동 계산

SELECT LAST_DAY(SYSDATE) FROM DUAL;
-- 이번달의 마지막 요일 계산 출력값 21/01/31

SELECT EMPNO, ENAME, HIREDATE 고용일 , LAST_DAY(HIREDATE) FROM EMP
WHERE EMPNO = 7839;
-- 사원번호 7839번의 입사한 달의 마지막 날짜와 이름, 입사일 출력

SELECT NEXT_DAY(ADD_MONTHS(SYSDATE, 5), '토') FROM DUAL;
-- SYSDATE를 기준 5개월 뒤 돌아오는 토요일 날짜를 계산

ROUND/ TRUNC 활용

ROUND는 가장 가가운 년도 또는 월로 반올림

ROUND(DATE, 'MONTH')

ROUND(DATE, 'YEAR')
TRUNC는 가장 가까운 년도 또는 월로 절삭

TRUNC(DATE, 'MONTH')

TRUNC(DATE, 'YEAR')

SELECT EMPNO, ENAME, HIREDATE,
ROUND(HIREDATE, 'MONTH'),
ROUND(HIREDATE, 'YEAR')
FROM EMP
WHERE EMPNO = '7839';
--출력값  7839    KING    81/11/17    81/12/01    82/01/01

SELECT EMPNO, ENAME, HIREDATE,
TRUNC(HIREDATE, 'MONTH'),
TRUNC(HIREDATE, 'YEAR')
FROM EMP
WHERE EMPNO = '7839';
--출력값  7839    KING    81/11/17    81/11/01    81/01/01

변환 함수

TO_NUM

묵시적 형 변환

자동으로 변환(적용) 되는것
명시적 형 변환

자동 적용이 안되서 프로그래머가 기술해주어야함

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO = '7900'
--묵시적인 경우 // EMPNO의 DATATYPE은 NUMBER지만 문자로 적힌 7900 인식함

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO = TO_NUMBER('7900')
--명시적인 경우 // '7900'을 NUMBER로 바꾸어줌

SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE = '82/01/23'
-- 묵시적인 경우

SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE = TO_DATE('82/01/23')
-- 명시적인 경우

TO_CHAR

데이터 출력시 출력형식을 설정할수 있음

형식: TO_CHAR(DATE, 'FORMAT')

EX) TO_CHAR(HIREDATE, ' YY "년" MM "월" DD"일" ')

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD, (AM) DY HH24:MI:SS')
FROM DUAL;
-- 2021/01/07, (오후) 목 12:38:36

SELECT TO_CHAR(SYSDATE, 'YYYY "년"') FROM DUAL;
--2021 년

SELECT TO_CHAR(SYSDATE, 'YYYY "년" MM "월"') FROM DUAL;
--2021 년 01 월

SELECT TO_CHAR(SYSDATE, 'YYYY "년" MM "월" DD "일"') FROM DUAL;
--2021 년 01 월 07 일

SELECT ENAME, HIREDATE, TO_CHAR(HIREDATE, 'YYYY "년" MM "월" DD"일" ') FROM EMP;
--사원들의 입사 날짜 형식 변경 [SMITH | 80/12/17 | 1980 년 12 월 17일 ]

SELECT ENAME, HIREDATE FROM EMP WHERE TO_CHAR(HIREDATE, 'MM') = '09';
--9월에 입사한 사원 출력  MARTIN|81/09/28

숫자형식모델

형식	설명
9	한 자리의 숫자 표현
0	앞부분을 0으로 표현
$	달러 기호를 앞에 표현
.	소수점을 표시
,	특정 위치에 , 표시
B	공백을 0으로 표현
L	지역 통화(LOCAL CURRENCY)

SELECT ENAME, TO_CHAR(SAL, 'L999,999') 원화 FROM EMP;
-- 사원들의 월급 원화로 표시

SELECT ENAME, TO_CHAR(SAL, 'L999,999') 원화, TO_CHAR(SAL, '$999,999') 달러 FROM EMP;
--원화 + 달러

TO_DATE

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD MM24:MI:SS'
-- 세션 설정 YY/MM/DD에서변경

--변경 전
SELECT TO_DATE('20170802', 'YYYYMMDD') FROM DUAL; -- 출력하면 17/08/02

--변경 후
SELECT TO_DATE('20170802181030', 'YYYYMMDDHH24MISS') FROM DUAL; --2017/08/02 18:10:30

SELECT SYSDATE - TO_DATE('20170802', 'YYYYMMDD') FROM DUAL;
--날짜 얼마나 지났는지 계산

SELECT ROUND(TRUNC(SYSDATE - TO_DATE('19940625', 'YYYYMMDD'))/365) FROM DUAL;
SELECT (TO_CHAR(SYSDATE, 'YYYY') - 1994) + 1 FROM DUAL;
-- 내 나이 구하기 // 아래꺼가 더 쉬움..

'ETC > 국비교육' 카테고리의 다른 글

국비 6일차 서브쿼리(단일행, 복수행, ALL, ANY, EXISTS, 다중칼럼, PAIRWISE, UNPAIRWISE, 인라인 뷰) (0)	2021.01.12
국비 5일차 JOIN(catasian, equi, non-equi, self, outer), ANSI JOIN(cross, natural, using, on, left, right, full outer) (0)	2021.01.11
국비 4일차, 그룹함수, GROUP BY, HAVING (0)	2021.01.08
국비 2일차, [오라클] SQL WHERE(비교연산자 BETWEEN, IN, IS NULL, LIKE,논리 연산자), 정렬, 함수(INITCAP, LOWER, UPPER, CONCAT, LENGTH, INSTR, LPAD, RPAD, SUBSTR, REPLACE) (0)	2021.01.06
국비 1일차, 오라클 설치, 데이터베이스 개요, SQL 종류 및 특징, SELECT(Null, Dual, 연결연산자, 리터럴, DISTINCT, WHERE, 비교연산자) (0)	2021.01.05

퍼즐메이킹

Menu

Category

Notice

Recent comments

Links

국비 3일차, 함수(TRIM), 숫자함수(CEIL, FLOOR, ROUND, TRUNC, MOD, SIGN, CASE, DECODE), 날짜함수(SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY,LAST_DAY), 변환함수(TO_NUM, TO_CHAR, TO_DATE)

2021.01.07 국비교육 3일차

함수

LTRIM

RTRIM

TRIM

숫자 함수

CEIL

FLOOR

ROUND

TRUNC

MOD

SIGN

CASE

DECODE

날짜 함수

RR과 YY 차이

SYSDATE

MONTHS_BETWEEN

ADD_MONTHS

NEXT_DAY

LAST_DAY

ROUND/ TRUNC 활용

변환 함수

TO_NUM

TO_CHAR

TO_DATE

'ETC > 국비교육' 카테고리의 다른 글

+ Recent posts

티스토리툴바