PL/SQL Table Function Examples

 Example 1 -

CREATE OR REPLACE FUNCTION squares (p_limit IN NUMBER) RETURN SYS.ODCINUMBERLIST IS l_squares SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(); BEGIN FOR i IN 1..p_limit LOOP l_squares.EXTEND; l_squares(l_squares.COUNT) := i * i; END LOOP; RETURN l_squares; END;

SELECT column_value FROM TABLE(squares(10));

----------------------------------------------

1 4 9 16 25 36 49 64 81 100

Example 2 -

CREATE OR REPLACE FUNCTION get_employees_salaries RETURN SYS.ODCIVARCHAR2LIST AS emp_salaries SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(); BEGIN SELECT ename || ': ' || sal INTO emp_salaries FROM emp; RETURN emp_salaries; END; /

SELECT * FROM TABLE(get_employees_salaries);