|
강좌 시작 >
2.2 사용자 정의 함수의 유형
앞서 필자는 사용자 정의 함수가 3가지 유형으로 나뉘어 진다고 언급하였다. 이제 3가지 유형들에 대해서 알아보도록 하자. 그럼 사용자 정의 함수의 유형을 분류하는 기준은 무엇일까? 간략하게 말하자면 사용자 정의 함수가 반환하는 데이터의 형이다. 반환되는 데이터 형? 어디 선가 본 듯한 희미한 기억이 있는가? 그렇다. 앞서서 사용자 정의 함수를 정의하면서 RETURNS 구문을 이용하여 반환되는 데이터형을 지정하였다.
|
CREATE FUNCTION fn_Get최대생산번호
(@생산일자 smalldatetime )
RETURNS char (11) |
"fn_Get최대생산번호"를 정의하는 구문을 다시 보면, RETURNS 구문 다음에 Char형의 11자리를 반환한다고 정의하고 있다. 바로 RETURNS 구문 다음에 어떠한 데이터의 형이 오는지에 따라서 사용자 정의 함수의 유형을 분류하는 것이다. 이에 따라서 사용자 정의 함수는
- 스칼라 함수
- 인라인 테이블 값의 함수
- 다중 문 테이블 값의 함수
등으로 나누어 볼 수 있다. 하나하나 차례로 각 함수의 내용을 둘러보기로 하자.
스칼라 함수
먼저 스칼라 함수이다. 스칼라 함수는 말 그대로 스칼라 값(또는 상수, 리터럴 값이라고도 한다.)을 반환하는 함수이다. 그럼 스칼라 값은 뭘까? 스칼라 값은 앞서 “fn_Get최대생산번호” 사용자 정의 함수에서 값을 반환하는 것처럼, char, varchar, integer 등과 같이 문자열이나 숫자형의 데이터를 말한다. 그럼 반대로 스칼라 값이 아닌 것에는 어떠한 데이터 형이 있을까? 이에는 timestamp, text, ntext, image, 사용자 정의 데이터 형, table, cursor 등이 있다.
스칼라 함수는 스칼라 형의 데이터를 반환하기 때문에, 마치 스칼라 데이터 형인 것처럼 사용이 될 수 있다. 다음의 구문을 보도록 하자.
|
-- 스칼라 함수의 사용
INSERT 생산목록(생산번호, 모델명, 생산담당자, 생산일자)
VALUES(dbo.fn_Get최대생산번호('2001-04-20'), 'MV100', '김성택', '2001-04-20' )
GO
-- 데이터의 확인
SELECT *
FROM 생산목록
WHERE 생산일자 = '2001-04-20'
GO |
INSERT 구문의 생산번호 컬럼을 보면, dbo.fn_Get최대생산번호('2001-04-20')로 사용자 정의 함수를 호출하고 있다. 사용자 정의 함수를 사용하지 않는다면, 여기에는 생산번호 컬럼에 입력될 최대 생산번호에 대한 스칼라 값이 제공되어야 한다. 하지만 위의 구문에서는 사용자 정의 함수를 호출하여 스칼라 값을 반환하고 있다. 즉 “dbo.fn_Get최대생산번호” 사용자 정의 함수는 스칼라 함수인 것이다. 이렇게 스칼라 함수는 스칼라 값이 사용될 수 있는 곳이라면, 어디서든지 대신하여 사용될 수 있다. 스칼라 함수의 사용에 대해서는 바로 뒤에서 설명을 하게 될 것이다.
여기서는 간략하게 스칼라 함수를 정의하는 방법에 대해서 살펴보도록 하자. 앞서 설명을 한 것처럼, "dbo.fn_Get최대생산번호"는 스칼라 함수이다. 간략하게 스칼라 함수의 정의구문을 둘러보도록 하자.
|
CREATE FUNCTION <scalar_function_name, sysname, test_function>
(<@param1, sysname, @p1> <data_type_for_param1, , int>,
<@param2, sysname, @p2> <data_type_for_param2, , int>)
RETURNS <function_data_type, ,int>
AS
BEGIN
<function_body, , RETURN @p1 + @p2 >
-- eg.
-- DECLARE @sum AS int
-- SELECT @sum = @p1 + @P2
-- RETURN @sum
END
GO |
위의 구문은 쿼리 분석기의 템플릿을 통해서 제공되는 기본적인 스칼라 함수의 정의 구문이다.
|
CREATE FUNCTION <scalar_function_name, sysname, test_function>
(<@param1, sysname, @p1> <data_type_for_param1, , int>,
<@param2, sysname, @p2> <data_type_for_param2, , int>) |
① 먼저 첫번째 라인을 보자. 첫번째 라인은 사용자 정의 함수의 명칭을 명시하는 부분이다. 사용자 정의 함수 역시 SQL Server의 개체이기 때문에 CREATE 구문에 의해서 정의되고 있다. 사용자 정의 함수의 명칭은 다른 개체들과 마찬가지로 식별자 규칙에 따라서 작성되면 된다. 이때 가능하면 다음의 구문과 같이, 소유자명을 명시하여 주는 것이 좋다.
|
CREATE FUNCTION dbo.fn_EndofMonth |
앞서서 이미 설명을 하였던 것처럼, 사용자 정의 함수를 호출할 때 반드시 "소유자.사용자 정의 함수명"과 같은 형식으로 호출되어야 한다. 또한 사용자 정의 함수는 소유자 별로 각각 달리 인식되기 때문에, 해당 사용자 정의 함수를 소유하고 있는 소유자까지도 개체명의 일부라고 생각하는 것이 좋다. 즉 dbo.fn_EndofMonth와 DBUser.fn_EndofMonth는 각각 다른 사용자 정의 함수로 인식된다.
② 다음의 두 라인은 매개변수를 명시하는 부분이다. 사용자 정의 함수의 매개변수는 저장 프로시저의 매개변수와 매우 흡사하다. 매개변수명은 반드시 "@"로 시작하여야만 하며, 필요에 따라서는 생략도 가능하다. 물론 저장 프로시저의 매개변수와 같이, 지역변수로도 사용이 가능하며, 기본값의 지정도 가능하다. 하지만 저장 프로시저와 같이 출력 매개변수는 지원하지 않고 있다.
여기서 주의할 점이 있다. 사용자 정의 함수에 정의된 매개변수에 대해서는 반드시 매개변수를 전달하여야 한다는 것이다.
|
CREATE FUNCTION dbo.fn_EndofMonth
(@Year char(4) = '2001' ,
@Month tinyint ) |
위의 구문과 같이 두 개의 매개변수가 정의된 사용자 정의 함수가 있다고 가정을 하자. 함수의 매개변수를 보면, @Year, @Month 두 개의 매개변수가 정의되어 있다. 특히 @Year 매개변수에는 '2001'이라는 기본값이 지정되어 있다. 이렇게 매개변수에 대해서 기본값이 지정되어 있다고 하더라도, 다음과 같이 모든 매개변수에 대해서 값을 전달하여야 한다.
|
잘못된 사용
- SELECT dbo.fn_EndofMonth (, 5) ---> (X)
- SELECT dbo.fn_EndofMonth ('', 5) ---> (X)
- SELECT dbo.fn_EndofMonth (NULL, 5) ---> (X)
- SELECT dbo.fn_EndofMonth @Month = 5 ---> (X)
올바른 사용
- SELECT dbo.fn_EndofMonth (Default, 5) ---> (O)
- SELECT dbo.fn_EndofMonth @Year = Default, @Month = 5 ---> (O)
|
③ 다음의 세 번째 라인은 반환되는 데이터의 형을 명시하는 부분이다.
|
RETURNS <function_data_type, ,int>
|
여기에는 앞서 정의하였던 "fn_Get최대생산번호" 사용자 정의 함수와 같이 char형이나 다른 스칼라 데이터 형의 어떠한 데이터라도 올 수 있다.
|
BEGIN
<function_body, , RETURN @p1 + @p2 >
-- eg.
-- DECLARE @sum AS int
-- SELECT @sum = @p1 + @P2
-- RETURN @sum
END
GO |
④ 다음의 BEGIN ~ END 블록으로 묶여져 있는 부분은 실질적인 해당 사용자 정의 함수의 정의 부분이다. 여기서 주의 깊게 보아야 할 부분이, 반드시 RETURN 구문을 이용하여 앞서 Returns 구문에 의해서 정의된 데이터형의 값을 반환해주어야 한다는 것이다.
2.2.2. 인라인 테이블 값의 함수
필자가 사용자 정의 함수를 접하면서 탄성을 질렀던 이유가 바로 사용자 정의 함수가 테이블 형식의 데이터를 반환 한다는 사실이었다. 기존에 저장 프로시저나 T-SQL 구문을 이용하여 일련의 결과집합의 데이터를 이용하기 위해서는 임시테이블을 생성하여 데이터를 입력하여 놓거나 커서를 이용하는 방법 외에는 다른 방법이 없었다. 상당히 복잡한 T-SQL 구문을 작성해야 했으며, 물론 그에 따라서 SQL Server의 성능 또한 저하되었던 것도 사실이다. SQL Server 2000에서는 테이블 형식의 데이터형과 테이블 값을 반환하는 함수를 제공함으로써, 좀 더 확장된 작업을 수행할 수 있는 기능을 지원하고 있다. 이러한 테이블 값을 반환하는 사용자 정의 함수에 대해서 알아보도록 하자.
|
테이블 데이터 형식
테이블(table)형은 테이블 값 함수의 결과 집합을 나중에 사용할 수 있도록 임시적으로 저장할 수 있는 데이터 형이다. 테이블과 같은 형태이며, 임시 테이블을 만들어 사용하는 것보다 성능이 좋아지기 때문에 가능할 때마다 table형을 사용하는 것을 권장하고 있다. |
스칼라 함수 이외의 함수들은 테이블 값을 반환하게 된다. 테이블 형식의 데이터를 반환하는 함수는 다시 어떻게 테이블 형식으로 데이터를 반환하는지에 따라서 인라인 테이블 값의 함수와 다중 문 테이블 값의 함수로 분류할 수 있다. 테이블 값을 반환하는 함수를 사용하면 여러 면에서 다양한 기능을 사용할 수 있다. 예를 들어 기존의 뷰(View)를 사용하는 것처럼 사용자 정의 함수를 사용할 수도 있으며, T-SQL 구문을 간략하게 작성을 할 수도 있다. 각각의 함수를 차례로 알아보도록 하자.
먼저 인라인 테이블 값을 반환하는 함수에 대해서 알아보자. 인라인 테이블 값을 반환하는 함수가 어떻게 생겼는지 쿼리 분석기의 템플릿을 이용하여 알아보도록 하자.
아래의 구문은 쿼리 분석기로부터 인라인 사용자 정의 함수의 정의 부분만을 복사하여 가져온 것이다.
|
CREATE FUNCTION <inline_function_name, sysname, test_function>
(<@param1, sysname, @p1> <data_type_for_param1, , int>,
<@param2, sysname, @p2> <data_type_for_param2, , char>)
RETURNS TABLE
AS
RETURN SELECT @p1 AS c1,
GO |
대부분의 정의 구문은 앞서 보았던 스칼라 함수와 다를 바가 없다. 다만 두 가지 부분에서 스칼라 함수의 정의와 다르다. 먼저 세 번째 라인의 RETURNS 구문을 보도록 하자. 스칼라 함수의 경우에는 RETURNS 구문 다음에 스칼라 값의 데이터 형이 명시된 반면에, 인라인 테이블 값의 함수에서는 TABLE이라는 구문을 이용하여 테이블을 반환하는 것임을 명시하고 있다.
또한 인라인 테이블 값의 함수에는 스칼라 함수와는 달리 BEGIN ~ END 블록이 없다. 자세히 보면 BEGIN ~ END 블록이 없이 RETURN 구문으로 결과 값을 반환하는 구문만이 명시되어 있다. RETURN 구문 다음에는 SELECT 절을 이용하여 반환되는 결과값으로 테이블의 내역을 반환하도록 하고 있다.
인라인 테이블 값 함수에 대한 정의와 사용방법에 대해서는 바로 뒤에서 설명을 하게 될 것이다.
2.2.3 다중 문 테이블 값의 함수
다중 문 테이블 값 함수는 앞서 설명한 인라인 함수보다는 좀 더 확장된 기능을 제공한다. (인라인 테이블 값 함수와 다중 문 테이블 값 함수에 대한 정의는 바로 뒤에서 더 자세하게 설명하게 될 것이다.) 우선 다중 문 테이블 값 함수가 어떻게 정의되는지 정의 구문을 살펴보도록 하자. 앞서와 마찬가지로 쿼리 분석기의 템플릿을 이용하도록 하자.
|
CREATE FUNCTION <table_function_name, sysname, test_function>
(<@param1, sysname, @p1> <data_type_for_param1, , int>,
<@param2, sysname, @p2> <data_type_for_param2, , char>)
RETURNS <@table_variable_name, sysname , @table_var> TABLE
(<column_1, sysname, c1> <data_type_for_column1, , int>,
<column_2, sysname, c2> <data_type_for_column2, , int>)
AS
BEGIN
INSERT <@table_variable_name, sysname, @table_var>
SELECT 1, 2
RETURN
END
GO |
위의 구문에서 보는 것과 같이, 다중 문 테이블 값 함수는 인라인 테이블 값 함수는 두 가지 측면에서 구문상의 차이를 보이고 있다. 먼저 CREATE FUNCTION 구문은 인라인 테이블 함수의 정의와 같다. 하지만 테이블 값을 반환하는 RETURNS 구문을 보도록 하자.
-RETURNS 구문의 비교
| 인라인 |
다중 문 |
| RETURNS TABLE |
RETURNS <@table_variable_name, sysname, @table_var> TABLE
(<column_1, sysname, c1> <data_type_for_column1, , int>,
<column_2, sysname, c2> <data_type_for_column2, , int>) |
위의 표에서 보는 것과 같이, RETURNS 구문에 명시하는 데이터의 형에 대한 표시형식이 상당히 다르게 명시되는 것을 볼 수 있다. 인라인 테이블 값 함수의 경우에는 RETURNS 구문 다음에 TABLE 이라는 키워드만을 명시하고, 실제 반환되는 테이블 형식은 정의 구문내의 RETURN 구문 다음에 오는 SELECT절에 의해서 명시되게 된다.
|
CREATE FUNCTION fn_GetSales_Inline(@min_qty SMALLINT)
RETURNS TABLE
AS
RETURN (SELECT stor_id, ord_num, ord_date
FROM sales
WHERE qty > @min_qty)
GO |
위의 구문은 판매(sales)테이블로부터 매개변수로 전달된 수량(qty)보다 많은 수량(qty)을 판매한 데이터를 반환하는 인라인 테이블 값 함수의 예제이다. 구문에서 보는 것과 같이, RETURNS 구문 다음에는 TABLE 키워드만을 명시하였고, 실제 반환되는 테이블의 정의는 SELECT절에서 찾아볼 수 있다.(즉 반환되는 테이블 형식은 stor_id, ord_num, ord_date 등 세 개의 컬럼으로 이루어진 테이블 형식의 데이터이다.)
그럼 다중 문 테이블 값 함수는 어떻게 정의가 되는 것일까? 위의 구문을 다중 문 테이블 값 함수로 변경하면 다음과 같이 변경이 가능하다.
|
CREATE FUNCTION fn_GetSales_Multi(@min_qty SMALLINT)
RETURNS @TableValue TABLE
(
점포번호 CHAR(4) NOT NULL PRIMARY KEY,
주문번호 VARCHAR(20),
주문일자 DATETIME NOT NULL
)
AS
BEGIN
INSERT @TableValue
SELECT stor_id, ord_num, ord_date
FROM sales
WHERE qty > @min_qty
RETURN
END
GO |
우선 RETURNS 구문을 보도록 하자. 인라인 테이블 값 함수에서는 RETURNS 구문 다음에 TABLE 키워드 만을 명시한 것에 반해서 RETURNS 구문과 TABLE 키워드 사이에 테이블 변수인 @TableValue라는 변수명을 명시하고 있다. 더욱 주의 깊게 보아야 할 부분은 TABLE 키워드 다음에, 마치 실제 사용자 테이블을 정의하듯이 필드명, 데이터형, NULL 유무, 기본키에 대한 정의를 하고 있다.
- BEGIN ~ END 블록
또한 다중 문 테이블 값 함수는 인라인 테이블 값 함수와는 달리, BEGIN ~ END 블록을 가지게 된다. 인라인 테이블 값 함수에서는 반환되는 테이블 값에 대한 일련의 작업을 할 수 없었다. 즉, 인라인 테이블 값 함수는 마치 뷰와 같이, 데이터를 반환하는 기능을 수행하지만 다중 문 테이블 값 함수는 BEGIN ~ END 블록에 의해서 여러 가지 작업을 수행할 수 있도록 지원을 하고 있다. 따라서 인라인 함수 보다는 좀 더 확장된 기능을 사용자 정의 함수 안에서 처리를 할 수 있는 것이다.
| 인라인 |
다중 문 |
RETURN
SELECT @p1 AS c1, |
BEGIN
INSERT <@table_variable_name, sysname, @table_var>
SELECT 1, 2
RETURN
END |
앞서 살펴보았던 fn_GetSales_Multi 함수의 정의 부분을 보도록 하자.
| 인라인 |
다중 문 |
RETURN (SELECT stor_id, ord_num, ord_date
FROM sales
WHERE qty > @min_qty) |
BEGIN
INSERT @TableValue
SELECT stor_id, ord_num, ord_date
FROM sales
WHERE qty > @min_qty
RETURN
END |
위의 표에서 보는 바와 같이, 인라인 테이블 값 함수는 단순히 RETURN 구문에 의해서 테이블 형식의 결과 값을 반환하는데 반해서, 다중 문 테이블 값 함수는 BEGIN ~ END 블록에 의해서 일련의 작업이 이루어지는 것을 볼 수 있다. BEGIN ~ END 블록 안을 보면, RETURNS 구문에서 선언한 테이블 변수인 @TableValue에 테이블 테이터를 입력하고, RETURN 구문에 의해서 반환을 하고 있다. 위의 fn_GetSales_Multi 함수의 BEGIN ~ END 블록에서는 테이블 형식의 데이터를 반환하는 구문만이 정의되어 있지만, 실질적으로 BEGIN ~ END 블록 안에서는 일련의 T-SQL 작업이 가능하다. 실질적인 예제는 다중 문 테이블 값 함수의 사용에서 살펴보도록 한다.
이렇게 다중 문 테이블 값 함수는 인라인 테이블 값 함수와는 몇 가지 측면에서 매우 다른 특성을 가지고 있다.
우선 다중 문 테이블 값 함수는 반환되는 테이블 변수에 대해서 정의가 가능하다. 위의 예제에서 살펴본 것과 같이, 컬러명, 데이터 형, NULL 여부, 기본키는 물론이거니와 유일값(UNIQUE), 체크(CHECK) 제약조건, 기본값(DEFAULT) 제약조건까지도 설정이 가능하다.
다중 문 테이블 값 함수는 인라인 테이블 값 함수와는 달리, BEGIN ~ END 블록으로 함수본문이 이루어져 있고, 함수본문에서는 다음과 같은 다중의 T-SQL 구문을 이용하여 작업이 가능하다.(온라인 설명서 참조)
- 할당문
- 흐름 제어 문(Control-of-Flow)
- 함수에서 로컬로 사용되는 데이터 변수와 커서를 정의하는 DECLARE 문
- 함수에서 로컬로 사용되는 변수에 값을 할당하는 식이 있는 선택 목록이 포함된 SELECT 문
- 함수에서 커서 선언, 열기, 닫기, 할당 취소 등 로컬 커서를 참조하는 커서 작업. INTO 절을 사용하여 로컬 변수에 값을 할당하는 FETCH 문만 허용되며 클라이언트에게 데이터를 반환하는 FETCH 문은 사용할 수 없습니다.
- 함수에서 로컬로 사용되는 table 변수를 수정하는 INSERT, UPDATE, DELETE 문
- 확장 저장 프로시저를 호출하는 EXECUTE 문
2.3 사용자 정의 함수의 변경, 삭제
사용자 정의 함수도 마찬가지로 SQL Server의 개체라고 하였다. 따라서 사용자 정의 함수의 정의를 변경하거나 삭제하는 구문은 다른 개체의 변경, 삭제구문과 같다.
우선 사용자 정의 함수의 변경은 다음과 같이 ALTER FUNCTION 구문을 이용하여 변경이 가능하다.
|
ALTER FUNCTION dbo.함수명
<함수내용 > |
이렇게 ALTER FUNCTION 구문을 이용하여 사용자 정의 함수를 변경할 때, 몇 가지 제약조건이 있다.
우선 스칼라 값의 함수를 테이블 값을 반환하는 함수로 변경할 수 없다. 또한 반대로 테이블 값을 반환하는 함수를 스칼라 값의 함수로 변경하는 작업도 불가능하다. 또한 테이블 값을 반환하는 함수에서도 인라인 테이블 값 함수를 다중 문 테이블 값의 함수로 변경하거나, 다중 문 테이블 값의 함수를 인라인 테이블 값 함수로 변경하는 작업은 불가능하다.
또한 사용자 정의 함수는 DROP FUNCTION 구문에 의해서 삭제가 가능하다.
|
