SQL Server에서 글로벌 변수(Global Variable) 구현하기
<?XML:NAMESPACE PREFIX = O />
한대성
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) <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><?XML:NAMESPACE PREFIX = ST1 /><?xml:namespace prefix = st2 ns = "urn:schemas:contacts" /><?XML:NAMESPACE PREFIX = ST2 />문자형 값을 저장 ----------------------------------------------------------------------- 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 |
"쇼핑몰·홈페이지·오픈마켓
블로그·페이스북·이메일 등의 각종 마케팅 글쓰기, 각종 광고, 영업, 판매, 제안서, 전단지 반응율 3배×10배 이상 높이는 마법의 8단계 공식" |
|
| ☞자세히보기 |
|
|
|
