|
개요
SQL 서버 2005에서는 .NET Framework2.0의 Common Language Runtime 기술을 이용하여 매우 향상된 데이터베이스 프로그램을 구현 할 수 있습니다. 이는 Microsoft Visual C#, Microsoft Visual Basic.NET, Microsoft Visual C++ 등의 CLR 언어를 통해서 저장프로시저, 사용자정의 함수, 트리거를 생성할 수 있을 뿐 아니라, 사용자 정의 데이터타입 및 집계의 생성을 가능하게 합니다.
TSQL의 그 능력의 한계
TSQL은 RDBMS만을 위한 언어이기 때문에 DML, DDL외의 프로그래밍 언어로서의 기능이 아주 미약합니다. TSQL에서 제공하는 함수는 제한적이며 (SQL2005에서 기능이 많이 추가되긴 하였지만), TSQL 로는 SQL 서버 외부 개체에 접근할 수 없습니다. 예를 들면 파일시스템 오브젝트를 통해서 파일의 내용을 읽고, 쓰는 등의 작업은 TSQL 로는 상상도 할 수 없습니다. 물론 SQL2000에서도 Extended Stored Procedure를 통해서 SQL외부 개체에 대한 핸들링을 할 수 있었으나, 상대적으로 어려운 C,C++프로그래밍에 대한 접근성과 in-process형태로 Sqlserver프로세스에서 실행되었기 때문에 자칫 확장 저장프로시져에 치명적인 코드결함이 있으면, SQL서버가 다운되는 경우도 있었습니다
하지만 CLR을 사용함으로써 SQL서버에서 기존에는 할 수 없었던 다양한 프로그래밍을 가능하게 합니다.
TSQL과 CLR의 몇 가지 장단점
- TSQL은 인터프리터 방식이므로 느리다.
- TSQL의 에러핸들링은 SQL2005에서 많이 향상되긴 했지만 비교적 좋지 않다.
- CLR은 컴파일 된 형태이므로 실행이 빠르다.
- CLR은 .NET프로그래밍 언어의 방대한 Class라이브러리를 사용하여 막강한 프로그래밍을 할 수 있다.
- TSQL은 대량의 데이터 액세스 혹은 데이터 조작 작업등에 유리하다.
- TSQL은 절차적 언어가 아니라 선언적 언어라서 문자열 조작, 데이터 포맷팅, 절차적 혹은 반복적인 로직에는 성능이 좋지 않다.
- CLR은 계산 집중적이거나 절차적,반복적 로직, 문자열 처리에서 좋은 성능을 나타낸다.
CLR 통합기능을 활용한 개발 절차
1. SQL2005에서 CLR통합기능 옵션 활성화
2. .NET Framework에서 지원하는 언어를 이용하여 클래스 작성
3. 언어컴파일러를 통한 클래스 컴파일
4. SQL서버에서 ASSEMBLY등록
5. 등록한 ASSEMBLY 를 이용하여 사용자 개체 생성
.NET CLR통합기능 활성화
SQL2005는 보안상의 이유로 .NET CLR통합기능을 비활성화 해두고 있습니다. 따라서 CLR통합기능을 사용하기 위해서는 ‘Clr Enabled’ 라는 구성옵션을 활성화 해야 합니다. .NET CLR통합 기능을 사용할 수 있게 해주는 옵션을 활성화하는 방법은 두 가지가 입니다.
첫 번째 방법은, sp_configure 구성옵션 명령을 통해서 ‘clr enabled’ 라는 구성옵션을 ‘1’로 세팅해주는 것입니다. 이 옵션은 수정 즉시 적용되므로, SQL서버를 재 시작 할 필요가 없습니다.
SP_CONFIGURE 'clr enabled',1
RECONFIGURE WITH OVERRIDE
GO |
SP_CONFIGURE
GO |
| name |
minimum |
maximum |
config_value |
run_value |
| ----------------------- |
------------ |
------------ |
------------ |
------------ |
| clr enabled |
0 |
1 |
1 |
1 |
| lightweight pooling |
0 |
1 |
0 |
0 | |
유의해야 할 사항은 clr enabled옵션이 해제되게 되면 등록된 모든 ASSEMBLY가 unload되게 됩니다.
그리고 clr enabled옵션과 lightweight pooling옵션은 병행해서 사용이 불가능합니다.
두 번째 방법은, SQL서버 노출영역 구성에서 기능에 대한 노출영역을 구성하는 것 입니다. 아래 그림과 같이 clr enabled 옵션을 체크 해주면 됩니다. 
예제1 – 사용자 정의 스칼라 함수 구현
그럼, CLR을 통한 간단한 사용자 정의 함수를 생성하고 SQL서버에 이식시켜 사용해보는 예제를 구현해보면서, CRL을 이용한 SQL프로그래밍에 대한 감을 잡아보겠습니다.
예제로 email주소를 정합성을 검사해주는 사용자함수 클래스를 C#으로 구현해 보겠습니다. 소스코드 작성을 위해서 메모장을 이용하셔도 무방합니다만.. 가능하시면 Visual Studio를 이용하시면 보다 더 훌륭한 개발 인터페이스를 통해서 더 빠르고 쉽게 코딩이 가능합니다. 여기서, c#코드에 대한 설명은 하지 않습니다.
개발 도구를 사용하여 아래 c#코드를 작성합니다.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server. SqlFunction] public static bool emailCheck( string email) {
string pattern=@"^[a-z][a-z|0-9|]*([_][a-z|0-9]+)*([.][a-z|" + @"0-9]+([_][a-z|0-9]+)*)?@[a-z][a-z|0-9|]*＼.([a-z]" + @"[a-z|0-9]*(＼.[a-z][a-z|0-9]*)?)$";
Match match = Regex.Match(email, pattern, RegexOptions.IgnoreCase);
if(match.Success) return true;
else return false;
} | |
[소스1] emailCheck.cs
소스코드 작성이 완료되면 c#컴파일러를 통해 cs파일을 dll형태로 컴파일 합니다.
| csc.exe /target:library /out:emailcheck.dll emailcheck.cs |
참고. csc컴파일러의 위치는 .NetFramework이 설치된 폴더에 있습니다.
C:＼WINDOWS＼Microsoft.NET＼Framework＼v2.0.50727
자 이렇게 해서 emailcheck.dll 이라는 어셈블리 파일을 생성했습니다. 이 파일을 관리하시는 적절한 폴더에 복사 하십시요. 이 파일은 추후에 SQL2005의 ASSEMBLY로 등록하게 되면, 삭제해도 무관합니다.
다음 단계로 CLR사용자 함수 ASSEMBLY를 SQL서버에 ASSEMBLY로 등록합니다.
CREATE ASSEMBLY SQLER_UDF
FROM 'C:＼TEMP＼emailcheck.dll'
WITH PERMISSION_SET = SAFE
GO |
CREATE ASSEMBLY는 이미 .dll파일로 미리 컴파일된 ASSEMBLY를 SQL2005 내부에서 사용할 수 있도록 업로드 혹은 바인딩 하는 작업입니다.
다음 단계로 ASSEMBLY를 매핑하여 사용자 정의 함수를 생성합니다.
CREATE FUNCTION UDF_VALIDATEEMAIL (@email as nvarchar (100))
RETURNS bit
AS EXTERNAL NAME SQLER_UDF.MyFunctions.emailCheck
GO |
사용자 개체 생성시EXTERNAL NAME 지정규칙은 다음과 같습니다.
[SQL Server Assembly Name].[Fully Qualified Path to Class].[Static Method Name]
자. 그럼 이제 생성한 스칼라 사용자 정의 함수를 사용해보겠습니다.
SELECT DBO.UDF_VALIDATEEMAIL ('bellvirus1@naver.com') --1
SELECT DBO.UDF_VALIDATEEMAIL ('bellvirus1@naver') --0
SELECT DBO.UDF_VALIDATEEMAIL ('bellvirus1.com') --0
GO |
상기 절차로 .NET 프로그램 코드를 통한 UDF생성을 간단하게 살펴보았습니다.
예제2 – 스칼라 함수, 저장프로시져 및 트리거 구현
다음 예제로 테이블의 전체 행 수를 반환하는 스칼라 함수와, 레코드 셋을 가져오는 저장프로시져, 데이터 삭제에 대해 로그를 기록하는 트리거를 Visual Basic .NET 으로 구현 해보겠습니다.
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Public Class MySQLClass
<SqlFunction(DataAccess:=DataAccessKind.Read)>_ Public Shared Function ReturnOrderCount() As Integer
Using conn As New SqlConnection( "context connection=true")
conn.Open()
Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'ORDERCNT' FROM SALES.SALESORDERDETAIL", conn)
Return CType(cmd.ExecuteScalar(), Integer) End Using End Function
Public Shared Sub GetSalesOrderDetail()
Using conn As New SqlConnection( "context connection=true")
conn.Open()
Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'ORDERCNT' FROM SALES.SALESORDERDETAIL", conn)
SqlContext.Pipe.ExecuteAndSend(command) End Using End Sub
<Microsoft.SqlServer.Server.SqlTrigger()>_ Public Shared Sub writeDeleteLog()
Dim oTc As SqlTriggerContext oTc = SqlContext.TriggerContext If (oTc.TriggerAction = TriggerAction.Delete) Then
Dim oFi As New System.IO.StreamWriter("c:＼temp＼log.txt", True)
oFi.Write("something deleted !")
oFi.Close() End If End Sub
End Class |
[소스2] MySQLClass.vb
코드 작성이 완료되면 컴파일러를 통해 DLL형태의 파일을 생성합니다.
| vbc /target:library /out:c:＼temp＼MySQLClass.dll c:＼temp＼MySQLClass.vb |
그런 다음에 SQL서버에서 ASSEMBLY로 등록합니다.
이번 예제에서는 ASSEMBLY생성시에 PERMISSION_SET옵션을 EXTERNAL_ACCESS를 지정하였는데, 그 이유는 writeDeleteLog 메서드가 SQL 외부 개체에 액세스 할 필요가 있기 때문입니다.
여기서 잠깐 PERMISSION_SET옵션에 대해 알아보고 넘어 가겠습니다.
- SAFE (기본옵션) : 등록하게 되는 어셈블리에서 실행한 코드는 파일, 네트워크, 환경변수 또는 레지스트리와 같은 외부 시스템 리소스에 액세스할 수 없습니다.
- EXTERNAL_ACCESS : 파일, 네트워크, 환경 변수 또는 레지스트리와 같은 외부 시스템 리소스에 액세스할 수 있습니다.
- UNSAFE : SQL Server 인스턴스의 내부 리소스와 외부 리소스 모두에 제한 없이 액세스할 수 있습니다.
CREATE ASSEMBLY SQLER_CLASS
FROM 'C:＼TEMP＼MYSQLClass.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO |
생성 명령을 실행하였으나 아래와 같은 오류가 발생합니다.
| 메시지10327, 수준14, 상태1, 줄1 어셈블리'MySQLClass'에PERMISSION_SET = EXTERNAL_ACCESS에대한권한이없으므로어셈블리'MySQLClass'에대한CREATE ASSEMBLY가실패했습니다. 어셈블리는DBO(데이터베이스소유자)에게EXTERNAL ACCESS ASSEMBLY 권한이있고데이터베이스에TRUSTWORTHY 데이터베이스속성이있는경우또는어셈블리가현재인증서로서명되어있거나EXTERNAL ACCESS ASSEMBLY 권한이있는관련로그인을소유한비대칭키로서명되어있는경우에권한이부여됩니다. |
사용자 정의함수나 저장프로시져에서 외부 개체에 접근하려면 Database의 TRUSTWORTHY 옵션을 활성화 해주어야 합니다. 아래 명령을 실행하여 외부 개체 접근을 허용해줍니다. 그런 다음 다시 ASSEMBLY를 생성해줍니다.
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON
GO
CREATE ASSEMBLY SQLER_CLASS
FROM 'C:＼TEMP＼MYSQLClass.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO |
SQLER_CLASS 어셈블리를 활용하여 사용자 정의 스칼라 함수를 생성합니다.
CREATE FUNCTION UDF_getOrderCount()
RETURNS INT
AS EXTERNAL NAME SQLER_CLASS.MySQLClass.ReturnOrderCount
GO |
생성한 함수를 실행하여 보겠습니다.
SELECT DBO.UDF_getOrderCount() AS orderCount
GO
orderCount
-----------
121317
|
SQLER_CLASS 어셈블리를 활용하여 저장프로시져를 생성합니다.
CREATE PROC UP_getSalesOrderDetail
AS EXTERNAL NAME SQLER_CLASS.MySQLClass.GetSalesOrderDetail
GO
|
프로시져를 실행하여 보겠습니다.
EXEC UP_getSalesOrderDetail
GO
43659 1 4911-403C-98 1 776 1 2024.994
43659 2 4911-403C-98 3 777 1 2024.994
43659 3 4911-403C-98 1 778 1 2024.994
43659 4 4911-403C-98 1 771 1 2039.994
43659 5 4911-403C-98 1 772 1 2039.994
43659 6 4911-403C-98 2 773 1 2039.994
43659 7 4911-403C-98 1 774 1 2039.994
43659 8 4911-403C-98 3 714 1 28.8404
43659 9 4911-403C-98 1 716 1 28.8404 | 결과를 잘 반환해 줍니다.
다음으로 트리거를 생성하여 보겠습니다.
이 예제를 위해서 간단한 샘플 테이블을 생성하겠습니다.
CREATE TABLE TBL_TEST (idx int, name varchar(20))
GO
INSERT INTO TBL_TEST VALUES (1,'KIM')
INSERT INTO TBL_TEST VALUES (1,'LEE')
INSERT INTO TBL_TEST VALUES (1,'PARK')
GO |
TBL_TEST테이블에 대해서 DELETE명령에 대한 트리거 생성합니다.
CREATE TRIGGER TRG_DELETE
ON TBL_TEST
FOR DELETE
AS EXTERNAL NAME SQLER_CLASS.MySQLClass.writeDeleteLog
GO |
트리거가 잘 생성되었고, 실제로 데이터를 삭제하여 트리거가 정상적으로 동작하는지 살펴보겠습니다. 앞서 작성한 writeDeleteLog 메서드는 삭제 트리거가 발생하면 메모장에서 log.txt파일에 Something Deleted! 를 기록하게 됩니다.
DELETE FROM TBL_TEST WHERE idx = 1
GO
메모장에서 log.txt파일 확인
|
예제3. 사용자 정의 집계함수(Aggegation)의 구현
이번 예제에서는 각 행들의 특정 문자열 컬럼을 합해주는 함수를 만들어 보겠습니다.
이 함수는 숫자형 집계함수인 SUM()과 유사합니다 ^^
이번 예제는 온라인 도움말에서 제공하는 샘플을 사용합니다.
using System;
using System.IO;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
[assembly: System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design",
"CA1020:AvoidNamespacesWithFewTypes", Scope = "namespace", Target =
"Microsoft.Samples.SqlServer")]
namespace Microsoft.Samples.SqlServer
{
[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Microsoft.SqlServer.Server.Format.UserDefined, //use clr
serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
] public classConcatenate : Microsoft.SqlServer.Server.IBinarySerialize {
/// <summary>///The variable that holds the intermediate result of the concatenation/// </summary> private StringBuilder intermediateResult; /// <summary>/// Initialize the internal data structures/// </summary>public void Init() {
intermediateResult = new StringBuilder(); }
/// <summary>///Accumulate the next value, nop if the value is null/// </summary>/// <param name="value"></param>public void Accumulate(SqlString value) {
if (value.IsNull)
{
return;
}
intermediateResult.Append(value.Value).Append(',');
} /// <summary>///Merge the partially computed aggregate with this aggregate./// </summary>/// <param name="other"></param>public void Merge(Concatenate other) {
intermediateResult.Append(other.intermediateResult); } /// <summary>/// Called at the end of aggregation, to return the results of the aggregation/// </summary>/// <returns></returns>public SqlString Terminate() {
string output = string.Empty;
//delete the trailing comma, if any
if (intermediateResult != null && intermediateResult.Length > 0)
output = intermediateResult.ToString(0, intermediateResult.Length - 1);
return new SqlString(output);
} public void Read( BinaryReader r) {
intermediateResult = new StringBuilder(r.ReadString()); } public void Write( BinaryWriter w) {
w.Write(intermediateResult.ToString()); } } } | [소스3] concat.cs
소스코드 작성이 완료되면 c#컴파일러를 통해 cs파일을 dll형태로 컴파일 합니다.
| csc.exe /target:library /out:concat.dll concat.cs |
concate.dll파일을 ASSEMBLY로 등록하고, 집계함수를 생성합니다.
CREATE ASSEMBLY STRINGHANDLES3
FROM 'C:＼TEMP＼concat.dll'
CREATE AGGREGATE [dbo].[Concatenate](@input nvarchar(4000))
RETURNS nvarchar(4000)
EXTERNAL NAME
STRINGHANDLES3.[Microsoft.Samples.SqlServer.Concatenate];
GO |
테스트를 위한 샘플 테이블을 생성하고, 데이터를 몇 건 삽입합니다.
CREATE TABLETEST (IDX INT, NAME VARCHAR(10))
GO
INSERT INTO TEST VALUES (1,'AAA')
INSERT INTO TEST VALUES (2,'BBB')
INSERT INTO TEST VALUES (3,'CCC')
INSERT INTO TEST VALUES (4,'DDD')
INSERT INTO TEST VALUES (5,'EEE')
INSERT INTO TEST VALUES (6,'KKK')
GO |
집계함수를 활용하여 쿼리를 실행합니다.
SELECT SUM(IDX)AS SUMIDX, dbo.concatenate(name) AS CONCATNAMES
FROM TEST
GO
SUMIDX CONCATNAMES
--------- ----------------------------------
21 aaa,bbb,ccc,ddd,eee,kkk
(1개행적용됨) |
예제4. 사용자 정의 테이블 값 함수의 구현
이번에는 특정 구분자를 이용하여 요소를 분리해주는 SPLIT함수를 구현해 보겠습니다.
using System;
using System.Collections;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public sealed class StringSplitter
{
[SqlFunction(FillRowMethodName = " FillRow")] public static IEnumerable Split(SqlString argument1, Char [] argument2) {
string value; if (argument1.IsNull)
value = ""; else
value = argument1.Value; return value.Split(argument2); } [System.Diagnostics.CodeAnalysis. SuppressMessage( "Microsoft.Design", "CA1021:AvoidOutParameters")] public static void FillRow( Object obj, out string stringElement) {
stringElement = (string)obj; } | [소스4] Split.cs
소스를 컴파일 합니다.
| csc.exe /target:library /out:split.dll split.cs |
어셈블리를 등록합니다.
CREATE ASSEMBLY STRINGHANDLES
FROM 'C:＼TEMP＼split.dll'
GO |
사용자 정의 함수를 생성합니다.
CREATE FUNCTION DBO.UDF_SPILT (@INPUT NVARCHAR (4000), @DELIMETER
NVARCHAR(10))
RETURNS TABLE (ELEMENT NVARCHAR (MAX))
AS EXTERNAL NAME STINGHANDLE.[ StringSplitter]. Split;
GO |
실행테스트
SELECT * FROM DBO.UDF_SPILT 'MSSQL/EXCHANGE/OFFICE/VISTA' , >'/');
GO
element
-----------------
MSSQL
EXCHANGE
OFFICE
VISTA
(4개행적용됨)
|
마치며..
간단한 예제 중심으로 SQL서버2005 .NET CLR통합 기능에 대해서 살펴보았습니다. 이처럼, TSQL만으로는 불가능 했던 프로그래밍을 가능하게 TSQL에 날개를 달아주는 것이 .NET CLR입니다. 언제나 그렇듯이, 좋은 기능을 제대로 사용하지 않거나, 남용하면 역효과를 가져 올 수 있습니다. TSQL만으로 구현 가능한 것들은 TSQL로 구현하는게 바람직 할 것입니다. 주어진 상황에 대해서 TSQL과 CLR 중 어떠한 것을 적용해야 효율적인가 에 대한 많은 고민과 테스트 과정을 통해서 올바른 판단을 해야 할 것입니다.
감사합니다. |
