VIEW(뷰) > 기술자료

• 목록

VIEW(뷰)

 : 유의 - SELECT문을 잘 써야한다~!!

가상의 테이블, 사용법과 생김새가 테이블과 거의 동일.
실제로 테이블이 존재하는것이 아님 (테이블의 거울)

**VIEW의 사용 예
카드회사의 회원테이블이 있다. 그 회원테이블의 컬럼들이 회원이름 회원아이디 회원 주소 카드번호 카드 비밀번호 등등이 있을 것이다. 그 회사에서 신입사원을 뽑았다. 신입사원에게 회원테이블 관리하는일을 시켰는데 회원테이블에는 너무 중요한 정보들이 들어있기때문에 신입사원에게 알려주기엔 무리가 있다. 그래서 신입사원에게 원본 테이블을 보여주는것이 아니라.. 신입사원이 봐도 무리가 없는 정보만 보여주려고 할때 새로운 테이블을 만들어서 보여주게 되면 원본테이블에서 정보가 바뀌면 새로 만든 테이블도 따로 정보를 바꿔줘야하기때문에 테이블의 거울인 뷰를 사용하여 필요한 컬럼만 볼수있다.

**VIEW의 사용목적
이렇듯 뷰를 사용하는 목적은 보안을 위한 것이고 사용이 편하기 때문이다.

**단순 VIEW

Q1. EMP 테이블에서 사원번호, 사원이름, 월급만 보여주는 VIEW의 생성
CREATE VIEW VIEW01
AS
SELECT EMPNO, ENAME, SAL
FROM EMP

SELECT * FROM VIEW01

//필요한 컬럼 추가
ALTER VIEW VIEW01
AS
SELECT EMPNO, ENAME, SAL,DEPTNO //필요한 부분만 수정하는것이 아니라 SELECT문부터 전부 써야함.
FROM EMP

EXEC SP_HELPTEXT VIEW01 // 소스보기 쿼리
EXEC SP_DEPENDS VIEW01  // 의존성여부 검사 , 참조관계를 알아낼때

Q2. 직업이 ‘SALESMAN’인 사원들의 이름, 월급, 직업만 보여주는 VIEW를 작성하라.

Q3. 부서번호가 30번인 사원들의 이름, 월급, 부서번호만 보여주는 VIEW를 작성하라.
CREATE VIEW VIEW03
AS
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO=30

SELECT * FROM VIEW03

Q4. 위에서 작성한 VIEW에 DML작업이 가능한지 알아보자.
    ‘ALLEN’의 월급을 0로 바꾸어 보자.

    이 작업을 할 때 BEGIN TRAN… ROLLBACK TRAN을 하는 것을 잊지 말자!
VIEW는 소스를 전달해주는것~!! 작업은 원본테이블에서 진행되므로 모든 변경작업들은 원본에서 하고 뷰에서는 가능하면 하지 않는 것이 좋다.

BEGIN TRAN

UPDATE VIEW03
SET SAL=0
WHERE ENAME='ALLEN'

SELECT *FROM VIEW03
SELECT *FROM EMP

ROLLBACK

※시스템 테이블 보기 : 함부로 건들이면 안됨~!! 치명적인 결과가 초래할수도....
   SELECT * FROM SYSOBJECTS
                WHERE XTYPE='V'   
   SELECT * FROM SYSCOMMENTS
                WHERE ID=1893581784

Q5. 부서별 최대 월급을 담고 있는 VIEW를 작성하라.

CREATE VIEW VIEW04
AS
SELECT DEPTNO,MAX(SAL) AS MASL  //MAX(SAL)은 컬럼명칭이 없으므로 컬럼별칭을 붙여주어야함
FROM EMP
GROUP BY DEPTNO

Q6. 직위별 총월급이 1000이상인 것만 담는 VIEW를 작성하라.

Q7. 직위별 총월급이 1000이상인 것만 담는 VIEW를 작성하되, 직위가 ‘SALESMAN’인 것은 제외하라.

Q8. 이름과 월급만 담는 VIEW를 작성하되, 월급이 높은 순으로 출력하라.
CREATE VIEW VIEW05
AS
SELECT ENAME, SAL
FROM EMP
ORDER BY SAL DESC

와 같이 하면

//ERROR : TOP을 함께 지정하지 않는 한 뷰, 인라인 함수, 파생 테이블, 하위 쿼리에서 ORDER BY 절을 사용할 수 없습니다.

CREATE VIEW VIEW05
AS
SELECT TOP 100 PERCENT ENAME, SAL
FROM EMP
ORDER BY SAL DESC

로 하면 에러없이 정상적으로 실행된다.

※알아두기

<VIEW 에 사용할 수 없는 구문>
1.ORDER BY
2.COMPUTE,COMPUTE BY
3.SELECT ..INTO구문(테이블 복사)
  CREATE VIEW VIEW06
  AS
  SELECT *
  INTO EMP 1000
  FROM EMP

4.임시테이블 참조

5.일괄처리에서 뷰 생성 및 참조
  CREATE VIEW VIEW06
  AS
  SELECT * FROM EMP
                                            <- GO를 넣어 주면 됨.
 SELECT * FROM VIEW06

VIEW에서의 DML 사용하기
☞ VIEW에서 DML이 안되는 경우

(DISTINCT)
Q1. EMP 테이블에서 중복된 행을 제거한 직업을 담는 VIEW를 작성하라.
CREATE VIEW VIEW07
AS
SELECT DISTINCT JOB
FROM EMP

SELECT *FROM VIEW07

Q2. 위에서 작성한 VIEW07에서 ‘SALESMAN’ 직업을 ‘SALES’라고 바꿔라.
UPDATE VIEW07
SET JOB='SALES'
WHERE JOB='SALESMAN'
-> ERROR : 뷰 또는 함수 'VIEW07' 정의에 DISTINCT 절이 있어서 업데이트할 수 없습니다.

원본데이터의 무결성을 보장해주기 위해 DISTINCT절을 사용하는 것~!! DISTINCT절로 된 뷰는 DML불가

(GROUP BY)
Q3. EMP 테이블에서 부서별 평균월급을 담는 VIEW를 작성하라.
CREATE VIEW VIEW08
AS
SELECT AVG(SAL) AS 평균월급
FROM EMP
GROUP BY DEPTNO

SELECT * FROM VIEW08

Q4. 위에서 작성한 VIEW에서 10번 부서의 평균월급을 0로 바꿔라.
UPDATE VIEW08
SET DEPTNO=10
WHERE AVG(SAL)=0

->ERROR :집계가 HAVING 절이나 SELECT 목록에 포함된 하위 쿼리 내에 없으면 WHERE 절에 나타나지 않습니다. 또한 집계 중인 열은 외부 참조입니다.

GROUP BY절로 묶여 있는것은 DML작업을 할수 없다

뷰의 참조 :뷰가 다른 뷰를 참조

:연쇄적으로 뷰가 중첩되어 참조되고 있을때 중간에 하나라도 DROP되어 버리면 문제가 생김

<VIEW10 생성>
CREATE VIEW VIEW10
AS
SELECT ENAME,SAL, DEPTNO
FROM EMP

<VIEW10을 참조하는 VIEW11>
CREATE VIEW VIEW11
AS
SELECT ENAME,SAL
FROM VIEW10

※알아두기 : VIEW, PROCEDURE, TFIGGER의 중첩 수준 :32단계

**WITH SCHEMABINDING

: 구조 변경을 못하게 함. 뷰랑 사용자 정의 함수에서만 사용할수 있다.

CREATE VIEW S_VIEW
WITH SCHEMABINDING
AS
SELECT DEPTNO, DNAME,LOC // *를 사용할수 없다. 구조를 묶을 컬럼명을 써줌
FROM DEPT

를 실행시키면

-> ERROR :'DEPT' 이름을 스키마 바인딩에 사용할 수 없으므로 뷰 'S_VIEW'을(를) 스키마 바인딩할 수 없습니다. 이름은 2부분으로 이루어져야 하며 개체는 자체 참조할 수 없습니다.

소유자의 이름을 써주어야 함..(엔터프라이즈 관리자에서 소유자의 이름을 찾는다.)

"소유자이름.테이블명" 으로 고쳐줌.

CREATE VIEW S_VIEW
WITH SCHEMABINDING
AS
SELECT DEPTNO, DNAME,LOC
FROM DBO.DEPT

 

로 실행시키면 에러없이 실행될것이다.

 

DROP TABLE DEPT // 삭제할수 없다. 구조자체가 WITH SCHEMABINDING으로 묶여있기 때문에

 

**WITH CHECK OPTION
Q1. 뷰를 통해 뷰의 정의를 벗어나는 데이터 수정, 추가하기
Q2. WITH CHECK OPTION 사용하기

 *WITH CHECK OPTION : VIEW에 WHERE절이 있을때 조건에 어긋나는 데이터는 입력받지 않겠다라는 구문..

 //VTEST라는 테이블 생성후 V라는 뷰 생성

CREATE TABLE VTEST
(ID INT)
GO
CREATE VIEW V
AS
SELECT *
FROM VTEST
WHERE ID>100

INSERT INTO V VALUES(1)
INSERT INTO V VALUES(100)

-->VIEW에 WHERE절이 있을때 조건에 어긋나는 데이터도 모두 입력이됨...
아래와 같이 마지막 줄에 WITH CHECK OPTION 구문을 추가해 주면 문제 해결

CREATE VIEW V
AS
SELECT *
FROM VTEST
WHERE ID>100
WITH CHECK OPTION

INSERT INTO V VALUES(2)
INSERT INTO V VALUES(201)

Q3. VIEW의 삭제

**암호화 사용하기

EXEC SP_HELPTEXT V  //소스보여주는 프로시져

소스를 보여주지 않게 하려면~

ALTER VIEW V
WITH ENCRYPTION
AS
SELECT *
FROM VTEST
WHERE ID>100
WITH CHECK OPTION

 

 

!! MS-SQL에서 암호화된 것을 복호화하는 방법은 없다.그러므로 암호화를 사용할때는 신중히 사용해야함... 잘못하면 자신이 만든 소스를 볼수 없게 된다.. 백업을 잘 해두도록..!!