반응형
반응형

SQL Server에서 글로벌 변수(Global Variable) 구현하기

  

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

블로그에 재미있는 주제와 관련된 글이 올라와서 이를 활용해서 소개해 드립니다.

#TempTable 과 같이 테이블 명 앞에 #을 붙이게 되면 이 테이블의 정보는 현재 세션에서 계속 사용할 수 있습니다.

이와 비슷한 방식으로 저장 프로시저 또는 쿼리를 작성하다 보면 값이 저장된 변수를 재정의하지 않고 지속적으로 사용해야 할 경우가 종종 있습니다. 하지만, SQL에서 변수는 하나의 일괄 처리 내에서만 사용 가능합니다. 동일한 세션에서 정의되고 값이 지정되더라도 “GO”로 일괄 처리가 끝나게 되면 다시 변수를 정의해야 하며, 이미 지정한 값은 사라지게 됩니다.

DECLARE @GlobalVar int

SET @GlobalVar = 10

 

SELECT @GlobalVar

GO

 

SELECT @GlobalVar+1

GO

 

/*

-----------

10

(1개 행 적용됨)

 

메시지 137, 수준15, 상태 2, 줄 2

스칼라 변수"@GlobalVar"을(를) 선언해야 합니다.

*/

두 번째 SELECT 문에서 @GlobalVar 변수를 사용하려고 하지만, 이미 변수가 정의된 후, GO로 일괄처리가 끝났기 때문에 더 이상 @GlobalVar 변수는 존재하지 않으며 위와 같이 에러가 발생합니다.

 

현재의 세션에서 값을 유지하기 위한 방법으로 CONTEXT_INFO라는 기능을 이용할 수 있습니다. CONTEXT_INFO는 세션 별로 varbinary(128)의 값을 저장할 수 있는 기능이며, 세션 당 한 개가 존재합니다. SQL 2005에서는 sys.dm_exec_sessions, SQL 2000에서는 master.dbo.sysprocesses 테이블에서 CONTEXT_INFO 값을 확인할 수 있으며, 쿼리에서는 CONTEXT_INFO()로 값을 읽어올 수 있습니다.

 

예) CONTEXT_INFO를 사용하여 값 저장하기 구현

-----------------------------------------------------------------------

-- 1) 문자형 값을 저장

-----------------------------------------------------------------------

DECLARE @BinVar as varbinary(128)

SET @BinVar = CAST('ABCDEFG' AS VARBINARY(128))

SET CONTEXT_INFO @BinVar

GO

 

SELECT session_id, context_info FROM sys.dm_exec_sessions

WHERE session_id = @@spid

GO

/*

session_id context_info

---------- ------------------

51           0x41424344454647

*/

 

 

SELECT CAST(CONTEXT_INFO() AS CHAR)

GO

SELECT CAST(CONTEXT_INFO() AS CHAR)

GO

/*

------------------------------

ABCDEFG                      

 

------------------------------

ABCDEFG                       

*/

 

 

-----------------------------------------------------------------------

-- 2) 숫자형 값을 저장 (숫자를 문자형으로 변환한 후, 저장)

-----------------------------------------------------------------------

DECLARE @BinVar as varbinary(128)

SET @BinVar = CAST(CAST(123456 AS CHAR) AS VARBINARY(128))

SET CONTEXT_INFO @BinVar

GO

 

SELECT CAST(CAST(CONTEXT_INFO() AS CHAR) AS INT)

GO

SELECT CAST(CAST(CONTEXT_INFO() AS CHAR) AS INT) + 100000

GO

 

/*

123456

 

223456

*/

 

CONTEXT_INFO를 이용하는 방식이 간단하기는 하지만, 다음과 같은 한계가 있습니다.

A.      세션당 한 개만 사용할 수 있음

B.      VARBINARY(128) 데이터만 저장 가능. 다른 유형의 데이터는 변형해야 함

 

 

다음에 소개되는 스크립트는 이러한 점을 해결할 수 있는 방식입니다. 테이블과 이 테이블에 데이터를 입력하고 출력하는 저장 프로시저를 이용하는 방식입니다.

USE master

GO

 

IF OBJECT_ID('dbo.sp_GlobalVariables') IS NOT NULL

    DROP TABLE dbo.sp_GlobalVariables

GO

CREATE TABLE dbo.sp_GlobalVariables

(

    varName VARCHAR(100),

    varValue SQL_VARIANT

)

GO

 

IF OBJECT_ID('dbo.sp_GetGlobalVariableValue') IS NOT NULL

    DROP PROC dbo.sp_GetGlobalVariableValue

GO

CREATE PROC dbo.sp_GetGlobalVariableValue

(

    @varName VARCHAR(100),

    @varValue SQL_VARIANT = NULL OUTPUT

)

AS

    SET NOCOUNT ON   

    -- 출력 변수 설정

    SELECT    @varValue = varValue

    FROM    sp_globalVariables

    WHERE    varName = @varName

   

    -- 결과 셋 출력

    SELECT    varName, varValue

    FROM    sp_globalVariables

    WHERE    varName = @varName

    SET NOCOUNT OFF

GO

 

IF OBJECT_ID('dbo.sp_SetGlobalVariableValue') IS NOT NULL

    DROP PROC dbo.sp_SetGlobalVariableValue

GO

CREATE PROC dbo.sp_SetGlobalVariableValue

(

    @varName NVARCHAR(100),

    @varValue SQL_VARIANT,

    @result CHAR(1) = NULL OUTPUT

)

AS

    SET NOCOUNT ON

    UPDATE    dbo.sp_GlobalVariables

    SET        varValue = @varValue

    WHERE    varName = @varName;   

    -- 입력된 변수가 존재하지 않는 경우 추가

    IF @@rowcount = 0

    BEGIN

        INSERT INTO dbo.sp_GlobalVariables(varName, varValue)

        SELECT @varName, @varValue

        -- 추가된 상태를 통보(I)

        SELECT @result = 'I'

    END

    -- 업데이트 된 상태를 통보(U)

    SELECT @result = 'U'

    SET NOCOUNT OFF

GO

 

 

-----------------------------------------------------------------------

--사용 예

-----------------------------------------------------------------------

DECLARE @dt DATETIME

SELECT @dt = GETDATE()

EXEC sp_SetGlobalVariableValue 'GlobalDate', @dt;

EXEC sp_SetGlobalVariableValue 'GlobalInt', 5;

EXEC sp_SetGlobalVariableValue 'GlobalVarchar', 'Global variable'

EXEC sp_SetGlobalVariableValue 'GlobalBinary', 0x0012314;

GO

 

EXEC sp_GetGlobalVariableValue 'GlobalDate'

EXEC sp_GetGlobalVariableValue 'GlobalInt'

EXEC sp_GetGlobalVariableValue 'GlobalVarchar'

EXEC sp_GetGlobalVariableValue 'GlobalBinary'

GO

/*

GlobalDate                    2007-04-24 11:18:29.000

GlobalInt                     5

GlobalVarchar                  Global variable

GlobalBinary                   0x00012314

*/

 

-- master DB에서 값 업데이트 수행

EXEC sp_SetGlobalVariableValue 'GlobalVarchar', 'New varchar value'

GO

EXEC sp_GetGlobalVariableValue 'GlobalVarchar'

GO

/*

GlobalVarchar                  New varchar value

*/

 

 

USE AdventureWorks

GO

EXEC sp_GetGlobalVariableValue 'GlobalDate'

EXEC sp_GetGlobalVariableValue 'GlobalInt'

EXEC sp_GetGlobalVariableValue 'GlobalVarchar'

EXEC sp_GetGlobalVariableValue 'GlobalBinary'

GO

 

-- AdventureWorks DB에서 값 업데이트 수행

EXEC sp_SetGlobalVariableValue 'GlobalInt', 6

GO

 

EXEC sp_GetGlobalVariableValue 'GlobalDate'

EXEC sp_GetGlobalVariableValue 'GlobalInt'

EXEC sp_GetGlobalVariableValue 'GlobalVarchar'

EXEC sp_GetGlobalVariableValue 'GlobalBinary'

GO

위 프로시저는 데이터베이스에 상관없이 전역적으로 사용할 수 있으며, SQL_VARIANT로 변수 값을 저장하기 때문에 TEXT, NTEXT, IMAGE 형의 데이터를 제외하고는 모두 사용할 수 있습니다.

 

이러한 방식을 이용하면 다음과 같이 자식 프로시저들을 호출할 때에도 이용할 수 있습니다.

 

USE TEMPDB

GO

 

-- GlobalInt라는 변수의 값을 읽어서 10을 더하는 프로시저

IF OBJECT_ID('dbo.usp_addVal') IS NOT NULL

    DROP PROC dbo.usp_addVal

GO

 

CREATE PROC dbo.usp_addVal AS

BEGIN

        DECLARE @I AS SQL_VARIANT

        DECLARE @R AS INT

 

        --GlobalInt 라는 변수의 값을 읽어와서 @I에 저장

        EXEC sp_GetGlobalVariableValue 'GlobalInt', @I OUTPUT

 

        --GlobalInt 라는 변수값에 10을 더해서 다시 저장

        SET @R = CAST(@I AS INT) + 10

        EXEC sp_SetGlobalVariableValue 'GlobalInt', @R

 

END

GO

 

 

-- dbo.usp_addVal을 5번 호출하는 부모 프로시저

IF OBJECT_ID('dbo.parentProc') IS NOT NULL

    DROP PROC dbo.parentProc

GO

 

CREATE PROC parentProc AS

BEGIN

        SET NOCOUNT ON

        --GlobalInt 에 초기값 셋팅

        EXEC sp_SetGlobalVariableValue 'GlobalInt', 10

        --10 증가 5회

        EXEC usp_addVal

        EXEC usp_addVal

        EXEC usp_addVal

        EXEC usp_addVal

        EXEC usp_addVal

        --GlobalInt 값 출력

        EXEC sp_GetGlobalVariableValue 'GlobalInt'

        SET NOCOUNT OFF

END

GO

/*

varName         varValue

--------       -------------

GlobalInt      10

GlobalInt      20

GlobalInt      30

GlobalInt      40

GlobalInt      50

GlobalInt      60

*/

 

 

스크립트 참고 : http://weblogs.sqlteam.com/mladenp/archive/2007/04/23/60185.aspx

 

본 게시판에 실린 글은 누구나 복사하셔서 이용하셔도 되지만, 반드시 출처(SQLLeader.com) 및 링크를 밝혀주셔야 합니다.

 

반응형
저작자표시

'연구개발 > DBA' 카테고리의 다른 글

간단한 암호화 복호화  (0) 2009.07.29
vbs코드를 이용한 Database Scripting  (0) 2009.07.29
파생된 테이블 사용 시 발생하는 구문 오류  (0) 2009.07.29
5개 연속 행에 대한 연산 쿼리  (0) 2009.07.29
같은 그룹의 데이터를 붙여서 출력하기  (0) 2009.07.29

+ Recent posts

반응형

티스토리툴바