[Sequel Safe] 발판 코드 or CRUD 코드를 이용하여 SP작성하기

테이블의 레이아웃 정보를 이용하여 CRUD용 SP 코드를 자동 생성해 주는 기능입니다.

참고로 CRUD란... Create Retrieve Update Delete의 머리 글자에서 따온 용어입니다.

발판 코드의 잇점을 나열하면 이렇습니다.

1. 테이블 별로 INSERT, SELECT, UPDATE, DELETE 작업을 수행하는 SP를 빠르게 작성할 수 있다.
2. 주석, 변수 명명, 에러 핸들링과 같은 코딩 규칙을 통일 할 수 있다.
3. 테이블의 컬럼 정의를 SP의 파라미터 정의에 재활용하여 주석 작성 시간을 줄여준다.


제 경험으로보면, 통상 테이블 수의 3 ~ 4배 가량의 Stored Procedure가 만들어집니다.
(그 이상이라면 SP가 적절히 추상화되지 못했기 때문일 겁니다.)

그 중 50% 이상이 단순 CRUD 작업이라는 점을 생각해보면, 발판 코드의 잇점을 쉽게 이해할 수 있습니다.


이제 Demo DB를 사용하여 실습을 해보도록 하겠습니다.

만들어 볼 SP는 "기사 카테고리 추가"입니다.

ERD를 기억하신다면, 이 작업이 dbo.Categories테이블에 INSERT하는 프로세스임을 쉽게 짐작하실 수 있을 겁니다.

먼저 테이블의 레이아웃을 조회해 봅시다. (테이블이 어떻게 생겼는지 감을 잡기 위해~)
저는 Ctrl + 5 단축키에 sp_helptable을 등록했으므로, Categories라는 테이블 이름을 타이핑하고 선택한 후 단축키를 누릅니다. (또는 EXEC dbo.sp_helptable Categories 를 실행)

실행 결과는 아래와 같습니다.

이제 발판 코드를 생성합니다.

제 단축키 설정으로는 Ctrl + 7이므로, 테이블 레이아웃을 조회할 때와 마찮가지로... Categories라고 타이핑 후 선택하고 단축키를 누릅니다.

단축키 설정을 하지 않았다면 아래와 같은 형태로 직접 작성하고 실행해야 합니다.
- 단축키 설정하시길 강력히 권합니다. (http://purumae.tistory.com/59 하단) 참조

EXEC dbo.sp_mkScaffold @nvcAuthorName=N'doeyull.kim'

       , @nvcAuthorEmail=N'purumae@gmail.com'

       , @nvcTableName=N'Categories'

실행 결과는 아래와 같습니다.

Messages 탭에 CRUD 발판 코드가 출력되는 것을 볼 수 있습니다.
(제공하는 소스는 Create 1개, Retrieve 4개, Update 1개, Delete 1개.. 총 7개입니다.)

1. 일단 Create 블럭의 소스를 편집창에 복사 - 붙여넣기합니다.

2. Ctrl + Shift + M을 누르고 아래와 같이 SP이름과 설명을 기입합니다.




생성된 SP 코드를 살펴보겠습니다.

SET ANSI_NULLS ON;

GO

SET QUOTED_IDENTIFIER ON;

GO

 

USE Demo;

GO

 

IF OBJECT_ID(N'dbo.P_AddCategory', N'P') IS NOT NULL

    DROP PROCEDURE dbo.P_AddCategory;

GO

 

/**

author : doeyull.kim

e-mail : purumae@gmail.com

created date : 2009-03-31

description : 기사카테고리를추가합니다.

return value :

0 = 에러가없습니다.

1 = 트랜잭션을Commit할수없는상태입니다. 트랜잭션을Rollback합니다.

100 = 시스템에러가발생하였습니다. dbo.ErrorLogs 테이블을조회하세요.

**/

 

CREATE PROCEDURE dbo.P_AddCategory

      @intTopCategoryID int --// 최상위기사카테고리일련번호.

    , @intParentCategoryID int --// 상위기사카테고리일련번호.

    , @nvcCategoryName nvarchar(100) --// 기사카테고리이름.

    , @intCategoryLevel int --// 기사카테고리계층깊이.

    , @intSortNumber int --// 동일깊이의카테고리에서의정렬순서.

    , @intCategoryID int = NULL OUTPUT --// 기사카테고리일련번호.

WITH EXECUTE AS 'SPExecutor'

AS

SET NOCOUNT ON;

SET XACT_ABORT ON;

 

DECLARE @intReturnValue int;

 

/**_# Rollback and return if inside an uncommittable transaction.*/

IF XACT_STATE() = -1

BEGIN

    SET @intReturnValue = 1;

    GOTO ErrorHandler;

END

 

BEGIN TRY

    /**_# [Categories] 테이블에INSERT합니다.*/

    INSERT dbo.Categories (topCategoryID, parentCategoryID, categoryName, categoryLevel, sortNumber)

    VALUES (@intTopCategoryID, @intParentCategoryID, @nvcCategoryName, @intCategoryLevel, @intSortNumber);

 

    SET @intCategoryID = SCOPE_IDENTITY();

END TRY

BEGIN CATCH

    IF ERROR_NUMBER() = 547 AND ERROR_MESSAGE() LIKE N'%FK_%'

        SET @intReturnValue = ;

 

    ELSE IF ERROR_NUMBER() = 547 AND ERROR_MESSAGE() LIKE N'%FK_%'

        SET @intReturnValue = ;

 

    ELSE IF ERROR_NUMBER() = 2627 AND ERROR_MESSAGE() LIKE N'%PK_%'

        SET @intReturnValue = ;

 

    ELSE IF ERROR_NUMBER() = 2601 AND ERROR_MESSAGE() LIKE N'%IX_UN_%'

        SET @intReturnValue = ;

 

    GOTO ErrorHandler;

END CATCH;

 

RETURN 0;

 

ErrorHandler:

IF XACT_STATE() <> 0

    ROLLBACK TRANSACTION;

 

IF @intReturnValue IS NULL OR @intReturnValue = 0

    EXEC @intReturnValue = dbo.P_AddErrorLog;

 

RETURN @intReturnValue;

GO

 

발판 코드는 어디까지나 프로덕트 코드로 가기 위한 출발점입니다.

게다가 처음 사용할 때는 익숙하지도 않죠.

하지만, 제가 Sequel Safe를 만들고 팀원들에게 공유했을 때 모두 빠른 속도로 적응 할 수 있었습니다.

필요한 것은 관심과 노력입니다.

3. 발판 코드를 수정한 프로덕트 코드는 이렇습니다.

- 파라미터를 가감하고, 로직을 추가하고, CATCH블럭을 완성하면 됩니다.

SET ANSI_NULLS ON;

GO

SET QUOTED_IDENTIFIER ON;

GO

 

USE Demo;

GO

 

IF OBJECT_ID(N'dbo.P_AddCategory', N'P') IS NOT NULL

    DROP PROCEDURE dbo.P_AddCategory;

GO

 

/**

author : doeyull.kim

e-mail : purumae@gmail.com

created date : 2009-03-31

description : 기사카테고리를추가합니다.

return value :

0 = 에러가없습니다.

1 = 트랜잭션을Commit할수없는상태입니다. 트랜잭션을Rollback합니다.

100 = 시스템에러가발생하였습니다. dbo.ErrorLogs 테이블을조회하세요.

101 = 상위기사카테고리가존재하지않습니다.

**/

 

CREATE PROCEDURE dbo.P_AddCategory

      @intParentCategoryID int = NULL --// 상위기사카테고리일련번호.

    , @nvcCategoryName nvarchar(100) --// 기사카테고리이름.

    , @intCategoryID int = NULL OUTPUT --// 기사카테고리일련번호.

WITH EXECUTE AS 'SPExecutor'

AS

SET NOCOUNT ON;

SET XACT_ABORT ON;

 

DECLARE

      @intReturnValue int

    , @intTopCategoryID int

    , @intCategoryLevel int

    , @intSortNumber int;

 

/**_# Rollback and return if inside an uncommittable transaction.*/

IF XACT_STATE() = -1

BEGIN

    SET @intReturnValue = 1;

    GOTO ErrorHandler;

END

 

BEGIN TRY

    BEGIN TRANSACTION;

        /**_# @intParentCategoryID 인풋값이NULL이면, 최상위카테고리의등록을의미합니다.*/

        IF @intParentCategoryID IS NULL

            SET @intCategoryLevel = 1;

 

        /**_# @intParentCategoryID 인풋값이있으면, 지금등록하는카테고리의레벨은상위카테고리의레벨+ 1입니다.*/

        ELSE

            SELECT @intCategoryLevel = (categoryLevel + 1), @intTopCategoryID = topCategoryID

            FROM dbo.Categories WITH (UPDLOCK)

            WHERE categoryID = @intParentCategoryID;

 

        /**_# [Categories] 테이블에INSERT합니다.*/

        INSERT dbo.Categories (topCategoryID, parentCategoryID, categoryName, categoryLevel, sortNumber)

        SELECT NULL, @intParentCategoryID, @nvcCategoryName, @intCategoryLevel, ISNULL(MAX(sortNumber), 0) + 1

        FROM dbo.Categories WITH (TABLOCKX)

        WHERE categoryLevel = @intCategoryLevel;

 

        SET @intCategoryID = SCOPE_IDENTITY();

    COMMIT TRANSACTION;

END TRY

BEGIN CATCH

    IF ERROR_NUMBER() = 547 AND ERROR_MESSAGE() LIKE N'%FK_Categories_Categories%'

        SET @intReturnValue = 101;

 

    GOTO ErrorHandler;

END CATCH;

 

RETURN 0;

 

ErrorHandler:

IF XACT_STATE() <> 0

    ROLLBACK TRANSACTION;

 

IF @intReturnValue IS NULL OR @intReturnValue = 0

    EXEC @intReturnValue = dbo.P_AddErrorLog;

 

RETURN @intReturnValue;

GO

 

 

TIP
EXEC dbo.sp_helpmodule을 실행하면, 지금 추가한 P_AddCategory가 문서화되어 출력됩니다.

CRUD 하나 하나를 상세히 다루는 것은 부득이 생략하도록 하겠습니다.
문의 사항이 있으시다면, 댓글을 달아 주시거나 purumae@gmail.com으로 연락 주십시오.