--[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) --최상위카테고리는Null
Insert Categories Values('노트북', 1, 1) --부모카테고리는1번인컴퓨터
Insert Categories(CategoryName, SuperCategory, Align) Values('핸드폰', Null, 2)
Insert Categories Values('신규', 3, 3)
--[1] 입력저장프로시저
Create Procedure dbo.AddCategory
(
@CategoryName VarChar(50), --매개변수처리가능
@SuperCategory Int,
@Align Int
)
As
Insert Into Categories Values(@CategoryName, @SuperCategory, @Align)
Go
--프로지서로입력
Execute AddCategory '냉장고', 3, 1
Go
--[2] 출력저장프로시저
Create Proc dbo.GetCategory
As
Select * From Categories Order By CategoryID Asc, Align Asc
Go
--실행
Exec GetCategory
Go
--[3] 상세저장프로시저
Create Proc dbo.GetCategoryByCategoryID
@CategoryID Int
As
Select * From Categories Where CategoryID = @CategoryID
Go
--실행
GetCategoryByCategoryID 1
Go
--[4] 수정저장프로시저
Create Proc dbo.UpdateCategory
@CategoryName VarChar(10),
@CategoryID Int
As
Update Categories
Set
CategoryName = @CategoryName
Where
CategoryID = @CategoryID
Select * From Categories
Go
--Drop Proc UpdateCategory
--실행: 1번카테고리명을'콤퓨터'로변경
UpdateCategory '콤퓨타', 1
--[5] 삭제저장프로시저
Create Proc dbo.DeleteCategory
@CategoryID Int
As
Begin Tran --수정/삭제시예외처리
Delete Categories
Where CategoryID = @CategoryID
Select @@ROWCOUNT --삭제된데이터의개수: 1
If @@ERROR > 0
Begin
RollBack Tran
End
Commit Tran --여기까지에러없이왔다면실행완료
Go
--실행
Exec DeleteCategory 7
--[6] 검색저장프로시저
--카테고리이름이모모모인것을검색?
Create Proc dbo.FindCategory
@CategoryName VarChar(50)
As
Declare @strSql VarChar(500) -- 검색어= ' + @검색어+ '
Set @strSql = 'Select * From Categories Where CategoryName Like ''%' + @CategoryName + '%'''
Print @strSql
Exec(@strSql)
Go
FindCategory '노트북'
Go
Create Proc dbo.PrintString
@Message VarChar(50)
As
Declare @strSql VarChar(255)
Set @strSql = '''@' + @Message + '@'''
Print @strSql
Go
PrintString '안녕'
최근 덧글