33. 저장프로시저

--[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)

 

 

 

--[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.GetCategories

As

       Select * From Categories Order By CategoryID Asc, Align Asc

Go

-- 실행

Exec GetCategories

Go

 

 

 

--[3] 상세저장프로시저

Create Proc dbo.GetCategoryByCategoryID

       @CategoryID Int

As

       Select * From Categories Where CategoryID = @CategoryID

Go

-- 실행

GetCategoryByCategoryID 1

Go

 

 

 

--[4] 수정저장프로시저

Create Procedure dbo.UpdateCategory

(

       @CategoryName VarChar(50),

       @CategoryID Int

)

As

       Update Categories

       Set

             CategoryName = @CategoryName

       Where

             CategoryID = @CategoryID

            

       Select * From Categories

Go

-- 실행: 1번카테고리명을'콤퓨타'로변경

UpdateCategory '콤퓨타', 1

Go

 

 

 

--[5] 삭제저장프로시저

Create Proc dbo.DeleteCategory

       @CategoryID Int

As

       Begin Tran                       -- 수정/삭제시예외처리

            

             Delete Categories

             Where CategoryID = @CategoryID

 

             Select @@RowCount   -- 삭제된데이터의개수: 1

 

             If @@Error > 0             -- 만약에에러개수가1이상이면, 롤백시켜라...

             Begin

                    RollBack Tran

             End

            

       Commit Tran         -- 여기까지에러없이왔다면실행완료

Go

 

Exec DeleteCategory 7

Go

 

 

 

--[6] 검색저장프로시저

-- 카테고리이름이모모모인것을검색?

Alter 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

FindCategory '핸드폰'

Go

 

Alter Proc dbo.PrintString

       @Message VarChar(50)

As

       Declare @strSql VarChar(255)

       Set @strSql = '''@''' + @Message + '''@'''

       Print @strSql

Go

 

PrintString '안녕'