[MSSQL] 스키마 바운드 함수

스키마 바운드 함수란?

함수에서 참조하는 테이블, 뷰 등이 수정되지 못하게 설정한 함수

- 예시 : 스칼라 함수를 생성하고, 함수가 참조하는 테이블의 열 이름을 변경하여 문제점 파악

- 스칼라 함수 생성 및 사용

-- 스칼라 함수 생성
CREATE FUNCTION ufn_discount(@id NVARCHAR(10))
    RETURNS BIGINT
AS
BEGIN
    DECLARE @totPrice BIGINT
    
    -- 입력된 사용자 id의 총 구매액
    SELECT @totPrice = sum(price*amount) FROM buyTbl WHERE userID = @id GROUP BY userID;
 
    -- 총 구매액에 따라서 차등된 할인율 적용
    SET @totPrice = 
    CASE
        WHEN (@totPrice >= 1500) THEN @totPrice*0.7
        WHEN (@totPrice >= 1000) THEN @totPrice*0.8
        WHEN (@totPrice >= 500) THEN @totPrice*0.9
        ELSE @totPrice
    END
 
    -- 구매기록이 없으면 0원
    IF @totPrice IS NULL
        SET @totPrice = 0;
 
    RETURN @totPrice
END
 
-- 함수 사용
SELECT userID, name, dbo.ufn_discount(userID) AS [할인된 총 구매액] FROM userTbl;

- 결과

- 열 이름 변경 및 함수 사용

-- 열이름 변경 : buyTbl의 price 열의 이름을 cost로 변경해 보자.
EXEC sp_rename 'buyTbl.price','cost','COLUMN';
 
-- 함수 실행
SELECT userID, name, dbo.ufn_discount(userID) AS [할인된 총 구매액] FROM userTbl;

- 결과 : 열이름이 변경되어 함수를 사용할 수 없게 되었다.

문제 해결

'WITH SCHEMABINDING' 옵션을 사용하여 참조하는 테이블의 열이름 변경을 방지

- 함수 수정

( ※ 수정시 스키마 이름 주의! )

ALTER FUNCTION ufn_discount(@id NVARCHAR(10))
    RETURNS BIGINT
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @totPrice BIGINT
    
    -- 입력된 사용자 id의 총 구매액
    SELECT @totPrice = sum(price*amount) FROM dbo.buyTbl WHERE userID = @id GROUP BY userID;
 
    -- 총 구매액에 따라서 차등된 할인율 적용
    SET @totPrice = 
    CASE
        WHEN (@totPrice >= 1500) THEN @totPrice*0.7
        WHEN (@totPrice >= 1000) THEN @totPrice*0.8
        WHEN (@totPrice >= 500) THEN @totPrice*0.9
        ELSE @totPrice
    END
 
    -- 구매기록이 없으면 0원
    IF @totPrice IS NULL
        SET @totPrice = 0;
 
    RETURN @totPrice
END
 

- 열 이름 변경

EXEC sp_rename 'buyTbl.price','cost','COLUMN';

- 결과

---

참고문헌

우재남, 『뇌를 자극하는 SQL Server 2012』 한빛미디어 (2013-03-02 초판발행)