서론
SQL 언어에서는 배열의 개념이 없습니다.
때문에 배열로 처리하여야 할 경우에 제약사항이 생기게 됩니다.
특히나 프로시저를 작성할 때 배열에 익숙한 저로써는 배열 없이 무언가를 작성할 때 에로사항이 많이 생기곤 합니다.
이럴 때, 문자열의 데이터를 배열과 같은 개념으로 변환하여 각각의 행에 대한 특정 명령을 수행하도록 쿼리를 작성해봅시다.
간단 예시
declare @a nvarchar(max)
set @a = '1001,1002,1003'- '1001,1002,1003'의 값을 갖는 문자열 데이터
모든 값 출력하기
1002의 값만 출력하기
문자열 데이터를 행으로 변환하기
문자열 데이터를 구분자를 이용해 분리하고, XML 형식으로 변환하여 행으로 다룰 수 있도록 가공합니다.
문자열 데이터를 XML로 변환
- 원래의 문자열 데이터 : "1001,1002,1003"
-- 문자열 데이터를 저장합니다.
declare @a nvarchar(max)
set @a = '1001,1002,1003'
- 문자열을 XML 형식으로 변환
- 시작 부분 : <root> <node> 의 형식으로 XML 형식을 구성해줍니다.
- 중간 부분(노드 정의) : 문자열의 구분자를 노드의 이름에 맞게 XML 형식으로 변경해줍니다.
- 끝 부분 : XML 형식의 마지막은 구성한 루트와 노드의 이름으로 닫아줍니다.
-- 문자열을 XML 형식으로 변환합니다.
declare @xml xml
set @xml = N'<root><item>'
+ replace(@a, ',', '</item><item>') -- @a = '1001,1002,1003'
+ N'</item></root>'
- 위에서 변경된 XML 형식은 아래와 같습니다.
<!-- 이해를 돕기 위한 XML 코드 구성 -->
<root>
<item>1001</item>
<item>1002</item>
<item>1003</item>
</root>XML 형식의 데이터를 행 단위로 결과 화면에 출력하기
XML 형식으로 변환된 문자열을 행으로 출력할 수 있도록 SELECT 문을 작성합니다.
SELECT
요소명.value('경로', '데이터타입')
FROM
@xml.nodes('경로')- 요소명 : FROM 절에서 지정한 경로에서 요소의 값을 불러옵니다.
- value('경로', '데이터타입') : 특정 노드의 값을 읽어오기 위한 함수입니다. FROM 절에서 지정한 경로에서 반환될 요소의 경로와 데이터타입을 지정해줍니다.
<root><item> 구조의 XML 형식에서의 데이터 호출
- FROM 절에서 AS를 통해 사용하기 쉽게 별칭을 정의해줍니다.
- SELECT 절에서 정의한 별칭을 이용하여 해당 컬럼 (예시에서는 x.num)에 값을 표시합니다.
- 값을 표시할 때에는 경로와 데이터타입을 지정하여 불러야합니다.
-- @xml : XML 형식의 문자열 데이터를 저장하고 있는 XML 타입의 변수
-- XML 형식: <root><item>1001</item><item>1002</item><item>1003</item></root
/* XML 데이터를 행으로 표현 */
SELECT
x.num.value('.','int') AS number -- ('.'는 현재 경로를 의미)
FROM
@xml.nodes('/root/item') AS x(num)XML 형식의 문자열 데이터 쿼리 수행하기
커서를 이용하여 한 줄마다 행을 읽어들여서 쿼리를 실행하도록 합니다.
커서에서는 위에서 작성한 행 단위로 출력된 XML 데이터를 이용합니다.
- 커서 선언
/* 커서 선언 */
-- DECLARE 커서이름 CURSOR FOR
-- 읽을 데이터
-- 커서를 선언하고 XML에서 데이터 추출
declare cursor_test cursor for
select x.num.value('.', 'int') as number from @xml.nodes('/root/item') as x(num)
- 커서 실행
- 이 부분은 커서에 대한 설명을 제외하면 따로 설명할 것이 없습니다.
- XML의 형식을 테이블 형식으로 변경하여 커서에서 한줄씩 읽도록 합니다.
- while @@fetch_status = 0 begin ... end 사이에 한줄씩 수행할 쿼리를 작성합니다.
-- 커서 열기
open cursor_test
-- 커서에서 값을 저장할 변수 선언
declare @var_num int
-- 커서에서 데이터를 한 행씩 읽어오고, 읽어온 데이터는 변수에 저장
fetch next from cursor_test into @var_num
-- 커서에서 데이터를 모두 읽을 때까지 반복
while @@fetch_status = 0
begin
/* 한줄 한줄에 대한 수행 쿼리 작성 */
-- 예시1) 읽어들인 행 출력하기
print @var_num
-- 예시2) 특정 값에 대한 작업 수행하기 : 특정 값 결과셋 출력
if @var_num = 1002
select @var_num as '내가 찾던 값'
-- 다음 행으로 이동합니다.
fetch next from cursor_test into @var_num
end
-- 커서 닫기
close cursor_test
-- 커서 자원 할당 해제
deallocate cursor_test
- 예시1 결과 화면
- 예시2 결과 화면
전체 쿼리
-- 문자열 데이터를 저장합니다.
declare @a nvarchar(max)
set @a = '1001,1002,1003'
-- 문자열을 XML 형식으로 변환합니다.
declare @xml xml
set @xml = N'<root><item>' + replace(@a, ',', '</item><item>') + N'</item></root>'
-- 커서를 선언하고 XML에서 데이터를 추출합니다.
declare cursor_test cursor for
select x.num.value('.', 'int') as number from @xml.nodes('/root/item') as x(num)
-- 커서를 엽니다.
open cursor_test
-- 변수를 선언하여 커서에서 값을 저장할 준비를 합니다.
declare @var_num int
-- 커서에서 데이터를 한 행씩 읽어옵니다.
fetch next from cursor_test into @var_num
-- 커서에서 데이터를 모두 읽을 때까지 반복합니다.
while @@fetch_status = 0
begin
-- 한줄 한줄에 대한 수행 쿼리 작성
-- 예시1) 읽어들인 행 출력하기
print @var_num
-- 예시2) 특정 값에 대한 작업 수행하기 : 특정 값 결과셋 출력
if @var_num = 1002
select @var_num as '내가 찾던 값'
-- 다음 행으로 이동합니다.
fetch next from cursor_test into @var_num
end
-- 커서를 닫습니다.
close cursor_test
-- 커서를 해제합니다.
deallocate cursor_test