스키마 바운드 함수란?
함수에서 참조하는 테이블, 뷰 등이 수정되지 못하게 설정한 함수
- 예시 : 스칼라 함수를 생성하고, 함수가 참조하는 테이블의 열 이름을 변경하여 문제점 파악
- 스칼라 함수 생성 및 사용
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | -- 스칼라 함수 생성 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; | cs |
- 결과
- 열 이름 변경 및 함수 사용
1 2 3 4 5 | -- 열이름 변경 : buyTbl의 price 열의 이름을 cost로 변경해 보자. EXEC sp_rename 'buyTbl.price','cost','COLUMN'; -- 함수 실행 SELECT userID, name, dbo.ufn_discount(userID) AS [할인된 총 구매액] FROM userTbl; | cs |
- 결과 : 열이름이 변경되어 함수를 사용할 수 없게 되었다.
문제 해결
'WITH SCHEMABINDING' 옵션을 사용하여 참조하는 테이블의 열이름 변경을 방지
- 함수 수정
( ※ 수정시 스키마 이름 주의! )
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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 | cs |
- 열 이름 변경
1 | EXEC sp_rename 'buyTbl.price','cost','COLUMN'; | cs |
- 결과
참고문헌
우재남, 『뇌를 자극하는 SQL Server 2012』 한빛미디어 (2013-03-02 초판발행)
'DEV LOGS' 카테고리의 다른 글
[MSSQL] 커서 (1) | 2018.07.10 |
---|---|
[MSSQL] 테이블 변수 VS 임시 테이블 (0) | 2018.07.10 |
[MSSQL] 사용자 정의 함수의 종류 (0) | 2018.07.10 |
[MSSQL] 사용자 정의 함수 (0) | 2018.07.10 |
[MSSQL] 저장 프로시저(Stored Procedure)의 문제점 (0) | 2018.07.10 |