프로시저

긴 문장을 짧은 하나의 명령어로 대체
C#의 함수처럼, 매개변수 및 반환값 처리 가능

인터프리터 방식이 아닌 컴파일 방식 : 속도가 빠름

 

--[0] 테이블설계

CreateTabledbo.Categories

(

        CategoryIDIntIdentity(1, 1)NotNullPrimaryKey,  --카테고리번호

        CategoryNameVarChar(50),                            --카테고리명

        --

        SuperCategoryIntNull,                              --부모카테고리번호(확장용)

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

)

Go

 

--[!] 4개SQL문연습

--[1] 입력: Add/Write

InsertCategoriesValues('컴퓨터',Null,DEFAULT)    --최상위카테고리는Null

InsertCategoriesValues('노트북', 1, 1)             --부모카테고리는1번인컴퓨터

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

InsertCategoriesValues('신규', 3, 3)

 

--[1] 입력저장프로시저

CreateProceduredbo.AddCategory

(

        @CategoryNameVarChar(50),    --매개변수처리가능

        @SuperCategoryInt,

        @AlignInt

)

As

        InsertIntoCategoriesValues(@CategoryName,@SuperCategory,@Align)

Go

--프로지서로입력

ExecuteAddCategory'냉장고', 3, 1

Go

 

--[2] 출력저장프로시저

CreateProcdbo.GetCategory

As

        Select*FromCategoriesOrderByCategoryIDAsc,AlignAsc

Go

--실행

ExecGetCategory

Go

 

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

CreateProcdbo.GetCategoryByCategoryID

        @CategoryIDInt

As

        Select*FromCategoriesWhereCategoryID=@CategoryID

Go

--실행

GetCategoryByCategoryID 1

Go

 

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

CreateProcdbo.UpdateCategory

        @CategoryNameVarChar(10),

        @CategoryIDInt

As

        UpdateCategories

        Set

               CategoryName=@CategoryName

        Where

               CategoryID=@CategoryID

              

        Select*FromCategories

Go

--Drop Proc UpdateCategory

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

UpdateCategory'콤퓨타', 1

 

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

CreateProcdbo.DeleteCategory

        @CategoryIDInt

As

        BeginTran                            --수정/삭제시예외처리

       

               DeleteCategories

               WhereCategoryID=@CategoryID

              

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

              

               If@@ERROR> 0

               Begin

                       RollBackTran

               End

              

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

Go

--실행

ExecDeleteCategory7

              

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

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

CreateProcdbo.FindCategory

        @CategoryNameVarChar(50)

As

        Declare@strSqlVarChar(500)-- 검색어= ' + @검색어+ '

        Set@strSql='Select * From Categories Where CategoryName Like ''%'+@CategoryName+'%'''

        Print@strSql

        Exec(@strSql)

Go

 

FindCategory'노트북'

Go

 

CreateProcdbo.PrintString

        @MessageVarChar(50)

As

        Declare@strSqlVarChar(255)

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

        Print@strSql

Go

 

PrintString'안녕'

 

[출처]33.저장 프로시저 (Stored Procedure)|작성자mini