제 8 장: 사용자 정의 테이블 데이터 형식

이번 강좌에서는 사용자 정의 테이블 데이터 형식(User Defined Table Data Type)에 대한 내용을 살펴보겠습니다. 이론적인 설명보다 다음의 예를 보시면 사용자 정의 테이블 데이터 형식이 어떤 용도로 사용될 수 있는지 알 수 있을것입니다. 1) 데이터베이스와 테이블 만들기 우선 다음과 같이 Sample 이라는 이름의 연습용 데이터베이스와 Member 테이블을 만들겠습니다. -- 연습용 데이터베이스 만들기    CREATE DATABASE Sample    GO    -- 테이블 만들기    USE Sample    GO    CREATE TABLE Member (     MemID varchar(10) PRIMARY KEY,     MemName nvarchar(10),     MemMail varchar(100)    )    GO   2) 회원 정보 입력하기 이러한 상황에서 우리는 일반적으로 다음과 같이 Member 테이블에 회원 정보를 기록하는 저장 프로시저를 만들어 사용을 합니다. -- 프로시저 만들기    CREATE PROCEDURE usp_InsertMember     @MemID varchar(10),     @MemName nvarchar(10),     @MemMail varchar(100)    AS    SET NOCOUNT ON    INSERT INTO Member(MemID, MemName, MemMail)       VALUES(@MemID, @MemName, @MemMail)     SET NOCOUNT OFF   GO   위와 같이 저장 프로시저를 만들고 다음과 같이 호출해서 회원 정보를 입력합니다. -- 프로시저 수행    EXECUTE usp_InsertMember 'jrlee', N'이장래', 'jrlee@sample.com'   EXECUTE usp_InsertMember 'hong', N'홍길동', 'gildong@sample.com'   EXECUTE usp_InsertMember 'han', N'한국인', 'han@sample.com'   GO    -- 확인    SELECT * FROM Member    GO    /*   [결과]   MemID   MemName   MemMail   ------- -------- ---------------------------------   han     한국인    han@sample.com   hong    홍길동    gildong@sample.com   jrlee   이장래    jrlee@sample.com   (3개 행이 영향을 받음)   */   자, 이제 사용자 정의 테이블 데이터 형식을 사용하는 예를 보도록 하겠습니다. 3) 새로운 테이블 만들기 사용자 정의 테이블 데이터 형식 사용예를 보기 위해 앞에서 사용한 Member 테이블과 같은 구조의 Member2 테이블을 만들어 사용하겠습니다. -- 테이블 만들기    CREATE TABLE Member2 (     MemID varchar(10) PRIMARY KEY,     MemName nvarchar(10),     MemMail varchar(100)    )    GO   4) 사용자 정의 테이블 데이터 형식 만들기 사용자 정의 테이블 데이터 형식은 다음과 같이 CREATE TYPE 문을 사용해 만듭니다. 일반적인 테이블을 만드는 방법과 거의 유사하게 만드는 것을 알 수 있습니다. -- 사용자 정의 테이블 데이터 형식 만들기    CREATE TYPE MemberTableType AS TABLE (     MemID varchar(10),      MemName nvarchar(10),      MemMail varchar(100)    )    GO   5) 저장 프로시저 만들기 앞의 단계에서 만든 사용자 정의 테이블 데이터 형식을 사용하는 저장 프로시저를 만듭니다. 매개변수에 @MemList MemberTableType 처럼 앞의 단계에서 만든 사용자 정의 테이블 데이터 형식을 갖는 매개 변수를 선언합니다. -- 저장 프로시저 만들기    CREATE PROCEDURE usp_InsertMember2        @MemList MemberTableType READONLY  -- READONLY 속성을 필히 지정해야 함    AS         SET NOCOUNT ON       INSERT INTO Member2(MemID, MemName, MemMail)            SELECT * FROM  @MemList    SET NOCOUNT ON   GO   6) 회원 정보 입력하기 앞의 단계에서 만든 저장 프로시저를 사용해 Member2 테이블에 회원 정보를 입력할 때 이제 다음과 같이 하면 됩니다. 앞에서 만든  MemberTableType 사용자 정의 테이블 데이터 형식을 갖는 변수 @MemberTVP를 선언하고 이 변수에 입력하고자 하는 회원 정보를 입력합니다. 그리고 @MemberTVP 변수를 저장 프로시저의 매개변수로 전달합니다. 이렇게 되면 @MemberTVP 변수에 입력된 여러 회원 정보가 저장 프로시저에 한번에 전달되고, 이 전달된 회원들 정보는 저장 프로시저에 의해 Member2 테이블에 입력됩니다. 결국 저장 프로시저는 한번만 호출된 결과를 가져옵니다. -- 프로시저 수행 예 (1)    DECLARE @MemberTVP AS MemberTableType    INSERT INTO @MemberTVP (MemID, MemName, MemMail)      VALUES('jang', N'이장군', 'jgoon@sample.com')    INSERT INTO @MemberTVP (MemID, MemName, MemMail)      VALUES('jane', N'박제인', 'jane@sample.com')    INSERT INTO @MemberTVP (MemID, MemName, MemMail)      VALUES('tomy', N'정토미', 'jtomy@sample.com')    EXEC usp_InsertMember2 @MemberTVP;    GO    -- 확인    SELECT * FROM Member2    GO    /*   [결과]   MemID  MemName  MemMail   ------ -------- ---------------------------------   jane   박제인    jane@sample.com   jang   이장군    jgoon@sample.com   tomy   정토미    jtomy@sample.com   (3개 행이 영향을 받음)   */   다음과 같은 수행도 가능합니다. 맨 앞에서 살펴본 일반적인 예에서 Member 테이블에 입력된 3명의 회원 정보를 Member2 테이블에 입력하고 싶으면 다음과 같이 수행하면 됩니다. -- 프로시저 수행 예(2)    DECLARE @MemberTVP AS MemberTableType;    INSERT INTO @MemberTVP (MemID, MemName, MemMail)      SELECT * FROM Member    EXEC usp_InsertMember2 @MemberTVP   GO    -- 확인    SELECT * FROM Member2     GO    /*   [결과]   MemID   MemName  MemMail   ------- -------- ---------------------------------   hong    홍길동    gildong@sample.com   jane    박제인    jane@sample.com   jang    이장군    jgoon@sample.com   jrlee   이장래    jrlee@sample.com   tomy    정토미    jtomy@sample.com   (5개 행이 영향을 받음)   */     이처럼 SQL Server 2008에 추가된 사용자 정의 테이블 데이터 형식은 행 집합을 저장 프로시저에 전달할 수 있어 매우 유용하게 사용될 수 있습니다. 단, 한번 만들어져서 사용 중인 사용자 정의 테이블 데이터 형식를 바꾸기 힘들고 제거하기도 힘듭니다. 물론 방법이 있지만 번거로운 작업이 됩니다. 그러므로 명확한 분석을 기반으로 디자인 되어야 합니다.