View

--VIEW 를 사용하는 이유
--1. 자주 쓰는 쿼리문을 단순화 하기 위하여
--2. 보안을 위해 :  불필요한정보들/민감한 정보들을 제외하고 정보를 제공하기 위하여

 

 

 

--view생성

create view v_userTbl
as
	select custId, custName, addr from customersTbl--이 안에내용만 바꾸면 됨
go 

 

 

 

--1. 에 해당할 때

 

--셀렉문 먼저 확인하기

select C.custName, s.saleDate, G.goodsName, S.sales
from customersTbl C
inner join salesTbl S ON C.custId = S.custId
inner join goodsTbl G ON S.goodsId = G.goodsId

 

--뷰로 만들기

create view v_userBuyTbl
as
	select C.custName, s.saleDate, G.goodsName, S.sales
	from customersTbl C
	inner join salesTbl S ON C.custId = S.custId
	inner join goodsTbl G ON S.goodsId = G.goodsId

 

--뷰를 이용한 검색

select * from v_userBuyTbl
where goodsName='산도';

 

--뷰 컬럼 수정하기

alter view v_userTbl as
	select custId, custName, addr from customersTbl

 

--뷰 내용 업데이트

update v_userBuyTbl
set saleDate = '2016-01-01'
where custName='홍길동'

 

--뷰 삭제

drop view v_userTbl

 

--뷰 내용 추가 : 는 기존 테이블에 영향을 주는 행위이므로 불허한다 (error)
--집계함수, UNION ALL, CROSS JOIN, DISTINCT, GROUP BY 등을 사용한 뷰는 수정 불가능

insert into v_userBuyTbl values('강호동', '2018-10-15', '새우깡', 5000)

 

 

--현재 생성된 뷰의 정보들 확인

select * from sys.sql_modules

 

 

--object_id를 object_name으로 변경해줌

select OBJECT_NAME(object_id), definition from sys.sql_modules

select OBJECT_NAME(1845581613), definition from sys.sql_modules