1. 저장프로시저란?
저장 프로시저(Stored Procedure)는 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합입니다. 저장프로시저를 잘 만들면 프로그램 내에서 복잡하게 코드를 구성하는 것을 막고 원하는 결과를 얻을 수 있습니다.
2. 저장프로시저 사용방법
저장프로시저를 사용하기 위해서는 먼저 MS SQL서버에 접속한 후 저장프로시저를 클릭해서 새 저장프로시저 추가를 누르면 저장프로시저가 생성됩니다.
저장프로시저 추가버튼을 누르면 다음과 같은 창이 생성됩니다. 여기서 CREATE PROCEDURE 라고 나오는 것은 프로시저를 새로 만들어야 하기 때문입니다. 한번 생성해서 저장한 후에는 ALTER PROCEDURE 라고 바뀌는데 이것은 저장프로시저를 수정하는 것을 뜻합니다. 첫 행 아래 부분에 괄호로 되어있는 곳에 매개변수를 넣어주는 곳인데요. 여기서 변수나 매개변수를 쓸 때는 @를 앞에 붙여 주셔야 합니다.
예를들면 @name varchar(20), @num int 이런식으로 사용할 수 있습니다. 그리고 AS 아래 부분에는 수행해야 할 쿼리문이나 다른 저장프로시저를 호출하는 코드를 작성 하는 곳입니다.
3. 저장프로시저 예제
- 회원가입에서 호출된 저장프로시저인 CheckID
ALTER PROCEDURE dbo.CheckID ( @id varchar(10), @check int output ) AS if Exists (select *from Member where m_id = @id) Begin set @check = 1 End
else Begin set @check = 0 End
RETURN
|
CheckID는 Member테이블 내에 매개변수로 넘어온 id와 같은 id가 있는지 존재여부를 판단해서 output으로 넘어온 변수에 값을 세팅해주는 역할을 합니다. 이 프로시저는 아래에 나오는 NewMember 에서 사용되는데요. 이 프로시저를 활용한 NewMember 저장프로시저에 대해서 알아보겠습니다.
- 계층화된 저장프로시저를 이용해서 회원가입.
ALTER PROCEDURE dbo.NewMember ( @id varchar(10), @pw varchar(10), @name varchar(10) ) AS declare @check int
Exec CheckID @id,@check output
if @check != 1 Begin insert into Member values(@id,@pw,@name,@check) End
RETURN |
NewMember 저장프로시저에서는 id와 pw, name을 매개변수로 받아와서 Member 테이블에 저장시키는 작업을 수행 하는 것입니다. 회원가입을 할 때 id가 중복되면 안되기 때문에 CheckID라는 저장프로시저를 호출합니다. Exec는 저장프로시저에서 다른 저장프로시저를 호출할 때 사용하는 키워드 입니다.
Declare는 AS 내부에 변수를 선언할 때 사용되는 키워드 입니다. 여기서 check라는 변수를 output 으로 전달하고 있습니다. output이란 C#에서 out과 같은 기능을 하는 것으로 호출된 저장프로시저 내부에서 check에 값을 넣어주면 값이 저장되서 사용할 수 있게 됩니다.
저장프로시저를 마우스 오른쪽 버튼 클릭한 후 실행을 하면 다음과 같은 창이 나오고
실행창에서 값을 입력해 저장프로시저가 제대로 작동하는지 확인 할 수 있습니다
제대로 실행되면 1개 행이 영향을 받았다고 출력됩니다.
중복아이디가 있어서 회원가입이 되지 않은 경우에는 0개 행이 영향을 받았다고 나옵니다.
그리고 저장프로시저에서 RETURN 반환 값을 지정할 경우 RETURN_VALUE에 반환되는 값이 출력되게 됩니다.