CREATE TABLE emp_test (
    emp_id      NUMBER,
    emp_name    VARCHAR2(100),
    salary      NUMBER
);

INSERT INTO emp_test VALUES (1, 'Arman', 1500);
INSERT INTO emp_test VALUES (2, 'Anna', 2200);
INSERT INTO emp_test VALUES (3, 'Gurgen', 1800);
COMMIT;


CREATE OR REPLACE TYPE t_emp_row AS OBJECT (
    emp_id      NUMBER,
    emp_name    VARCHAR2(100),
    salary      NUMBER
);


CREATE OR REPLACE TYPE t_emp_tab IS TABLE OF t_emp_row;


--A) Normal Function Returning a Table
----------------------------------------
CREATE OR REPLACE FUNCTION f_get_emp
    RETURN t_emp_tab
IS
    v_data t_emp_tab;
BEGIN
    SELECT t_emp_row(emp_id, emp_name, salary)
    BULK COLLECT INTO v_data
    FROM emp_test;

    RETURN v_data;
END;
/

SELECT * FROM TABLE(f_get_emp());
---------------------------------------
--B) PIPELINED Function (PIPE RETURN)
---------------------------------------

CREATE OR REPLACE FUNCTION f_get_emp_pipe
    RETURN t_emp_tab PIPELINED
IS
BEGIN
    FOR r IN (SELECT emp_id, emp_name, salary FROM emp_test) LOOP
        PIPE ROW (t_emp_row(r.emp_id, r.emp_name, r.salary));
    END LOOP;

    RETURN;
END;
/

SELECT * FROM TABLE(f_get_emp_pipe());
---------------------------------------
--C) PIPELINED Function With Parameter
CREATE OR REPLACE FUNCTION f_get_emp_by_salary(p_min_salary NUMBER)
    RETURN t_emp_tab PIPELINED
IS
BEGIN
    FOR r IN (SELECT emp_id, emp_name, salary
              FROM emp_test
              WHERE salary >= p_min_salary)
    LOOP
        PIPE ROW (t_emp_row(r.emp_id, r.emp_name, r.salary));
    END LOOP;
END;
/

SELECT * FROM TABLE(f_get_emp_by_salary(1800));
---------------------------------------
