[Tips] SQL 스토어드 프로시저(Stored Procedure) 작성 요령

안녕하세요. 바른 호랑이입니다.

이번 게시글에서는 SQL을 사용하면서 업무 자동화 및 데이터 파이프라인 구축을 위해 자주 쓰이는 Stored Procedure작성 요령에 대해 소개해드리려합니다. 

작성요령에 대해 설명하기에 앞서 간단하게 스토어드 프로시저(Stored Procedure)에 대해 간략하게 알아보도록 하겠습니다. 스토어드 프로시저란 일련의 쿼리를 하나의 함수처럼 실행하기 위한 쿼리 집합으로 자바나 C의 메소드()와 비슷한 역할을 수행합니다. Oracle, MSSQL, MySQL등 대다수의 주요 SQL언어들이 지원하며, DB의 보안을 향상시킬 수 있고 기능 추상화 및 반복업무 자동화와 같은 장점을 가집니다. 이번 게시글에서는 MSSQL을 기준으로 스토어드 프로시저 작성요령에 대해 소개해드릴 예정이지만 다른 언어들도 비슷한 구조로 작성을 진행하셔도 무방하시니 보다 깔끔하게 스토어드 프로시저를 작성하고 싶으신 분들은 참고하시면 좋겠습니다.

MSSQL의 경우 보다 편리하게 개발을 하기 위한 툴로 SSMS(SQL Server Management Studio)를 사용합니다. SSMS는 기본적으로 스토어드 프로시저 작성 양식을 제공하며, 해당 양식을 변형해서 사용하면 체계적인 스토어드 프로시저 사용이 가능합니다. 기본 제공 양식을 활용하기 위해서는 SSMS 실행 후 Object Explorer에서 [DB서버접속] - [원하는 DB 선택] - [Programmability] - [Stored Procedures] - [우클릭 후 New - Stored Procedure...] 순으로 진행하시면 되며 기본 제공양식을 생성하면 아래와 같은 기본 구조로 스토어드 프로시저가 생성되게 됩니다. 

-- =======================================================
-- Create Stored Procedure Template for Azure SQL Database
-- =======================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author, , Name>
-- Create Date: <Create Date, , >
-- Description: <Description, , >
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
(
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- Insert statements for procedure here
    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

해당 양식을 기본으로 하여 상황에 맞게 변형하여 사용하면 되며, 추가적으로 로그 기록용 테이블을 생성하여 오류발생시 오류 메시지 기록 및 Delete, Insert, Update 실행 기록을 관리하면 보다 체계적으로 스토어드 프로시저를 관리하실 수 있습니다. 보다 자세한 사항은 아래의 예시를 참고하시면 되겠습니다. 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[up_create_test_sp] @v_from [NVARCHAR](10),@v_to [NVARCHAR](10) AS 
/******************************************************************************
** Name: [cdm].[up_create_test_sp]
**
** Parameters:
** Input Output
** ---------- -----------
** @v_from ~ @v_to  : 실행 대상 날짜
**
** Auth: 작성자 이름
** Date: 2023.04.17 작성일시
--실행 예시 
exec [dbo].[up_create_test_sp] '2023-04-01', '2023-04-18'

*******************************************************************************
** Change History
*******************************************************************************

1. 최초 생성 : 2023.04.17 (작성자 이름)
2. 테이블 명 일부변경 : 2023.04.18 (수정자 이름)
   1) 세부 변경이력 기록
   
******************************************************************************/
--<STEP> 1. 변수선언
DECLARE
      @V_SP_NM                NVARCHAR(100)       = 'up_create_test_sp'		--sp명 
    , @V_PARAM                NVARCHAR(100)       = N'v_from_date : ' + @v_from + N', v_to_date : ' + @v_to		--parameter
    , @V_ROW_CNT_I            INT                 = NULL								--insert cnt
    , @V_ROW_CNT_U            INT                 = NULL								--update cnt
    , @V_ROW_CNT_D            INT                 = NULL								--delete cnt
    , @V_START_TM             DATETIME2           = DATEADD(HOUR, 9, GETDATE())			--시작시간
    , @V_END_TM               DATETIME2           = NULL                                --종료시간
    , @V_ERR_MSG              NVARCHAR(1000)      = ''									--error message
    , @V_FROM_DATE            NVARCHAR(10)        = @v_from                             -- 추가로 활용할 수 있는 날짜변수
    , @V_TO_DATE              NVARCHAR(10)        = @v_to                               -- 추가로 활용할 수 있는 날짜변수

SET NOCOUNT ON

BEGIN TRY
/*-------------------------------------------------------------------------------------------------*/
-- 실행시간 기록 uf_datediff_time은 기록용 자체제작 함수 자세한 쿼리는 하기 추가내용 참조
DECLARE	@V_TIME_STEP_1 DATETIME2	= DATEADD(HOUR, 9, GETDATE())
PRINT 'STEP_01 : ' + [dbo].[uf_datediff_time](@V_TIME_STEP_1, DATEADD(HOUR, 9, GETDATE()));
DECLARE	@V_TIME_STEP_2	DATETIME2	= DATEADD(HOUR, 9, GETDATE())
/*-------------------------------------------------------------------------------------------------*/
        
/*--------------------------------------------------------------------------------------------------
  <STEP> 1. dbo.test_table 데이터 삭제 및 Insert
--------------------------------------------------------------------------------------------------*/
DELETE dbo.test_table WHERE period >= @v_from AND period <= @v_to
OPTION (LABEL = 'DELETE TEST')

SET @V_ROW_CNT_D = @@ROWCOUNT -- @@ROWCOUNT는 영향받은 행 수를 반환하는 명령문
-- @@ROWCOUNT를 활용하거나 추가 프로시저를 제작하여 활용해도됨. 
-- 예시 : EXEC [dbo].[GetRowCount] @Label = N'DELETE TEST', @RowCount = @V_ROW_CNT_D OUTPUT

INSERT INTO dbo.test_table
SELECT * FROM dbo.test_stg_table
OPTION (LABEL = 'INSERT TEST')

SET @V_ROW_CNT_I = @@ROWCOUNT -- @@ROWCOUNT는 영향받은 행 수를 반환하는 명령문
-- @@ROWCOUNT를 활용하거나 추가 프로시저를 제작하여 활용해도됨. 
-- 예시 : EXEC [dbo].[GetRowCount] @Label = N'INSERT TEST', @RowCount = @V_ROW_CNT_I OUTPUT

/*-------------------------------------------------------------------------------------------------*/
-- 실행시간 기록 uf_datediff_time은 기록용 자체제작 함수 자세한 쿼리는 하기 추가내용 참조
PRINT 'STEP_02 : ' + [dbo].[uf_datediff_time](@V_TIME_STEP_2, DATEADD(HOUR, 9, GETDATE()));
DECLARE	@V_TIME_STEP_3	DATETIME2	= DATEADD(HOUR, 9, GETDATE())
/*-------------------------------------------------------------------------------------------------*/

--<STEP> 2. 통계정보 갱신 (모든 배치작업 후에 Analyze 수행 - 테이블/파티션 레벨)
UPDATE STATISTICS dbo.test_table

--임시(stg)테이블 Truncate
TRUNCATE TABLE dbo.test_stg_table

--배치 종료시각
SET @V_END_TM    =  DATEADD(HOUR, 9, GETDATE()) 

--로그 적재
-- log_test_table은 자체적으로 상황에 맞게 제작
INSERT INTO dbo.log_test_table (sp_nm, param, start_tm, end_tm, rslt_cnt_i, rslt_cnt_u, rslt_cnt_d)
VALUES (@V_SP_NM, @V_PARAM, @V_START_TM, @V_END_TM, @V_ROW_CNT_I, @V_ROW_CNT_U, @V_ROW_CNT_D) ;

/*----------------------------------------------------------------------*/
-- 최종 실행 시간 출력
PRINT N'STEP_99 : ' + [dbo].[uf_datediff_time](@V_START_TM, @V_END_TM);
/*----------------------------------------------------------------------*/
   
END TRY  

BEGIN CATCH  
-- 오류 발생시 오류로그 기록
DECLARE  
    @ErrorMessage  NVARCHAR(4000),  
    @ErrorMessage2 NVARCHAR(4000),  
    @ErrorSeverity NVARCHAR(4000),    
    @ErrorState    NVARCHAR(4000);  

SELECT     
    @ErrorMessage  = ERROR_MESSAGE(),    
    @ErrorSeverity = ERROR_SEVERITY(),   
    @ErrorState    = ERROR_STATE();   

SET @ErrorMessage2 = 'Error : ' + @ErrorMessage   +  ' (' + @ErrorSeverity  + ', ' + @ErrorState  + ') '     

--배치 종료시각
SET @V_END_TM    =  DATEADD(HOUR, 9, GETDATE())

--로그 적재    
-- log_test_table은 자체적으로 상황에 맞게 제작
INSERT INTO dbo.log_test_table (sp_nm, param, start_tm, end_tm, rslt_cnt_i, rslt_cnt_u, rslt_cnt_d, rslt_msg)
VALUES (@V_SP_NM, @V_PARAM, @V_START_TM, @V_END_TM, @V_ROW_CNT_I, @V_ROW_CNT_U, @V_ROW_CNT_D, @ErrorMessage2) ;

RAISERROR (
    @ErrorMessage, -- Message nvarchar(4000).    
    @ErrorSeverity, -- Severity.    
    @ErrorState -- State.    
);    
    
END CATCH

/****** Object:  StoredProcedure [dbo].[GetRowCount] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetRowCount] @Label [varchar](300),@RowCount [bigint] OUT AS
/*-----------------------------------------------------------------------------------
최근 실행된 쿼리에 대한 @@ROWCOUNT 반환
-----------------------------------------------------------------------------------*/

BEGIN

DECLARE @RowCountTemp BIGINT = (
    SELECT  t1.row_count 
      FROM  (
                SELECT  TOP 1 step_index, row_count
                  FROM  sys.dm_pdw_request_steps
                 WHERE  row_count >= 0
                   AND  request_id IN (
                        SELECT  TOP 1 request_id
                          FROM  sys.dm_pdw_exec_requests
                         WHERE  1=1
                           AND  session_id = SESSION_ID()
                           AND  status <> 'Running'
                           AND  resource_class IS NOT NULL
                           AND  [Label] LIKE RTRIM(@Label) + '%'
                        ORDER BY submit_time DESC, request_id DESC
                     )
                 ORDER BY step_index DESC
      ) t1
)

SET @RowCount = ISNULL(@RowCountTemp, NULL)

END

/****** Object:  UserDefinedFunction [dbo].[uf_datediff_time] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[uf_datediff_time] (@V_START_TM [DATETIME2],@V_END_TM [DATETIME2]) RETURNS NVARCHAR(10)
AS
BEGIN 
--변수선언
DECLARE @V_TIME_OUT NVARCHAR(20) = NULL 
    
SET @V_TIME_OUT = 
  CONVERT(varchar(10), DATEDIFF(SS, @V_START_TM , @V_END_TM) / 3600) + N':'
+ RIGHT('0'+CONVERT(varchar(2),( DATEDIFF(SS, @V_START_TM , @V_END_TM) %3600) / 60),2) + N':'
+ RIGHT('0'+CONVERT(varchar(2), DATEDIFF(SS, @V_START_TM , @V_END_TM) %60),2) + N''
		
RETURN(@V_TIME_OUT) 
END

 

P.S 더 나은 개발자가 되기위해 공부중입니다. 잘못된 부분을 댓글로 남겨주시면 학습하는데 큰 도움이 될 거 같습니다.