Stored Procedure

Stored Procedure

DB 내부에 저장된 일련의 SQL 명령문들을 마치 하나의 함수처럼 실행하기 위해서 쿼리를 묶어둔것. 반드시 값을 리턴할 필요가 없다는 점에서 함수와 다르다. (stored function은 반드시 결과값 리턴해야함) DB를 하다보면 여러 쿼리를 같이 써야지만 원하는 기능을 수행할 수 있는 경우가 종종 발생하는데, 이때 일련의 동작을 묶어서 그리고 기존 함수의 유용한 기능들 (조건분기, 변수저장)을 포함시켜서 보다 유연하게 사용할 수 있도록 만들어둔 것이 stored procedure이다.

장점과 단점

DB 작업은 어쩃든 I/O다. 명령을 여러번 수행하면 그때마다 I/O를 수행하는데 부하가 걸린다. Stored Procedure는 DB(서버)에 저장하여 내부적으로 동작하기 때문에 여러번의 명령을 한번의 I/O를 통해 수행시킬 수 있다. 같은 이슈로 데이터 트래픽이 감소한다는 장점도 있다. 그리고 만들어둔 SP는 DB에 저장되는 것이므로 여러 응용프로그램에서 재사용도 가능하다. 그리고 데이터에 대해서 직접 접근을 막고 정해진 인터페이스를 통해서 DB에 접근할 수 있도록 하여 데이터를 캡슐화할 수 있다는 장점도 있다.

하지만, 언어 자체의 기능이 제한적이다. 그래서 원하는 로직을 구현하는 것에 애로사항이 꽃필것이다. 개발 툴 및 디버깅 도구가 부족하다는 현실적인 문제도 있다.

사용 예

Player 테이블의 CRUD를 SP를 통해 구현해보자.

• PlayerTable

CREATE TABLE [PlayerTable](
	[playerUID] [int] NOT NULL PRIMARY KEY IDENTITY(100, 1),
	[playerName] [nvarchar](32) NOT NULL DEFAULT (N'noname'),
	[currentPosX] [float] NOT NULL DEFAULT ((0)),
	[currentPosY] [float] NOT NULL DEFAULT ((0)),
	[currentPosZ] [float] NOT NULL DEFAULT ((0)),
	[createTime] [datetime] NOT NULL,
	[isValid] [tinyint] NOT NULL,
	[comment] [nvarchar](256) NULL
)

• Create

CREATE PROCEDURE [CreatePlayer]
	@name	NVARCHAR(32)
AS
BEGIN
        --새로운 플레이어를 테이블에 등록
	INSERT INTO [PlayerTable] ([playerName], [createTime], [isValid]) VALUES (@name,  GETDATE(), 1);
        --등록한 새 플레이어의 ID를 리턴
	DECLARE @retID INT;
	SELECT @retID = [playerUID] FROM [dbo].[PlayerTable] WHERE [playerName] = @name;
	RETURN @retId;
END

• Read

CREATE PROCEDURE [ReadPlayer]
	@playerUID	INT
AS
BEGIN
        -- 플레이어 정보  얻어오기
	SELECT [playerName],[currentPosX],[currentPosY], [currentPosZ], [isValid], [comment] 
	FROM [PlayerTable] WHERE [PlayerUID] = @playerUID;
END		   

• Update

CREATE PROCEDURE [UpdatePlayerPosition]
	@playerUID	INT,
	@posX	FLOAT,
	@posY	FLOAT,
	@posZ	FLOAT
AS
BEGIN
        -- 해당 플레이어의 정보(x,y,z) 업데이트 
	UPDATE [PlayerTable] 
	SET [currentPosX] = @posX, [currentPosY] = @posY, [currentPosZ] = @posZ
	WHERE [PlayerUID] = @playerUID;
END

• Delete

CREATE PROCEDURE [spDeletePlayer]
	@playerUID	INT
AS
BEGIN
	--해당 플레이어 삭제
	DELETE FROM [PlayerTable] WHERE [playerUID] = @playerUID;
END