저장 프로시저 및 함수의 마법 깨트리기 #1

 

저장 프로시저 및 함수의 마법 깨트리기

Herts Chen

본 자료는 OPENQUERY를 사용하여 UDF 및 SQL 문에서 저장 프로시저를 호출할 수 없는 문제를 극복하는 방법을 다룬 Herts Chen의 세 개의 시리즈 기사 중 첫 번째입니다. 이 자료에서는 OPENQUERY의 한계 중 하나에 대해 다루며 OPENQUERY가 그러한 한계를 극복하도록 하는 방법을 제시합니다.

RDBMS의 장점 중 하나는 기본이 되는 집합 기반 쿼리 언어가 SQL이라는 것입니다. 쿼리는 항상 연속되는 다량의 T-SQL 스크립트에서 실행해야 하는 경우보다는 단일 SQL 문으로 표시할 수 있을 경우 더 간결하고 더 잘 최적화됩니다. 예를 들면 레코드가 50만개인 200MB 테이블이 있다고 가정해 봅시다. 단 한 줄의 UPDATE 문으로 이 테이블 전체에서 인덱스되지 않은 열을 업데이트하려면 약 2분정도 걸릴 것입니다. 하지만 WHILE 루프에서 50만 개의 UPDATE 문을 배포하여 동일한 변경을 실행하면 거의 세 시간이 걸릴 것입니다. 하지만 단일한 SQL 문에서 이 결과를 유도해 내기가 불가능한 상황이나 “사용 사례”가 있습니다. 그러한 경우에 대한 한 가지 대안이 이러한 사용 사례의 순서 설명을 저장 프로시저 또는 사용자 정의 함수(UDF)로 캡슐화하는 것입니다.

물론, UDF는 SQL 문 내부에 직접 포함될 수 있습니다. 어떤 점에서는 SQL이 순서 설명을 필요한 만큼 모듈화하는데 함수가 도움이 되므로 함수가 SQL의 표현력을 높여줍니다. 하지만 저장 프로시저는 SQL 문으로는 표현될 수 없으므로 SQL 문 코딩의 관점에서 보면 함수가 좀더 유용한 도구라고 생각됩니다.

하지만 함수는 프로시저를 완벽하게 대체할 수 없습니다. 사실, SQL Server는 프로시저보다 함수에 대해 보다 엄격하게 제한을 둡니다. 예를 들면, 함수에서는 테이블을 업데이트한다든가 하는 글로벌 데이터베이스 상태 변경을 실행할 수 없습니다. 이 외에도 SQL Server는 시스템 정보를 시스템 함수(예: user_name())보다는 저장 프로시저(예: sp_who)로 더 많이 패키지화합니다. SQL 2000 마스터 데이터베이스에서 프로시저와 함수를 놓고 보면, 우리가 생각해낼 수 있는 모든 시스템 정보를 SELECT, UPDATE, INSERT 또는 DELETE하는데 도움이 되는 저장 프로시저는 973개나 되는 반면 구성, 메타데이터, 보안, 시스템 및 시스템 통계 정보를 사용자가 선택(SELECT)은 할 수 있지만 변경은 할 수 없는 함수는 101개 뿐입니다.

시스템 정보를 단일한 SELECT 문으로 캡슐화할 수 있었으면 하고 바라는 경우가 종종 있을 것입니다. 예를 들면, "SELECT spid, login, BlkBy FROM sp_who2 WHERE spid > 50"을 사용하여 사용자 프로세스의 차단 정보만을 볼 수 있다면 좋을 것입니다. 아니면, 단일한 INSERT, UPDATE 또는 DELETE 문으로 데이터베이스 상태에 복잡한 변경을 실행할 수 있었으면 하고 바랄 때도 있을 것입니다. 하지만 안타깝게도 이러한 환상은 절대 실현되지 못할 것입니다. 최소한, SQL 또는 SQL에 포함된 함수 내에서 저장 프로시저를 호출할 수 있게 되기 전까지는 말합니다. SQL Server는 현재 함수 내에서 저장 프로시저를 호출하거나 테이블을 업데이트하는 것을 금지하고 있습니다. 이 제한때문에 우리는 시스템 정보를 사용하거나 단일 SQL 문에서 복잡한 변경을 실행할 수 없습니다. 본 자료는 함수와 프로시저에 걸린 “마법”을 깨뜨려 SQL 문에서 이 둘을 최대환 활용할 수 있도록 하기 위한 의도로 만들어진 시리즈 자료 중 첫 번째입니다.

집합 기반 쿼리

결과 집합처럼 보이는 출력을 반환하는 DBCC 명령 또는 저장 프로시저(sp’s)가 많으며, 이러한 출력이 다른 결과 집합과 직접 조인이 가능하다면 좋을 것입니다. 예를 들어, 이러한 결과 집합을 프로그래밍 방식으로 액세스하여 시스템 관리 작업을 자동화하거나 한 응용 프로그램을 위해 여러 소스에서 데이터를 통합할 수 있다면 좋을 것입니다. DBCC 또는 sp의 다수의 결과 집합을 하나의 SQL 문으로 조인할 수 없고 DBCC 또는 sp에 대해 커서를 선언할 수 없다면 유일한 옵션은 다수의 임시 테이블을 사용하는 것 뿐입니다. 게다가, 이 마지막 방법 역시 DBCC 또는 sp 결과 집합의 모든 열의 데이터 유형을 알고 있어서 임시 테이블을 만들어 DBCC 또는 sp 결과 집합을 그 임시 테이블에 넣을 수 있어야만 가능한 방법입니다. (SELECT INTO를 사용하여 DBCC 또는 sp 결과 집합에서 임시 테이블을 “몰래” 만들수는 없기 때문입니다.) DBCC 또는 sp의 결과 집합이 문서화(예: sp_who2)되지 않았으며 그 스냅샷 중 하나만을 기초로 임시 테이블을 만들면 이후 스냅샷이 더 긴 문자열이나 다른 데이터 형식을 반환할 경우 이 임시 테이블은 손상됩니다. 목록 1에는 먼저 그 필요성은 절실함에도 불구하고 지원은 되지 않는, 두 sp의 결과 집합을 조인하는 집합 기반 SQL 문을 제시하였습니다. 하지만 “최후의 수단”인, 임시 테이블에 의존하는 방법으로 눈길을 돌리지 않을 수 없었습니다.

목록 1. 집합 기반 쿼리와 프로시저 스크립트 비교

--set-based query:
--a much needed, but unsupported, SQL
select * from (Exec sp_who) w inner join 
(Exec sp_lock) l on w.spid = l.spid
where w.spid > 50 and w.blk <> 0

--a batch of scripts:
--temporary tables must be created first before insert
create table #who(spid smallint,ecid smallint,status 
nchar(30),loginame nchar(128),
hostname nchar(128),blk char(5), dbname nchar(128),
cmd nchar(16))
create table #lock(spid smallint,dbid smallint,
ObjId int,IndId smallint,Type nchar(4), 
Resource nchar(16),Mode nvarchar(8),Status nvarchar(5))
insert #who
Exec sp_who
insert #lock
Exec sp_lock
select * from #who w inner join #lock l on w.spid = l.spid
where w.spid > 50 and w.blk <> 0
drop table #who
drop table #lock

여기서 보는 것처럼 SQL 문이 일련의 스크립트보다 훨씬 더 간단하고 효율적입니다. 하지만 시스템 상의 한계때문에 쿼리가 다른 쿼리의 중간 결과에 의지하고 순차적 단계로 나누어져야 할 수도 있습니다. 이러한 한계 중 하나가 SQL 문의 어디서도 DBCC 명령이나 저장 프로시저를 직접 호출할 수 없다는 것입니다. 다음 단락에서는 아주 일반적인 몇몇 시나리오를 살펴 보겠습니다.

사용 사례 1: 프로세스 저장 프로시저 및 DBCC 차단
이 사례는 Kalen Delaney의 저서 Inside Microsoft SQL Server 2000의 926페이지에서 발췌한 것입니다. 새 응용 프로그램이 연결되지 않거나 기존 응용 프로그램이 진행되지 않거나 또는 Enterprise Manager(EM)의 일부 또는 전체가 중단되면 시스템 비상 사태가 발생합니다. 응용 프로그램 “중단”은 대형 프로덕션 SQL Server의 경우에도 그리 드문 일이 아닙니다. 일반적으로 차단이 그 원인이므로 EM을 사용하여 차단/차단된 프로세스와 잠금/잠긴 개체를 찾을 수 있습니다. 하지만 EM이 중단되면 어떻게 해야 할까요? 아니면 차단 체인의 문제가 너무 복잡해서 비상 시 수동으로 신속하게 해결할 수 없는 경우에는 어떻게 해야 할까요? 다행스럽게도 응용 프로그램이 중단되었다고 해도 일반적으로 SQL Server 시스템이 중단된 것을 뜻하지는 않습니다. 따라서 여전히 Query Analyzer와 같은 다른 응용 프로그램에 연결할 수 있습니다. 게다가 SQL Server는 각각 sysprocesses와 syslockinfo 테이블에서 그 소스보다 훨씬 더 읽기 쉬운 형식으로 주요 프로세스와 잠금 정보를 추출하는 sp_who2와 sp_lock를 제공합니다. 심지어는 DBCC INPUTBUFFER(spid)를 사용하면 차단 프로세스가 실행한 마지막 SQL 일괄 처리도 찾아낼 수 있습니다. 여기에서 spid는 그 차단 프로세스의 ID입니다.

따라서 쿼리 분석기에서 대화형으로 SQL 문을 입력하고 차단 프로세스, 차단 응용 프로그램 호스트, 잠긴 개체, 그리고 마지막 SQL 일괄 처리의 세부 사항만 신속하게 검색할 수 있다면 그보다 더 좋은 일이 있겠습니까? 비상 시에 이 결과 집합은 어떤 프로세스에 통지를 하고 어떤 프로세스를 중단할 지에 대한 정확한 결정을 내리는데 큰 도움이 됩니다. 장기적인 관점에서 보면 이 결과 집합은 그 응용 프로그램의 잠금 동작을 새롭게 변형하는 데에도 도움이 됩니다. 다음은 그러한 이상적인 SQL 문입니다. sp_who2의 BlkBy 열에는 차단 프로세스의 spid가 기록된다는 점에 유의하십시오. 프로세스가 차단되지 않았으면 그 BlkBy 열 값은 spid 대신 " . "가 됩니다. 따라서 차단 체인의 근원은 BlkBy는 " . "이지만 spid가 최소한 하나 이상의 BlkBY에 표시되어 있는 프로세스입니다.

select blocking.*, l.*, i.*
from (exec sp_who2) blocking inner join 
(Exec sp_lock) l 
on l.spid = blocking.spid inner join 
(DBCC INPUTBUFFER(all)) i
on l.spid = i.spid
where blocking.BlkBy like '%.%'
and blocking.spid in
(select CAST(blocked.BlkBy as int) from (exec sp_who2)
blocked where blocked.BlkBy not like '%.%')

사용 사례 2: 확장 저장 프로시저
확장 저장 프로시저(이하 xp’s)는 사용자 지정 기능에 “플러그인”하여 SQL Server를 “확장”하도록 SQL Server가 허용하는 몇몇 방법 중 하나입니다. 예를 들면, 전체적으로 또는 부분적으로 범람원에서 50피트 내에 있는 세금 구역의 결과 집합을 반환할 수 있는 xp_intersects라는 xp를 만들었습니다. 제가 xp_intersects를 보험 테이블, 허가증 테이블, 소유자 테이블 및/또는 시장 가치 테이블에 임의로 손쉽게 조인할 수 있다면 이러한 xp는 SQL Server를 위성 정보 시스템(GIS)과 같은 수직적 응용 프로그램의 영역으로 밀어넣는 좋은 예가 될 것입니다. 다음 SQL 문은 SQL Server를 확장하는 단순하면서도 우수한 방법입니다.

SELECT t.taxlot_id, i.*, v.*
From (Exec xp_intersects('polygon_100_year_flood', 
'taxlots', 50)) t inner join insurance i on t.taxlot_id = 
i.taxlot_id inner join market_value v on
t.taxlot_id = v.taxlot_id 

사용 사례 3: XML 데이터 소스
XML은 빠르게 사실상의 데이터 교환 표준이 되었을 뿐만 아니라 직접적인 데이터 소스로도 유용합니다. 예를 들면, 공급 업체는 HTTP를 통해 XML 문자열로 구매 주문을 받을 수 있습니다. 이 XML 문자열은 제품 ID와 수량을 인코딩할 것입니다. 공급자의 응용 프로그램이 이 XML 문자열을 구입 제품이 들어 있는 테이블처럼 처리하여 그 공급자의 카탈로그 및 저장소 테이블과 조인시켜 제품 선적이 자동으로 처리되도록 하면 간편할 것입니다. SQL Server 2000의 새 행집합 공급자 OPENXML은 XML 문서를 통해 결과 집합을 반환할 수 있다는 것을 알고 있을 것입니다. 하지만 OPENXML을 사용할 경우 이 구매 주문 XML 문자열이 다른 테이블에 조인하려면 다음 세 단계를 거쳐야만 합니다.

-- parameter @XMLDoc varchar(8000) contains XML string
DECLARE @iDoc int
Exec sp_xml_preparedocument @iDoc OUTPUT, @XMLDoc
SELECT * FROM OpenXML(@iDoc, '/ROOT/Order',0) WITH
(ProductID int, Quantity int) o inner join catalog c
on o.ProductID = c.ProductID inner join warehouse w
on c.WarehouseID = w.WarehouseID
Exec sp_xml_removedocument @iDoc

sp_xml_preparedocument와 sp_xml_removedocument는 각각 메모리에서 XML 문자열의 구문을 검색하고 그 메모리를 다시 할당하는 xp's입니다.

XML 단계를 sp_OpenXML와 같은 프로시저로 그룹화하여 이러한 단계를 단일한 SQL 문으로 코드화할 수 있다면 좋지 않겠습니까?

-- parameter @XMLDoc varchar(8000) 
SELECT *
FROM (Exec sp_OpenXML(@XMLDoc) o inner join catalog c
on o.ProductID = c.ProductID inner join warehouse w
on c.WarehouseID = w.WarehouseID)

집합 기반 접근법

집합 기반 프로세스의 기능을 완벽하게 활용하기 위해서는 지금까지 설명한 사용 사례에 맞는 솔루션을 만드는데 도움이 되는 방법이나 도구를 찾아야 합니다. 이 도구는 SQL 문에 사용될 수 있어야 하고 sp, xp, OPENXML 및 DBCC 명령에서 직접 결과 집합을 반환할 수 있어야 합니다. 본 자료의 나머지 부분에서는 가능성 있는 두 도구를 살펴보고 어떤 도구가 위의 두 가지 요구 사항을 모두 충족시키는지 알아 보겠습니다.

UDF는 언뜻 보면 논리적인 선택으로 여겨질 수 있습니다. 사실상 UDF는 SQL 문에 사용할 수 있는 유일한 사용자 확장입니다. 게다가 UDF는 테이블 변수나 스칼라 변수 중 하나를 반환할 수 있습니다. 예를 들어 세 번째 사용 사례를 충족시키려면 다음과 같은 실행을 하고 싶어질 것입니다.

--Function that encapsulates xp's and OPENXML
--creation of function udf_xml succeeds
Create function udf_xml(@XMLDoc varchar(8000))
Returns @t table(ProductID int, Quantity int)
as
begin
declare @iDoc int
Exec sp_xml_preparedocument @iDoc OUTPUT, @XMLDoc
INSERT @t
SELECT * FROM OPENXML(@idoc, '/ROOT/Order',0) 
  WITH (ProductID int, Quantity  int)
Exec sp_xml_removedocument @iDoc
return
end
GO
--now join result set of udf_xml() in a SQL statement
--execution of function udf_xml fails
DECLARE @XMLDoc varchar(8000)
set @XMLDoc =
'<ROOT>
  <Order ProductID="11" Quantity="12"/>
  <Order ProductID="42" Quantity="10"/>
  <Order ProductID="72" Quantity="3"/>
</ROOT>'
select * from udf_xml(@XMLDoc) o inner join catalog c
on o.ProductID = c.ProductID inner join warehouse w
on c.WarehouseID = w.WarehouseID
GO

-- Error message output
Server: Msg 557, Level 16, State 2, 
Procedure udf_xml, Line 6
Only functions and extended stored procedures 
can be executed from within a function.

udf_xml은 XML 결과 집합을 반환하는 세 단계를 성공적으로 캡슐화하지만(그리고 조인 SQL 문도 아주 간단하지만) udf_xml 실행은 실패합니다. 사실, 함수 자체만으로는 여기 제시된 어떤 사용 사례도 해결할 수 없습니다. 함수에는 다음과 같은 제약이 따르기 때문입니다.

• 함수는 저장 프로시저를 실행할 수 없습니다. 함수는 함수와 일부 xp's는 실행할 수 있지만 sp's는 실행할 수 없습니다. 이러한 한계 때문에 함수는 사용 사례 1과 3을 해결할 수 있는 후보에서 즉시 탈락됩니다.
• 함수는 임시 테이블 또는 다른 어떤 형태의 테이블도 만들 수 없습니다. xp 결과 집합을 반환하려면 함수는 그 결과 집합을 어딘가에는 저장할 수 있어야 합니다. 테이블이 가장 일반적인 선택 방법입니다. 하지만 안타깝게도 함수는 글로벌 데이터베이스 상태를 변경할 수 없습니다. 그 때문에 글로벌 데이터베이스 상태를 변경할 수 있는 임시 또는 영구 테이블을 만드는 것도 제한됩니다.
• 함수는 임시 테이블을 액세스할 수 없습니다. 글로벌 임시 테이블이 미리 만들어져 있다 하더라도 SQL Server는 여전히 함수에서 그 테이블이나 기존의 다른 임시 테이블로의 데이터 삽입을 허용하지 않습니다.
• 함수는 기존 테이블에 삽입을 할 수 없습니다. 함수 외부에 영구 테이블을 만들면 어떨까라는 생각도 할 수 있습니다. 하지만 함수에서 기존 영구 테이블로의 삽입 역시 글로벌 데이터베이스 상태를 변경하는 것으로 간주됩니다.
• 기존 DBCC, sp 또는 xp 실행 결과 집합을 테이블 변수에 삽입할 수 없습니다. 테이블 변수를 사용하여 결과 집합을 저장하는 방법은 어떨까요? 안타깝게도 EXECUTE를 사용하여 테이블 변수에 데이터를 삽입할 수 없습니다. 하지만 EXECUTE는 함수 내에서 DBCC, sp 및 xp가 포함되는 SQL 문자열을 발행할 수 있는 유일한 방법입니다. 이 마지막 한계에 다다르자 우리는 UDF는 위 사용 사례에 대한 해답이 아니라는 결론을 내리지 않을 수 없었습니다.

마지막 방법 OPENQUERY

제 조사에 따르면 OPENQUERY가 SQL 문에서 sp, xp 및 DBCC 결과 집합을 반환하는 유일한 방법으로 여겨집니다. 하지만 OPENQUERY 역시 완벽하지는 않습니다. 예를 들면, OPENQUERY를 사용하면 다음 예에서 볼 수 있는 것처럼 sp_who 결과 집합은 손쉽게 반환할 수 있지만 sp_who2 결과 집합은 반환할 수 없습니다.

--openquery returns the result set of sp_who just fine
select * from openquery(csherts, 'Exec sp_who')
go
(14 row(s) affected)

--openquery finds no result set from sp_who2
select * from openquery(csherts, 'Exec sp_who2')
go
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Exec sp_who2'. 
The OLE DB provider 'SQLOLEDB' indicates that 
the object has no columns.

OPENQUERY는 SELECT 문의 FROM 절에 들어갈 수 있으므로 첫 번째 요구 사항은 만족시킵니다. OPENQUERY를 사용하면 UDF는 sp나 xp, OPENXML 또는 DBCC를 간접적으로 호출할 수 있습니다. FROM 절 외에도 함수는 SQL의 어디서나 사용될 수 있습니다. 두 번째 요구 사항의 경우에는, OPENQUERY는 일부 sp’s에 대해서만 실행되며 xp's나 DBCC와는 실행되지 안습니다. 사실, OPENQUERY가 입력 SQL 문자열을 기초로 한 열 이름과 출력 결과 집합 유형을 알 수 없으면 이 쿼리는 실패하며 “이 개체에는 열이 없습니다”라고 응답합니다. OPENXML의 경우 OPENQUERY는 다음 코드 블록의 예에서 볼 수 있는 것처럼 처음에는 모든 것이 잘 진행된다는 느낌을 줄 수 있습니다. 하지만 두 번째 예를 자세히 살펴 보면 OPENQUERY는 OPENXML에는 현실적이지 않다는 것을 알 수 있습니다. 이는, XML 문자열은 사실상 변수로 구성되는 경우가 많은데 OPENQUERY는 변수를 인수로 사용하지 않기 때문입니다.

그럼에도 불구하고 OPENQUERY와 UDF 조합은 위 세 사용 사례를 모두 지원할 수 있는 가능성이 있습니다. 다시 말하면 OPENQUERY만이 sp’s와 함수에 걸린 마법을 깰 수 있는 유일한 도구입니다. 본 시리즈의 다음(두 번째) 자료에서는 OPENQUERY의 동작을 자세히 설명하고 그 한계를 간단하게 요약한 다음 그러한 한계를 극복하는 방법을 제시하겠습니다. 그리고 OPENQUERY를 사용하여 본 자료에서 설명한 사용 사례를 구현해 보겠습니다.

-- Example 1 OPENQUERY works for OPENXML only if XML 
-- is a constant string
-- XML specified in the constant argument to OPENQUERY
select * from openquery(csherts,
'
declare @iDoc int
Exec sp_xml_preparedocument @iDoc OUTPUT, ''<ROOT>
      <Order ProductID="11" Quantity="12"/>
      <Order ProductID="42" Quantity="10"/>
      <Order ProductID="72" Quantity="3"/>
</ROOT>''
SELECT * FROM OPENXML(@idoc, ''/ROOT/Order'',0) 
              WITH (ProductID int, Quantity  int)
Exec sp_xml_removedocument @iDoc
')
go
-- Output
(3 row(s) affected)

--Example 2 XML concatenates with OPENXML steps in a 
--variable argument to OPENQUERY. Illustrates that 
--OPENQUERY does not accept variable arguments
DECLARE @XMLDoc varchar(8000)
set @XMLDoc =
'<ROOT>
  <Order ProductID="11" Quantity="12"/>
  <Order ProductID="42" Quantity="10"/>
  <Order ProductID="72" Quantity="3"/>
</ROOT>'
set @XMLDoc = '
declare @iDoc int
Exec sp_xml_preparedocument @iDoc OUTPUT, ''' +
 @XMLDoc + '''
SELECT * FROM OPENXML(@idoc, ''/ROOT/Order'',0) 
              WITH (ProductID int, Quantity  int)
Exec sp_xml_removedocument @iDoc
'
select * from openquery(csherts,@XMLDoc)
go
-- Output
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '@XMLDoc'.

[지난 호의 관련 자료: Andrew Zanevsky의 "Granting Users' Wishes with UDFs" (2000년 9월), "Inline Table-Valued Functions" (2000년 10월), "Multi-Statement Table-Valued Functions" (2000년 11월), "UDF Performance… or Lack of It" (Anton Jiline 공저, 2001년 10월). Tom Moreau의 "Dynamic DTS Tasks and OPENROWSET" (2001년 1월), "How Do You Feed an Array to a Stored Procedure?" (2002년 4월). Scott Whigham의 "How to Write your Own System Functions" (2001년 12월).—편집자 주.]

QUERY1.SQL (영문) 다운로드

이 글은 스프링노트에서 작성되었습니다.