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