1. SUM, AVG, MAX, MIN, COUNT 그룹함수 사용 WHERE, GROUP BY, HAVING 사용



1
2
3
4
5
6
7
8
9
10
11
SELECT DEPTNO
     , SUM(SAL)
     , AVG(SAL)
     , MAX(SAL)
     , MIN(SAL)
     , COUNT(SAL)
  FROM EMP
 WHERE JOB='MANAGER'
 GROUP BY DEPTNO
 HAVING DEPTNO >= 10
 ORDER BY DEPTNO
cs

출력 결과




2. ROUND 반올림, TRUNC 값짜름



1
2
3
4
5
6
7
8
9
-- ROUND 반올림, TRUNC 짜른값  
SELECT ROUND(45.62,0) -- 45
     , ROUND(45.62,1) -- 45.6
     , ROUND(45.62,2) -- 45.62
     , ROUND(45.62,-1) -- 50
     , TRUNC(45.62,0) -- 45
     , TRUNC(45.62,1) -- 45.6
     , TRUNC(45.62,2) -- 45.2
  FROM DUAL
cs


출력 결과



3. 다중행 서브 쿼리( IN / ALL / ANY / SOME / EXISTS )



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--IN : 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 하나라도 일치하면 참, ‘=‘비교만 가능
SELECT *
  FROM EMP
 WHERE SAL IN(800, 1600) -- 
;
 
-- ALL : 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 모든값이 일치하면 참,
SELECT *
  FROM EMP
 WHERE SAL < ALL(2000, 2500) -- WHERE SAL < 2000 AND SAL < 2500
;
 
--ANY, SOME : 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 하나라도 일치하면 참, ‘=‘비교만 가능
SELECT *
  FROM EMP
 WHERE SAL < ANY(2000, 2500) -- WHERE SAL < 2000 OR SAL < 2500
;
  
SELECT *
  FROM EMP
 WHERE SAL < SOME(2000, 2500) -- WHERE SAL < 2000 OR SAL < 2500
;
cs


IN, EXISTS


값이 안에 존재할때 IN

값이 안에 존재할때 EXSITS


두 표현을 사용할 수 있다.


아래 식은 같은 결과 값을


IN과 EXISTS를 각각 따로 사용해 작성한 쿼리이다.



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 --IN
SELECT *
  FROM EMP
 WHERE DEPTNO IN(10,20)
;
 
 --EXISTS
SELECT *
  FROM EMP
 WHERE EXISTS
       (
        SELECT DEPTNO
          FROM DEPT
         WHERE DEPTNO IN(10,20)
       )
;
cs


'전체 > Oracle' 카테고리의 다른 글

ORACLE CREATE TABLE, ALTER TABLE, DROP TABLE, RENAME, DELETE, TRUNCATE, DROP  (0) 2017.06.15
클러스터 테이블, 언클러스터 테이블  (0) 2017.06.15
ORACLE 날짜 함수(MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, TO_DATE) 활용, DECODE, CASE 문 사용  (0) 2017.06.12
ORACLE DUAL, 문자처리 함수(UPPER, LOWER, INITCAP, INSTR, SUBSTR, LPAD, RPAD, LTRIM, RTRIM) 정리  (0) 2017.06.12
ORACLE ESCAPE, NVL, NVL2, ORDER BY, DISTINCT 정리  (0) 2017.06.12


1. MONTHS_BETWEEN


달수와 달수 사이를 구한다.



1
2
3
4
5
6
7
-- MONTHS_BETWEEN
SELECT MONTHS_BETWEEN
       (
       TO_DATE('2017-07-01','YYYY-MM-DD')
     , TO_DATE('2017-06-01','YYYY-MM-DD')
       ) AS MONTHS_BETWEEN
  FROM DUAL
cs

출력 결과




2. ADD_MONTHS


달수를 더한다.



1
2
3
-- ADD_MONTHS
SELECT ADD_MONTHS(TO_DATE('2017-07-01','YYYY-MM-DD'),1) AS ADD_MONTHS
  FROM DUAL
cs


출력 결과




3. NEXT_DAY


기준날짜에서 요일을 찾아 몇일인지 반환한다.



1
2
3
-- NEXT_DAY
SELECT NEXT_DAY(TO_DATE('2017-06-05','YYYY-MM-DD'),'FRIDAY') 
  FROM DUAL
cs


출력 결과




4. LAST_DAY


기준날짜에서 마지막 날을 출력한다.



1
2
3
-- LAST_DAY
SELECT LAST_DAY(TO_DATE('2017-06-01','YYYY-MM-DD'))
  FROM DUAL
cs


출력 결과




5. TO_DATE


DATE 형식으로 출력한다.



1
2
3
4
5
6
7
8
9
10
11
12
-- TO_DATE1
SELECT TO_DATE('2017-06-01','YYYY-MM-DD') MDATE1
     , TO_DATE('2017/06/01','YYYY/MM/DD') MDATE2
     , TO_DATE('20170601','YYYY-MM-DD') MDATE3
     , TO_DATE('20170601','YYYYMMDD') MDATE4
     , TO_DATE('20170601112030','YYYYMMDDHH12MISS') MDATE5
     , TO_DATE('20170601232030','YYYYMMDDHH24MISS') MDATE6
     , TO_DATE('2017-06-01-11-20-30','YYYY-MM-DD-HH12-MI-SS') MDATE7
     , TO_DATE('2017-06-01-23-20-30','YYYY-MM-DD-HH24-MI-SS') MDATE8
     , TO_DATE('2017/06/01/11/20/30','YYYY/MM/DD/HH12/MI/SS') MDATE9
     , TO_DATE('2017/06/01/23/20/30','YYYY/MM/DD/HH24/MI/SS') MDATE10
  FROM DUAL
cs

출력 결과



1
2
3
4
5
6
7
8
9
10
11
12
13
-- TO_DATE2
SELECT TO_DATE(SYSDATE,'YYYY-MM-DD') MDATE1
     , SYSDATE MDATE2
     , TO_CHAR(SYSDATE) MDATE3
     , TO_CHAR(SYSDATE,'YYYY-MM-DD') MDATE4
     , TO_CHAR(SYSDATE,'YYYY/MM/DD') MDATE5
     , TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') MDATE6
     , TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD'),'YYYY/MM/DD') MDATE7
     , TO_CHAR(SYSDATE,'YYYY-MM') MDATE8
     , TO_CHAR(SYSDATE,'YYYY/MM') MDATE9
     , TO_CHAR(SYSDATE,'MM') MDATE10
     , TO_CHAR(SYSDATE,'DD') MDATE11
  FROM DUAL
cs


출력 결과




6. DECODE


DECODE(VALUE, IF1, THEN1, IF2, THEN2)


DECODE 함수는 조건에 따라 데이터를 다른 값이나 컬럼값으로 추출 할 수 있다.

DECODE(VALUE, IF1, THEN1, IF2, THEN2...) 형태로 사용 할 수 있다.

VALUE 값이 IF1일 경우에 THEN1 값을 반환하고, VALUE 값이 IF2일 경우에는 THEN2 값을 반환한다.

DECODE 함수 안에 DECODE함수를 중첩으로 사용 할 수 있다.



1
2
3
4
SELECT DEPTNO
     , DECODE(DEPTNO, 10, 'CLERK',20, 'MANAGER',30, 'SALESMAN') AS DECODE
     , DECODE(DEPTNO, 10, SAL)
  FROM EMP;
cs


출력 결과




7. CASE


CASE 

 WHEN 

 THEN 

 ELSE 

END 컬럼명


CASE 함수는 DECODE함수가 제공하지 못하는 비교연산의 단점을 해결할 수 있는 함수이다.

DECODE함수에서 비교연산을 수행하기 위해서는 GREATEST, LEAST등의 함수를 사용해야 하지만, CASE함수에서는 조건 연산자를 모두 사용 할 수 있다.

CASE함수는 IF.. THEN .. ELSE 구문과 비슷 하다. WHEN절 다음에 여러 조건이 올 수 있다.



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT JOB
     , CASE
        WHEN JOB='CLERK'
        THEN 'MY_CLERK'
       END JOB1
     , SAL
     , CASE 
        WHEN SAL<1000
        THEN SAL+200
       END SAL1
     , HIREDATE
     , CASE
        WHEN HIREDATE<TO_DATE('1981-03-01','YYYY-MM-DD')
        THEN ADD_MONTHS(HIREDATE,1)
       END HIREDATE1
  FROM EMP
cs


출력 결과



'전체 > Oracle' 카테고리의 다른 글

클러스터 테이블, 언클러스터 테이블  (0) 2017.06.15
ORACLE SUM, AVG, MAX, MIN, COUNT, GROUP BY, HAVING, ROUND, TRUNC, IN / ALL / ANY / SOME / EXISTS  (0) 2017.06.13
ORACLE DUAL, 문자처리 함수(UPPER, LOWER, INITCAP, INSTR, SUBSTR, LPAD, RPAD, LTRIM, RTRIM) 정리  (0) 2017.06.12
ORACLE ESCAPE, NVL, NVL2, ORDER BY, DISTINCT 정리  (0) 2017.06.12
ORACLE 산술, 비교, 논리, 기타 연산자 정리 AND,OR,NOT,BETWEEN,IN,LIKE  (0) 2017.06.12


1. DUAL


듀얼 테이블(DUAL table)은 모든 오라클 데이터베이스 설치본에 기본으로 존재하는, 한 열로 이루어진 특별한 테이블이다. 

SYSDATE, USER와 같은 의사컬럼 선택에 사용하는 것이 적절하다. 

이 테이블은 'X' 값을 포함하는 DUMMY(더미)라 불리는 하나의 VARCHAR2(1) 열이 있다.

일반적으로 DUAL TABLE의 Owner는 SYS이지만 모든 사용자가 사용할 수 있다. 그리고 SELECT 문장에서 조회 시 오직 하나의 Row, 하나의 Column에 'X'만 출력된다.


DUAL 예제



1
2
3
4
5
6
7
8
SELECT SYSDATE 
  FROM DUAL;
 
SELECT 10*20 
  FROM DUAL;
 
SELECT * 
  FROM DUAL;
cs


2. 문자처리 함수


 함수

 설명 

 UPPER 

 대문자로 변환시킨다.

 LOWER

 소문자로 변환시킨다. 

 INITCAP

 맨 앞 이니셜만 대문자로 변환시켜준다. 

 LENGTH

 문자열의 길이를 알려준다. 

 INSTR

 특정문자가 출현하는 위치를 알려준다. 

 SUBSTR

 문자의 일부분을 추출한다.

 LPAD

 오른쪽 정렬 후 왼쪽에 생긴 빈 공백에 특정 문자를 채운다. 

 RPAD

 왼쪽 정렬 후 오른쪽에 생긴 빈 공백에 특정 문자를 채운다. 

 LTRIM

 왼쪽에서 특정 문자를 삭제한다. 

 RTRIM

 오른쪽에서 특정 문자를 삭제한다.


문자처리 함수 예제



1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT EMPNO
     , ENAME 
     , LOWER(ENAME) AS LOWER -- 소문자 출력
     , UPPER(LOWER(ENAME)) AS UPPER -- 대문자 출력
     , INITCAP(ENAME) AS INITCAP -- 첫 글자 이니셜만 대문자 출력
     , LENGTH(ENAME) AS LENGTH -- 문자열 길이 출력
     , INSTR(ENAME,'E') AS INSTR -- 문자열 E의 위치 값 출력 
     , SUBSTR(ENAME,1,3) AS SUBSTR -- 문자의 1번째부터 3번째까지 문자열 출력
     , LPAD(ENAME,6,'@') AS LPAD -- 오른쪽정렬 후 왼쪽 6자리까지 @로 채움
     , RPAD(ENAME,6,'@') AS RPAD-- 왼쪽정렬 후 오른쪽 6자리까지 @로 채움
     , LTRIM(ENAME) AS LTRIM -- 인자가 없을경우 공백제거
     , LTRIM(ENAME,'S') AS LTRIM -- 왼쪽에서부터  S로 시작하는 문자 자름
     , RTRIM(ENAME,'H') AS RTRIM -- 오른쪽에서부터 H로 시작하는 문자 자름
  FROM EMP
cs


출력 결과




'전체 > Oracle' 카테고리의 다른 글

ORACLE SUM, AVG, MAX, MIN, COUNT, GROUP BY, HAVING, ROUND, TRUNC, IN / ALL / ANY / SOME / EXISTS  (0) 2017.06.13
ORACLE 날짜 함수(MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, TO_DATE) 활용, DECODE, CASE 문 사용  (0) 2017.06.12
ORACLE ESCAPE, NVL, NVL2, ORDER BY, DISTINCT 정리  (0) 2017.06.12
ORACLE 산술, 비교, 논리, 기타 연산자 정리 AND,OR,NOT,BETWEEN,IN,LIKE  (0) 2017.06.12
ORACLE INDEX  (0) 2017.06.12


LIKE/%/ESCAPE/IS NULL/IS NOT NULL/ORDER BY/ASC/DISTINCT


NULL인 컬럼을 추출하는 연산자와 ORDER BY 절을 이용하여 특정 컬럼에 대해 정렬과 DISTINCT/별칭/연결 연산자



1. ESCAPE


ESCAPE는 %(퍼센트)나 _(언더바)가 같이 특수문자를 조건 안에 넣기 위해 사용한다.


만약 LIKE 연산자로 '%SAMPLE', 'SAM%PLE', 'SAMPLE%' 라는 단어를 찾을때 %나 _인 문자를 어떻게 찾을것인가?


SELECT *
  FROM EMP
 WHERE JOB LIKE 'S%'
  
SELECT *
  FROM EMP
 WHERE JOB LIKE '%S'
    
위와 같은 방식으로 찾기 어렵다는 한계 발견하였다.


다음과 같이 ESCAPE를 사용해 해당하는 단어를 추출할수 있다.



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--테스트 위해 샘플 데이터 INSERT
 
SELECT * FROM DEPT
INSERT INTO DEPT VALUES(50,'MY','NEW_YORK')
INSERT INTO DEPT VALUES(60,'MY2','NEW%YORK')
 
 
--'_' 가 있는 문자 검색 -1
SELECT LOC
  FROM DEPT
 WHERE LOC LIKE '%#_%' ESCAPE '#'
 
 
--'_' 가 있는 문자 검색 -2
SELECT LOC
  FROM DEPT
 WHERE LOC LIKE '%N%@_%' ESCAPE '@' 
 
--'%' 가 있는 문자 검색 -1
     
SELECT LOC
  FROM DEPT
 WHERE LOC LIKE '%@%%' ESCAPE '@'
cs


2. IS NULL / IS NOT NULL


NVL


사용 방법 : NVL(expr, expr1)


NVL 함수는 NULL 값을 다른 값으로 바꿀 때 사용하며, 모든 데이터 타입에 적용이 가능하다.



1
2
3
4
5
6
SELECT EMPNO
     , ENAME
     , JOB
     , NVL(MGR,0) AS MGR --MGR이 없는 경우(NULL)
     , NVL(COMM,0) AS COMM --COMM이 없는 경우(NULL)
  FROM EMP
cs


NVL2


사용 방법 : NVL2(expr, expr1, expr2)


NVL2라는 함수는 NVL함수의 DECODE 함수의 개념을 합쳤다고 생각하면 쉽다.

expr의 값이 NULL이 아닐 경우에는 expr1의 값을 반환 하고, NULL일 경우에는 expr2의 값을 반환 한다.



1
2
3
4
5
6
SELECT EMPNO
     , NVL2(ENAME,ENAME,0) AS ENAME 
     , NVL2(JOB,JOB,0) AS JOB
     , NVL2(MGR,MGR,0) AS MGR
     , NVL2(COMM,COMM,0) AS COMM
  FROM EMP
cs


3. ORDER BY


컬럼 값을 기준으로 정렬할 때 사용한다.



1
2
3
4
5
6
7
SELECT *
  FROM EMP
 ORDER BY SAL -- 오름차순 정렬
  
SELECT *
  FROM EMP
 ORDER BY SAL DESC -- 내림차순 정렬
cs


4. DISTINCT


조회하려는 칼럼의 중복되는 값은 제거 후 보여준다.

DISTINCT 키워드 뒤에 2개 이상의 칼럼을 정의하면 하나의 RECORD로 인식하여 DISTINCT 키워드 이후에 오는 칼럼에 대해 모두 중복 제거한다.



1
2
SELECT DISTINCT JOB
  FROM EMP
cs

출력 결과



'전체 > Oracle' 카테고리의 다른 글

ORACLE 날짜 함수(MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, TO_DATE) 활용, DECODE, CASE 문 사용  (0) 2017.06.12
ORACLE DUAL, 문자처리 함수(UPPER, LOWER, INITCAP, INSTR, SUBSTR, LPAD, RPAD, LTRIM, RTRIM) 정리  (0) 2017.06.12
ORACLE 산술, 비교, 논리, 기타 연산자 정리 AND,OR,NOT,BETWEEN,IN,LIKE  (0) 2017.06.12
ORACLE INDEX  (0) 2017.06.12
ORACLE VIEW  (0) 2017.06.12


ORACLE 에서는 여러 연산자를 사용 할 수 있다. 산술, 비교, 논리, 기타 연산자를 정리해보았다.


1. 산술 연산자


+ - * / 덧셈, 뺄셈, 곱셈, 나눗셈


2. 비교 연산자


= > < >= <= 비교


<> != ^= 같지 않다


3. 논리 연산자



3-1. AND

두 가지 조건을 모두 만족해야만 검색할 수 있다.



1
2
3
4
5
-- AND 
SELECT * 
  FROM EMP
 WHERE JOB = 'SALESMAN' 
   AND SAL >=500
cs



3-2. OR

두 가지 조건중 하나만 만족해도 검색할 수 있다.



1
2
3
4
5
-- OR
SELECT *
  FROM EMP
 WHERE JOB = 'SALESMAN'
    OR JOB = 'MANAGER'
cs



3-3. NOT

조건을 만족하지 않아야 검색할 수 있다.



1
2
3
4
5
-- NOT
SELECT *
  FROM EMP
 WHERE NOT JOB = 'SALESMAN'
   AND NOT JOB = 'MANAGER'
cs


4. 기타 연산자



4-1. BETWEEN 범위를 지정하여 조건을 걸때 사용 한다.



1
2
3
4
-- BETWEEN
SELECT *
  FROM EMP
 WHERE SAL BETWEEN 1000 AND 2000
cs



4-2. IN 범위에 포함되는 것 


1
2
3
4
 -- IN 포함되는 것
SELECT *
  FROM EMP
 WHERE JOB IN('SALESMAN','MANAGER') 
cs


1
2
3
4
 -- NOT IN 포함되지 않는 것
SELECT *
  FROM EMP
 WHERE JOB NOT IN('SALESMAN','MANAGER') 
cs



4-3. LIKE ~와 같은 조건 사용



1
2
3
4
5
6
7
8
9
10
11
12
13
14
 -- LIKE - 1 시작이 S로 나타날때
SELECT *
  FROM EMP
 WHERE JOB LIKE 'S%'
 
 -- LIKE - 2 끝이 S로 끝날때
SELECT *
  FROM EMP
 WHERE JOB LIKE '%S'
   
 -- LIKE - 3 S가 시작,끝 모두 포함
SELECT *
  FROM EMP
 WHERE JOB LIKE '%S%'
cs


'전체 > Oracle' 카테고리의 다른 글

ORACLE DUAL, 문자처리 함수(UPPER, LOWER, INITCAP, INSTR, SUBSTR, LPAD, RPAD, LTRIM, RTRIM) 정리  (0) 2017.06.12
ORACLE ESCAPE, NVL, NVL2, ORDER BY, DISTINCT 정리  (0) 2017.06.12
ORACLE INDEX  (0) 2017.06.12
ORACLE VIEW  (0) 2017.06.12
ORACLE PIVOT 사용 달력 만들기, 분석함수 사용 달력 만들기  (0) 2017.06.11


인덱스란?


SQL명령문의 처리속도를 향상시키기 위해 컬럼에 대해서 생성하는 오라클 객체


인덱스 장점 및 왜 사용하는가?


인덱스 사용하면 검색속도 높일수 있음.

검색결과가 늦게 나올 경우 인덱스를 재생성해줘야 함

검색속도가 빨라짐

시스템 부하 줄여 시스템 전체 성능 향상 시킴

색인에서 해당단어(테이블)를 찾아 그 페이지로 이동


인덱스의 단점


인덱스를 생성하는데 시간이 걸림

인덱스를 위한 추가적인 공간이 필요함

데이터 변경 작업(INSERT, UPDATE, DELETE) 자주 일어날 경우 성능저하됨


인덱스의 특징


인덱스는 기본키나 유일키와 같은 제약조건을 지정하면 따로 인덱스를 생성하지 않더라도 자동으로 생성

기본키나 유일키는 데이터 무결성을 확인하기 위해서 수시로 데이터를 검색하기 때문

빠른 조회를 목적으로 오라클에서는 내부적으로 해당컬럼에 인덱스를 자동으로 생성


인덱스를 사용해야 하는 경우


테이블에 행의 수가 많을 때

WHERE문에 해당 컬럼이 많이 사용될 때

JOIN에 자주사용되는 컬럼이나 NULL을 포함하는 컬럼이 많은 경우


인덱스를 사용하지 말아야 하는 경우


테이블에 행의 수가 적을 때

WHERE문에 해당컬럼이 자주 사용되지 않을 때

테이블에 DML작업이 많은 경우


인덱스를 재생성하는 문법


ALTER INDEX EMP REBUILD



인덱스 생성하기 및 제거하기



1. 인덱스 없이 실행하기



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- EMP08 에는 INDEX 존재하지 않음, EMP로 부터 제약조건이 복사되지 않기 때문에
CREATE TABLE EMP08 
    AS SELECT * FROM EMP; 
 
SELECT TABLE_NAME
     , INDEX_NAME
     , COLUMN_NAME 
  FROM USER_IND_COLUMNS 
 WHERE TABLE_NAME IN('EMP', 'EMP08'); 
 
INSERT INTO EMP08 SELECT * FROM EMP08; 
INSERT INTO EMP08 (EMPNO, ENAME) VALUES(1111, 'MJS'); 
 
-- INDEX 없이 SELECT 실행
SELECT DISTINCT EMPNO
     , ENAME 
  FROM EMP08 WHERE ENAME='MJS'; 
cs



2. 인덱스 만들어 실행하기



1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- EMP08에 INDEX 생성
CREATE INDEX IDX_EMP08_ENAME 
    ON EMP08(ENAME); 
    
SELECT TABLE_NAME
     , INDEX_NAME 
  FROM USER_INDEXES 
 WHERE TABLE_NAME IN('EMP08'); 
 
-- INDEX 유 SELECT 실행
SELECT DISTINCT EMPNO
     , ENAME 
  FROM EMP08 
 WHERE ENAME='MJS'; 
cs



3. 인덱스 제거하기



1
2
3
4
5
6
7
8
-- DROP INDEX 사용
DROP INDEX IDX_EMP08_ENAME; 
 
 -- 인덱스 제거 확인
SELECT TABLE_NAME
     , INDEX_NAME 
  FROM USER_INDEXES 
 WHERE TABLE_NAME IN('EMP08'); 
cs


'전체 > Oracle' 카테고리의 다른 글

ORACLE ESCAPE, NVL, NVL2, ORDER BY, DISTINCT 정리  (0) 2017.06.12
ORACLE 산술, 비교, 논리, 기타 연산자 정리 AND,OR,NOT,BETWEEN,IN,LIKE  (0) 2017.06.12
ORACLE VIEW  (0) 2017.06.12
ORACLE PIVOT 사용 달력 만들기, 분석함수 사용 달력 만들기  (0) 2017.06.11
ORACLE PIVOT과 UNPIVOT 정리  (2) 2017.06.09


뷰의 정의


물리적인 테이블을 근거한 논리적인 가상 테이블


뷰를 왜 쓰는가? 


특정 중요 데이터를 뽑으려고 할때 기존에 있는 테이블을 복사 하는게 아니라 가상테이블 뷰 VIEW를 이용하면 됨


테이블은 데이터가 있지만 뷰는 데이터가 없음(SQL만 저장)


사용자가 해당 VIEW에 접근하면, 그때 VIEW에 들어 있던 SQL이 수행되어 결과를 가져오는 것.


CREATE OR REPLACE VIEW 같은 이름의 VIEW가 있을 경우 삭제 후 다시 생성
FORCE 기본 테이블의 존재 여부에 상관없이 VIEW 생성
WITH CHECK OPTION 주어진 제약 조건에 맞는 데이터만 입력 및 수정
FOREIGN KEY SELECT만 가능한 읽기 전용 뷰를 생성


뷰의 장점


복잡하고 긴 쿼리문을 뷰로 정의하면 접근 단순화 가능


뷰는 물리적으로 데이터를 저장하지 않고 CREATE VIEW명령어로 뷰를 정의할때 AS절 다음에 기술한 쿼리 문장 자체를 저장함


단순 뷰 , 복합 뷰


 단순 뷰 

 복합 뷰 

 하나의 테이블로 생성

 여러개의 테이블로 생성

 그룹함수 사용이 불가능

 그룹 함수의 사용이 가능

 DISTINCT 사용 불가능

 DISTINCT 사용이 가능

 DML(INSERT,UPDATE,DELETE) 사용이 가능

 DML사용이 불가능



단순 뷰에서 DML명령어 사용이 불가능한 경우 : 뷰 정의에 포함되지 않은 컬럼 중에 기본 테이블의 칼럼이 NOT NULL 제약조건이 지정되어 있는 경우
 INSERT문 사용 불가능

-> 뷰에 대한 INSERT문은 기본 테이블에 NULL값을 입력하는 형태가 되기 때문

단순 뷰에서 DISTINCT를 포함한 경우에도 DML 명령 사용이 불가능, 그룹함수나 GROUP BY 절을 포함한 경우 DML명령 사용이 불가능


단순 뷰

예 1) 뷰를 생성할 수 있는 권한을 부여한다.


SQL> CONN system/[사용자 암호]

SQL> GRANT CREATE VIEW TO scott;

SQL> CONN scott/[사용자 암호] 

SQL> CREATE VIEW EMP_VIEW30 AS SELECT EMPNO, ENAME, DEPTNO FROM EMP_COPY WHERE DEPTNO=30; 


예 2) 뷰의 생성을 확인한다.


SQL> SELECT * FROM EMP_VIEW30; 


예 3) USER_VIEWS 데이터 딕셔너리를 확인한다.


SQL> COLUMN VIEW_NAME FORMAT A15

SQL> COLUMN TEXT FORMAT A50

SQL> SELECT VIEW_NAME, TEXT FROM USER_VIEWS; 


예 4) EMP_VIEW30 뷰에 데이터를 추가


SQL> INSERT INTO EMP_VIEW30 VALUES(8000, 'ANGEL', 30);

SQL> SELECT * FROM EMP_VIEW30; 


예 5) EMP_VIEW30 뷰의 기본 테이블인 EMP_COPY 테이블을 확인


SQL> SELECT * FROM EMP_COPY; 


복합 뷰


예 1) 사원 테이블과 부서 테이블을 조인하여 복합 뷰를 생성한다

CREATE VIEW EMP_VIEW_DEPT AS SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO ORDER BY EMPNO DESC; 


예 2) 뷰를 생성한 후, 아래의 명령문을 활용하면, 복잡한 질의를 쉽게 처리 할 수 있다.

SELECT * FROM EMP_VIEW_DEPT; 



'전체 > Oracle' 카테고리의 다른 글

ORACLE 산술, 비교, 논리, 기타 연산자 정리 AND,OR,NOT,BETWEEN,IN,LIKE  (0) 2017.06.12
ORACLE INDEX  (0) 2017.06.12
ORACLE PIVOT 사용 달력 만들기, 분석함수 사용 달력 만들기  (0) 2017.06.11
ORACLE PIVOT과 UNPIVOT 정리  (2) 2017.06.09
ORACLE 정규표현식 SUBSTR, REPLACE, INSTR, DECODE, TRIM 사용하기  (2) 2017.06.05


1. oracle pivot 사용 행을 열로 바꿔 달력 만들기



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 1. PIVOT 사용 행을 열로 바꾸기
SELECT SUN, MON, TUE, WED, THU, FRI, SAT
  FROM (
        SELECT TRUNC(DT + LEVEL-1, 'D') AS WEEK -- 현재 날짜 주의 시작 요일로 잘라버림
             , TO_CHAR(DT + LEVEL-1, 'D') AS DW -- 17-06-01부터 DAY값 일-토 1-7 값 매김
             , LPAD(LEVEL, 2, '0') AS DD -- LEVEL의 1-9까지 0 채워줌
          FROM (
--                SELECT TRUNC(SYSDATE,'MM') AS DT -- 현재 달의 첫번째 날 구함
                SELECT TO_DATE('20170601','YYYYMMDD') AS DT
                  FROM DUAL
               )
       CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(SYSDATE),'DD') -- 현재 날짜의 마지막 날인 YYYY-MM-DD를 구해 DD만 뽑음 30
       )
 PIVOT (MAX(DD) FOR DW IN ('1' AS SUN, '2' AS MON, '3' AS TUE, '4' AS WED, '5' AS THU, '6' AS FRI, '7' AS SAT)) -- 그룹으로 관리할 컬럼 지정 그룹함수 사용가능 MAX(DD), 기준이 되는 컬럼 DW, 기준이 되는 컬럼 구성하기 위한 값 정의 IN()
-- PIVOT (MAX(DD) FOR DW IN (1 SUN, 2 MON, 3 TUE, 4 WED, 5 THU, 6 FRI, 7 SAT)) 
 ORDER BY WEEK;
cs


출력 결과





2. oracle 분석 함수 사용(MAX, MIN...) 행을 열로 바꿔 달력 만들기



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 2. 분석함수 사용 행을 열로 바꾸기
SELECT MAX(DECODE(DW,'1', DD)) 일 
     , MAX(DECODE(DW,'2', DD)) 월
     , MAX(DECODE(DW,'3', DD)) 화
     , MAX(DECODE(DW,'4', DD)) 수
     , MAX(DECODE(DW,'5', DD)) 목
     , MAX(DECODE(DW,'6', DD)) 금
     , MAX(DECODE(DW,'7', DD)) 토
  FROM (
        SELECT TRUNC(DT + LEVEL-1, 'D') AS WEEK 
             , TO_CHAR(DT + LEVEL-1, 'D') AS DW 
             , LPAD(LEVEL, 2, '0') AS DD 
          FROM (
                SELECT TRUNC(SYSDATE,'MM') AS DT 
                  FROM DUAL
               )
       CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(SYSDATE),'DD') 
       )
 GROUP BY WEEK -- 주 단위로 그룹 지음
 ORDER BY WEEK;
cs

출력 결과



다음과 같이 pivot(oracle 11g 사용가능), 분석함수를 이용해 행을 열로 바꿔 달력을 표현할 수 있다.




'전체 > Oracle' 카테고리의 다른 글

ORACLE INDEX  (0) 2017.06.12
ORACLE VIEW  (0) 2017.06.12
ORACLE PIVOT과 UNPIVOT 정리  (2) 2017.06.09
ORACLE 정규표현식 SUBSTR, REPLACE, INSTR, DECODE, TRIM 사용하기  (2) 2017.06.05
ORACLE 계층형 쿼리 만들기  (0) 2017.06.05

+ Recent posts

티스토리툴바