32. 뷰(View)

--카테고리(상품카테고리) 응용프로그램설계

 

 

--[0] 테이블설계

CREATE TABLE dbo.Categories

(

    CategoryID Int IDENTITY(1, 1) Not Null Primary Key,  --카테고리번호

    CategoryName VarChar(50),                            --카테고리명

    --

    SuperCategory Int Null,        --부모카테고리번호(확장용)

    Align SmallInt Default(0)      --카테고리보여지는순서(확장용)

)

Go

 

 

 

--[!] 4개SQL문연습

--[1] 입력: Add / Write

Insert Categories Values('컴퓨터', Null, Default)

Insert Into Categories Values('노트북', 1, 1)

Insert Categories(CategoryName, SuperCategory, Align) Values('핸드폰', Null, 2)

Insert Categories Values('신규', 3, 3)

 

 

 

--[2] 출력: Get / List

Select CategoryName From Categories Order By Align Asc

 

 

 

--[3] 상세: View / Get(-> 1개레코드에대해서상세보기)

Select * From Categories Where CategoryID = 1

 

 

 

--[4] 수정: Modify / Edit

Update Categories

Set

       CategoryName = '콤퓨타'

Where CategoryID = 1

 

 

 

--[5] 삭제: Delete

Delete Categories Where CategoryID = 2

 

 

 

--[6] 검색: Search / Find

Select * From Categories

Where

       CategoryName Like '%퓨%'

       And

       SuperCategory Is Null --널(NULL)값비교(-> 즉, 대분류만검색해라!)

 

      

      

--[7] 뷰(View) 생성: Select문전용

--[a] 대분류데이터를출력하는구문을줄여서출력

Select CategoryID, CategoryName

From Categories

Where SuperCategory Is Null

Go

 

--[b] 위구문을줄여주는뷰(View) 생성

Create View dbo.GetTopCategory

As

       Select CategoryID, CategoryName

       From Categories

       Where SuperCategory Is Null

Go

 

--[c] 뷰(가상테이블) 사용: 약간줄어들죠???

Select * From GetTopCategory

Order By CategoryName Asc

Go

 

--[d] 뷰(가상테이블) 수정: 암호화

sp_helptext GetTopCategory --(생성한)뷰구문보기

Go

 

--[!] 뷰구문수정: 암호화처리

Alter View dbo.GetTopCategory

With Encryption -- 개체암호화옵션

As

       Select CategoryID, CategoryName

       From Categories

       Where SuperCategory Is Null

Go

 

sp_helptext GetTopCategory --안보임

Go

 

--[!] 뷰구문수정: 스키마바인딩적용

Alter View dbo.GetTopCategory

With SchemaBinding -- Categories 테이블(=부모테이블) 변경불가능

As

       Select CategoryID, CategoryName

       From dbo.Categories

       Where SuperCategory Is Null

Go

 

--[!] 뷰에다가직접데이터입력

Insert Into GetTopCategory(CategoryName) Values('가전')

Go

Select * From Categories

Go

 

-- With Check Option 사용(하면기본적으로테이블에입력(수정)이되지않는다.)

Alter View dbo.GetTopCategory

As

       Select *

       From dbo.Categories

       Where SuperCategory Is Null -- 대분류만...

      

       With Check Option --조건절에해당하는데이터만입력/수정가능

Go

 

-- 에러: Identity값입력X, SuperCategory => Null 입력

Insert Into GetTopCategory Values('오디오', 5, 2) --

Go

 

-- 기본

Set Identity_Insert Categories On

 

-- 실행: 조건절이(대분류만가능토록되어있기에)

Insert Into GetTopCategory(CategoryName) Values('오디오') -- 에러난다.

Go

Insert Into GetTopCategory(CategoryID, CategoryName)

Values(7, '오디오') -- 실행됨

Go

Insert Into GetTopCategory(CategoryID, CategoryName, SuperCategory)

Values(8, '오디오', Null) -- 실행됨

Go