[2k8] CLR 등록 및 사용(구분자로 구성된 열의 행 변환)

/*
-- Title : [2k8] CLR 등록 및 사용(구분자로 구성된 열의 행 변환)
-- Key word : CLR dll function assembly 어셈블리 함수 멀티값 싱글값 clr enabled
*/

-- CLR ENABLED 확인
SP_CONFIGURE 'CLR ENABLED';

-- CLR ENABLED 설정
SP_CONFIGURE 'CLR ENABLED', 1

RECONFIGURE WITH OVERRIDE;

-- 외부 개체 접근위한 DB Trustworthy 설정
ALTER DATABASE TestDB SET TRUSTWORTHY ON;

-- 설정 확인
SELECT NAME, IS_TRUSTWORTHY_ON FROM SYS.DATABASES

-- DB 변경
USE TestDB;

-- 어셈블리 등록
CREATE ASSEMBLY pjt_Split FROM 'C:\Database\pjt_Split.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;

-- 함수 생성(테이블 함수)
-- DLL 함수명과 SQL 함수명 등록시 대소문자 구분됨.
CREATE FUNCTION dbo.fn_CLR_Split
( @str nvarchar(max), @delimiter nvarchar(10))
RETURNS TABLE
( id nvarchar(max) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME pjt_Split.cls_Split.fn_CLR_Split
GO

/* 스칼라 함수 생성시 샘플
CREATE FUNCTION dbo.fn_Search_Word
( @source nvarchar(max), @searchword nvarchar(20))
RETURNS BIT
AS
EXTERNAL NAME pjt_CLOB_Search.Util.fn_Search_Word
GO
*/

/* 프로시저 생성시 샘플
CREATE PROCEDURE dbo.up_CLR_cl_jp
( @param bigint)
AS 
EXTERNAL NAME Pjt_CLR_cls.Cls_CLR_cls.up_CLR_cls
GO
*/

-- 메타확인
SELECT A.*
FROM SYS.ASSEMBLY_FILES  A
INNER JOIN SYS.ASSEMBLIES B
ON A.ASSEMBLY_ID = B.ASSEMBLY_ID;

SELECT OBJECT_NAME(OBJECT_ID) "OBJECT_NAME", *
FROM SYS.ASSEMBLY_MODULES  A
INNER JOIN SYS.ASSEMBLIES B
ON A.ASSEMBLY_ID = B.ASSEMBLY_ID;

-- 활용
SELECT * FROM dbo.fn_CLR_split('1/2/3/4/5','/');

-- 함수 삭제
DROP FUNCTION dbo.fn_CLR_Split;

-- 어셈블리 삭제
DROP ASSEMBLY pjt_Split;

-- 성능
-- SQL Function보다 DLL Function이 무자게~!! 빨랐다.
-- 물론 내부 Split함수를 사용해서 일지도..
-- 저사양 서버에서는 느리더이다..!!

-- C# 소스

 

 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

public partial class cls_Split
{
    [SqlFunction(Name = "fn_CLR_Split",
    FillRowMethodName = "FillRow",
    TableDefinition = "id nvarchar(max)")]

    public static IEnumerable fn_CLR_Split(SqlChars str, SqlChars delimiter)
    {
        SqlString test = str.ToSqlString();

        if (delimiter.Length == 0)
            return new string[1] { test.Value };
       
        return test.Value.Split(delimiter[0]);
    }

    public static void FillRow(object row, out SqlString str)
    {
        str = new SqlString((string)row);
    }
};