16-3.함수
1.사용자 정의 함수
함수는 필드나 변수를 집계 및 가공하는 역할을 한다. 인수를 받아
계산을 통해 결과값 하나를 리턴한다. 오라클이 기본 제공하는
ROUND, DECODE, NVL 등의 시스템 함수는 앞에서 이미 사용해 보았으며 결과값을 리턴한다.
시스템이 충분한 함수를 제공하지만 응용 프로그램 고유의 특수한 기능까지 다 제공할 수는 없다. 필요에 따라 직접 만들어 쓰는 함수를 사용자 정의 함수라고 부른다. 명령의
집합이라는 점에서 저장 프로시저와 유사하며 선언하는 형식도 비슷하지만 몇 가지 차이점이 있다.
l 함수의 인수는 모두
입력용이다. 따라서 IN이라고 밝힐 필요가 없다.
l RETURN 문으로 하나의 결과값을 리턴한다. 리턴값이 없어도 안되고
두 개여서도 안된다.
l 함수 호출문이 값 하나와
같아 SELECT 문이나 수식 내에서도 사용할 수 있다.
계산을 위해 필요한 인수는 몇 개든 받을 수 있지만 리턴은 딱 하나만 가능하다.
그래야 함수 호출문을 수식에서 바로 사용할 수 있다.
CREATE FUNCTION 명령으로 정의하며 형식은 프로시저 정의문과
유사하다. AS 이전에 RETURN 문으로 리턴할 값의 타입을
밝히고 본체에서 RETURN 명령으로 결과값을 리턴한다. 다음
함수는 인수로 전달받은 두 개의 정수를 더한 값을 계산한다.
CREATE OR REPLACE FUNCTION FN_AddInt(a INT, b INT)
RETURN INT
AS
BEGIN
RETURN
a + b;
END;
함수는 보통 접두 FN_을 붙인다.
인수 a, b는 모두 정수형이며 리턴값도 정수 타입이다.
본체에서는 a + b를 계산하여 리턴한다. 함수는
쿼리문 내에서 이름으로 바로 호출하며 수식 내에서도 사용할 수 있다.
SELECT FN_AddInt(2, 3) FROM dual;
SELECT * FROM tCity WHERE popu > FN_AddInt(10,
20);
2와 3을 더한 5를 출력하고 10과 20을
더한 30보다 인구가 더 많은 도시 목록을 출력한다. 보다시피
필드 목록이나 WHERE 절 등 어디에서나 호출할 수 있다. 시스템
함수인 TRIM, POWER 등을 어디서나 호출할 수 있는 것과 같다.
코드 내에서도 물론 호출할 수 있다.
DECLARE v_sum INT;
BEGIN
v_sum := FN_AddInt(2, 3);
DBMS_OUTPUT.PUT_LINE(v_sum);
END;
FN_AddInt의 리턴값을 v_Sum
변수에 대입한 후 출력했다. 사용자가 정의했다는 것 외에는 시스템 함수와 자격이 같다. 함수도 영구 저장되는 DB 오브젝트이며 DROP FUNCTION 명령으로 삭제한다. 다음은 더 실용적인 함수를
만들어 보자.
CREATE OR REPLACE FUNCTION FN_GetSum(p_upBound
INT)
RETURN INT
AS
v_total INT := 0;
BEGIN
FOR v_num IN 1 .. p_upBound
LOOP
v_total := v_total + v_num;
END LOOP;
RETURN v_total;
END;
이 함수는 p_upBound 인수를 전달받아 1 ~ p_upBound 까지의
합계를 구해 리턴한다. 합계를 구할 범위를 인수로 지정할 수 있어 활용성이 높다. 다음 명령은 1 ~ 10까지의 정수 합계인 55를 출력한다.
SELECT FN_GetSum(10) FROM dual;
함수를 잘 활용하면 여러 가지 이점이 있다. 저장 프로시저의 장점과
유사하되 동작을 처리하기보다는 계산을 한다는 점이 다르다.
l 반복적으로 사용할 코드를
모듈화하여 수식내에서 재사용하기 편리하다.
l 미리 컴파일해 놓아
속도가 빠르다.
l 함수 호출문은 쿼리보다
짧아 네트워크 트래픽이 줄어든다.
반면 반복적인 루프나 조건절에 사용하면 매번 호출되어 성능을 떨어뜨릴 소지가 있다. 만 개의 레코드를 읽는 쿼리문의 WHERE 절에서 FN_GetSum 함수를 호출하면 합계를 만 번이나 구해야 한다. 단, 엔진의 최적화 여부에 따라 어느 정도는 성능 감소를 상쇄하기도 한다.
연습 문제
2.도시의 이름을 입력받아 인구밀도를 구하는 함수를 작성해 보자.
3.시작값, 끝값, 증가값을 인수로 받아 범위 내의 모든 정수의 합을 구하는 함수를 작성하라. 예를
들어 10, 20, 2를 전달하면 10부터 20까지 2씩 건너뛰며 10 + 12
+ 14 + 16 + 18 + 20을 계산한다.
2.테이블 반환 함수
일반적인 함수는 딱 하나의 값을 리턴하는데 비해 테이블 반환 함수는 테이블을 생성하여 리턴한다. 어쨌거나 리턴하는 것은 테이블 하나이지만 이 안에 열과 행으로 구성한 대규모의 정보를 한꺼번에 리턴할 수 있다.
리턴 대상의 구조가 복잡하다 보니 함수를 만드는 절차도 복잡하다. 먼저
행을 정의하고 그 행을 모아 테이블을 정의한다. 그리고 테이블 안에 내용을 채워 리턴한다. 구구단 테이블을 출력하는 함수를 만들어 보자. 먼저 한 행에 대한
타입을 정의한다.
CREATE OR REPLACE TYPE gu_row AS OBJECT
(
dan INT,
num INT,
multi INT
);
구구단의 한 행은 3 * 4 = 12 식으로 세 숫자로 구성된다. gu_row 타입은 단수인 dan, 곱하는 값 num, 그리고 두 값을 곱한 multi 필드를 가진다. 필드는 필요한 만큼 얼마든지 정의할 수 있다. 다음은 이 행을 모아
테이블 타입을 정의한다.
CREATE OR REPLACE TYPE gu_table AS TABLE OF
gu_row;
gu_row 레코드를 모아 테이블을 구성하면 구구단이 된다. 함수는 단수인 p_dan을 인수로 받아 구구단 테이블에 레코드를
채워 넣는다.
CREATE OR REPLACE FUNCTION makeDan(p_dan INT)
RETURN gu_table PIPELINED
AS
v_row gu_row;
BEGIN
FOR v_num IN 1..9
LOOP
v_row := gu_row(p_dan, v_num, p_dan * v_num);
PIPE
ROW(v_row);
END LOOP;
RETURN;
END;
리턴 타입은 gu_table이며 테이블을 리턴한다는 의미로 PIPELINED 키워드를 명시한다. 본체에서는 인수로 받은 p_dan에 대해 1~9까지 루프를 돌며 한 행을 생성하고 PIPE ROW 명령으로 이 행을 테이블에 삽입한다. 루프를 다 돈
후 완성한 테이블을 리턴한다.
makeDan 함수로 원하는 단수만 전달하면 해당 단을 테이블로 생성하여
리턴한다. 리턴값이 테이블이므로 당연히 FROM 절에 사용할
수 있다. 다음 명령은 5단과 7단을 테이블로 만들어 리턴한다.
SELECT * FROM TABLE(makeDan(5));
SELECT * FROM TABLE(makeDan(7));
테이블을 생성하기 위한 인수만 전달하면 된다. 계산에 의해 만들든, 다른 테이블에서 조회해서 조합하든, 인수 내부의 값을 분해하여 생성하든
다양한 방법으로 테이블을 생성할 수 있다.