[SQL Server] 저장 함수(stored function)만들기

SQL Server에서의 "저장"은 두 가지의 경우로 나눠서 볼 수 있다.

① 저장 함수(Function) → 리턴값이 있음

② 저장 프로시저(PROCEDURE) → 실행만 하고 리턴값은 없음

즉, 리턴값이 있으면 함수로, 리턴값이 필요하지 않으면 프로시저로 만들면 된다. 이번 포스팅에서는 "함수"에 대해 살펴보고자한다.

 

1. 저장 함수(stored function)

---

SQL Server에서 저장 함수를 작성하기 위해서는 [CREATE FUNCTION~] 구문을 사용한다. Oracle과 같이 [OR REPLACE]를 사용할 수 없다는 점이 불편하다.

기본적으로 아래의 구조를 따른다.

CREATE FUNCTION {함수명}(@파라미터{데이터형}) RETURN {데이터형}
AS
BEGIN
  ｛처리｝
END

파라미터

1) 기본 → 값을 전달

2) OUTPUT → 참고를 전달

데이터형

1) varchar → 문자열 데이터형

2) int 정수형

3) decimal 소수형

4) date 날짜형

변수의 선언

DECLARE@변수 데이터형

대입

SET{대입되는 값} = {대입하는 값}

 

2. 저장함수를 만드는 CREATE FUNCTION

---

예시1. 저장함수의 기본 작성

-- 저장 함수를 작성
CREATE FUNCTION dbo.funA RETURN int 
AS
BEGIN
  --변수 선언
  DECLARE @iCnt int;
  SET @iCnt := 1;
  RETURN @iCnt;
END
GO

여기서는 "1"을 반환하는 간단한 형태의 저장함수를 작성해 보았다.

예시2. 저장함수의 작성(변수 전달)

CREATE FUNCTION dbo.funA
(@iEno int) RETURN varchar(30)
AS
BEGIN
  --변수 선언
　@vEname varchar(30);

  --SELECT실행（패턴1）
  SELECT @vEname = empname 
  FROM emp
  WHERE empno = @iEno;
 
 --SELECT실행 （패턴2）
  SET @vEname = (SELECT empname FROM emp WHERE empno = @iEno);

  RETURN @vEname;
END
GO

여기서는 파라미터(변수) "iEno"에 값을 전달하고, 그것을 저장 함수가 받아 SELECT의 조건으로 사용하고 그 결과를 "@vEname"에  저장해 반환하는 저장함수를 작성했다.

SELECT의 결과를 변수에 넣는 방법은 위에서 확인 할 수 있듯, 두 가지 패턴이 있다. 이에 대해 조금 더 자세히 살펴보자.

SELECT결과를 변수에 전달(패턴1)

패턴1은 SQL의 안에 직접 넣는 방법이다. [SELECT @변수 = 열1 FROM 테이블]과 같이 작성하고 있다. 여기서는 [@vEname=empname]으로 하여, [empname]의 결과를 변수 [@vEname]에 넣었다.

SELECT결과를 변수에 전달(패턴2) 

패턴2에서는 변수의 대입과 동일하게 "SET"을 사용한다. 그리고 SELECT한 결과를 [@vEname]에 대입한다.

---

참고자료

sql-oracle.com/sqlserver/?p=728