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

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

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

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

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

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

6. 스칼라 함수 예제 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) END GO SELECT dbo.Proper('ha sunghee') --> 결과 : Ha Sunghee

7. 인라인 테이블 값 함수 • 단일 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)

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

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

10. 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 다중 명령문 테이블 값 함수 예제

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

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

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

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

15. 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

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

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

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

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

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

21. 재귀 호출 - 예제 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

22. 재귀 호출 – 예제 SELECT dbo.ufn_GetAncestor(11, 2) GO SELECT * FROM Employees WHERE empid = dbo.ufn_GetAncestor(11, 2) GO 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) GO

23. 재귀 호출 예제  반복문 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

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

25. 통계 예제 - 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

26. 통계 예제 - 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')

27. 계층 관리 예제 --서브 트리의 합계 계산 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 END

28. 계층 관리 예제 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 END

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

30. 계층 관리 예제 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 END GO SELECT * FROM ufn_GetMgmtChain(14) ORDER BY lvl DESC

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

32. 종속 관계 확인 • sp_depends • 종속 레벨 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 exec sp_MSdependencies fn_CubicVolume_Main, null, 0x0011FF

33. 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

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

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

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

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

38. 시스템 함수 조회 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) GO

39. 시스템 함수 만들기 - 예제 USE master GO exec sp_configure 'allow updates',1 reconfigure with override GO 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 GO exec sp_configure 'allow updates',0 reconfigure with override GO

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

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

42. 질문 ?