반응형
DBMS - Oracle Database
VERSION - Oracle Database 11g Release 2
IDE - SQL Developer
실습용 데이터를 입력하지 않고, 오라클 데이터베이스를 설치하면 기본으로 제공되는 테이블과 계정들로 학습한 내용입니다.
※ 문자 데이터를 가공하는 문자 함수
1. 대 · 소문자를 바꿔주는 UPPER, LOWER, INITCAP 함수
| 함수 | 설명 |
| UPPER(문자열) | 괄호 안 문자 데이터를 모두 대문자로 변환하여 반환 |
| LOWER(문자열) | 괄호 안 문자 데이터를 모두 소문자로 변환하여 반환 |
| INITCAP(문자열) | 괄호 안 문자 데이터 중 첫 글자는 대문자로, 나머지 문자를 소문자로 변환 후 반환 |
SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
FROM EMP; 결과 >>
▼ UPPER 함수로 문자열 비교하기(사원 이름에 BLAKE 단어를 포함한 데이터 찾기)
SELECT * FROM EMP
WHERE UPPER(ENAME) LIKE UPPER('%blake%');
2. 문자열 길이를 구하는 LENGTH 함수
사원 이름의 길이가 5 이상인 행 출력하기
SELECT ENAME, LENGTH(ENAME) FROM EMP
WHERE LENGTH(ENAME) >= 5;
3. 문자열 일부를 추출하는 SUBSTR 함수
SELECT JOB,
SUBSTR(JOB, -LENGTH(JOB)),
-- -5자리(CLERK의 -LENGTH(JOB))부터 끝까지 출력
SUBSTR(JOB, -LENGTH(JOB), 2),
-- -5자리(CLERK의 -LENGTH(JOB))부터 두 글자 출력
SUBSTR(JOB, -3)
-- -3자리(CLERK의 -LENGTH(JOB))부터 끝까지 출력
FROM EMP; 결과 >>
4. 문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR 함수
SELECT INSTR('HELLO, ORACLE!', 'L') AS INSTR_1,
-- 시작 위치와 몇 번째 L인지 정해지지 않아 처음부터 검색
INSTR('HELLO, ORACLE!', 'L', 5) AS INSTR_2,
-- 다섯 번째 글자 O부터 L을 찾음
INSTR('HELLO, ORACLE!', 'L', 2, 2) AS INSTR_3
-- 두 번째 글자 E부터 시작해서 두 번째 L을 찾음
FROM DUAL; 결과 >>
5. 특정 문자를 다른 문자로 바꾸는 REPLACE 함수
SELECT '010-1234-5678' AS REPLACE_BEFORE,
REPLACE('010-1234-5678', '-', ' ') AS REPLACE_1,
-- '-' 문자를 한 칸 공백으로 바꾸어 출력
REPLACE('010-1234-5678', '-') AS REPLACE_2
-- 대체할 문자를 지정하지 않아 - 문자가 삭제된 상태로 출력
FROM DUAL;
6. 데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수
Left Padding, Right Padding
SELECT 'Oracle',
LPAD('Oracle', 10, '#') AS LPAD_1,
RPAD('Oracle', 10, '*') AS RPAD_1,
LPAD('Oracle', 10) AS LPAD_2,
RPAD('Oracle', 10) AS RPAD_2,
RPAD('950403-', 14, '*') AS RPAD_JMNO
FROM DUAL; 결과 >>
7. 두 문자열 데이터를 합치는 CONCAT 함수
SELECT CONCAT(EMPNO, ENAME),
CONCAT(EMPNO, CONCAT(' : ', ENAME))
FROM EMP
WHERE ENAME = 'JAMES'; || 연산자는 CONCAT 함수와 유사하게 열이나 문자열을 연결
SELECT EMPNO || ENAME,
EMPNO || ':' || ENAME
FROM EMP
WHERE ENAME = 'JAMES';
8. 특정 문자를 지우는 TRIM, LTRIM, RTRIM
--LTRIM([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택)])
--RTRIM([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택)])
SELECT '[' || TRIM(' _Oracle_ ') || ']' AS TRIM,
'[' || LTRIM(' _Oracle_ ') || ']' AS LTRIM,
'[' || LTRIM('<_Oracle_>', '_<') || ']' AS LTRIM_2,
'[' || RTRIM(' _Oracle_ ') || ']' AS RTRIM,
'[' || RTRIM('<_Oracle_>', '>_') || ']' AS RTRIM_2
FROM DUAL; 결과 >>
※ 숫자 데이터를 연산하고 수치를 조정하는 숫자 함수
1. 특정 위치에서 반올림하는 ROUND 함수
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,
ROUND(1234.5678, -2) AS ROUND_MINUS2
FROM DUAL; 결과 >>
| 1234.5678 | ||||
| 자연수 둘째자리 반올림 1234.5678 |
자연수 첫째자리 반올림 1234.5678 |
소수점 첫째자리 반올림 1234.5678 |
소수점 둘째자리 반올림 1234.5678 |
소수점 셋째자리 반올림 1234.5678 |
| -2 | -1 | 0 | 1 | 2 |
| 1200 | 1230 | 1235 | 1234.6 | 1234.57 |
2. 특정 위치에서 버리는 TRUNC 함수
SELECT TRUNC(1234.5678) AS TRUNC,
TRUNC(1234.5678, 0) AS TRUNC_0,
TRUNC(1234.5678, 1) AS TRUNC_1,
TRUNC(1234.5678, 2) AS TRUNC_2,
TRUNC(1234.5678, -1) AS TRUNC_MINUS1,
TRUNC(1234.5678, -2) AS TRUNC_MINUS2
FROM DUAL;
3. 지정한 숫자와 가까운 정수를 찾는 CEIL, FLOOR 함수
각각 입력된 숫자와 가까운 큰 정수, 작은 정수를 반환하는 함수이다.
SELECT CEIL(3.14),
FLOOR(3.14),
CEIL(-3.14),
FLOOR(-3.14)
FROM DUAL; 결과 >>
4. 숫자를 나눈 나머지 값을 구하는 MOD 함수
SELECT MOD(15, 6),
MOD(10, 2),
MOD(11, 2)
FROM DUAL; ※ 날짜 데이터를 다루는 날짜 함수
1. SYSDATE 함수를 사용하여 날짜 출력하기
SELECT SYSDATE AS NOW,
SYSDATE-1 AS YESTERDAY,
SYSDATE+1 AS TOMORROW
FROM DUAL; 결과 >>
2. 몇 개월 이후 날짜를 구하는 ADD_MONTH 함수
SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 3)
FROM DUAL; 입사 10주년이 되는 사원들 데이터 출력하기
SELECT EMPNO, ENAME, HIREDATE,
ADD_MONTHS(HIREDATE, 120) AS WORK10YEAR
FROM EMP;
3. 두 날짜 간의 개월 수 차이를 구하는 MONTHS_BETWEEN 함수
SELECT EMPNO, ENAME, HIREDATE, SYSDATE,
MONTHS_BETWEEN(SYSDATE, HIREDATE) AS MONTHS1,
TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS MONTHS2
FROM EMP;
4. 돌아오는 요일, 달의 마지막 날짜를 구하는 NEXT_DAT, LAST_DAY 함수
SELECT SYSDATE,
NEXT_DAY(SYSDATE, '월요일'),
LAST_DAY(SYSDATE)
FROM DUAL; 결과 >>
5. 날짜의 반올림, 버림을 하는 ROUND, TRUNC 함수
SELECT SYSDATE,
ROUND(SYSDATE, 'CC') AS FORMAT_CC,
ROUND(SYSDATE, 'YYYY') AS FORMAT_YYYY,
ROUND(SYSDATE, 'Q') AS FORMAT_Q,
ROUND(SYSDATE, 'DDD') AS FORMAT_DDD,
ROUND(SYSDATE, 'HH') AS FORMAT_HH
FROM DUAL; 결과 >>
SELECT SYSDATE,
TRUNC(SYSDATE, 'CC') AS FORMAT_CC,
TRUNC(SYSDATE, 'YYYY') AS FORMAT_YYYY,
TRUNC(SYSDATE, 'Q') AS FORMAT_Q,
TRUNC(SYSDATE, 'DDD') AS FORMAT_DDD,
TRUNC(SYSDATE, 'HH') AS FORMAT_HH
FROM DUAL;결과 >>
※ 자료형을 변환하는 형 변환 함수
| 종류 | 설명 |
| TO_CHAR | 숫자 또는 날짜 데이터를 문자 데이터로 변환 |
| TO_NUMBER | 문자 데이터를 숫자 데이터로 변환 |
| TO_DATE |
1. 날짜, 숫자 데이터를 문자 데이터로 변환하는 TO_CHAR 함수
| 형식 | 설명 |
| CC | 세기 |
| YYYY, RRRR | 연(4자리 숫자) |
| YY, RR | 연(2자리 숫자) |
| MM | 월(2자리 숫자) |
| MON | 월(언어별 월 이름 약자) |
| MONTH | 월(언어별 월 이름 전체) |
| DD | 일(2자리 숫자) |
| DDD | 1년 중 며칠 (1 ~ 366) |
| DY | 요일(언어별 요일 이름 약자) |
| DAY | 요일(언어별 요일 이름 전체) |
| W | 1년 중 몇 번째 주 (1 ~ 53) |
SELECT SYSDATE,
TO_CHAR(SYSDATE, 'MM') AS MM,
TO_CHAR(SYSDATE, 'MON') AS MON,
TO_CHAR(SYSDATE, 'MONTH') AS MONTH,
TO_CHAR(SYSDATE, 'DD') AS DD,
TO_CHAR(SYSDATE, 'DY') AS DY,
TO_CHAR(SYSDATE, 'DAY') AS DAY
FROM DUAL; 결과 >>
| 형식 | 설명 |
| HH24 | 24시간으로 표현한 시간 |
| HH, HH12 | 12시간으로 표현한 시간 |
| MI | 분 |
| SS | 초 |
| AM, PM, A.M, P.M. | 오전, 오후 표시 |
시간 형식 지정하여 출력하기
SELECT SYSDATE,
TO_CHAR(SYSDATE, 'HH24:MI:SS') AS HH24MISS,
TO_CHAR(SYSDATE, 'HH12:MI:SS AM') AS HHMISS_AM,
TO_CHAR(SYSDATE, 'HH:MI:SS P.M.') AS HHMISS_PM
FROM DUAL; 결과 >>
2. 문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수
SELECT 1300 - '1500',
'1300' + 1500
FROM DUAL;
3. 문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수
SELECT TO_DATE('2018-07-14', 'YYYY-MM-DD') AS TODATE1,
TO_DATE('20180714', 'YYYY-MM-DD') AS TODATE2
FROM DUAL; 1981년 6월 1일 이후에 입사한 사원 정보 출력하기
SELECT * FROM EMP
WHERE HIREDATE > TO_DATE('1981/06/01', 'YYYY/MM/DD');
4. NVL 함수
▶ NVL 함수는 첫 번째 입력 데이터가 NULL이 아니면 그 데이터를 그대로 반환하고 NULL이라면 두 번째 입력 데이터에 지정한 값을 반환한다.
▶ NVL2 함수는 NVL 함수와 비슷하지만 데이터가 NULL이 아닐 때 반환할 데이터를 추가로 지정해 줄 수 있다.
SELECT EMPNO, ENAME, COMM,
NVL(COMM, 0),
NVL2(COMM, 'O', 'X'),
NVL2(COMM, SAL*12+COMM, SAL*12) AS ANNSAL
FROM EMP; 결과 >>
※ 상황에 따라 다른 데이터를 반환하는 DECODE 함수와 CASE문
1. DECODE 함수
SELECT EMPNO, ENAME, JOB, SAL,
DECODE(JOB,
'MANAGER' , SAL*1.1,
'SALESMAN', SAL*1.05,
'ANALYST' , SAL,
SAL*1.03) AS UPSAL
FROM EMP;
2. CASE문
SELECT EMPNO, ENAME, JOB, SAL,
CASE JOB
WHEN 'MANAGER' THEN SAL*1.1
WHEN 'SALESMAN' THEN SAL*1.05
WHEN 'ANALYST' THEN SAL
ELSE SAL*1.03
END AS UPSAL
FROM EMP; 결과 >>
Reference
 |
|
반응형
댓글