이처럼 여러가지 함수를 조합하여 사용자가 원하는 결과를 만들어 내는데 SQL Server에서는 이런 정해진 함수가 아닌 사용자가 원하는 특정한 함수를 직접 만들어서 사용할 수도 있습니다. 이러한 유형의 함수를 '사용자 정의 함수'라고 합니다.
1. 사용자 정의 함수 생성
| Create Function <이름>( @<변수명> ) Returns <데이터형> As Begin <query> Return @<결과값> End |
값을 반환하는 함수를 정의합니다.
함수생성 구문에서 @<변수명>은 사용자 함수가 실행되는 본체내부에서 필요한 값을 전달해 주는 매개변수입니다. 이 매개변수를 받아서 <Query>내에 구현된 실행문에 따라 처리가 진행되고 그 결과를 Return을 통해 얻게 됩니다.(물론 매개변수가 필요하지 않으면 @<변수명>부분은 생략할 수 있습니다.)
Data Returns의 <Data Type>에는 반환되는 값의 Data형을 지정합니다.
다음은 두개의 정수값을 받아서 이 값을 더하고 결과를 표시하는 함수의 작성 예제입니다.
| Create Function data_Sum( @Value1 Int, @Value2 Int ) Returns Int As Begin Declare @Sum_Value Int; Set @Sum_Value = (@Value1 + @Value2); Return @Sum_Value; End Select dbo.data_Sum(10, 20); |
함수를 생성한 후 해당 함수에 값을 넘겨 결과를 확인합니다.
사용자 정의함수 생성시 매개변수를 전달하고자 할때 기본값을 정하고자 한다면 @Value1 Int = 10의 형태를 통해서 원하는 값을 설정할 수 있습니다. 이렇게 하면 해당 매개변수에 값을 지정하지 않았을 경우 기본값으로 정의된 10이라는 Ddata를 사용하게 되는 것입니다.(기본값이 지정된 매개변수에 값을 넘겨주지 않고 기본값 그대로 사용하기 위해서는 Default구문을 사용합니다.)
| Create Function data_Sum( @Value1 Int = 10, @Value2 Int = 20 ) Returns Int As Begin Declare @Sum_Value Int; Set @Sum_Value = (@Value1 + @Value2); Return @Sum_Value; End |
@Value1과 @Value2에 10과 20이라는 기본값을 지정하였습니다.
| Select dbo.data_Sum(Default, 50); |
기본값 사용의 경우 Default라는 Keyword를 사용합니다.
위에서 보시는 것처럼 값을 반환화는 함수의 경우 Return문을 통해 특정값을 지정하여 반환하도록 하고 있습니다. 그러나 반환하는 Data가 값이 아닌 Table형태의 경우 약간 다른 방식으로 Return을 지정해야 합니다.
| Create Function Return_table( @Value1 Int = 10 ) Returns Table As Return( Select DepartmentID, Name, GroupName From HumanResources.Department Where DepartmentID = @Value1 ) |
Table형태의 Data를 반환하는 함수로서 넘겨지는 매개변수의 @Value1의 값에 따라 HumanResources.Department Table을 조회하도록 하였습니다. 값 형태와 달리 Table반환 형식에는 Begin/End가 존재하지 않으며 결과는 Return()을 통해 Select 조회 전체를 지정하고 있는데 이때 Returns를 통해 반환되는 데이터형에도 Table만 올 수 있습니다.
| Select * From dbo.Return_table(8); |
Table반환형식의 함수를 호출합니다. 만일 Table반환형식의 함수 호출이라면 함수가 아닌 실제 Table처럼 From절까지 써서 호출해야 합니다.
지금까지 값을 반환하는 형태와 Table을 반환하는 형태의 두가지 함수정의에 대해 살펴보았습니다.
값을 반환하는 형태의 경우 Begin/End안의 Query문을 통해 특정값을 얻을 수 있도록 다소 자유로운 구현이 가능했습니다만 오로지 값의 형태만 반환할 수 있다는 단점이 있었습니다. Table의 경우에도 값이 아닌 Table형태의 반환값으로 값형식 보다는 좀더 넓은 범위의 반환형태를 가지고 있지만 Begin/End가 없어 Table조회 이외의 구문구현이 어렵다는 단점이 있었습니다.
만일 어떤 함수에서 위 두가지 특징을 모두 가져야 하는 경우에는 이 두개를 섞은 결합적인 함수를 구현할 수도 있습니다.
| Create Function Return_table( @Value1 Int = 10, @Value2 Int = 20 ) Returns @temp_table Table( ID Int, Name nVarChar(50), GroupName nVarChar(50) ) As Begin Declare @Return_Value Int; Set @Return_Value = (@Value1 + @Value2); Insert Into @temp_table Select DepartmentID, Name, GroupName From HumanResources.Department Where DepartmentID = @Return_Value; Return; End |
위 함수는 Table형식의 값을 반환하면서 값 형식처럼 특정 Query구현이 가능한 형태를 가지는 사용자 함수정의입니다. Table반환값의 경우에는 Returns다음에 반환할 임시 Table형태를 지정하고 이후 Beign/End를 써서 Returns에정의된 임시Table에 값을 넣는 구문을 완성하면 됩니다. 이렇게 되면 Return문을 만나 끝나는 시점에서 Returns에 지정된 임시 Table의 내용을 반환하게 될 것입니다.
| Select * From dbo.Return_table(8, 2); |
위에서 구현한 사용자 정의 함수를 호출합니다.
정의된 함수를 수정할때는 Create대신 Alter문만 써주면 됩니다. 그외에 것은 생성할때와 별 차이가 없으므로 큰 어려움이 없을 것입니다.(물론 함수이름은 맞아야 하며 값형태의 반환값을 갖는 함수에서 Table형태의 값을 반환하도록 수정하는 것은 불가능하고 그 반대의 경우도 마찬가지 입니다.)
| Alter Function Return_table( @Value1 Int = 10, @Value2 Int = 20, @Value3 Int ) Returns @temp_table Table( ID Int, Login_ID nVarChar(256), JobTitle nVarChar(50) ) As Begin Declare @Return_Value Int; Set @Return_Value = (@Value1 + @Value2); Set @Return_Value = (@Return_Value - @Value3); Insert Into @temp_table Select BusinessEntityID, LoginID, JobTitle From HumanResources.Employee Where BusinessEntityID = @Return_Value; Return; End |
Return_table함수를 수정하여 인수를 3개 가지도록 하였으며 대상 Table도 HumanResources.Employee Table로 바꾸었습니다.
| Select ID, Login_ID, JobTitle From dbo.Return_table(8, 2, 1) |
변경된 사용자 정의 함수를 호출합니다.
사용자 정의 함수의 삭제하려면 Drop을 사용합니다.
| Drop Function Return_table; |
2. Schemabinding
위에서 HumanResources.Employee Table을 조회하는 사용자 함수를 생성했을때 만일 HumanResources.Employee Table의 이름이 바뀌거나 혹은 함수내에서 구현한 Column의 이름이 바뀌면 어떻게 될까요? 당연히 함수가 작동할때 오류를 일으키게 될 것입니다.
이런 상황이 생기는 것을 방지하기 위해 함수를 정의할때는 함수내에서 사용하는 특정 Table이나 View와 같은 대상의 이름이 바뀌지 않도록 Schemabinding Option을 지정할 수 있습니다.
| Create Function Return_table( @Value1 Int = 10, @Value2 Int = 20, @Value3 Int ) Returns @temp_table Table( ID Int, Login_ID nVarChar(256), JobTitle nVarChar(50) ) With Schemabinding As Begin Declare @Return_Value Int; Set @Return_Value = (@Value1 + @Value2); Set @Return_Value = (@Return_Value - @Value3); Insert Into @temp_table Select BusinessEntityID, LoginID, JobTitle From HumanResources.Employee Where BusinessEntityID = @Return_Value; Return; End |
With Schemabinding option을 설정한 후
| sp_rename 'HumanResources.Employee', 'Employees' |
Employee Table의 이름이 Employees로 바뀌도록 변경을 시도합니다.
3. 생성한 사용자 함수가 어디에서 쓰이고 있을까?
사용자 정의 함수를 삭제하는 것은 위에서 본것처럼 생성이나 변경할때보다 아주 간단합니다. 하지만 잘못 삭제했다가는 해당 함수를 사용하고 있는 다른 부분에서 생각지 못한 오류를 만날 수도 있습니다. 따라서 현재 삭제하고자 하는 함수가 혹시 다른곳에서도 사용되고 있는지 확인해 볼 필요가 있는데 이 경우 Sp_depends를 사용하면 됩니다.
| sp_depends ufnGetContactInformation; |
Adventureworks2008 예제 Database의 ufnGetContactInformation 이라는 사용자 정의 함수가 어디에서 쓰이고 있는지 확인합니다.
4. Apply
Table형태의 값을 반환하는 함수에 한하여 매개변수에 값을 전달할때 일반적으로 사용자가 직접 값을 작성하지만 Apply를 이용하면 다른 Table을 통해 값을 순차적으로 줄 수도 있습니다.
| Create Table tmp_table( Col1 Int, Col2 Int20 ) Create Function ( @Value1 Int = 10 ) Returns Table As Return( Select , Name, GroupName From HumanResources.Department Where DepartmentID = @Value1 ) |
Apply를 Test하기 위한 함수를 생성합니다.
| Select * From HumanResources.EmployeeDepartmentHistory As A Outer Apply Return_table(A.DepartmentID); |
HumanResources.EmployeeDepartmentHistory Table의 내용을 가져오되 위에서 정의한 사용자 함수를 호출하여 매개변수에 HumanResources.EmployeeDepartmentHistory의 DepartmentID열값을 전달하며 이렇게 조회된 함수의 내용과 HumanResources.EmployeeDepartmentHistory Table이 Outer Join되어 결과를 표시하게 됩니다.(Outer는 Join방식입니다. 필요하다면 다른 Join을 지정해 사용할 수도 있습니다.)
댓글 없음:
댓글 쓰기