[091001] VIEW

VIEW

SELECT 쿼리의 실행 결과를 화면에 저장한 논리적 가상 테이블
실제 테이블과는 다르게 실질적 데이터를 저장하고 있진 않지만
사용자는 테이블을 사용하는 것과 동일하게 사용 가능

 

• 보안/관리의 편의성 
• 손쉬운 사용성

 

사용자가 테이블을 직접 다루는게 아니라 중간의 관리자가 뷰라는 것을 만들어서 테이블의 일부분만 사용자가 다룰 수 있는 형태로 (전체 테이블이 아닌 일부의 컬럼) 제한. 

사용자가 어떠한 테이블인지 알지 못하게 만든다던가 컬럼의 일부 데이터만 조회할 수 있게 만들어 주는 것.

사용자 입장에서는 그냥 뷰라는 것을 테이블처럼 쓰기 때문에 어떤 테이블인지 알 수 없다.

일반 테이블처럼 조회, 수정, 삭제, 추가가 가능하다.

뷰로 조회할 테이블 데이터도 수정, 추가, 삭제가 가능하나 많은 제약이 있다. <예시 3>

-> 뷰는 원래 VIEW 용도로 사용하려고 만드는 것이기 때문에 권장하지 않는다.

 

조회구문을 만들어 사용자에게 보여준다.  CREATE VIEW (OR REPLACE 는 옵션)

AS SELECT 이하 내용들은 사용자에게 보여줄 가상의 테이블(VIEW)이다. 

<예시1>

CREATE OR REPLACE VIEW V_EMPLOYEE          -- 만들거나 교체해라 V_EMPLOYEE 가 없으면 CREATE 있으면 REPLACE AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_NAME      FROM EMPLOYEE      LEFT JOIN DEPARTMENT ON(DEPT_ID = DEPT_CODE)      LEFT JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE)      LEFT JOIN NATIONAL USING(NATIONAL_CODE); -- 조회 구문 SELECT * FROM V_EMPLOYEE;

 

<예시2>

CREATE OR REPLACE VIEW V_EMP_JOB(사번, 이름, 직급, 성별, 근무년수)  -- 아래 컬럼에 각각 대응하는 별칭 지정 AS SELECT EMP_ID, EMP_NAME, JOB_NAME,                 DECODE(SUBSTR(EMP_NO, 8, 1), 1, ‘남’, 2, ‘여’),                EXTRACT(YEAR FROM SYSDATE) – EXTRACT(YEAR FROM HIRE_DATE)      FROM EMPLOYEE      JOIN JOB USING(JOB_CODE); * 서브쿼리의 SELECT절에 함수가 사용된 경우 반드시 별칭 지정

 

<예시 3>

CREATE OR REPLACE VIEW V_JOB AS SELECT JOB_CODE, JOB_NAME       FROM JOB; INSERT INTO V_JOB VALUES(‘J8’, ‘인턴’);    -- 데이터 삽입 (그러나 만일 기존 테이블에 다른 컬럼이있고 제약조건이 NOT NULL 이라면 데이터가 삽입 되지 않는다.) SELECT * FROM V_JOB; SELECT * FROM JOB;

---

▶ DML명령어로 VIEW 조작이 불가능한 경우

1. 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우

2. 뷰에 포함되지 않은 컬럼 중에 베이스가 되는 컬럼이 NOT NULL 제약조건이 지정된 경우

3. 산술 표현식으로 정의된 경우

   ex) 컬럼 타입이 10자리로 지정 되어있는데 산술 계산한 결과 값이 10자리를 넘어가는 상황

4. 그룹함수나 GROUP BY절을 포함한 경우

5. DISTINCT를 포함한 경우

6. JOIN을 이용해 여러 테이블을 연결한 경우

 

1. 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우 CREATE OR REPLACE VIEW V_JOB2 AS SELECT JOB_CODE      FROM JOB; INSERT INTO V_JOB2 VALUES(‘J8’, ‘인턴’); * 뷰 정의에 포함되지 않은 컬럼을 INSERT/UPDATE하는 경우 에러 발생 * 단 DELETE는 가능 (다른 상황에 따라 안 되는 경우도 생길 수 있다.)

2. 뷰에 포함되지 않은 컬럼 중에 베이스가 되는 테이블 컬럼이 NOT NULL제약조건이 지정된 경우 CREATE OR REPLACE VIEW V_JOB3 AS SELECT JOB_NAME      FROM JOB; INSERT INTO V_JOB3 VALUES(‘인턴’); * 뷰에 포함되지 않은 NOT NULL제약조건이 있는 컬럼이 존재하면 INSERT/UPDATE 시 에러 발생 * 단 DELETE는 가능

3. 산술 표현식으로 정의된 경우 CREATE OR REPLACE VIEW EMP_SAL AS SELECT EMP_ID, EMP_NAME, SALARY,                (SALARY + (SALARY*NVL(BONUS, 0)))*12 연봉      FROM EMPLOYEE; INSERT INTO EMP_SAL VALUES(800, ‘정진훈’, 3000000, 4000000); * 뷰에 산술 계산식이 포함된 경우 INSERT/UPDATE 시 에러 발생 * 단, DELETE는 가능

4. 그룹함수 또는 GROUP BY절을 포함한 경우 CREATE OR REPLACE VIEW V_GROUPDEPT AS SELECT DEPT_CODE, SUM(SALARY) 합계, AVG(SALARY) 평균      FROM EMPLOYEE      GROUP BY DEPT_CODE; INSERT INTO V_GROUPDEPT VALUES(‘D10’, 6000000, 4000000); DELETE FROM V_GROUPDEPT WHERE DEPT_CODE = ‘D1’; * 그룹함수 또는 GROUP BY를 사용한 경우 INSERT/UPDATE/DELETE 시 에러 발생

5. DISTINCT를 포함한 경우 CREATE OR REPLACE VIEW V_DT_EMP AS SELECT DISTINCT JOB_CODE      FROM EMPLOYEE; INSERT INTO V_DT_EMP VALUES(‘J9’); DELETE FROM V_DT_EMP WHERE JOB_CODE = ‘J1’; * DISTINCT를 사용한 경우 INSERT/UPDATE/DELETE 시 에러 발생

6. JOIN을 이용해 여러 테이블을 연결한 경우 CREATE OR REPLACE VIEW V_JOINEMP AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE      FROM EMPLOYEE JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID); INSERT INTO V_JOINEMP VALUES(888, ‘조세오’, ‘인사관리부’); * 뷰 정의 시 JOIN을 사용한 경우 INSERT/UPDATE 시 에러 발생 * 단 DELETE는 가능

---

VIEW 실습

SELECT * FROM EMPLOYEES; SELECT * FROM DEPARTMENTS; -- 뷰 생성 CREATE OR REPLACE VIEW V_EMP_INFO AS     SELECT A.FIRST_NAME AS 이름         , A.LAST_NAME AS 성         , A.PHONE_NUMBER AS 전화번호         , A.SALARY AS 급여액         , B.DEPARTMENT_NAME AS 부서명     FROM EMPLOYEES A JOIN DEPARTMENTS B         ON A.DEPARTMENT_ID = B.DEPARTMENT_ID; -- 뷰 조회 SELECT * FROM V_EMP_INFO; SELECT * FROM V_EMP_INFO WHERE 부서명 = 'Marketing'; -- 생성된 뷰의 정보를 확인할 때 사용 SELECT * FROM USER_VIEWS WHERE VIEW_NAME = 'V_EMP_INFO'; -- cf. 뷰에 대한 권한이 없다고 에러 나오는 경우 접속한 유저의 시스템 권한 확인 SELECT * FROM USER_SYS_PRIVS; -- 뷰 권한 없다면 시스템계정으로 접속해서 해당 유저에게 권한 부여 -- 뷰 옵션 적용 CREATE OR REPLACE VIEW V_EMP_INFO AS     SELECT A.FIRST_NAME AS 이름         , A.LAST_NAME AS 성         , A.PHONE_NUMBER AS 전화번호         , A.SALARY AS 급여액         , B.DEPARTMENT_NAME AS 부서명     FROM EMPLOYEES A JOIN DEPARTMENTS B         ON A.DEPARTMENT_ID = B.DEPARTMENT_ID WITH READ ONLY;  -- 뷰 오로지 조회 용으로 사용, 데이터 삽입이나 업데이트 불가

---

[210930] 연습문제 답안 및 뷰 예제로 사용하기 위해 수정

 

/* 1. Develop1 계정을 하나 새로 생성한다.         - 이 계정의 패스워드는 임의로 생성한다.         - 이 계정은 Oracle DBMS에 접속할 수 있어야 한다.         - 이 계정은 테이블 생성 및 수정, 삭제 권한과 데이터 조회, 추가, 수정, 삭제 권한이 필요하다. */ CREATE USER develp1 IDENTIFIED BY Database1234; GRANT CONNECT, RESOURCE, INSERT ANY TABLE     , UPDATE ANY TABLE, DELETE ANY TABLE     , CREATA VIEW TO develop1; ALTER USER develop1 quota 10M ON USERS; /*     2. 1 번에서 생성한 계정으로 Oracle DBMS 에 접속하여 테이블을 생성하도록 한다.(컬럼에 대한 주석(비고) 반드시 작성)         - 가계부 작성을 위한 테이블을 생성한다.             테이블 명 :              컬럼명    데이터타입    제약조건        비고             ID        NUMBER        PRIMARY KEY     ACCOUNTS 테이블의 행 데이터를 식별하기 위한 값이 저장되는 컬럼이다.             AMOUNT    NUMBER        NOT NULL        입/출 금액을 저장하기 위한 컬럼이다.             INOUT     CHAR(1)       CHECK           입금(I), 출금(O) 을 구분하기 위한 컬럼으로 저장 값은 I, O 로 제한한다.             ACC_DATE  DATE          NOT NULL        입/출 금액이 발생한 일자를 저장 하기 위한 컬럼이다.             ACC_TYPE  CHAR(3)       FOREIGN KEY     입/출 금액의 용도를 별도로 구분하기 위한 컬럼으로 참조 테이블(ACCTYPES)                                                     의 ID 컬럼의 값을 참조한다. */ CREATE TABLE ACCOUNTS (        ID        NUMBER      , AMOUNT    NUMBER      , INOUT     CHAR(1)      , ACC_DATE  DATE      , ACC_TYPE  CHAR(3) ); COMMENT ON COLUMN ACCOUNTS.ID IS 'ACCOUNTS 테이블의 행 데이터를 식별하기 위한 값이 저장되는 컬럼이다.'; COMMENT ON COLUMN ACCOUNTS.AMOUNT IS '입/출 금액을 저장하기 위한 컬럼이다.'; COMMENT ON COLUMN ACCOUNTS.INOUT IS '입금(I), 출금(O) 을 구분하기 위한 컬럼으로 저장 값은 I, O 로 제한한다.'; COMMENT ON COLUMN ACCOUNTS.ACC_DATE IS '입/출 금액이 발생한 일자를 저장 하기 위한 컬럼이다.'; COMMENT ON COLUMN ACCOUNTS.ACC_TYPE IS '입/출 금액의 용도를 별도로 구분하기 위한 컬럼으로 참조 테이블(ACCTYPES)     의 ID 컬럼의 값을 참조한다.'; ALTER TABLE ACCOUNTS ADD CONSTRAINT ACCOUNTS_ID_PK PRIMARY KEY(ID); ALTER TABLE ACCOUNTS MODIFY AMOUNT CONSTRAINT ACCOUNTS_AMOUNT_NN NOT NULL; ALTER TABLE ACCOUNTS ADD CONSTRAINT ACCOUNTS_INOUT_CK CHECK(INOUT IN('I', 'O')); -- ALTER TABLE ACCOUNTS MODIFY INOUT CHECK(INOUT IN(NULL));  제약조건 수정, NULL값으로 CHECK하기도 가능하다! ALTER TABLE ACCOUNTS MODIFY ACC_DATE CONSTRAINT ACCOUNTS_ACC_DATE_NN NOT NULL; ALTER TABLE ACCOUNTS ADD CONSTRAINT ACCOUNTS_ACC_TYPE_FK FOREIGN KEY(ACC_TYPE) REFERENCES ACCTYPES(ID);  --  ㄴ외래키로 쓸 테이블(ACCTYPES) 생성하고 나서야 외래키 설정 가능하다. -- 기본값 설정 -- 뷰 인서트 작업 수월하게 하기 위해 디폴트값 넣어 준다. ALTER TABLE ACCOUNTS MODIFY INOUT DEFAULT('O'); -- CHECK도 조건설정한 값만 허용하고 NULL값은 허용 안하므로 기본값 설정해둔다. ALTER TABLE ACCOUNTS MODIFY ACC_DATE DEFAULT(SYSDATE); /*         - ACCTYPES 테이블을 생성한다.             테이블 명 : ACCTYPES             컬럼명    데이터타입    제약조건        비고             ID        CHAR(3)       PRIMARY KEY     ACCTYPES 테이블의 행 데이터를 식별하기 위한 값이 저장되는 컬럼이다.             TYPE_NAME VARCHAR2(150) NOT NULL        입/출 금액의 용도가 저장되는 컬럼이다. */ CREATE TABLE ACCTYPES (       ID        CHAR(3)     , TYPE_NAME VARCHAR2(150) ); COMMENT ON COLUMN ACCTYPES.ID IS 'ACCTYPES 테이블의 행 데이터를 식별하기 위한 값이 저장되는 컬럼이다.'; COMMENT ON COLUMN ACCTYPES.TYPE_NAME IS '입/출 금액의 용도가 저장되는 컬럼이다.'; ALTER TABLE ACCTYPES ADD CONSTRAINT ACCTYPES_ID_PK PRIMARY KEY(ID); ALTER TABLE ACCTYPES MODIFY TYPE_NAME CONSTRAINT ACCTYPES_TYPE_NAME_NN NOT NULL; /*     3. 2 번에서 생성한 테이블에 초기 데이터를 저장한다.         - ACCTYPES 에는 다음의 데이터를 추가 한다.             ID: I01,    TYPE_NAME: 급여     | ID: O01,    TYPE_NAME: 통신비             ID: O02,    TYPE_NAME: 교통비   | ID: O03,    TYPE_NAME: 주유비             ID: O04,    TYPE_NAME: 식비     | ID: O05,    TYPE_NAME: 여가비             ID: I02,    TYPE_NAME: 복권당첨 | ID: O06,    TYPE_NAME: 가전제품 */ INSERT INTO ACCTYPES VALUES('I01','급여'); INSERT INTO ACCTYPES VALUES('O02','교통비'); INSERT INTO ACCTYPES VALUES('O04','식비'); INSERT INTO ACCTYPES VALUES('I02','복권당첨'); INSERT INTO ACCTYPES VALUES('O01','통신비'); INSERT INTO ACCTYPES VALUES('O03','주유비'); INSERT INTO ACCTYPES VALUES('O05','여가비'); INSERT INTO ACCTYPES VALUES('O06','가전제품'); -- 뷰 생성 후 INSERT문 통해 테이블에 데이터 삽입. CREATE OR REPLACE VIEW V_ACC AS     SELECT ID AS 번호          , AMOUNT AS 지출액  -- NOT NULL 이었으므로 INSERT시 값 꼭 넣어야 함           , ACC_TYPE AS 지출타입 -- 외래키 쓰므로 찾아서 맞춰 넣어줘야 함       FROM ACCOUNTS      WHERE INOUT = 'O'; INSERT INTO V_ACC VALUES(1, 250000, 'O05'); -- 뷰로쓰는 ACCOUNTS 테이블에 INOUT은 CHECK 제약조건 설정되어 있기 때문에                                             -- 원래 이대로 인서트시 널값으로 들어가야 해서 오류가 나야하지만                                              -- 위에서 미리 'O' 디폴트 값을 넣어 주었기 때문에 정상 작동한다. INSERT INTO V_ACC VALUES(2, 85000, 'O02'); INSERT INTO V_ACC VALUES(3, 55000, 'O01'); INSERT INTO V_ACC VALUES(4, 6500, 'O04'); INSERT INTO V_ACC VALUES(5, 6500, 'O04'); -- 뷰 수정 UPDATE V_ACC    SET 지출액 = 7000  WHERE 지출타입 = 'O04'; -- 뷰 데이터 삭제 DELETE FROM V_ACC WHERE 지출타입 = 'O04'; SELECT * FROM V_ACC; -- 뷰2 생성 CREATE OR REPLACE VIEW V_ACC2 AS     SELECT A.ID AS 번호          , A.AMOUNT AS 지출액          , B.TYPE_NAME AS 지출타입       FROM ACCOUNTS A JOIN ACCTYPES B         ON A.ACC_TYPE = B.ID      WHERE A.INOUT = 'O'; -- 조인 실행한 뷰에 대해서는 삽입 불가 에러 (ACCTYPES 테이블 '식비' 문제) -- INSERT INTO V_ACC2 VALUES(4, 6500, '식비'); -- INSERT INTO V_ACC2 VALUES(5, 6500, '식비'); --조인 썼지만 딱히 걸릴 게 없는 지출액(특정) 컬럼은 수정 가능  UPDATE V_ACC2    SET 지출액 = 90000  WHERE 지출타입 = '통신비'; --키가 유지되지 않는 테이블에 매핑되는 열을 수정할 수 없습니다. 에러 --가전제품에 대한것 변경하려고하면 조인이 되어있어서 연계 문제 -- UPDATE V_ACC2 --    SET 지출타입 = '가전제품' --  WHERE 지출타입 = '여가비'; -- 삭제는 가능한 경우에 해당함 DELETE FROM V_ACC2 WHERE 지출타입 = '여가비'; SELECT * FROM V_ACC2; SELECT * FROM ACCOUNTS; SELECT * FROM ACCTYPES;

 

---

▶ VIEW 옵션

 

옵션 작성 위치

1. OR REPLACE 옵션
생성한 뷰가 존재하면 뷰를 갱신함

2. FORCE/NOFORCE 옵션
FORCE옵션은 기본 테이블이 존재하지 않더라도 뷰 생성
NOFORCE 옵션이 기본 값으로 지정되어 있음

 

실제로 테이블이 없다면 뷰를 만들 수가 없어야 하는데 강제로 만들 수 있게 하는 옵션 

오라클에 일단 만들 수는 있다.(아래 구문으로 뷰 조회 가능) 나중에 테이블 만들어서 동작 시키면 되는 것

SELECT * FROM USER_VIEWS WHERE VIEW_NAME = 'V_EMP_INFO';

3. WITH CHECK OPTION 옵션
옵션을 설정한 컬럼의 값을 수정 불가능하게 함

- 조건 컬럼값을 변경하지 못하게 하는 옵션

- 뷰를 정의하는 서브 쿼리문에 WHERE절을 추가하여 기본테이블 중 특정 조건에 만족하는 행만으로 구성된 뷰를 생성할 수 있다.

- 뷰를 생성할 때 WHERE 다음에 WITH CHECK OPTION을 기술하면 그 조건에 의해 기본 테이블에서 정보가 추출되는 것이므로, 조건에 사용된 컬럼값은 뷰를 통해서는 변경이 불가능하게 된다.

- 뷰를 생성할 때 부서번호가 20인 사원정보만 추출했다면, 해당 뷰로 부서번호를 30번으로 업데이트 할 수 없게 한다는 의미이다.

 

인서트할때나 업데이트할때 WHERE절에 설정한 그 범위에 대해서만 급여액을 이천에서 팔천사이로 두었다면 그 사이의 급여가 아니면 업데이트가 안 됨 그걸 체크하기 위한 옵션

뷰를 이용해 사용자한테 테이블의 특정 뷰를 보는 목적에 DML 작업까지 하게 제공은 하는지만 인서트 업데이트하는 특정 컬럼에 대해 추가 수정에 대한 제약을 주겠다는 뜻이다. (볼 수 있는 정보에 맞춰서만 작업 가능하도록 만드는 것, WHERE절로 범위 좁혀놨으면 거기까지만 건드리는 것이 가능)

4. WITH READ ONLY 옵션
뷰에 대해 조회만 가능하고 삽입, 수정, 삭제 등은 불가능하게 함