\
1. 리턴값이 있다.
2. 데이터 베이스 객체로 저장되어, 컴파일 된 상태에서 수행된다.
3. 단독적인 사용보다, SQL 에서 많이 수행된다.
4. 예외 처리가 가능하다.
1. 모듈화된 프로그램이 가능하다.
2. 변수 및 다양한 제어 문 사용이 가능해서, 복잡한 비지니스 로직도 쉽게 구현이 가능하다.
3. WAS 서버와 네트워크 부하를 줄일수 있다.
- DB 서버에서 추출된 많은 데이터를 WAS 서버의 물리 메모리에 적재한 후 가공해야 함으로 WAS 서버의 물리 메모리와 CPU 사용량이 증가.
- 많은 데이터를 전송하는 과정에서, 전송량과 전송 횟수가 증가로 인한 네트워크 트래픽 증가.
- DB 서버에서 추출된 데이터를 가공하여 전송하도록 Function 을 생성하여 사용하면 WAS 서버와, 네트워크 트래픽의 감소 효과.
- 하지만, DB 의 Function 사용이 과도하게 증가할 경우 DB 서버의 CPU 사용률을 높이는 비효율이 발생.
4. 유지보수 측면에서 매우 효율적이다.
- 공통 업무에서 사용하는 SQL 의 변경시, SQL 을 사용하는 모든 프로그램 소스를 찾아 수정한다.
- SQL 이 아닌 Function 을 사용한다면, Function 소스만 수정하여 재 컴파일 하면, 수정내역이 모든 SQL 에 반영된다.
1. 입력값을 받아 결과 값을 리턴하는 기본적인 Function
2. Select 절에 사용할 경우 최대 SQL 추출 데이터 만큼 Function 을 반복 수행.
CREATE OR REPLACE FUNCTION dept_nm(pdeptno IN NUMBER)
RETURN VARCHAR2 IS
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;
SQL> /
#####################################################################
SQL> select empno, ename, deptno, dept_nm(deptno) dname
2 from emp
3 /
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- ------------------------------
7369 SMITH 20 RESEARCH
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
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- ------------------------------
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
14 rows selected.
SQL>
SQL 에 사용된 Function 은 입력 값에 대해서 매번 수행됨으로 14회 수행된다.
1. Not Deterministic Function 은 추출 데이터 만큼 반복 수행하여, 성능의 저하가 온다.
2. 입력 값에 해당하는 컬럼 값의 종류 (NUM_DISTINCT 값) 가 적다면, 자연스럽게 입력 값이 동일한 경우가 많아, Function 을 매번 수행하지 않고
메모리 내에 Cache 된 결과 값을 바로 가져올수 있다.
CREATE OR REPLACE FUNCTION dept_nm(pdeptno IN NUMBER)
RETURN VARCHAR2 DETERMINISTIC
IS
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;
SQL> /
#####################################################################
SQL> select empno, ename, deptno, dept_nm(deptno) dname
2 from emp
3 /
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- ------------------------------
7369 SMITH 20 RESEARCH
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
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- ------------------------------
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
14 rows selected.
SQL>
SQL 에 사용된 Function 은 입력 값에 대해서 Cache 된 값을 이용하므로, Function 의 수행횟수가 14번이 아닌 입력 값의 종류 (10, 20, 30) 만큼 3번 수행된다.
1. Pipeline Table Function 은 Multi Column + Multi Row 형태를 입력 값을 리턴할수 있는 Function 이다.
2. 9i 이전에 사용했던 Table Function 과는 다르게 전체범위 처리하지 않고, 부분범위 처리가 가능하다.
3. RETURN 절에 PIPELINED 옵션을 선언하여 Function 을 생성해야 한다.
4. 데이터를 추출할때 PIPE ROW 함수를 사용하여 ROW 단위로 데이터를 추출해야 한다.
1. Type 생성
CREATE TYPE table_type_row AS OBJECT (
SEQ NUMBER,
DTL VARCHAR2 (50));
CREATE TYPE table_type_tab IS TABLE OF table_type_row;
SQL> CREATE OR REPLACE FUNCTION pipe_table (end_num IN NUMBER)
2 RETURN table_type_tab pipelined AS
3 BEGIN
4 FOR i IN 1 .. end_num LOOP
5 dbms_output.put_line ('count ==> ' || i );
6 pipe row (table_type_row (i, 'count for ' || i ));
7 END LOOP;
8 RETURN ;
9 END ;
10 /
Function created.
SQL> set serveroutput on;
SQL> select *
2 from table (pipe_table (10))
3 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
SQL>
Function 의 생성 구문을 보면 PIPE ROW 전에 DBMS OUTPUT 을 먼저 수행하도록 작성되어 있다.
Pipeline 은 로우 단위로 매번 출력하지만, DBMS_OUTPUT 패키지를 사용한 출력은 Function 이 모두 처리된 후에 될수 있다.
SQL>
1 select *
2 from table (pipe_table (10))
3* where ROWNUM <= 5
SQL> /
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
SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 3000705851
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 10 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | COLLECTION ITERATOR PICKLER FETCH| PIPE_TABLE | 5 | 10 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Function 은 1부터 10까지 Loop 를 수행하게 되어 있지만, Where 절에 ROWNUM 조건이 있는 경우 조건에 만족하는 데이터만 추출하고 수행이 멈추게 되는 것이다.
실행계획에서 Count Stopkey 를 보고도 알수 있다.