[Oracle] 오라클 - Function 생성 및 실행 방법

오라클에서는 TO_DATE, CONCAT 등과 같은 내장함수가 있지만 사용자가 정의하여 사용할 수 있는 사용자 정의 함수(User Defined Function)를 생성할 수도 있다.

 

기본적인 함수를 만드는 문법은 아래와 같다.

 

CREATE OR REPLACE FUNCTION function_name (agr1 NUMBER)
    RETURN VARCHAR2
IS
    return_value VARCHAR(14);
BEGIN
    SELECT 
    	data
    INTO
    	return_value
    FROM 
    	TABLE
    WHERE 
    	sequence = arg1
        and ROWNUM =1;
    RETURN return_value;
END;

 

생성한 함수를 실행하는 방법은 아래와 같다.

 

SELECT function_name(1000413) FROM dual;

위의 방법은 Function 반환 결과가 1 Row 1 Column 일때 사용할 수 있다. 그러나 여러개의 Row와 여러개의 Column을 반환하려면 Oracle Table Function 또는 Pipelined Table Function을 사용해야 한다.

 

Oracle Table Function은 Result Set(Multi column + Multi Row)의 형태를 인자 값으로 받아들여 값을 Return할 수 있는 PL/SQL Function이다. 

 

Pipelined Table Function은 Oracle Table Function과 비슷하지만 전체 집합을 한 번에 처리하지 않고 Row 단위로 한 건씩 처리할 수 있다.

 

Pipelined Table Function을 가지고 예제를 만들어 본다.

 

1. Return 받을 Object Type 생성

CREATE OR REPLACE TYPE test_type AS OBJECT
(
    sn NUMBER,
    yyyymmdd VARCHAR2(16)
);

 

2. Object Type을 가지는 Collection Type 생성

CREATE OR REPLACE TYPE test_table AS TABLE OF test_type;

 

3. Pipelined Table Function 생성

CREATE OR REPLACE FUNCTION test_func_2 (sn NUMBER)
    RETURN test_table
    PIPELINED
IS
    t_type test_type;
BEGIN
    FOR rec IN (
         SELECT sn
        		, yyyymmdd
         FROM data_table
         WHERE sequence = sn
    ) LOOP
        t_type := test_type(rec.sn, rec.yyyymmdd);
        PIPE ROW(t_type);
    END LOOP;
    
    RETURN;
END;

 

Pipelined Table Function를 실행하는 방법은 아래와 같다.

SELECT * FROM TABLE(test_func_2(1000413));

 

References:

gent.tistory.com/270

kyh0714.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-Function-%EC%8B%A4%ED%96%89%EB%B0%A9%EB%B2%95

www.gurubee.net/lecture/2238