Stored Procedures

Stored Procedure

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

장점과 단점

DB 작업은 어쩃든 I/O다. 명령을 여러번 수행하면 그때마다 I/O를 수행하는데 부하가 걸린다. Stored Procedure는 DB(서버)에 저장하여 내부적으로 동작하기 때문에 여러번의 명령을 한번의 I/O를 통해 수행시킬 수 있다.  따라서 프로그램에서 DB서버로 보내야하는 데이터양이 줄기도하고( 명령 길이 자체가 줄어드니까), 왔다갔다하는 트래픽도 감소한다. 그리고 만들어둔 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