데이터베이스의 저장 프로시저를 자동으로 생성

ADVANCED T-SQL

데이터베이스의 저장 프로시저를 자동으로 생성

Peter W. DeBetta (영문)

andJ. Byer Hill (영문)

Level of Difficulty123

이 문서에 사용된 코드 다운로드: StoredProcedures.exe (108KB)

요약

디자인 타임 자동화는 코딩 속도를 빠르게 해주며 생성된 모든 프로시저가 동일한 명명 규칙과 구조를 사용하도록 보장합니다. 대규모 SQL 프로젝트에서 코딩의 효율성을 개선하려는 노력으로 저자는 런타임 저장 프로시저를 생성하는 일련의 디자인 타임 저장 프로시저를 작성했으며 이후의 프로젝트에서 이 프로시저를 사용했습니다. 최근 저자는 사용자 정의 함수를 비롯한 SQL Server 2000 기능을 활용하기 위해 자신의 프로시저를 업데이트했습니다. 이 기사에서는 공용 데이터베이스 저장 프로시저의 코딩을 자동화하기 위해 이러한 동적 T-SQL 스크립트를 만들고 실행하는 방법에 대해 설명합니다.

---

얼마 전 비교적 대규모인 n-계층 클라이언트/서버 프로젝트를 개발하기 시작했습니다. 초기 계획 중에, 데이터베이스의 수많은 테이블에 액세스하기 위한 일련의 방법이 필요하다는 결정을 내렸습니다. 각 테이블에 대해 선택, 삽입, 업데이트 및 레코드 삭제를 수행하기 위해 네 개의 기본 저장 프로시저가 사용되었습니다. 필요한 일련의 저장 프로시저는 디자인에서 유사할 수 있지만, 각 테이블의 고유한 열 구조로 인해 각 개별 프로시저의 중요한 세부 사항이 다르기 때문에 필요한 모든 프로시저를 작성하는 것은 매우 지루한 작업일 수 있습니다. 따라서 저장 프로시저 작성 프로세스를 반드시 자동화해야 했습니다.

실험적으로 출발했던 것이 일련의 핵심적인 디자인 타임 저장 프로시저가 되었습니다. 이 저장 프로시저는 해당 프로젝트에서 모든 데이터베이스 테이블에 대해 일련의 기본 런타임 저장 프로시저를 작성합니다. 이러한 디자인 타임 프로시저를 작성하고 사용함으로써, 지루한 시간을 단축했을 뿐만 아니라 고객의 비용도 감소하였고 현재도 여전히 사용되는 익숙한 코드를 만들었습니다.

디자인 타임 프로시저는 SQL Server 2000의 일부 기능 특히 사용자 정의 함수(UDF)를 활용하기 위해서 업데이트되었습니다. 이제 코드가 더 모듈화되고 다른 작업을 위해 추가적인 함수를 사용할 수 있습니다.

디자인 타임 자동화가 제공하는 또 다른 이점은 생성된 수백 개의 프로시저가 일관적으로 구성되며 표준 명명 규칙을 사용한다는 것입니다. 우리의 경우, 생성된 모든 런타임 프로시저 이름의 형식은 prApp_TableName_Task와 같습니다. 여기는 Task는 Select, Insert, Update 또는 Delete 중 하나가 될 수 있습니다. Customers 및 Orders 테이블의 프로시저는 다음과 유사합니다.

prApp_Customers_DeleteprApp_Customers_InsertprApp_Customers_SelectprApp_Customers_UpdateprApp_Orders_DeleteprApp_Orders_InsertprApp_Orders_SelectprApp_Orders_Update

여기서 알 수 있듯이 이 규칙은 데이터베이스의 구성을 상당히 향상시켜주며 임의의 프로시저를 쉽게 찾을 수 있고 각 프로시저 이름을 알아보기 쉽게 지정할 수 있습니다. 개발자가 신속하게 코드를 찾고 작성할 수 있습니다. 무엇보다도 나중에 들어온 프로젝트 구성원이 코드와 프로시저를 쉽게 이해하고 따라올 수 있습니다. 물론, 다른 명명 규칙이 이미 있는 경우에는 코드에서 몇 줄만 바꾸어 이 규칙을 대신 사용할 수 있습니다.

이 네 가지 디자인 타임 프로시저는 다른 프로젝트에서 템플릿으로 사용되도록 만들어졌습니다. 이들 프로시저는 프로젝트 데이터베이스에 설치되며 필요한 경우, 특정 응용 프로그램의 필요에 맞게 수정됩니다. 예를 들어, 여러 응용 프로그램에서 우리는 레코드가 수정될 때마다 추적 레코드를 별도의 데이터베이스에 유지하는 코드를 추가했습니다.

간단한 예제

시작하기에 앞서 먼저 Northwind 데이터베이스에서 Order_Details 테이블(이 테이블의 이름에 있는 공백이 밑줄로 대체)을 사용하는 간단한 예제를 살펴보겠습니다. 개체 이름에는 공백과 기타 문자가 허용되지만 이 자동화된 저장 프로시저를 사용할 때 문제를 막기 위해 개체 이름에는 일반 식별자를 사용하는 것이 좋습니다. 자세한 내용은 SQL Server 온라인 설명서의 "식별자 사용 (영문)"을 참조하십시오.

첫 번째 작업은 Order_Details 테이블의 데이터를 업데이트하는 런타임 프로시저를 만들기 위해 디자인 타임 프로시저를 실행하는 것입니다.

EXEC pr__SYS_MakeUpdateRecordProc 'Order_Details'

디자인 타임 프로시저를 실행하면 그림 1과 같은 T-SQL 스크립트가 출력으로 생성됩니다. 이 T-SQL 스크립트가 실행되면 Order_Details 테이블에 대해 새로운 Update 저장 프로시저가 만들어집니다. 모든 열은 새로운 저장 프로시저 코드에서 매개 변수가 됩니다. 하지만 다른 Non-key 필드는 Update 문의 Set 절에 일부분이 되는 반면, 기본 키 열(OrderID 및 ProductID)은 Where 절에서 어떻게 나타나는지를 살펴보십시오. 우리의 디자인 타임 프로시저에서는 SQL Server 시스템 테이블에 저장된 Order_Details 메타데이터를 검토했으며 이 정보를 사용하여 적절한 T-SQL 스크립트를 출력으로 만들었습니다. 그런 다음 이 스크립트를 실행하면 최종 런타임 프로시저가 만들어집니다.

이 실행은 출력만을 생성할 뿐이지 새로운 런타임 프로시저를 생성하지는 않습니다. 하지만 간단한 수정을 하면 디자인 타임 프로시저는 최종 단계에서 생성된 T-SQL 스크립트를 실제로 실행할 수 있습니다. 이를 위해 우리는 디자인 타임 프로시저를 실행하고 두 번째 옵션 매개 변수의 비트 플래그로 값 1을 전달하고 이 프로시저를 다시 실행합니다.

EXEC pr__SYS_MakeUpdateRecordProc 'Order_Details', 1

그러면 이전과 같이 출력을 표시할 뿐만 아니라 이 출력을 실행하여 런타임 프로시저를 만듭니다.

이제 이 새로운 응용 프로그램별 런타임 저장 프로시저를 만들었던 디자인 타임 프로시저 코드에 대해 살펴보겠습니다.

SQL Server 시스템 테이블 및 뷰

이 디자인 타임 저장 프로시저를 만들기 위해서는 테이블의 열 정의 정보를 SQL Server 시스템 테이블 및 정보 스키마 뷰에서 가져오는 방법을 알아야 합니다. 먼저 우리는 열 자체를 찾아야만 하며 이 열 중 기본 키 열이 어떤 것이고 각 열이 어떤 데이터 형식을 지원하고 열이 Null을 허용하는지 여부를 알아내야 합니다.

그림 2시스템 테이블 보기

SQL Server 엔터프라이즈 관리자를 사용하면 그림 2와 같은 대화 상자에서 서버 등록의 속성을 변경하여 시스템 테이블을 볼 수 있습니다. Enterprise Manager에서 서버를 마우스 오른쪽 단추로 클릭한 다음 "SQL Server 등록 속성 편집(Edit SQL Server Registration properties)"을 선택하면 대화 상자가 하나 나타납니다. 대화 상자 하단에 "시스템 데이터베이스 및 시스템 개체 표시(Show system databases and system objects)"라는 확인란이 있습니다. 이 옵션을 선택하면 시스템 개체를 볼 수 있으며 이 옵션을 해제하면 테이블 목록 보기를 더 쉽고 간편하게 읽을 수 있습니다.

테이블 열 분석

syscolumns 테이블은 열 이름, ID, 길이 및 Null 여부와 같은 필수적인 메타데이터 정보를 상당수 제공합니다. 이 테이블은 또한 sysindexes 테이블과 함께 사용되어 테이블의 기본 키 필드를 결정합니다. 또한 열의 기본값을 검색하기 위해 INFORMATION_SCHEMA.COLUMNS 뷰가 사용되는 것을 알 수 있습니다.

모든 프로시저가 동일한 메타데이터 정보를 찾고 있으므로 모듈화와 유지 관리성을 위해 이 검색 작업을 별도의 코드에 캡슐화하는 것이 좋습니다. 이미 설명했듯이 이전 버전의 SQL Server에는 사용자 정의 함수(UDF)가 없기 때문에 이와 같은 모듈화가 어렵게 됩니다. 하지만 SQL Server 2000 사용자 정의 함수를 통해 우리는 코드를 한 단계 더 발전시키고 네 가지 디자인 타임 저장 프로시저의 일반적인 함수를 모듈화하기로 결정했습니다. 시스템 테이블과 정보 스키마 뷰를 처리하기 위해 다섯 개의 새로운 UDF가 만들어졌으며 이를 통해 모든 메타데이터 검색을 캡슐화합니다.

새로운 런타임 저장 프로시저를 제대로 만들기 위해서는 해당 테이블에 대해 다음과 같은 메타데이터 열 정보를 알아야 합니다.

• 열 이름
• 열 ID
• 열의 데이터 형식
• 열의 최대 길이(문자 및 이진 데이터에 해당)
• 열의 정밀도 및 값의 자리수(십진 및 숫자 데이터의 경우)
• 열의 진법 및 소수점 자리수(십진 및 숫자 데이터의 경우)
• 열의 Null 허용
• 열이 기본 키의 일부분인지 여부
• 열이 ID 열인지 여부
• 열의 기본값

대부분의 경우, 이 정보는 syscolumns 테이블에서 가져오지만 몇 가지 예외가 있습니다. 실제로 기본값은 INFORMATION_SCHEMA.COLUMNS 뷰에서 검색됩니다. 데이터 형식 이름은 systypes 테이블에서 가져오며 열이 기본 키의 일부분인지 결정하기 위해 더 복잡하게 조합된 syscolumns, sysindexes 및 sysindexkeys가 사용됩니다. 실제로 이 기능을 자체 UDF에 캡슐화하는 것은 그다지 복잡하지 않았습니다.

이 메타데이터 정보를 더 보여주는 그림 3과 같은 기본 함수에 대해 살펴보겠습니다. 이 UDF는 드러나게 복잡하지는 않습니다. 알고 있듯이 대부분의 메타데이터 정보는 열 이름, 열 번호, 길이, 정밀도, 진법, IsNullable 및 형식 이름을 비롯한 어떤 것도 수정되지 않고 반환됩니다(예외로 단순히 열 이름이 변경되는 경우는 있습니다). 나머지 경우에는 이 정보를 위해 약간의 작업이 더 필요합니다. 기본 키 메타데이터를 위해 열이 테이블 기본 키의 일부분인지 결정하는 또 다른 UDF를 만들었습니다. 우리는 이러한 추가적인 UDF에 대해 간단히 살펴볼 것입니다.

대체 형식 및 ID 상태에 대해 살펴보겠습니다. syscolumns의 상태 필드의 8번째 비트(128)는 열이 ID 열인지 여부를 나타냅니다. (이것은 언제 Insert 및 Update 스크립트를 만들어야 할지를 알기 위해 특히 중요합니다.) 우리의 간단한 방법은 이 값에 논리 AND(&)를 수행한 다음, 그 결과를 Sign 함수에 넣는 것입니다. 비트가 설정되면 열이 ID 열임을 나타내며 c.status & 128이 값 128을 반환할 것이며 비트가 설정되지 않으면 값 0을 반환할 것입니다. Sign 함수는 양수에 대해 값 1을 반환하며 음수에 대해 값 –1을 반환하고 값 0에 대해서는 0을 반환합니다. 따라서 평가되는 열이 ID 열인 경우, 값 1이 반환되고 그렇지 않으면 값 0이 반환됩니다.

대체 형식은 데이터 형식을 정의할 경우에 추가적인 정보(길이 또는 정밀도 및 진법)가 필요한지 여부를 나타냅니다. 우리는 문자 및 이진 데이터 형식을 1 대체 형식으로, 십진 및 숫자는 2 대체 형식으로, 기타 모든 데이터 형식은 0 대체 형식으로 분류합니다. 이 값은 길이 또는 정밀도 및 진법 값을 매개 변수 정의에 추가해야 하는지 여부를 결정하기 위해 저장 프로시저에 사용됩니다.

기본 키 열 찾기

알고 있듯이 열 정보를 찾는 것은 어려운 일이 아닙니다. 하지만 필드가 기본 키의 일부분인지 여부를 알아내는 것은 약간 어렵습니다. 검색할 수 있는 필드의 목록이 있지만 이 필드는 syscolumns, sysindexes 및 sysindexkeys 테이블에서 검색되며 우리가 요청한 열(@sColumnName 매개 변수로 UDF에 전달된 열)과 비교되어야 합니다. 따라서 별도의 사용자 정의 함수에서는 이 작업을 단일 함수 호출로 캡슐화할 수 있으므로 더 쉽게 기본 키를 찾을 수 있습니다.

이 함수에 대해 자세히 살펴보겠습니다.

CREATE FUNCTION dbo.fnIsColumnPrimaryKey    (@sTableName varchar(128), @sColumnName varchar(128))RETURNS bitASBEGIN    DECLARE    @nTableID int,               @nIndexID int,               @i int        SET @nTableID = OBJECT_ID(@sTableName)

이 함수에는 테이블 이름과 열 이름의 두 매개 변수가 있으며 지정된 열이 테이블 기본 키의 일부분임을 나타내는 비트를 반환합니다. 그런 다음 프로시저에 사용할 일부 변수를 선언하고 일부 초기 값을 할당해야 합니다. 이제가 흥미로운 부분입니다. 기본 키 정보를 찾아보겠습니다. 먼저 아래 코드와 같이 테이블 기본 키 인덱스의 Index ID를 검색합니다.

SELECT @nIndexID = indidFROM      sysindexesWHERE  id = @nTableID AND      indid BETWEEN 1 And 254  AND      (status & 2048) = 2048ORDER BY indidIF (@nIndexID  Is Null)    RETURN 0

이제 테이블 기본 키 인덱스의 Index ID를 변수 @nIndexID에 할당합니다. 상태 열의 12번째 비트(2048)는 인덱스가 기본 키 인덱스인지 여부를 나타냅니다. 기본 키 인덱스가 없으면 레코드가 반환되지 않으며 @nIndexID가 Null 값으로 남겨집니다. 따라서 @nIndexID에 Null 값이 포함되어 있으면 함수를 남겨두고 0 값을 반환합니다. 즉, 기본 키 인덱스가 없으므로 해당 열이 기본 키의 일부분이 될 수 없습니다. 이제 기본 키 인덱스의 열 목록과 비교하여 요청된 열(@sColumnName)을 확인해야 합니다.

    IF @ColumnName IN        (SELECT sc.[name]          FROM       sysindexkeys sik          INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid =                     sc.colid          WHERE      sik.id = @nTableID          AND        sik.indid = @nIndexID        )     BEGIN        RETURN 1     END    RETURN 0END

이전에 검색된 IndexID를 사용하여 syscolumns 및 sysindexkeys 테이블의 조인으로부터 열 이름을 선택합니다. 이 테이블은 열 ID 및 개체 ID를 사용하여 조인됩니다. Where 절은 우리가 요청한 테이블(sik.id = @nTableID)에 관련된 인덱스에서만 열을 선택하고 기본 키 인덱스(sik.indid = @nIndexID)에 대해서만 열을 선택하도록 기준을 설정합니다. 이 검색된 열 목록에 @sColumnName이 있으면 1 값을 반환하고 그렇지 않으면 0 값을 반환합니다. 0 값은 일치하는 내용이 검색되지 않았음을 나타냅니다.

열 기본값

테이블에 레코드를 삽입할 때 특정 열에 값이 제공되지 않고 이 열에 기본값이 있으면 열 데이터를 채우기 위해 기본값이 사용됩니다. 우리가 새로 만든 테이블 삽입 프로시저는 삽입 가능한 모든 열에 대해 매개 변수를 가지고 있으며 변수 하나에는 값(Null 값 포함) 하나가 포함되어야 하므로 테이블의 기본값이 사용되지 않을 것입니다. 기본적으로 모든 열에 명시적으로 값(Null 값 포함)을 제공하여 열의 기본값을 오버라이드(override)합니다. 우리가 만든 프로시저의 이러한 특성을 없애기 위해서는 데이터를 삽입할 때 기본값을 제공할 수 있어야 합니다. 이 기사의 뒷부분에서는 자동화된 프로시저에서 기본값을 사용하는 방법에 대해 살펴보겠지만 지금은 이 기본값을 구하는 방법에 대해 먼저 살펴보겠습니다.

우리가 사용할 UDF는 열의 기본값을 제공하는 기본 제공 INFORMATION_SCHEMA.COLUMNS 뷰를 참조합니다. 이 뷰를 사용하면 sysconstraints 시스템 테이블을 사용할 때보다 쉽게 기본값을 검색할 수 있습니다. 다음 UDF는 이 프로세스를 더 단순화하기 위해 기본값 검색 논리를 간단한 함수 호출에 넣습니다.

CREATE FUNCTION dbo.fnColumnDefault(@sTableName varchar(128),                                     @sColumnName varchar(128))RETURNS varchar(4000)ASBEGIN    DECLARE @sDefaultValue varchar(4000)    SELECT  @sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT)      FROM      INFORMATION_SCHEMA.COLUMNS      WHERE     TABLE_NAME = @sTableName      AND       COLUMN_NAME = @sColumnName    RETURN  @sDefaultValueEND 

열 기본값은 괄호 쌍에 넣어져 저장되지만 우리의 구현에서는 필요가 없습니다. 여기서 알 수 있듯이 COLUMN_DEFAULT 필드를 또 다른 함수인 fnCleanDefaultValue에 전달합니다. 이 함수는 괄호를 벗겨내고 실제 기본값을 반환합니다.

예를 들어, nQty라는 열의 기본값이 1인 경우, COLUMN_DEFAULT 값에는 실제로 (1)이 포함됩니다. "Enter Text Here"의 기본값은 ("Enter Text Here")가 될 것입니다. 다음은 이 UDF의 소스 코드입니다.

CREATE FUNCTION dbo.fnCleanDefaultValue(@sDefaultValue varchar(4000))RETURNS varchar(4000)ASBEGIN    RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2)END

이제 자동화된 프로시저를 실제로 만드는 데 필요한 모든 메타데이터 정보를 구했습니다.

동적으로 T-SQL 실행

동적 T-SQL 실행은 우리 저장 프로시저의 또 다른 중요한 기능입니다. 이 기능을 사용하면 T-SQL 스크립트를 작성하는 일반 T-SQL 스크립트를 작성할 수 있습니다. 일반 T-SQL 스크립트를 사용하여 실제로 특정 출력을 실행하고 응용 프로그램에 의해 사용되는 런타임 저장 프로시저를 만들도록 해주는 것은 T-SQL EXECUTE 문입니다.

EXECUTE 또는 EXEC에는 실제로 두 가지 기능이 있습니다. 즉, 기존 저장 프로시저를 실행할 수 있으며 문자열로 전달된 SQL 문을 동적으로 실행할 수 있습니다. 저장 프로시저를 자동으로 만들기 위해 우리가 이전에 가져온 열 메타데이터와 함께 사용할 기능은 후자의 기능입니다. 단순한 프로세스 뷰는 저장 프로시저를 만드는 데 필요한 저장 프로시저 코드로 varchar 변수를 채울 것입니다(메타데이터 사용). 그런 다음 varchar 변수의 내용을 동적으로 실행하여 새로운 저장 프로시저를 만들 것입니다.

간단한 동적 T-SQL의 예제를 살펴보겠습니다.

CREATE PROC prGetAuthor    @au_id char(11)ASDECLARE @sExec varchar(8000)SET @sExec = 'SELECT * FROM authors WHERE au_id = ''' + @au_id + ''''EXEC (@sExec)

이 예제에서 우리는 한 저자의 ID를 전달하고 이 저자의 테이블에서 저자를 검색하는 SELECT 문에 이 ID를 연결합니다.

다음과 같은 프로시저를 호출한다고 가정해 보겠습니다.

EXEC prGetAuthor '123-45-6789'

prGetAuthor 프로시저는 아래와 같은 SQL 문을 만듭니다.

SELECT * FROM authors WHERE au_id = '123-45-6789'

그러면 이 문이 EXEC 문에서 실행되고 ID가 123-45-6789인 저자가 반환될 것입니다. 곧바로 알겠지만 디자인 타임 프로시저는 이 기능을 훨씬 더 향상시킵니다.

아마도 이미 알고 있겠지만, 이 방법은 동적 T-SQL의 권장되는 사용 방법은 아닙니다. 외부 세계에서 동적 T-SQL 코드에 액세스할 수 있는 경우라면 언제나 SQL 주입 공격의 가능성이 존재합니다. 우리는 관리 및 작업상의 용도로만 동적 T-SQL을 사용하는 것이 관행이며 시스템 또는 관리자가 아닌 다른 사람이 액세스할 수 있는 프로시저에는 이 기능을 절대로 노출시키지 않습니다.

저장 프로시저 작성

이 디자인 타임 프로시저를 작성하는 첫 단계는 상당히 표준적입니다. 프로시저가 정의되고 변수가 선언되고 일부 변수가 초기화됩니다. 이 코드를 훑어보면 놀라운 것이 전혀 없으며 프로시저의 나머지 단계를 설정합니다. 우리는 특별한 두 문자열 변수를 만듭니다. 이 중 하나는 탭 문자를 포함하고 다른 하나는 캐리지 리턴 및 줄 바꿈을 포함합니다. 이들 변수도 UDF로 설정될 수 있지만 독자의 연습을 위해 우리는 그렇게 하지 않기로 결정했습니다. 이들 변수는 코드 출력의 서식을 지정하는 데 사용됩니다. 그림 4와 같은 이 프로시저의 시작 부분에 대해 살펴보겠습니다.

다시 말하지만 여기에는 T-SQL과 관련하여 놀라운 것이 전혀 없습니다. 무엇보다도 먼저 우리 테이블에 기본 키 값이 있는지 확인합니다. 이렇게 하면 피해를 유발할 가능성이 있는 런타임 프로시저가 우리 코드에서 생성되는 것을 막아줍니다. 그런 다음 변수와 일부 기본값을 설정합니다. 프로시저의 다음 단계는 새로운 프로시저에 대해 DROP 문을 설정하고 몇몇 주석을 만들고 실제 프로시저 정의를 만드는 것입니다(그림 1)의 처음 몇 줄). 이 코드를 수정하여 새로운 런타임 저장 프로시저를 만들 수 있습니다. 단, 아직 존재하지 않는 경우에만 만들 수 있으며 이미 존재하는 경우는 아무 동작도 수행하지 않습니다. 또한 이 새로운 논리를 제어하기 위해 세 번째 옵션 매개 변수인 @bIfExistsDoNothing을 추가할 수 있습니다. 이것도 독자의 간단한 연습을 위해 남겨두겠습니다.

그 다음 코드는 동적 T-SQL을 만드는 과정부터 시작합니다. 기존 프로시저를 제거하고 새로운 프로시저 정의를 위한 코드가 추가됩니다((그림 5참조). 코드의 실제 실행 여부를 결정하기 위해 두 번째 옵션 매개 변수인 @bExecute가 어떻게 사용되는지를 살펴보십시오. 자동화된 프로시저의 정의에서 이 매개 변수는 옵션이며 기본값은 0이 됩니다. 즉, 실제로 코드를 실행하지 않습니다.

그 다음이 흥미로운 부분입니다. 우리는 fnTableColumnInfo가 테이블 값 함수임을 알고 있으며 fnTableColumnInfo UDF를 커서의 원본으로 사용합니다. 네 개의 자동화된 프로시저에서 이 함수가 각각 사용하는 더 복잡한 T-SQL을 작성하는 대신 이제는 커서의 선언부에서 UDF를 참조하면 됩니다. 커서를 선언한 후 커서를 열고 첫 번째 레코드를 변수(메타데이터 정보가 포함된 변수) 집합으로 가져옵니다. 이 메타데이터 정보는 새로운 프로시저를 만드는 데 사용될 것입니다(그림 6참조).

물론 우리는 WHILE 문을 사용하여 루프를 구성할 것입니다. 이 루프는 검색할 유효 행이 있는 동안에서 계속해서 실행됩니다(@@FETCH_STATUS = 0). 이제 새로운 저장 프로시저를 위해 열 정보를 분석하고 키 세그먼트를 만들 준비가 되었습니다.

다음 코드 샘플에서는 커서 검색을 시작하고 열 메타데이터를 사용하여 코드를 생성합니다. @sKeyFields, @sSetClause 및 @sWhereClause와 같은 세 개의 키 변수가 수정되고 있음을 알 수 있습니다. 첫 번째 변수는 저장 프로시저의 매개 변수 목록을 만드는 데 사용됩니다(그림 1의 CREATE PRDC 블록 내). 두 번째 변수는 그림 1에서 UPDATE 문의 SET 절에 사용됩니다. 마지막 변수는 그림 1의 끝에 있는 WHERE 절에 사용됩니다. 이제 이 코드의 첫 부분을 검토해야 합니다(그림 7참조).

그림 7에는 새 저장 프로시저의 매개 변수 목록을 만드는 코드가 포함되어 있습니다. 첫 번째 IF 문은 변수에 데이터가 이미 추가되어 있는지를 확인합니다. 데이터가 이미 추가된 경우는 매개 변수 목록에 있는 각 매개 변수가 올바로 종료되도록 쉼표와 캐리지 리턴/줄 바꿈을 추가합니다. 이 확인을 수행하지 않고 이 세그먼트의 끝에 쉼표만 추가한 경우는 쉼표만 무수하게 나타날 것입니다. 이 문제를 해결하기 위해 우리는 그 다음 열을 추가하기 전에 쉼표를 추가합니다.

다음은 탭 문자, @ 기호, 열 이름, 공백 및 열 형식 이름(문자와 메타데이터 정보를 연결한 이름)을 추가합니다. 그런 다음 데이터 형식에 대한 추가적인 정보가 필요한지를 확인해야 합니다. 우리는 정밀도, 진법 또는 길이에 대한 정보가 데이터 형식에 필요한지를 확인합니다. 임의의 정보가 필요한 경우는 T-SQL 구문의 요구에 따라 괄호에 쌓인 값을 추가적으로 추가합니다.

마지막으로 열이 ID 열이 아니고 열이 Null 값을 허용하거나 열이 타임스탬프인 경우는 매개 변수 정의에 "= NULL"을 추가합니다. 타임스탬프는 직접 업데이트되므로 업데이트가 허용되지 않습니다. 예를 들어, pubs 데이터베이스에 있는 discounts 테이블의 열이 다음과 같을 수 있습니다.

discounttype varchar(40),stor_id char(4) = NULL,lowqty smallint = NULL,highqty smallint = NULL,discount decimal(4, 2)

그러면 이 문이 EXEC 문에서 실행되고 ID가 123-45-6789인 저자가 반환될 것입니다. 곧바로 알겠지만 디자인 타임 프로시저는 이 기능을 훨씬 더 향상시킵니다.

아마도 이미 알고 있겠지만 이 방법은 동적 T-SQL의 권장되는 사용 방법은 아닙니다. 외부 세계에서 동적 T-SQL 코드에 액세스할 수 있는 경우라면 언제나 SQL 주입 공격의 가능성이 존재합니다. 우리는 관리 및 작업상의 용도로만 동적 T-SQL을 사용하는 것이 관행이며 시스템 또는 관리자가 아닌 다른 사람이 액세스할 수 있는 프로시저에는 이 기능을 절대로 노출시키지 않습니다.

SET    UnitPrice = @UnitPrice,       Quantity = @Quantity,       Discount = @Discount

다음은 새 프로시저에 대해 WHERE 절을 만드는 코드가 있습니다. 이 코드 세그먼트는 ELSE 문으로 시작됨을 알 수 있습니다. 이것은 기본 키 확인의 또 다른 조건입니다. 즉, 열이 기본 키의 일부분인 경우에만 이 코드를 실행할 수 있습니다(그림 9참조).

항목이 WHERE 절에서 첫 번째 항목인지 여부에 따라 WHERE 절로 변수를 시작하거나 AND 절을 추가합니다. 다음은 탭 문자, 열 이름, 문자열 " = @", 그리고 다시 열 이름을 추가합니다. Order_Details 예제의 결과는 다음과 같습니다.

WHERE     OrderID = @OrderID AND     ProductID = @ProductID

WHILE 루프를 종료하기 전에 커서에서 그 다음 행을 가져오고 변수에 메타데이터 값을 다시 넣어야 합니다. 루프가 완료되면 커서를 닫고 할당을 해제합니다. 이제 새로운 런타임 프로시저를 인쇄하고 선택적으로 만드는 데 필요한 모든 정보를 구했습니다(그림 10참조).

마지막으로 디자인 타임 저장 프로시저는 새로운 런타임 저장 프로시저를 위해 만든 T-SQL을 인쇄하기 위해 먼저 캐리지 리턴/줄 바꿈을 SET 절에 추가합니다(서식 지정 전용). 다음은 우리 키 필드(우리 프로시저의 매개 변수)와 키워드 AS(저장 프로시저의 정의에 필요)를 추가합니다. 그런 다음, 업데이트하려는 테이블의 이름과 함께 UPDATE 문을 추가합니다. 마지막으로 SET 절 변수와 WHERE 절 변수를 추가하여 프로시저 정의를 완료합니다. 이미 말했듯이 새로운 런타임 저장 프로시저용 T-SQL을 포함하는 @sProcText 변수는 선택적으로 실행이 가능하며 이 변수를 실행하면 새로운 런타임 저장 프로시저가 데이터베이스 스키마에 추가됩니다.

결론

이것은 우리가 개발한 네 개의 자동화된 저장 프로시저 중 하나에 불과합니다. 물론 각 프로시저는 필요에 따라 바뀝니다. 예를 들어, 런타임 삭제 프로시저를 만드는 디자인 타임 프로시저는 각 테이블의 기본 키 필드만을 사용합니다. 그 이유는 임의 테이블에서 행을 삭제하기 위해서는 이 기본 키 필드만 있으면 되기 때문입니다. 모든 사용자 정의 함수와 저장 프로시저의 원본은 이 기사의 맨 위에 있는 링크에서 다운로드할 수 있습니다.

이들 프로시저에는 다른 여러 기능이 추가될 수 있으며 이 기능 중 일부에 대해 이미 언급했습니다. 이러한 기능으로는 개체 이름을 괄호에 포함하고, 개체 존재 여부를 확인하고, 감사 추적을 만들고, 생성된 프로시저가 이미 존재하는 경우, alter 문을 사용하는 기능 등이 있습니다. 마지막 기능은 생성된 프로시저의 보안을 유지하고 XML을 통합하는 데 매우 뛰어난 기능입니다. 또한 테이블에 추가적인 설정을 만들고 이 설정을 사용하여 코드 생성을 지원할 수 있습니다. 즉, 이 프로시저는 다른 자동화된 코드 생성 작업을 위한 출발점이 될 수 있으며 또한 사용자의 필요를 있는 그대로 채워줄 수 있습니다. 어느 경우나 이 코드는 상당한 시간과 노력을 줄여주며 T-SQL의 다른 흥미로운 기술을 개발하도록 도와줍니다.

---

관련 기사는 다음 링크를 참조하십시오:
.NET Reflection: .NET 메타데이터 및 리플렉션을 이용하여 데이터 레이어를 저장 프로시저 및 SQL 명령과 동적으로 바인딩하기 (영문)
SQL and XML: XML을 사용하여 웹 상의 저장 프로시저를 호출 및 반환하기 (영문)
Serving the Web: Visual Basic Boosts Productivity의 저장 프로시저 마법사 (영문)
배경 정보는 다음 서적을 참조하십시오:
Inside Microsoft SQL Server 2000, 저자: Kalen Delaney(Microsoft Press, 2000)
The Guru's Guide to SQL Server Stored Procedures, XML, and HTML, 저자: Ken Henderson(Addison-Wesley Professional, 2001)

http://www.microsoft.com/korea/msdn/SQL/sqldatabase/default.aspx?pull=/korea/msdn/msdnmag/issues/03/04/storedprocedures/default.aspx

여기서 참조.