오라클 데이터 처리 가공 함수모음

대,소문자를 바꿔주는 UPPER,LOWER,INITCAP 함수

​

*UPPER(문자열):괄호 안 문자 데이터를 모두 대문자로 변환하여 반환

*LOWER(문자열):괄호 안 문자 데이터를 모두 소문자로 변환

*INITCAP(문자열):괄호 안 문자 데이터 중 첫 글자는 대문자로, 나머지 문자를 소문자로 변환 후 반환

​

ex)

SELECT ENAME,UPPER(ENAME),LOWER(ENAME),INITCAP(ENAME) FROM EMP;

​

​

ex2) UPPER 함수로 문자열 비교하기(사원 이름 SCOTT인 데이터 찾기)

​

SELECT * FROM EMP WHERE UPPER(ENAME) = UPPER('scott');

​

ex3) UPPER 함수로 문자열 비교하기(사원 이름에 SCOTT 단어를 포함한 단어찾기)

​

SELECT * FROM EMP WHERE UPPER(ENAME) LIKE UPPER('%scott%');

​

​

문자열 길이를 구하는 LENGTH 함수

특정 문자열의 길이를 구할 때 사용

​

ex)ENAME 열의 길이 구해보기

​

SELECT ENAME,LENGTH(ENAME) FROM EMP;

​

ex2)이름의 길이가 5이상인 행 출력해보자

​

SELECT ENAME,LENGTH(ENAME) FROM EMP WHERE LENGTH(ENAME) >=5;

​

TIP)LENGTH, LENGTHB 차이는 문자열,바이트수 차이

​

​

문자열 일부를 추출하는 SUBSTR 함수

*SUBSTR(문자열 데이터,시작 위치, 추출 길이) : 문자열 데이터 시작위치부터 추출 길이만큼 추출,

시작 위치가 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 시작

*SUBSTR(문자열 데이터,시작 위치):문자열 데이터의 시작 위치부터 문자열 데이터 끝까지 추출, 시작 위치가 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 끝까지 추출

​

ex)

SELECT JOB,SUBSTR(JOB,1,2),SUBSTR(JOB,3,2),SUBSTR(JOB,5) FROM EMP;

​

ex2) SUBSTR 함수 안에 다른 함수(LENGTH) 같이 사용해보자

​

SELECT JOB,

SUBSTR(JOB,-LENGTH(JOB)),

SUBSTR(JOB,-LENGTH(JOB),2),

SUBSTR(JOB,-3)

FROM EMP;

​

​

​

특정 문자를 다른 문자로 바꾸는 REPLACE 함수

REPLACE 함수는 특정 문자열 데이터에 포함된 문자를 다른 문자로 대체할 경우에 유용하다.

​

REPLACE([문자열 데이터 또는 열 이름(필수)],[찾는 문자(필수)],[대체할 문자(선택)])

​

ex)문자열 안에 있는 특정 문자 바꿔보기

​

SELECT '010-1234-5678' AS REPLACE_BEFORE,

REPLACE('010-1234-5678','-',' ') AS REPLACE_1,

REPLACE('010-1234-5678','-') FROM EMP;

​

​

데이터의 빈 공간을 특정 문자로 채우는 LPAD,RPAD 함수

​

*LPAD 는 데이터 자리수를 지정한후 데이터 길이가 지정한 자리수보다 작을경우 나머지공간을 특수문자로 대체

*RPAD는 왼쪽을 특수문자로 대체

​

ex)

SELECT 'ORACLE',LPAD('ORACLE',10,'#') AS LPAD,

RPAD('ORACLE',10,'*') AS RPAD

FROM EMP;

​

​

두 문자열 데이터를 합치는 CONCAT 함수

CONCAT함수는 두 개의 문자열 데이터를 하나의 데이터로 연결해주는 역할.

​

ex)

SELECT CONCAT(EMPNO,ENAME),

CONCAT(EMPNO,CONCAT(' : ',ENAME))

FROM EMP

WHERE ENAME='KING';

특정 위치에서 반올림하는 ROUND 함수

ROUND 함수는 TRUNC 함수와 많이사용하는 것중 하나, 특정 숫자를 반올림하되 반올림할 위치를 지정할수 있음

지정하지않으면 소수점 첫째자리에서 반올림한 결과가 반환.

​

*ROUND([숫자(필수)],[반올림 위치(선택)])

​

ex)

SELECT ROUND(1234.5678) AS ROUND,

ROUND(1234.5678,0) AS ROUND_0,

ROUND(1234.5678,1) AS ROUND_1,

ROUND(1234.5678,2) AS ROUND_2,

ROUND(1234.5678,-1) AS ROUND_MINUS1

FROM DUAL;

​

​

특정 위치에서 버리는 TRUNC 함수

*TRUNC([숫자(필수)],[버림위치선택])

​

ex)

​

​

지정한 숫자와 가까운 정수 찾는 CEIL,FLOOR 함수

​

예제를보면 그냥 알수있다.

​

ex)

SELECT CEIL(3.14),

FLOOR(3.14) FROM DUAL;

​

​

​

​

날짜 데이터 함수

​

연산	설명
날짜 데이터 + 숫자	날짜 데이터보다 숫자만큼 일수 이후의 날짜
날짜 데이터 - 숫자	날짜 데이터보다 숫자만큼 일수 이전의 날짜
날짜 데이터 - 날짜 데이터	두 날짜 데이터 간의 일수 차이
날짜 데이터 + 날짜 데이터	연산 불가, 지원하지 않음

​

ex) SYSDATE 함수를 사용해서 날짜 출력해보자

SELECT SYSDATE AS NOW,

SYSDATE-1 AS YESTERDAY,

SYSDATE+1 AS TOMORROW

FROM DUAL;

​

​

​

몇 개월 이후 날짜를 구하는 ADD_MONTHS 함수

ADD_MONTHS 함수는 특정 날짜에 지정한 개월 수 이후 날짜 데이터를 반환하는 함수.

다음과 같이 날짜형 데이터 그리고 더할 개월 수를 정수로 지정하여 사용

​

*ADD_MONTHS([날짜 데이터(필수)], [더할 개월 수(정수)(필수)])

​

ex) SYSDATE와 ADD_MONTHS 함수로 3개월 후 날짜 구해보자

SELECT SYSDATE,ADD_MONTHS(SYSDATE,3) FROM DUAL;

ex2)10주년이 되는 사원들 데이터 추출

​

SELECT EMPNO,ENAME,HIREDATE,

ADD_MONTHS(HIREDATE,120) AS WORK10YEAR FROM EMP;

​

​

​

ex3) 입사 32년 미만인 사원 데이터출력 해보기

SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE ADD_MONTHS(HIREDATE,384) > SYSDATE;

​

ex4)HIREDATE와 SYSDATE의 사이의 개월 수를 MONTHS_BETWEEN 함수로 출력 (소수점 0번쨰자른것도포함)

​

SELECT EMPNO,ENAME,HIREDATE,SYSDATE, MONTHS_BETWEEN(SYSDATE,HIREDATE) AS MONTH

,TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)) AS MONTHTRUNC FROM EMP;

​

​

돌아오는 요일,달의 마지막 날짜를 구하는 NEXT_DAY, LAST_DAY 함수

​

*NEXT_DAY([날짜 데이터(필수)],[요일 문자(필수)])

​

ex) 두개의 함수를 사용해보자

SELECT SYSDATE,

NEXT_DAY(SYSDATE,'월요일'),

LAST_DAY(SYSDATE)

FROM DUAL;

​

​

날짜 반올림,버림 하는 ROUND,TRUNC함수

숫자 데이터의 반올림,버림 처리에 사용한 ROUND,TRUNC 함수는 날짜 데이터를 입력데이터로 사용할수

있다. 소수점 위치 정보를 입력하지 않고 반올림,버림의 기주니될 포맷값 지정

​

입력 데이터 종류 사용방식

*숫자데이터 ROUND([숫자(필수)],[반올림 위치])

TRUNC([숫자(필수)],[버림 위치])

​

*날짜데이터 ROUND([날짜데이터(필수)],[반올림 기준 포멧])

TRUNC([날짜데이터(필수)],[버림 기준 포맷])

​

​

​

​

​

NULL 처리함수

​

NVL 함수의 기본 사용법

​

*NVL([NULL인지 여부를 검사할 데이터 또는 열(필수)],[앞의 데이터가 NULL일 경우 반환할 데이터(필수))

​

ex)

NVL 함수를 사용해 출력해보자

​

SELECT EMPNO,ENAME,SAL,COMM,SAL+COMM,

NVL(COMM,0),

SAL+NVL(COMM,0)

FROM EMP;

​

​

​

NVL2 함수의 기본 사용법

NVL2 함수는 NVL과 비슷하지만 NULL이 아닐때 반환할 데이터를 추가로 지정가능

​

ex) COMM 값이 있으면 O 아니면 X

​

SELECT EMPNO,ENAME,COMM,

NVL2(COMM,'O','X'),

NVL2(COMM,SAL*12+COMM,SAL*12) AS ANSAL

FROM EMP;

​

​

​

CASE 문

​

CASE [검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택)]

WHEN[조건1] THEN [조건 1의 결과 값이 TRUE 일때 반환할 결과]

WHEN[조건2] THEN [조건 2의 결과 값이 TRUE 일때 반환할 결과]

WHEN[조건3] THEN [조건 3의 결과 값이 TRUE 일때 반환할 결과]

ELSE [위 조건1~조건n과 일치하는 경우가 없을 때 반환할 결과]

END

​

​

ex)각직업마다 추가 봉급주고 나머지 ELSE 는 1.03배 해주기

​

SELECT EMPNO,ENAME,JOB,SAL

, CASE JOB

WHEN 'MANAGER' THEN SAL*1.1

WHEN 'SALESMAN' THEN SAL*1.2

WHEN 'ANALYST' THEN SAL

ELSE SAL*1.03

END AS UPSAL

FROM EMP;

​

​

​

TIP) 기준 데이터 없이도 가능

EX)

SELECT EMPNO,ENAME,COMM

CASE

WHEN COMM IS NULL THEN '해당사항없음'

WHEN VCOMM = 0 THEN '수당없음'

END AS COMM_TEXT

FROM EMP;