- Sql Server CLR Integration
- ¿¹Á¦
- ÇÁ·ÎÁ§Æ® »ý¼º
- UDF Á¦ÀÛ
- ¾î¼Àºí¸® µî·Ï
- UDF µî·Ï
- UDF »ç¿ë
- ¸µÅ©
1 Sql Server CLR Integration
»ç¿ëÀÚ ÇÔ¼ö¸¦ ¸¸µå´Â °ÍÀº ÀÌÀü¿¡µµ °¡´ÉÇßÁö¸¸, CLRÀÇ µµÀÔÀ¸·Î ±× ÀÏÀÌ ÈξÀ ÆíÇØÁ³´Ù.
2 ¿¹Á¦
'Inside Microsoft SQL Server 2005: T-SQL Programming'¿¡ ³ª¿Â ¿¹Á¦·Î¼, CLR ÂÊÀÇ Á¤±Ô½Ä ÇÔ¼ö¸¦ SQL Server ÂÊ¿¡¼ »ç¿ëÇÏ´Â ¿¹Á¦´Ù. .NET ¹öÀü Â÷ÀÌ ¶§¹®¿¡ ¿¡·¯³ª´Â ºÎºÐµéÀº ¼öÁ¤Çß´Ù. (2.0 ±âÁØ)
2.1 ÇÁ·ÎÁ§Æ® »ý¼º
- New Project -> Visual C# -> Windows -> Ŭ·¡½º ¶óÀ̺귯¸®
¼¹ö¸¦ ÁöÁ¤ÇØÁÖÁö´Â ¾Ê¾Æµµ µÇ³ª, ¹ÙÀεùÀÌ ±ÍÂú´Ù.
- New Project -> Visual C# -> µ¥ÀÌÅͺ£À̽º -> SQL Server ÇÁ·ÎÁ§Æ®
°³¹ßÀÌ Æí¸®ÇØÁö±â´Â Çϳª, ¼¹ö¸¦ ÁöÁ¤ÇØ¾ß ÇÑ´Ù´Â Á¡ÀÌ Á» ²¬²ô·´´Ù.
2.2 UDF Á¦ÀÛ
using System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
//using System.Diagnostics;
//using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
namespace ClrUtility
{
public partial class ClrUtility
{
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlBoolean fn_RegExMatch(SqlString inpStr, SqlString regExStr)
{
if (inpStr.IsNull || regExStr.IsNull)
return SqlBoolean.Null;
else
return (SqlBoolean)Regex.IsMatch(inpStr.Value, regExStr.Value, RegexOptions.CultureInvariant);
}
}
} ÄÄÆÄÀÏÇϰí DLLÀ» ¾îµò°¡¿¡ µÐ´Ù. ¾îÆ®¸®ºäÆ® ÂÊ Á» Á¶»çÇØºÁ¾ßÇÒ °Í °°Àºµ¥.
2.3 ¾î¼Àºí¸® µî·Ï
SET NOCOUNT ON;
USE master;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
IF DB_ID('ClrUtilityDatabase') Is NOT NULL
DROP DATABASE ClrUtilityDatabase;
GO
CREATE DATABASE ClrUtilityDatabase;
GO
USE ClrUtilityDatabase;
GO
CREATE ASSEMBLY ClrUtility
FROM 'C:\ClrUtility.dll'
WITH PERMISSION_SET = SAFE; PERMISSION_SET ºÎºÐ¿¡ µé¾î°¡´Â ³»¿ëÀº ´ÙÀ½°ú °°´Ù.
- SAFE: ÆÄÀÏ, ³×Æ®¿öÅ©, ȯ°æº¯¼ö, ·¹Áö½ºÆ®¸® µî¿¡ Á¢±ÙÇÒ ¼ö ¾ø´Ù. ±âº»°ªÀÌ´Ù.
- EXTERNAL_ACCESS: ÆÄÀÏ, ³×Æ®¿öÅ©, ȯ°æº¯¼ö, ·¹Áö½ºÆ®¸® µî¿¡ Á¢±ÙÇÒ ¼ö ÀÖ´Ù.
- UNSAFE: EXTERNAL_ACCESS ±ÇÇÑ + unmanaged ÄÚµå ½ÇÇà ±ÇÇÑ
Âü°í·Î EXTERNAL_ACCESS ÀÌ»óÀÇ ±ÇÇÑÀ» ÁÖ±â À§Çؼ´Â ¾Æ·¡¿Í °°Àº ¸í·É¾î¸¦ ÅëÇØ TRUSTWORTHY ¼Ó¼ºÀ» ÄÑÁà¾ß ÇÑ´Ù.
ALTER DATABASE ClrUtilityDatabase SET TRUSTWORTHY ON
2.4 UDF µî·Ï
USE ClrUtilityDatabase;
GO
IF OBJECT_ID('dbo.fn_RegExMatch') IS NOT NULL
DROP FUNCTION dbo.fn_RegExMatch;
GO
CREATE FUNCTION dbo.fn_RegExMatch
(@inpStr AS NVARCHAR(MAX), @regExStr AS NVARCHAR(MAX))
RETURNS BIT
EXTERNAL NAME ClrUtility.[ClrUtility.ClrUtility].fn_RegExMatch; ³×ÀÓ½ºÆäÀ̽º ºÎºÐ °³°°À¸´Ï ÁÖÀÇ.
2.5 UDF »ç¿ë
USE ClrUtilityDatabase;
SELECT dbo.fn_RegExMatch('abcd', '[a-z]+');
3 ¸µÅ©
SeriousMoin v1 (koMoinMoin 1.0a4 Modified)