함수 Function (2)

타입 반환 함수

타입정의

CREATE OR REPLACE TYPE ename_type AS OBJECT
(
	first_name VARCHAR2(20),
	last_name VARCHAR2(20)
);

#테이블 타입 정의
CREATE OR REPLACE TYPE 	ename_table AS TABLE OF ename_type;

 

테이블 반환 함수 (Pipelined Table Function)

CREATE OR REPLACE FUNCTION emp_table(emp_id NUMBER)
	RETURN ename_table;
    	PIPELINED
IS
	ename ename_type;
BEGIN
	FOR emp IN(SELECT first_name, last_name FROM employees WHERE employee_id = emp_id)
    LOOP
    	ename := ename_type(emp.first_name, emp.last_name);
        PIPE ROW(ename);
    END LOOP;
    RETURN;
END;