오라클 뷰(ORACLE VIEW)란?

 오라클 뷰(ORACLE VIEW)란?

 

 

뷰는 하나이상의 테이블 또는 다른 뷰를 기반으로 만들어진 가상의 창(WINDOW) 같은 것으로 뷰를 만드는 SQL문장만 오라클 딕셔너리에 저장되고 SQL문에서 테이블 처럼 사용가능한 가상 테이블 입니다.

뷰 종류

하나의 BASE TABLE을 기준으로 단순히 컬럼만 갖고 와서 사용하는 단순 뷰simple view, 두개 이상의 BASE TABLE로 구성되고 조인, GROUP BY, 컬럼 표현식등을 사용할 수 있는 복합 뷰complex view, FROM 절 아래 서브 쿼리 형태로 SQL문 안에서 사용되는 인라인 뷰inline view, 뷰 자체가 실제 데이터도 가지고 있으면서 그룹함수(MIN, MAX, SUM, AVG, COUNT) 튜닝을 위해 사용되는 구체화 뷰materialized view등이 있습니다.

뷰가 꼭 필요한 이유

1. 뷰의 주요 목적은 데이터를 저장하기 위해 테이블을 만들지 않고도 다른 테이블 및 뷰를 결합하여 새로운 데이터 형식을 만들 수 있습니다. 

2. 뷰는 복잡성을 숨길 수 있는데 여러 테이블을 조인해야 하거나 복잡한 논리 또는 계산이 필요한 쿼리가있는 경우 복잡한 부분을 뷰안으로 숨기고 사용자에게는 뷰 만 보여줍니다.

3. 뷰는 보안 메커니즘으로 사용될 수 있는데, 사용자에게 필요한 데이터만 보여주고 나머지는 숨길 수 있습니다. 뷰는 테이블에서 특정 컬럼 및 행을 선택하여 보여줄 수 있습니다. 이를 통해 사용자가 봐야하는 데이터 만 표시 할 수 있는 것이죠.

4. 잘못설계된  테이블을 리팩터링 해야하는 경우 테이블을 동일한 이름의 뷰로 바꿀 수 있습니다. 뷰는 원래 테이블과 똑같은 스키마를 제공하지만 실제 테이블은 아닙니다. 하지만  테이블을 참조하는 레거시 코드가 손상되지 않으므로 남는 시간에 레거시 코드를 변경할 수 있습니다.

13.1. 뷰(View)란?

뷰view는 하나 또는 그 이상의 테이블이나 뷰를 이용하여 생성되는 가상 테이블로 실제 데이터는 저장하지 않고 뷰를 만드는 SELECT 쿼리문만 오라클 딕셔너리에 저장됩니다. 그래서 뷰는 데이터베이스에 저장된 명명 된 쿼리라고 하며, 뷰에서 데이터를 쿼리 할 때 오라클은이 저장된 쿼리를 사용하여 기본 테이블base tables에서 데이터를 검색합니다.

뷰는 데이터 접근을 제한하는 용도로 사용하는데, 테이블 전체 데이터중 일부칼럼, 일부 레코드만 보여줌으로써 데이터의 보안과 사용 편의성을 제공 합니다. EMP 테이블에서 10번 부서만 보는 뷰를 만들수도 있고 empno, ename만 보는 뷰를 만들수도 있습니다. 

뷰의 기본이 되는 테이블을 BASE TABLE 이라고 하며 뷰를 통한 데이터의 입력, 수정, 삭제 가 가능합니다. 뷰의 기본이 되는 BASE TABLE의 무결성 제약조건은 상속됩니다. 또한 뷰자체를 수정할 수는 없습니다.

뷰 생성을 위해서는 CREATE VIEW 시스템 권한이 있어야 합니다.  

[그림13.1 뷰개요]

[기본형식]

CREATE [OR REPLACE] [FORCE|NO FORCE] VIEW  view_name   [(alias[,alias]...)] AS Subquery [WITH READ ONLY] [WITH CHECK OPTION [CONSTRAINT constraint]]; CREATE [OR REPLACE] [FORCE|NO FORCE] VIEW  view_name   [(alias[,alias]...)] AS Subquery [WITH READ ONLY] [WITH CHECK OPTION [CONSTRAINT constraint_name]] OR REPLACE : 이미 뷰가 존재 한다면 RE-CREATE 하라는 의미. FORCE : BASE TABLE 및 컬럼의 존재유무와 관계없이 뷰 생성 가능. NO FROCE : 기본 값이며, BASE TABLE이 존재해야 뷰 생성 가능. ALIAS : BASE TABLE의 컬럼명과 다르게 뷰 컬럼을 생성할 때 사용. WITH READ ONLY : 읽기 전용 뷰 생성 WITH CHECK OPTION : 뷰의 생성 쿼리가 SELECT 할 수 있는 데이터만 INSERT, UPDATE, DELETE 가능. CONSTRAINT constraint_name : 체크 제약 조건의 이름

CREATE VIEW 문을 사용하여 하나 이상의 테이블 또는 뷰를 기반으로하는 논리 테이블 인 뷰를 정의할 수 있습니다. 

일반 뷰와 비슷하지만 XMLType의 XMLSchema기반 테이블에서 데이터를 표시하는 XMLType 뷰를 만들 수도 있지만 자주 사용하지 않으므로 본서에서는 소개하지는 않습니다.

 

#오라클뷰, #부, #뷰란, #ORACLEVIEW, #VIEW, #​ORACLE, #SQL교육, #SQL강좌

ORACLE SQL UPDATE 명령어

 

ORACLE SQL UPDATE 명령어

테이블의 데이터를 변경하기 위한 명령어 입니다. 

- 자신의 계정에서 만든 테이블 이거나 테이블에 대한 UPDATE 권한이 있어야 합니다. 

- WHERE 조건을 생략하면 테이블의 모든 레코드가 변경되므로 조심해야 합니다.

- WHERE 조건은 컬럼명, 표현식, 상수, 서브쿼리, 비교연산자 등이 올 수 있습니다.

[기본 형식]

UPDATE [table_name | subquery] SET column = value[,column = value] [WHERE 조건]

EMP 테이블에서 이름이 ‘SMITH’인 사원의 급여를 1000으로 변경 하세요.

UPDATE emp        SET sal = 1000 WHERE ename = 'SMITH'

<실행결과>

1 행 이(가) 업데이트되었습니다.

SELECT ename, sal FROM emp  WHERE  ename = 'SMITH';

<실행결과>

	ENAME	SAL
1	SCOTT	1000

수정한 값을 원래대로 되돌립니다.

ROLLBACK

SELECT ename, sal FROM emp  WHERE  ename = 'SMITH';

<실행결과>

	ENAME	SAL
1	SCOTT	800

서브 쿼리를 이용한 UPDATE : EMP 테이블에서 이름이 ‘SMITH’인 사원의 급여를 이름이 ‘FORD’인 사원의 급여로 수정 하세요. 먼저 ‘SMITH’와 ‘FORD’의 급여를 확인 후 UPDATE 구문을 작성하세요.

SELECT ename, sal FROM emp  WHERE  ename IN ('SMITH','FORD');

<실행결과>

	ENAME	SAL
1	SCOTT	800
2	FORD	3000

UPDATE emp        SET sal = (SELECT sal FROM emp                           WHERE ename = 'FORD' ) WHERE  ename = 'SMITH';

<실행결과>

1 행 이(가) 업데이트되었습니다.

SELECT ename, sal FROM emp  WHERE  ename IN ('SMITH','FORD');

<실행결과>

	ENAME	SAL
1	SCOTT	3000
2	FORD	3000

수정한 값을 원래대로 되돌립니다.

ROLLBACK

 

#SQL, #UPDATE, #오라클, #오라클SQL, #오라클교육, #SQL강좌

오라클 조인, SQL 조인, 외부 조인(Outer Join)

 

오라클 조인, SQL 조인, 외부 조인(Outer Join)

일반적인 조인(내부조인)은 조인 조건을 만족시키는 레코드만 출력됩니다. 즉 두 테이블중 한쪽 테이블의 값이 일치하지 않는 다면 해당 레코드는 출력되지 않는데, 이 경우에도 결과로 출력하고 싶은 경우에는 외부조인(Outer Join)을 사용합니다. 외부조인은 LEFT OUTER JOIN, RIGTH OUTER JOIN, FULL OUTER JOIN 3가지 형태가 있습니다. 

사원테이블(EMP), 부서테이블(DEPT)을 조인하여 사원명(ename)과 부서명(dname)을 출력한다고 했을 때 EMP 테이블의 “KING” 사원은 최고관리자로 부서코드가 NULL 값 이므로 emp.deptno = dept.deptno 로 조인을 한다면 “KING” 사원은 은 출력되지 않습니다. 

내부 조인은 조인 조건을 만족하는 행들만 추출되는데, 앞의 Self Join 예문처럼 조인 조건을 만족하지 않는다면 해당 레코드는 출력되지 않습니다(“KING” 사원은 mgr 컬럼이 NULL 이므로 출력되지 않았습니다). Outer Join은 조인에서 한쪽 테이블의 행에 대하여 다른 쪽 테이블에 일치하는 행이 없더라도 다른 쪽 행을 NULL로 하여 행을 반환하게 하는 겁니다. 

오라클은 (+) 기호로 외부조인을 지원하는데 어디에 “(+)”를 하여야 할까요? 사원 쪽이 다 출력 되어야 하는 경우인데, 관리자가 있든지 없든지 사원 리스트의 모든 사원은 나와야 하는 겁니다. 다 나와야 하는 반대쪽인 관리자 쪽에 (+) 표시를 하면 되는데 “KING” 사원이 출력됨을 확인 하세요.

SELECT "사원".empno AS "사원ID"              , "사원".ename AS "사원명"              , nvl("관리자".ename, '관리자없음') AS "관리자명" FROM  emp "사원", emp "관리자" WHERE "사원".mgr = "관리자".empno(+)

<실행결과>

	사원ID	사원명	관리자명
1	7788	SCOTT	JONES
2	7902	FORD	JONES
3	7499	ALLEN	BLAKE
4	7521	WARD	BLAKE
5	7654	MARTIN	BLAKE
6	7844	TURNER	BLAKE
7	7900	JAMES	BLAKE
8	7934	MILLER	CLARK
9	7876	ADAMS	SCOTT
10	7566	JONES	KING
11	7698	BLAKE	KING
12	7782	CLARK	KING
13	7369	SMITH	FORD
14	7839	KING	관리자없음

만약  이러한 기호의 위치 문제가 고민 된다면 표준 ANSI JOIN 형태의 SQL 구문을 작성하면 됩니다. 아래 두 쿼리는 위와 같은 결과를 만듭니다.

SELECT "사원".empno "사원ID"              , "사원".ename "사원명"              , nvl("관리자".ename, '관리자없음') "관리자명" FROM  emp "사원" LEFT OUTER JOIN emp "관리자"  ❶ ON         "사원".mgr = "관리자".empno

 ❶ LEFT OUTER JOIN은 왼쪽 테이블인 “사원”의 조인 레코드는 mgr이 NULL이라도 다 출력하라는 의미 입니다. 그냥 간단히 왼쪽 레코드는 다출력된다고 생각을 하시면 되겠습니다.

SELECT "사원".empno "사원ID"              , "사원".ename "사원명"              , nvl("관리자".ename, '관리자없음') "관리자명" FROM  emp "관리자" RIGHT OUTER JOIN emp "사원" ❶ ON       "사원".mgr = "관리자".empno

❶ RIGHT OUTER JOIN은 오른쪽 테이블인 “사원”의 조인 레코드는 mgr이 NULL이라도 다 출력하라는 의미 입니다. 그냥 간단히 오른쪽 레코드는 다출력된다고 생각을 하시면 되겠습니다.

다른 형태의 예문을 살펴 보겠습니다. 먼저 EMP 테이블 및 DEPT 테이블의 데이터를 확인해 보겠습니다. EMP 테이블에서 “KING”사원은 최고관리자로 부서코드 컬럼이 NULL 이며 40번 부서에 속한 사원은 없습니다.

SELECT empno, ename, deptno FROM emp

<실행결과>

	empno	ename	deptno
1	7369	SMITH	20
2	7499	ALLEN	30
3	7521	WARD	30
4	7566	JONES	20
5	7654	MARTIN	30
6	7698	BLAKE	30
7	7782	CLARK	10
8	7788	SCOTT	20
9	7839	KING
10	7844	TURNER	30
11	7876	ADAMS	20
12	7900	JAMES	30
13	7902	FORD	20
14	7934	MILLER	10

부서 테이블(DEPT)의 데이터 입니다.

SELECT * FROM    dept

<실행결과>

	deptno	dname	loc
1	10	ACCOUNTING	NEW YORK
2	20	RESEARCH	DALLAS
3	30	SALES	CHICAGO
4	40	OPERATIONS	BOSTON

부서별 사원수를 출력 하는 쿼리문 입니다. 사원이 한명도 없는 40번 “OPERATION” 부서는 출력되지 않습니다.

SELECT dname              , COUNT(E.empno)  AS emp_cnt ❶ FROM    emp E, dept D WHERE E.deptno = D.deptno GROUP BY dname ❷

<실행결과>

	dname	empCnt
1	RESEARCH	5
2	SALES	6
3	ACCOUNTING	2

❶ COUNT 함수는 그룹 함수로 SELECT 절에서 dname 처럼 다른 컬럼하고 같이 쓰이면 부서별로 사번을 카운트한다는 의미로 반드시 ❷ GROUP BY dname 형태 구문이 와야 합니다. 만약 SELECT 절에 dname 컬럼이 출현하지 않았다면 전체 사원테이블의 empno를 카운트하는 의미로 ❷ GROUP BY dname은 기술할 필요 없습니다.

40번 “OPERATION” 부서를 외부조인을 이용하여 출력되도록 해보겠습니다. 오라클에서 다 출력되야 하는 반대쪽에 (+) 표시를 하므로 사원쪽(emp)에 (+) 표시를 했습니다.

SELECT dname              , COUNT(E.empno)  AS emp_cnt FROM    emp E, dept D WHERE E.deptno(+) = D.deptno GROUP BY dname

<실행결과>

	dname	empCnt
1	RESEARCH	5
2	SALES	6
3	ACCOUNTING	2
4	OPERATIONS	0

표준 ANSI SQL 구문인 RIGHT OUTER JOIN을 이용하여 다음과 같이 작성할 수 있습니다.

SELECT dname              , COUNT(E.empno)  AS emp_cnt FROM    emp E RIGHT OUTER JOIN dept D ❶  ON         E.deptno = D.deptno ❷ GROUP BY dname

❶ RIGHT OUTER JOIN을 사용하면서 조인조건을 ❷ ON절에 기술 했습니다.

SELECT dname             , COUNT(E.empno)  AS emp_cnt FROM   emp E RIGHT OUTER JOIN dept D ❶ USING (deptno) ❷ GROUP BY dname

EMP, DEPT 테이블에 deptno 라는 이름이 같은 컬럼이 있으므로 ❶ RIGHT OUTER JOIN을 쓰면서 조인 조건을 생략하고  ❷ USING문에 deptno 컬럼을 기술 했습니다.

EMP, DEPT 테이블을 조인하여 사원이름과 부서명을 출력하는데 아래 조건을 만족하도록 작성을 해보겠습니다. 부서가 없는 KING도 출력  부서원이 없는 40번부서도 출력 즉 조인 조건이 맞지 않더라도 부서도 다 출력해야 하고 사원도 다 출력해야 하는 쿼리를 만들어야 하는데요, 이 경우에는 전체 외부조인(FULL OUTER JOIN)을 사용해야 합니다.

SELECT ename              , dname  FROM    emp FULL OUTER JOIN dept ON         emp.deptno = dept.deptno

<실행결과>

	ename	dname
1	SMITH	RESEARCH
2	ALLEN	SALES
3	WARD	SALES
4	JONES	RESEARCH
5	MARTIN	SALES
6	BLAKE	SALES
7	CLARK	ACCOUNTING
8	SCOTT	RESEARCH
9	KING
10	TURNER	SALES
11	ADAMS	RESEARCH
12	JAMES	SALES
13	FORD	RESEARCH
14	MILLER	ACCOUNTING
15		OPERATIONS

오라클에서 (+) 기호를 양쪽에 기술하는 것은 지원하지 않습니다.

SELECT ename              , dname  FROM    emp, dept WHERE emp.deptno(+) = dept.deptno(+)

<실행결과>

ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다

4행, 21열에서 오류 발생

 

#SQL강좌, #SQL교육, #SQL조인, #외부조인, #OuterJoin, #ORACLE교육, #ORACLE강의, #오라클교육, #SQLJoin, #오라클