SQL Server™ 2000: 사용자 정의 함수 하 성희

사용자 정의 함수 (UDF) 사용자가 함수를 정의하여 사용 가능 SQL Server 2000에서 추가된 기능 쿼리에서 호출 가능한 다중 명령문 T-SQL 루틴 저장 프로시저의 기능과 뷰의 기능 결합 & flexibility 확장 결과 : 스칼라 값 또는 테이블 값 스키마 바운드로 생성 가능

UDF의 장점 FROM 절에서, 반환된 결과 사용 가능 테이블의 컬럼에 직접 사용 가능 중간 결과 저장 없이 직접 수행 결과 조인 가능 IN 연산자에서 사용 가능 WHERE 절의 서브 쿼리에서 사용 가능 단일 SELECT 문으로 작성할 수 없는 뷰 생성 가능 뷰와 유사하면서 매개 변수 사용 가능 새로운 산술 함수 생성 용이

UDF의 유형 – 반환 값 기준 스칼라 함수 인라인 테이블 값 함수 다중 명령문 테이블 값 함수 단일 값 반환 단일 SELECT 문 결과 / table 값 반환 다중 명령문 테이블 값 함수 다중 명령문 수행 결과 / table 값 반환

스칼라 함수 일반적인 시스템 함수와 같이 쿼리문에서 사용 가능 유의 사항 단일 값을 반환 다중 명령문 Body의 맨 마지막에 Outer BEGIN…END 사용 금지 호출 시 Owner 명시 요 단일 값을 반환 다중 명령문 Body의 맨 마지막에 RETURN <scalar-expression>

스칼라 함수 예제 CREATE FUNCTION dbo.Proper(@Name sysname) RETURNS sysname AS BEGIN DECLARE @len int, @i int, @Outname sysname, @LastSpc bit SET @len=DATALENGTH(@Name) SET @i=1 SET @LastSpc=1 SET @Outname='' WHILE @i<@len BEGIN SET @Outname=@Outname+ CASE @Lastspc WHEN 1 THEN UPPER(SUBSTRING(@Name,@i,1)) ELSE LOWER(SUBSTRING(@Name,@i,1)) END SET @LastSpc=CASE SUBSTRING(@Name,@i,1) WHEN ' ' THEN 1 ELSE 0 END SET @i=@i+1 END RETURN(@Outname) GO SELECT dbo.Proper('ha sunghee') --> 결과 : Ha Sunghee

인라인 테이블 값 함수 단일 SELECT 문으로 구성 예제 RETURN (<select 문>) CREATE FUNCTION SalesByStore(@storid varchar(30)) RETURNS TABLE AS RETURN (SELECT title, qty FROM sales s, titles t WHERE s.stor_id = @storid and t.title_id = s.title_id)

인라인 테이블 값 함수 SELECT 문의 결과를 반환 함수 body가 없다. SELECT 문의 FROM 절에서 사용 가능 “Parameterized View” 기능 다중 명령문 테이블 값 함수와의 주된 차이점 RETURNS 절에 반환 타입을 TABLE 로만 기술 (정의 불필요) 다른 함수 유형과의 차이점 outer BEGIN…END 사용 금지

다중 명령문 테이블 값 함수 Table 데이터 타입을 반환 Header : 반환 table 변수 정의 포함 Body 정의 INSERT, UPDATE, DELETE 들을 조합 사용하여 반환할 변수의 값을 구성 함수의 마지막 부분에서 반환 변수 값을 반환 결과 바로 처리 가능  임시 테이블 사용 대체

다중 명령문 테이블 값 함수 예제 CREATE FUNCTION SalesByStore(@storid varchar(30)) RETURNS @sales TABLE(title varchar(30), qty int) AS BEGIN INSERT @sales SELECT title, qty FROM sales s, titles t WHERE s.stor_id = @storid and t.title_id = s.title_id RETURN END

UDF 작성 시 권장 사항 시스템 함수 활용 복잡한 함수는 보다 단순한 함수들로 분리 모든 행을 반환하는 DEFAULT 사용 자제 예: WHERE name like ‘@value%’  WHERE name like ‘%’

스키마 바운드 함수를 함수가 참조하는 오브젝트에 연결  함수에 영향을 미치는 오브젝트의 변경 방지 SCHEMABINDING 옵션 WITH SCHEMABINDING CREATE FUNCTION, CREATE VIEW ALTER FUNCTION, ALTER VIEW REFERENCES 권한 필요

UDF의 제약 사항 영구 오브젝트 생성 불가 임시 테이블 생성 불가 저장 프로시저 호출 불가 RAISERROR() 호출 불가 Table 변수 사용 저장 프로시저 호출 불가 RAISERROR() 호출 불가 환경 설정 불가 예: SET NOCOUNT ON 호출 시 Four-part name 사용 불가 Remote function 호출 시 오브젝트 이름을 매개 변수로 전달 불가 인라인 함수에서 ORDER BY 사용하려면 TOP 사용 요

UDF: No Side Effects 작업 불가 작업 가능: 테이블에 대한 Update, global cursor statements, DDL, transaction statements 등. 작업 가능: Table 변수에 대한 Update Local cursor statements

Workaround – 원격 함수 호출 1. select testsvr1...calc_interest (10000 , 10 , 1) 2. select * from openquery (testsvr1, 'select dbo.calc_interest (10000 , 10 , 1)') 3. declare @interest int exec testsvr1...sp_executesql N'select @int=dbo.calc_interest(@prin, @rate, @years)', N'@prin int, @rate int, @years int, @int int OUT', 10000, 10, 1, @interest OUT select @interest

Workaround – 저장 프로시저 호출 불가 확장 프로시저 호출 가능 Xp로 시작하는 이름을 가진 확장 함수에 한함 예: sp_executesql 사용 불가 Sp_executesql 대체 확장 프로시저 생성 및 활용

Workaround – 오브젝트 매개 변수화 UDF에서 사용하는 오브젝트들을 매개 변수화 할 수 없다. UDF에서 확장 프로시저는 호출 가능 예제

UDF 호출하기 스칼라 UDF : 최소 2-part name 사용 테이블 값 UDF : 1-part name으로도 가능 예: SELECT dbo.Proper('ha sunghee') 테이블 값 UDF : 1-part name으로도 가능 예: SELECT * FROM SalesByStore('7131') 기본 제공 사용자 정의 함수 1-part name 사용 Table 반환 함수 : 이름에 접두어 fn 추가 예: SELECT * FROM ::fn_helpcollations()

UDF 호출하기 스칼라 UDF는 scalar expression 어디서든 사용 가능 SELECT 리스트, WHERE 절 CHECK 제약 조건 정의 DEFAULT 정의 Table UDF는 FROM 절에서 사용 가능 매개 변수를 완전히 생략할 수는 없음. DEFAULT 예약어 기술 요

재귀 호출 저장 프로시저, 트리거와 같이 UDF도 recursion 지원 제약 사항 Nesting level = 32 무한 루핑 위험 제거 32 초과 가능성 존재 시 방식 변경 요

재귀 호출 - 예제 CREATE FUNCTION dbo.ufn_GetAncestor ( @empid AS int, @lvl AS int = 1 -- 몇 단계 위 상급자인지 지정 ) RETURNS int AS BEGIN IF @lvl IS NULL or @empid IS NULL or @lvl < 0 RETURN NULL IF @lvl = 0 RETURN @empid RETURN dbo.ufn_GetAncestor( (SELECT mgrid FROM Employees WHERE empid = @empid), @lvl -1) END

재귀 호출 – 예제 SELECT dbo.ufn_GetAncestor(11, 2) GO SELECT * FROM Employees WHERE empid = dbo.ufn_GetAncestor(11, 2) SELECT E.empname AS employee, A.empname AS ancestor FROM Employees AS E LEFT OUTER JOIN Employees AS A ON A.empid = dbo.ufn_GetAncestor(E.empid, 2)

재귀 호출 예제  반복문 CREATE FUNCTION dbo.ufn_GetAncestor2 ( @empid AS int, @lvl AS int = 1 -- 몇 단계 위 상급자인지 지정 ) RETURNS int AS BEGIN IF @lvl IS NULL or @empid IS NULL or @lvl < 0 RETURN NULL DECLARE @mgrid AS int SET @mgrid = @empid WHILE @lvl > 0 AND @mgrid IS NOT NULL SELECT @mgrid = mgrid, @lvl = @lvl - 1 FROM Employees WHERE empid = @mgrid RETURN @mgrid END

활용 예 UDF를 지원하게 됨으로써, T-SQL이 통계 계산 언어로서 보다 강력해졌다. 통계 업무 계층 관리 업무 Clipping Histograms Time Series Trend Analysis 계층 관리 업무

통계 예제 - Clipping CREATE FUNCTION dbo.MiddleTemperatures(@ClipSize int = 2) RETURNS TABLE AS RETURN(SELECT v.MiddayTemp FROM tempdb..TemperatureReadings v CROSS JOIN tempdb..TemperatureReadings a GROUP BY v.MiddayTemp HAVING COUNT(CASE WHEN a.MiddayTemp <=v.MiddayTemp THEN 1 ELSE NULL END) > @ClipSize AND COUNT(CASE WHEN a.MiddayTemp >= v.MiddayTemp THEN 1 ELSE NULL END) >@ClipSize) GO SELECT * FROM dbo.MiddleTemperatures(2) ORDER BY MiddayTemp

통계 예제 - Histogram CREATE FUNCTION dbo.SalesHistogram(@payterms varchar(12)='%') RETURNS TABLE AS RETURN( SELECT PayTerms=isnull(s.payterms,'NA'), “- 10"=COUNT(CASE WHEN s.sales>=0 AND s.sales<10 THEN 1 ELSE NULL END), "10-19"=COUNT(CASE WHEN s.sales>=10 AND s.sales<20 THEN 1 ELSE NULL END), "20-29"=COUNT(CASE WHEN s.sales>=20 AND s.sales<30 THEN 1 ELSE NULL END), "30-39"=COUNT(CASE WHEN s.sales>=30 AND s.sales<40 THEN 1 ELSE NULL END), "40-49"=COUNT(CASE WHEN s.sales>=40 AND s.sales<50 THEN 1 ELSE NULL END), "50 or more"=COUNT(CASE WHEN s.sales >=50 THEN 1 ELSE NULL END) FROM (SELECT t.title_id, s.payterms, sales=ISNULL(SUM(s.qty),0) FROM titles t LEFT OUTER JOIN sales s ON (t.title_id=s.title_id) GROUP BY t.title_id, payterms) s WHERE s.payterms LIKE @payterms GROUP BY s.payterms ) GO SELECT * FROM dbo.SalesHistogram(DEFAULT) SELECT * FROM dbo.SalesHistogram('Net 30')

계층 관리 예제 --서브 트리의 합계 계산 CREATE FUNCTION dbo.ufn_GetSubtreeSalary ( @mgrid AS int ) RETURNS int AS BEGIN RETURN (SELECT Salary FROM Employees WHERE empid = @mgrid) + CASE WHEN EXISTS (SELECT * FROM Employees WHERE mgrid = @mgrid) THEN (SELECT SUM(dbo.ufn_GetSubtreeSalary(empid)) FROM Employees WHERE mgrid = @mgrid) ELSE 0 END

계층 관리 예제 CREATE FUNCTION ufn_GetSubtree ( @mgrid AS int ) RETURNS @tree table ( empid int NOT NULL, mgrid int NULL, empname varchar(25) NOT NULL, salary money NOT NULL, lvl int NOT NULL, path varchar(900) NOT NULL ) AS BEGIN DECLARE @lvl AS int, @path AS varchar(900) SELECT @lvl = 0, @path = '.' INSERT INTO @tree SELECT empid, mgrid, empname, salary, @lvl, '.' + CAST(empid AS varchar(10)) + '.' FROM Employees WHERE empid = @mgrid WHILE @@ROWCOUNT > 0 BEGIN SET @lvl = @lvl + 1 INSERT INTO @tree SELECT E.empid, E.mgrid, E.empname, E.salary,@lvl, T.path + CAST(E.empid AS varchar(10)) + '.' FROM Employees AS E JOIN @tree AS T ON E.mgrid = T.empid AND T.lvl = @lvl - 1 END RETURN

계층 관리 예제 SELECT * FROM ufn_GetSubtree(2) ORDER BY path GO SELECT REPLICATE (' | ', lvl) + empname AS employee FROM ufn_GetSubtree(1)

계층 관리 예제 CREATE FUNCTION ufn_GetMgmtChain ( @empid AS int ) RETURNS @tree table ( empid int NOT NULL, mgrid int NULL, empname varchar(25) NOT NULL, salary money NOT NULL, lvl int NOT NULL ) AS BEGIN DECLARE @lvl AS int SET @lvl = 0 INSERT INTO @tree SELECT empid, mgrid, empname, salary, @lvl FROM Employees WHERE empid = @empid WHILE @@ROWCOUNT > 0 BEGIN SET @lvl = @lvl + 1 INSERT INTO @tree SELECT E.empid, E.mgrid, E.empname, E.salary, @lvl FROM Employees AS E JOIN @tree AS T ON E.empid = T.mgrid AND T.lvl = @lvl - 1 END RETURN GO SELECT * FROM ufn_GetMgmtChain(14) ORDER BY lvl DESC

UDF 삭제하기 DROP FUNCTION 삭제 작업 전 종속성 점검 요망 예: DROP FUNCTION dbo.Proper 예: 제약 조건에 사용된 경우 삭제 불가 다른 함수, 뷰, 트리거, 저장 프로시저에서 사용되는 함수 삭제  해당 함수 참조 오브젝트 다음 실행 시 오류 발생 스키마 바운드와 연관

종속 관계 확인 sp_depends sp_Msdependencies 종속 레벨 1로 제한적 예: exec sp_depends fn_CubicVolume_Main sp_Msdependencies Sp_depends 보다 진보된 sp 종속 레벨 제한 없음 Undocumented stored procedure Enterprise Manager에서 사용 예: exec sp_MSdependencies fn_CubicVolume_Main, null, 0x0411FF fn_CubicVolume_Main, null, 0x0011FF

Meta-data SELECT LEFT(name,20) AS [Function], OBJECTPROPERTY(id,'IsScalarFunction') AS Scalar, OBJECTPROPERTY(id,'IsTableFunction') AS [Table], OBJECTPROPERTY(id,'IsInlineFunction') AS Inline, OBJECTPROPERTY(id,'IsDeterministic') AS Determ, OBJECTPROPERTY(id,'IsSchemaBound') AS SchemaBound FROM sysobjects WHERE type in ('IF','TF','FN') ORDER BY name

확정성(Determinism) 확정적 함수가 되기 위한 조건: 동일한 입력 값  동일한 결과 값 함수를 인덱스된 뷰 또는 인덱스된 계산된 컬럼에 사용할 수 있는지를 결정 ObjectProperty() 함수를 사용하면 함수가 확정적인지 확인할 수 있다 SELECT OBJECTPROPERTY (OBJECT_ID('fn_CubicVolume'), 'IsDeterministic')

확정적 함수 조건 로컬 table 변수를 제외하고 어떤 테이블도 액세스하지 않는다 비확정적 함수를 호출하지 않는다 (builtin 이건 UDF이건) 스키마 바운드이다 CREATE FUNCTION 시점에 자동으로 확정성을 점검한다

비확정적 함수 함수가 스키마 바인드가 아니다. 그 함수가 호출하는 함수 중 적어도 하나가 비확정적이다. 그 함수가 함수 범위 밖의 데이터베이스 오브젝트를 참조한다. 그 함수가 확장 저장 프로시저 (extended stored procedure)를 호출한다.

시스템 함수 시스템 함수 조건 모든 데이터베이스에서 데이터베이스 이름 없이 쿼리 가능 Database : master Owner : system_function_schema Name : fn으로 시작 & 소문자 ‘allow updates’ = 1

시스템 함수 조회 USE master GO SELECT name FROM sysobjects WHERE uid=USER_ID('system_function_schema') AND (OBJECTPROPERTY(id, 'IsScalarFunction')=1 OR OBJECTPROPERTY(id, 'IsTableFunction')=1 OR OBJECTPROPERTY(id, 'IsInlineFunction')=1)

시스템 함수 만들기 - 예제 USE master GO exec sp_configure 'allow updates',1 reconfigure with override CREATE FUNCTION system_function_schema.fn_greatest (@x bigint, @y bigint) RETURNS bigint AS BEGIN RETURN (CASE WHEN @x>@y THEN @x ELSE @y END) END exec sp_configure 'allow updates',0

기본 제공 함수 새로운 기본 제공 함수들 GetUTCDate() SCOPE_IDENTITY() 현재의 국제 표준시 반환 SCOPE_IDENTITY() 같은 범위에서 삽입된 마지막 identity 값 반환 IDENT_CURRENT(‘tblname’) 임의의 세션과 범위에서 마지막으로 삽입된 identity 값 반환

성능 측면 인라인 테이블 값 함수와 다중 명령문 테이블 값 함수 비교 읽기 전용 뷰와 인라인 테이블 값 함수 대개 인라인 테이블 값 함수가 다중 명령문 테이블 값 함수보다 성능이 좋다. 인라인 함수가 인덱스를 보다 효율적으로 사용 인라인 함수로 가능한 경우에는 인라인 함수 사용 권장 읽기 전용 뷰와 인라인 테이블 값 함수 인라인 테이블 값 함수는 매개 변수 사용 가능 인라인 테이블 값 함수는 맨 처음 실행 시 최적화되고 컴파일됨  성능 이득

질문 ?