스칼라 함수 만들기

스칼라 함수는 재사용 가능한 논리를 캡슐화하고 단일 값을 반환할 수 있는 SQL Server의 필수 도구입니다. SELECT 문, WHERE 절 및 기타 T-SQL 식에서 직접 사용할 수 있으므로 쿼리를 더 쉽게 유지 관리하고 코드를 모듈화할 수 있습니다.

스칼라 함수 기본 사항 이해

스칼라 함수는 0개 이상의 매개 변수를 허용하고 지정된 데이터 형식의 단일 값을 반환합니다. 저장 프로시저와 달리 스칼라 함수는 열 또는 변수를 사용하는 모든 위치에서 SQL 식에 직접 포함할 수 있습니다.

스칼라 함수의 주요 특성에는 입력 매개 변수를 수락하고, 계산 또는 변환을 수행하고, 정확히 하나의 값을 반환하는 기능이 포함됩니다. 생성 시 SQL Server가 유효성을 검사하는 함수 정의에서 반환 데이터 형식을 명시적으로 정의합니다.

스칼라 함수를 만들 때 다른 개발자가 데이터베이스 전체에서 호출할 수 있는 재사용 가능한 논리 부분을 만듭니다. 이렇게 하면 코드 재사용이 촉진되고 애플리케이션 전체에서 일관성을 유지할 수 있습니다.

스칼라 함수 구문 정의

스칼라 함수를 만들려면 특정 구문 구성 요소가 포함된 문을 사용합니다 CREATE FUNCTION . 기본 구조에는 함수 이름, 매개 변수, 반환 형식 및 함수 본문이 포함됩니다.

기본 구문 패턴은 다음과 같습니다.

CREATE FUNCTION schema_name.function_name 
(
    @parameter1 datatype,
    @parameter2 datatype
)
RETURNS return_datatype
AS
BEGIN
    -- Function logic here
    RETURN @result
END

이 절은 RETURNS 함수가 반환하는 단일 값의 데이터 형식을 지정합니다. BEGIN...END 블록 내에서 T-SQL 논리를 작성하고 문을 사용하여 RETURN 결과를 다시 보냅니다.

예를 들어 판매세를 계산하는 간단한 함수를 만들 수 있습니다.

CREATE FUNCTION dbo.CalculateSalesTax
(
    @Amount DECIMAL(10,2),
    @TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @TaxAmount DECIMAL(10,2)
    SET @TaxAmount = @Amount * @TaxRate
    RETURN @TaxAmount
END

이 함수는 두 개의 매개 변수를 허용하고 계산된 세금 금액을 반환합니다. 이 함수는 모든 SELECT 문에서 사용할 수 있습니다.

비즈니스 논리를 사용하여 스칼라 함수 구현

스칼라 함수는 데이터베이스 전체에서 일관되게 적용해야 하는 비즈니스 규칙 및 계산을 캡슐화하는 데 탁월합니다. 스칼라 함수를 사용하면 여러 쿼리 또는 애플리케이션 코드에서 중복될 수 있는 논리를 중앙 집중화합니다.

몇 년 동안 직원 임기를 계산해야 하는 시나리오를 고려해 보세요. 고용 날짜를 수락하고 전체 연도 수를 반환하는 스칼라 함수를 만듭니다.

CREATE FUNCTION dbo.GetEmployeeTenure
(
    @HireDate DATE
)
RETURNS INT
AS
BEGIN
    DECLARE @Tenure INT
    SET @Tenure = DATEDIFF(YEAR, @HireDate, GETDATE())
    RETURN @Tenure
END

쿼리에서 이 함수를 사용하여 임기 정보를 표시할 수 있습니다.

SELECT 
    EmployeeName,
    HireDate,
    dbo.GetEmployeeTenure(HireDate) AS YearsOfService
FROM Employees
WHERE dbo.GetEmployeeTenure(HireDate) >= 5

이 방법은 데이터베이스 전체에서 일관된 임기 계산을 보장합니다. 비즈니스 규칙이 변경되면 여러 쿼리를 업데이트하지 않고 함수를 한 번 수정합니다.

스칼라 함수에 대한 모범 사례 적용

스칼라 함수를 만들 때 몇 가지 모범 사례는 최적의 성능과 유지 관리를 보장하는 데 도움이 됩니다. 이러한 사례를 이해하면 일반적인 문제를 방지하고 효율적이고 신뢰할 수 있는 함수를 만들 수 있습니다.

먼저 가능하면 스칼라 함수를 결정적으로 유지합니다. 결정적 함수는 동일한 입력 매개 변수가 지정된 경우 항상 동일한 결과를 반환합니다. 시스템 날짜/시간 함수 또는 테이블을 참조하는 함수는 비결정적이며 특정 쿼리 최적화를 방지할 수 있습니다.

또한, 함수에서 부작용을 피하십시오. 스칼라 함수는 데이터베이스 상태를 수정하거나 외부 리소스에 대한 종속성을 가지면 안 됩니다. 이 제한은 SQL Server가 함수를 여러 번 실행하거나 예상과 다른 순서로 실행할 수 있기 때문에 존재합니다.

마지막으로 성능에 미치는 영향을 염두에 두어야 합니다. 스칼라 함수를 WHERE 절 또는 SELECT 목록에서 큰 테이블과 함께 사용하는 경우, SQL Server는 행마다 함수를 실행할 수 있을 수도 있습니다. 이는 쿼리 성능에 큰 영향을 미칠 수 있습니다. 이러한 시나리오의 경우 인라인 테이블 반환 함수를 대안으로 고려합니다.

다음은 이러한 사례를 따르는 잘 설계된 스칼라 함수의 예입니다.

CREATE FUNCTION dbo.FormatPhoneNumber
(
    @PhoneNumber VARCHAR(10)
)
RETURNS VARCHAR(14)
AS
BEGIN
    DECLARE @FormattedNumber VARCHAR(14)
    
    IF LEN(@PhoneNumber) = 10
        SET @FormattedNumber = '(' + SUBSTRING(@PhoneNumber, 1, 3) + ') ' +
                               SUBSTRING(@PhoneNumber, 4, 3) + '-' +
                               SUBSTRING(@PhoneNumber, 7, 4)
    ELSE
        SET @FormattedNumber = @PhoneNumber
    
    RETURN @FormattedNumber
END

이 함수는 결정적이며 부작용이 없으며 간단한 변환을 수행합니다. 전화 번호가 예상 형식과 일치하지 않는 경우 원래 값을 반환하여 잘못된 입력을 정상적으로 처리합니다.

스칼라 함수 수정 및 관리

스칼라 함수를 만든 후, ALTER FUNCTION 문을 사용하여 정의를 수정할 수 있습니다. 함수를 삭제하고 다시 만들지 않아도 ALTER FUNCTION 구문은 CREATE FUNCTION과 비슷하게 함수를 변경할 수 있도록 하여, 사용 권한과 종속성을 보존할 수 있습니다.

ALTER FUNCTION dbo.CalculateSalesTax
(
    @Amount DECIMAL(10,2),
    @TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @TaxAmount DECIMAL(10,2)
    -- Updated logic with rounding
    SET @TaxAmount = ROUND(@Amount * @TaxRate, 2)
    RETURN @TaxAmount
END

스칼라 함수를 제거하려면 다음 문을 사용합니다.DROP FUNCTION

DROP FUNCTION IF EXISTS dbo.CalculateSalesTax

이 절은 IF EXISTS 함수가 없는 경우 오류를 방지하며 이는 배포 스크립트에 유용합니다. 함수를 삭제하기 전에 다음과 같은 sys.sql_expression_dependencies시스템 보기를 확인하여 다른 데이터베이스 개체가 함수에 의존하지 않는지 확인합니다.