꿈꾸는 개발자, DBA 커뮤니티 구루비

1.USER DEFINED FUNCTION의 특징과 장점

1)특징

리턴값이 있다.
데이터베이스 객체로 저장되어, 컴파일된 상태에서 수행된다.
단독적인 사용보다, SQL에서 많이 수행된다.
예외 처리가 가능하다.

2)장점

모듈화된 프로그램이 가능하다.
변수 및 다양한 제어문 사용이 가능해서 복잡한 비즈니스 로직도 쉽게 구현이 가능하다.
WAS 서버와 네트워크 부하를 줄일 수 있다.
유지보수 측면에서 매우 효율적이다.

2.USER DEFINED FUNCTION의 종류와 사용법

1)NOT DETERMINISTIC FUNCTION

특별한 옵션을 주지 않을 경우 Default로 생성되는 Function
입력값을 받아 결과값을 리턴하는 기본적인 Function
추출데이터 만큼 반복수행

사용법



CREATE [OR REPLACE] FUNCTION [함수 명]
( [ param1 [mode] data_type1]
  [,param2 [mode] data_type2]
  [,param3 [mode] data_type3]
......
)
  return data_type; ---> 크기지정은 불가
IS
 변수 선언 ---> FUNCTION 내에서 사용할 변수를 선언한다.
 BEGIN
  실제 PL/SQL Program
  RETURN (변수);
 EXCEPTION
   ...........
END;

- CREATE [OR REPLACE] FUNCTION : Function을 생성 및 재생성 할때 사용함
- 함수 명 : 데이터베이스 내에 저장될 Function 이름
- param : Function내로 어떤 값을 전달할 때 매개변수 명
- mode : 매개변수의 역할을 의미하며 IN , OUT, IN OUT 3가지 중 선택
- data_type :param 변수의 데이터 타입
- exception : 특정조건에 대한 예외처리 부분
- begin ~ end : Function의 시작 ~ 끝을 의미

예제



-부서명번호를 입력받아서 부서명을 리턴하는 Function 생성
CREATE OR REPLACE FUNCTION dept_nm(pdeptno IN NUMBER)
RETURN VARCHAR2 IS --> RETURN 값에 대해 아무런 옵션이 주어지지 않는다.
pdeptnm VARCHAR2(32);
BEGIN
SELECT dname INTO pdeptnm
FROM dept
WHERE deptno = pdeptno;
RETURN pdeptnm;
EXCEPTION WHEN NO_DATA_FOUND THEN pdeptnm :='' ;
 RETURN pdeptnm ;
END dept_nm;
/

-Function의 사용
SQL> SELECT empno,
            ename,
            deptno,
            dept_nm(deptno) dname
       FROM emp ;
EMPNO ENAME DEPTNO DNAME
----- ---------- ------ ----------------------------------------
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING


=> DEPT 테이블의 부서명은 Function을 통해 추출되었으며 결과건수(13번)만큼 반복 수행되었다.

2)DETERMINISTIC FUNCTION

입력값이 같다면, 리턴값도 항상 같음을 보장하는 Fuction
입력값에 해당하는 컬럼값의 종류가 적으면 , Function을 매번 수행하지 않고 메모리내에 cache된 값을 가져옴
입력값에 대한 Cache는 SQL Level이 아닌 Fetch Level이므로 Cache 효과를 극대화 하려면 FETCH ARRAY SIZE를 적절하게 설정해야 함

사용예제


CREATE OR REPLACE FUNCTION d_dept_nm(pdeptno IN NUMBER)
RETURN VARCHAR2 DETERMINISTIC <-- RETURN절 선언부에 DETERMINISTIC 옵션을 추가
IS
135
pdeptnm VARCHAR2(32);
BEGIN
SELECT dname INTO pdeptnm
FROM dept
WHERE deptno = pdeptno;
RETURN pdeptnm;
EXCEPTION WHEN no_data_found THEN
          pdeptnm :='' ;
  RETURN pdeptnm ;
END d_dept_nm;
/

SQL> SELECT empno,
            ename,
            deptno,
            d_dept_nm(deptno) dname
     FROM emp ;

EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- -------------------------------------------
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING

=> Function 입력값에 대해서 Cache된 값을 이용하므로 Function의 수행횟수가 13번이 아닌 3번(10,20,30)만 수행

3)PIPELINE TABLE FUNCTION

Multi Column + Multi Row 형태의 입력값을 받아들여 값을 리턴할수 있는 Function
부분범위 처리 가능

사용법


- 생성절차
  (1)데이터를 담을 User Object Type을 만든다.
    CREATE TYPE table_type_row AS OBJECT ( 
    SEQ NUMBER,
    DTL VARCHAR2(50)); 
    /
 (2)데이터를 담기위해 (1)에서 만든 Type을 이용해 테이블 형태(2차원배열형태)의 Type을 생성한다.
    CREATE TYPE table_type_tab IS TABLE OF table_type_row ;
 (3)Pileline Table Function을 생성한다.
    CREATE OR REPLACE FUNCTION pipe_table (end_num IN NUMBER)
    RETURN table_type_tab pipelined AS <-- PIPELINED 옵션 선언 
    BEGIN
        FOR i IN 1 .. end_num LOOP
            dbms_output.put_line ('count ==> ' || i); -- dbms_output 으로 출력
                        pipe row(table_type_row (i,'count for '|| i)); -- 매건 마다 출력
                END LOOP;
    RETURN ;
    END ;
   /

- 사용예제1
SELECT *
FROM TABLE ( pipe_table (10) )
ORDER BY seq DESC;

SEQ DTL
---------- --------------------------------------------------
10 count for 10
9 count for 9
8 count for 8
7 count for 7
6 count for 6
5 count for 5
4 count for 4
3 count for 3
2 count for 2
1 count for 1
10 rows selected.
count ==> 1
count ==> 2
count ==> 3
count ==> 4
count ==> 5
count ==> 6
count ==> 7
count ==> 8
count ==> 9
count ==> 10
  
- 사용예제2 부분범위처리확인
SELECT *
FROM TABLE ( pipe_table (10) )
WHERE ROWNUM <= 5;
SEQ DTL
---------- -------------
1 count for 1
2 count for 2
3 count for 3
4 count for 4
5 count for 5

count ==> 1
count ==> 2
count ==> 3
count ==> 4
count ==> 5

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 10 |
|* 1 | COUNT STOPKEY | | | | <-- 부분범위 처리로 수행됨
| 2 | COLLECTION ITERATOR PICKLER FETCH| PIPE_TABLE | | | |
---------------------------------------------------------------------------------

Function의 종류와 특성을 잘 이해하고 적시 적소에 잘 사용한다면 SQL의 성능을 향항 시킬 수 있다