ms-sql view 에 관한 내용

** VIEW **

뷰는 가상 테이블 또는 저장된 쿼리이다.
뷰를 통해 액세스할 수 있는 데이터는 데이터베이스에 하나의
개체로 저장되지 않는다.
데이터베이스에는 SELECT 문이 저장.
뷰에서 반환하는 가상 테이블은 SELECT 문의 결과 집합으로 구성.
테이블을 참조할 때와 같은 방식으로 뷰를 참조하여 이 가상
테이블을 사용.

- 사용자를 테이블의 특정 행으로 제한.
예를 들어, 사원이 작업 관리 테이블에서 자신의 작업을
기록한 행만 보도록 할 수 있다.

- 사용자를 특정 열로 제한.
예를 들어, 봉급과 관련된 작업을 하지 않는 사원은 사원
테이블에서 이름, 사무실, 작업 번호 및 부서 열만 볼 수
있고 봉급 정보 또는 인사 정보는 보지 못하도록 할 수 있다.

- 여러 테이블의 열을 조인하여 하나의 테이블처럼 보이도록
할 수 있다.

- 자세한 정보를 제공하는 대신 정보를 집계.
예를 들어, 열에서 열 합계, 최대값 또는 최소값을 제공.

========================================================================
**모든 DB의 객체는 sys~로 시작하는 시스템 테이블에 저장된다.

* 뷰에대한 정보보기

SELECT * FROM sysobjects
WHERE xtype LIKE 'V' and name LIKE 'titles_view'

DECLARE @vID int
SET @vID = (SELECT id FROM sysobjects WHERE xtype LIKE 'V'
and name LIKE 'titles_view')

*뷰의 컬럼에 대한 정보 보기
SELECT * FROM syscolumns WHERE id = @vID

*뷰의 의존성 보기
SELECT * FROM sysdepends WHERE id = @vID

*뷰의 생성 구문 보기
SELECT * FROM syscomments WHERE id = @vID
========================================================================


-- 뷰의 생성 --
USE pubs

CREATE VIEW view_test1
AS
SELECT * FROM titles

SELECT * FROM view_test1


CREATE VIEW view_test2
AS
SELECT title, type, price FROM titles

SELECT * FROM view_test2


CREATE VIEW view_test3
AS
SELECT titles.title, authors.au_lname, publishers.pub_name
FROM titles INNER JOIN titleauthor ON titles.title_id = titleauthor.title_id
INNER JOIN authors ON titleauthor.au_id = authors.au_id
INNER JOIN publishers ON titles.pub_id = publishers.pub_id

SELECT * FROM view_test3

CREATE VIEW view_test4
AS
SELECT title, au_ord, au_lname, price, ytd_sales, pub_id
FROM authors AS a
JOIN titleauthor AS ta ON (a.au_id = ta.au_id)
JOIN titles AS t ON (t.title_id = ta.title_id)

SELECT * FROM view_test4


--뷰를 참조하여 뷰 생성 --
CREATE VIEW Cust_view
AS
SELECT title, au_lname, price, pub_id
FROM view_test4

SELECT * FROM Cust_view

-- 뷰생성이 불가한 경우 --
COMPUTE 또는 COMPUTE BY 절 포함
SELECT 문의 컬럼에 TOP을 사용하지 않는 ORDER BY 절 포함
INTO 키워드 포함
임시 테이블 또는 테이블 변수 참조
GROUP BY나 AVG등을 이용한 계산(집계)함수는 사용이 가능.

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'titles_view')
DROP VIEW titles_view
GO

* GROUP BY 구문과 같은 계산(집계) 함수가 포함된 구문
CREATE VIEW titles_view
AS
SELECT pub_id 저자, type 책종류, royalty 로열티, ytd_sales 팔린수, AVG(price) 평균값
FROM titles
GROUP BY pub_id, type, royalty, ytd_sales

SELECT * FROM titles_view


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'titles_view2')
DROP VIEW titles_view2
GO

* SUM계산(집계) 함수가 포함된 구문
CREATE VIEW titles_view2
AS
SELECT SUM(price) 책평균가격
FROM titles

SELECT * FROM titles_view2


-- 저장 프로시저를 이용해서 view 구문 보기 --
sp_helptext view_test1
sp_helptext view_test2
sp_helptext view_test3
sp_helptext view_test4


-- 뷰에대한 암호화 옵션 --
CREATE VIEW enc_test (title, advance, tot)
WITH ENCRYPTION
AS
SELECT title, advance, price * royalty * ytd_sales
FROM titles
WHERE price > $10

sp_helptext enc_test

이때는 반드시 생성하신분이 뷰 생성 스크립트를 백업.

*암호화된 저장 프로시저의 ID 및 텍스트를 검색하기 위한 쿼리
SELECT c.id, c.text
FROM syscomments c, sysobjects o
WHERE c.id = o.id and o.name = 'enc_test'


--Check 옵션 --
CREATE VIEW CAonly
AS
SELECT au_lname, au_fname, city, state
FROM authors
WHERE state = 'CA' or state = 'or'

SELECT * FROM CAonly

UPDATE CAonly
SET state = 'OR'
WHERE au_lname = 'White' -- 수정이 된다.

drop view caonly

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'CAonly')
DROP VIEW CAonly

CREATE VIEW CAonly
AS
SELECT au_lname, au_fname, city, state
FROM authors
WHERE state = 'CA'
WITH CHECK OPTION

UPDATE CAonly SET state = 'OR' WHERE au_lname = 'Carson'

sp_help CAonly


--- 뷰 변경 --
CREATE VIEW All_authors (au_fname, au_lname, address, city, zip)
AS
SELECT au_fname, au_lname, address, city, zip
FROM pubs..authors

--Public역할(일종의 사용자 그룹이라고 생각)에
--뷰를 SELECT할 수 있는 권한 부여
--GRANT는 SELECT, UPDATE등의 권한을 부여하는 명령.

GRANT SELECT ON All_authors TO public

ALTER VIEW All_authors (au_fname, au_lname, address, city, zip)
AS
SELECT au_fname, au_lname, address, city, zip
FROM pubs..authors
WHERE state = 'UT'


-- 뷰 삭제 --
drop view 뷰이름

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'view_test1')
DROP VIEW view_test1

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
SELECT * FROM sysobjects WHERE xtype like 'V'


-- 뷰 옵션 --
ENCRYPTION | SCHEMABINDING | VIEW_METADATA

* SCHEMABINDING 옵션

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'titles_view')
DROP VIEW titles_view

CREATE VIEW titles_view
AS
SELECT title, type, price, pubdate
FROM titles

만약 뷰의 원본 테이블인 titles 테이블에 변형이 일어 났다...
뷰는 깨져 버리게 된다.
이럴 함부로 원본 테이블을 변환시키지 못하게 SCHEMABINDING 이라는 옵션을
주어 원본테이블을 수정하려할 경우 원본 테이블의 형을 참조하는 개체가 있으니
수정이 불가 하다는 메세지를 리턴.

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'titles_view')
DROP VIEW titles_view

CREATE VIEW titles_view
AS
SELECT title, type, price, pubdate
FROM titles
SCHEMABINDING

sp_help titles

* 원본 테이블 수정 title컬럼은 VARCHAR(80)으로 되어 있다.

ALTER TABLE titles ALTER COLUMN title VARCHAR(100) NOT NULL

ALTER TABLE titles ALTER COLUMN title VARCHAR(50) NOT NULL

 
* VIEW_METADATA 옵션

INSERT INSTEAD OF TRIGGER 또는 UPDATE INSTEAD OF TRIGGER 가 있을경우
뷰에대한 모든 열을 수정할 수 있다.



-- 뷰를 통한 데이터 수정 --

1. insert 할 경우

CREATE TABLE mytable1(
c1 int NOT NULL
, c2 int NOT NULL
, c3 varchar(30) NOT NULL
)

INSERT INTO mytable1 (c1, c2, c3) VALUES(1,2,'하이')
INSERT INTO mytable1 (c1, c2, c3) VALUES(3,4,'호호')
INSERT INTO mytable1 (c1, c2, c3) VALUES(5,6,'히히')


CREATE VIEW myview1
AS
SELECT c2, c3 FROM mytable1

SELECT * FROM myview1

INSERT INTO myview1 (c2, c3) VALUES(7, '크크')

--c1은 NOT NULL로 지정 되었으므로 데이터 삽입 불가



CREATE TABLE mytable2(
c1 int NULL
, c2 int NOT NULL
, c3 varchar(30) NOT NULL
)

INSERT INTO mytable2 (c1, c2, c3) VALUES(NULL,2,'하이')
INSERT INTO mytable2 (c1, c2, c3) VALUES(NULL,4,'호호')
INSERT INTO mytable2 (c1, c2, c3) VALUES(5,6,'히히')

CREATE VIEW myview2
AS
SELECT c2, c3 FROM mytable2

SELECT * FROM myview2

INSERT INTO myview2 (c2, c3) VALUES(7, '크크')


2 .계산(집계) 함수를 이용한 뷰 생성후 또는 파생된 컬럼일 경우

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'categories')
DROP VIEW categories
GO
CREATE VIEW categories (category, average_price)
AS
SELECT type, AVG(price)
FROM titles
GROUP BY type
GO

SELECT * FROM categories

UPDATE categories SET average_price = 13.7300
WHERE average_price > 10


3. 조인된 뷰에대한 수정

조인은 일반적으로 두개 이상의 테이블을 엮어서 생성
컬럼의 이름의 경우 분명히 겹칠 수 있으며 문제가 될 수 있다.
이럴 경우 두개의 테이블에 대해서 이를 처리 하려면?
==> 각각의 수정사항에 대해서 동시에 수정.



-- SQL서버의 시스템 뷰 --

information_schema
--테이블들의 정보를 조회
SELECT * FROM information_schema.tables

--뷰들의 정보를 조회
SELECT * FROM information_schema.views

--어떤 information_schema가 존재하는지 조회를 하기위한 쿼리  
USE master
DECLARE @uid int
SET @uid = (SELECT uid FROM sysusers
WHERE name like 'INFORMATION_SCHEMA')
SELECT * FROM sysobjects WHERE xtype like 'V'
and uid = @uid
GO

--뷰의 생성 스크립트 보기
EXEC master..sp_helptext 'information_schema.tables'
GO

EXEC master..sp_helptext 'information_schema.views'
GO



-- 실무에서 뷰의 사용 --

기 구축된 시스템에 특정 난이도의 모듈을 붙이는 작업을 합니다.
저 혼자일 경우도 있고.. 혼자서 안할 경우도 있지요.
대부분의 이런 작업은 특정 모듈이 이미 시스템에 있는 경우 입니다.
예를들어 사내 오퍼레이터가 사용하던 VB 또는 기타 웹로직 등으로 생성된
컴포넌트 모듈이 있으며 이 모듈을 타게하는 인자들만 정확하게 넘기는 식으로 사용 하지요..
대부분의 삽입, 수정, 삭제 들은? 이 모듈을 타게함이 역시나 좋습니다. 왜냐구요?
테이블 수가 수십, 또는 수백개 일 경우 정규화는 사실 문제 입니다.
대부분 특정 업무별로 팀장을 한명씩 지정해 고유 스키마를 생성후
다른 업무와 연계짓는 부분만 서로 상의를 해서 정규화를 진행하는 방법이
일반적이지요. 만약 제가 INSERT를 했는데.. 수백개중에서 2개 테이블에 넣는줄
알았더니.. 대략 10개 정도의 테이블에 INSERT등을 하더라~~~
하면? 저는 잘못된 데이터를 넣었더~~~~ 하면서 칼을 맞을 수도 있겠지요?
그렇다고 문서화가 잘 되어 있는가???? 제 경험에 의하면?
컴퍼넌트 모듈이나 DB의 객체에 대해서 문서화가 잘된 시스템 몇몇
대기업을 제외하고는 본적 없습니다. 그렇다고 사이트에서 업무를 이해하는데 많이 도와주나?
그런것도 아닙니다. 그냥 필요한 만큼 - 1만 알려주고 말지요..
물론 친절한 분들도 많이 계시지만... 뭐 말이 흘렀군요... -_-;;;;
이런 저런 이유로 대부분의 수정 모듈은 기구축된 컴퍼넌트 모듈을 타게 하는것이
더 좋은 방법 입니다.
 
문제는 다양한 방법으로 사용자에게 보여주는!!! SELECT를 하는 부분 입니다.
테이블이 몇개? 이게 중요한게 아니죠?
문제는 너무 많은 내부적인 채번이나 시스템 정보를 제외한 개발에 필요한 정보만을
봐야 하는데... 이게 스키마가 클수록 시간적인 문제라는 겁니다.
그래서!!! 바로 필요한 모듈만 뽑아서 뷰로 생성후 여기에 대해서 작업을 진행하고
처리하는 방법을 취하게 되는 것이지요. 그러면? 상당한 효율을 얻을 수 있습니다.
자신이 필요한 정보에 대해서만 촛점을 맞출 수 있기 때문이지요.
 
두번째는 역시나 보안 문제 입니다.
간단히 인사고과 테이블이나 연봉이라는 컬럼이 존재하는 테이블이라면?
아예 시작부터 뷰로 잡고 여기서 처리를 진행 하지요.
신경을 까딱 놓으면? 누구연봉 얼마래~~~ 하는 이야기 퍼지기 십상이니 특히나
더 조심을 해야 하는 겁니다. 참고로 일반적인 전산실에 근무하시는 분들은
회사내 전원의 연봉을 손바닥 보듯이 다 알고 있지요. 조금만 롤과 유저 권한에 대해서
공부를 하신다면? 충분이 막을수 있는 부분인데도 말이지요...
 
뷰는 속도와는 무관 합니다.
하지만 SQL서버2000 부터는 바로 뒤에 이야기할 뷰에 인덱스 생성,
분할(Partitioned) 뷰 생성으로 상당한 성능을 높일 수 있는 기반이 마련 되어 있습니다.

다운로드
view.jpg (49.8KB)

의견 1 신규등록      목록