35.MS_SQL 2008 - 저장 프로시저 ( Stored Procedure )
--tempdb-에서새쿼리문실행(9월일오전11시30분)
--저장프로시저(Stored Procedure) : 매개변수처리가능
--[0] 테이블설계
Create Table dbo.Categories
(
CategoriesID 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 Categories Values('노트북', 1, 1)
--('상품명자리', 부모 CategoriesID로 들어가는데 위 에서
--컴퓨터 CategoriesID가 ‘1’이여서 컴퓨터 아래로 들어간다, 순서)
Insert Categories (CategoryName, SuperCategory, Align)
Values('휴대폰', Null, 2)
Insert Categories Values('신규', 3, 3)
--[1]입력저장프로시저
/*
Insert Categories Values('냉장고', 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]출력저장프로시저
/* CategoriesID가1번인것만출력
Select *From Categories
Where CategoriesID Like 1
Order By CategoriesID Asc, Align Asc
*/
--프로시저사용
Create Proc dbo.GetCategories
As
Select *From Categories
Order By CategoriesID Asc, Align Asc
Go
--실행
Exec GetCategories
Go
--[3]상세저장프로시저(카테고리ID를프로시저로저장)
/*
Select *From Categories
Where CategoriesID = @CategoryID
*/
Create Proc dbo.GetCategoryByCategoryID
@CategoryID Int--매개변수사용
As
Select *From Categories
Where CategoriesID = @CategoryID
Go
--실행
GetCategoryByCategoryID 5
Go
--[4]수정저장프로시저
/*Update Categories
Set
CategoryName = '콤퓨타'
Where
CategoriesID = 1
Go*/
--카테고리로설정
Create Procedure dbo.UpdateCategory
(
@CategoryName VarChar(50),
@CategoryID Int
)
As
Update Categories
Set
CategoryName = @CategoryName
Where
CategoriesID = @CategoryID
Select *From Categories
Go
--실행: 1번카테고리명을'콤퓨타'로변경
UpdateCategory '콤퓨타', 1
Go
--[5]삭제저장프로시저
Create Proc dbo.DelectCategory
@CategoryID Int
As
Begin Tran --수정/삭제시예외처리
Delete Categories
Where CategoriesID = @CategoryID
Select @@ROWCOUNT --삭제된데이터개수: 1
If @@ERROR > 0 –만약 에러가 1이상이면, 롤백
Begin
RollBack Tran
End
Commit Tran --여기까지에러없이왔다면실행완료
Go
Exec DelectCategory 3
Go
--결과확인
select *from Categories
--[6]검색저장프로시저
--카테고리이름이노트북인것을검색
/*
Select *From Categories
Where CategoryName Like '%노트북%' --'%노트북%' 작은따음표안은문자열이여서매개변수로사용하지못한다.
Go
*/
--프로시저사용
Create Proc dbo.FindCategory --변경할때Alter
@CategoryName VarChar(50)
As
Declare @strSql VarChar(500)--검색어=' + @검색어+'
Set @strSql =
'Select *From Categories
Where CategoryName Like ''%노트북%'''—메시지 확인
--수정1차= 매개변수로사용
--'Select * From Categories
Where CategoryName Like ''%' +
@CategoryName + '%'''
Print @strSql
Exec (@strSql)
Go
FindCategory '무조건노트북'
--수정차매개변수로사용하면아래가실행된다.
--FindCategory '냉장고'
--[!]문자열출력에관한간단한예제
Create Proc dbo.PrintString
@Message VarChar(50)
As
Declare @strSql VarChar(255)
Set @strSql = '''@' + @Message + '@'''
--Set @strSql = '''@''' + @Message +
'''@''' --구분할줄알아야한다.
Print @strSql
Go
PrintString '안녕'
35Stored Procedure.sql