TABLE FUNCTION
A table function is a function that can be called from within the FROM clause of a query,as if it were a relational table. Table functions return collections (nested tables or VARRAYs),which can then be transformed with the TABLE operator into a structure that can be queried using the SQL language.
Table functions come in very handy when you need to :-
• Perform very complex transformations of data, requiring the use of PL/SQL, but need to access that data from within an SQL statement.
• Pass complex result sets back to the host (that is, non-PLSQL) environment.
--Creating A Object With Similar Structure As EMP Table.
SQL> CREATE OR REPLACE TYPE emp_obj_test AS OBJECT
2 (
3 EMPNO NUMBER (4),
4 ENAME VARCHAR2 (10 BYTE),
5 JOB VARCHAR2 (9 BYTE),
6 MGR NUMBER (4),
7 HIREDATE DATE,
8 SAL NUMBER (7, 2),
9 COMM NUMBER (7, 2),
10 DEPTNO NUMBER (2)
11 );
12 /
Type created.
--Creating A NESTED TABLE Collection Of The Above Object Type.
SQL> CREATE TYPE emp_nt IS TABLE OF emp_obj_test;
2 /
Type created.
--TABLE Function That Returns A Object Type Collection.
SQL> CREATE OR REPLACE FUNCTION test_func (p_num_in NUMBER)
2 RETURN emp_nt
3 IS
4 nt_var emp_nt := emp_nt ();
5 BEGIN
6 FOR indx IN 1 .. p_num_in
7 LOOP
8 nt_var.EXTEND;
9 nt_var (indx) :=
10 emp_obj_test (SUBSTR (ABS (DBMS_RANDOM.RANDOM), 1, 4),
11 DBMS_RANDOM.string ('U', 5),
12 'Engineer',
13 SUBSTR (ABS (DBMS_RANDOM.RANDOM), 1, 4),
14 TO_DATE ('03/30/1984', 'mm/dd/yyyy'),
15 1000,
16 200,
17 SUBSTR (ABS (DBMS_RANDOM.RANDOM), 1, 2)
18 );
19 END LOOP;
20 RETURN nt_var;
21 END test_func;
22 /
Function created.
--Accessing The EMP Table By Calling The Function By TABLE Operator.
SQL> SELECT * FROM TABLE (test_func (14));
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
2003 NZATC Engineer 7459 30-MAR-84 1000 200 19
2111 CIXLQ Engineer 1077 30-MAR-84 1000 200 10
9549 TAEGD Engineer 3039 30-MAR-84 1000 200 20
7323 AHIWO Engineer 1298 30-MAR-84 1000 200 17
1815 WTLFT Engineer 3074 30-MAR-84 1000 200 20
1529 YYTAO Engineer 1588 30-MAR-84 1000 200 63
6620 WXWIX Engineer 1121 30-MAR-84 1000 200 87
2339 HJPQN Engineer 1972 30-MAR-84 1000 200 12
1947 CJQJV Engineer 1173 30-MAR-84 1000 200 15
8144 ZXHRH Engineer 1095 30-MAR-84 1000 200 75
1749 FFFLY Engineer 4665 30-MAR-84 1000 200 56
1516 DBWGP Engineer 2588 30-MAR-84 1000 200 13
1894 IKKJC Engineer 5681 30-MAR-84 1000 200 19
1594 VEAFG Engineer 2024 30-MAR-84 1000 200 16
14 rows selected.PIPELINED TABLE FUNCTION
A pipelined table function is a table function that returns a collection in PIPELINED fashion,meaning that data is returned to the calling program while the function is still executing.In other words, the database no longer waits for the function to run to completion, storing all the rows it computes in the PL/SQL collection, before it delivers the first rows. Instead, as each row is ready to be assigned into the collection, it is piped out of the function.
The PIPELINED keyword specifies that the results of this table function should be returned iteratively via the PIPE ROW command.
Notice the empty RETURN call, since there is no collection to return from the function.
PIPELINE Table Function reduce PGA memory comsumption & improve performance.
--Creating A Object With Similar Structure As EMP Table.
SQL> CREATE OR REPLACE TYPE emp_obj_test AS OBJECT
2 (
3 EMPNO NUMBER (4),
4 ENAME VARCHAR2 (10 BYTE),
5 JOB VARCHAR2 (9 BYTE),
6 MGR NUMBER (4),
7 HIREDATE DATE,
8 SAL NUMBER (7, 2),
9 COMM NUMBER (7, 2),
10 DEPTNO NUMBER (2)
11 );
12 /
Type created.
--Creating A NESTED TABLE Collection Of The Above Object Type.
SQL> CREATE TYPE emp_nt IS TABLE OF emp_obj_test;
2 /
Type created.
--PIPELINED TABLE Function That Returns A Object Type Collection.
SQL> CREATE OR REPLACE FUNCTION test_func (p_num_in NUMBER)
2 RETURN emp_nt PIPELINED
3 IS
4 BEGIN
5 FOR indx IN 1 .. p_num_in
6 LOOP
7 PIPE ROW(emp_obj_test (SUBSTR (ABS (DBMS_RANDOM.RANDOM), 1, 4),
8 DBMS_RANDOM.string ('U', 5),
9 'Engineer',
10 SUBSTR (ABS (DBMS_RANDOM.RANDOM), 1, 4),
11 TO_DATE ('03/30/1984', 'mm/dd/yyyy'),
12 1000,
13 200,
14 SUBSTR (ABS (DBMS_RANDOM.RANDOM), 1, 2)
15 ));
16 END LOOP;
17 RETURN;
18 END test_func;
19 /
Function created.
--Accessing The EMP Table By Calling The Function By TABLE Operator.
SQL> SELECT * FROM TABLE (test_func (14));
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
6007 PVCML Engineer 1461 30-MAR-84 1000 200 17
1114 LNQLS Engineer 1904 30-MAR-84 1000 200 10
6831 HSAKC Engineer 9055 30-MAR-84 1000 200 11
1947 KFQFJ Engineer 1745 30-MAR-84 1000 200 69
9414 XYTBQ Engineer 1761 30-MAR-84 1000 200 86
1287 XAUVO Engineer 1219 30-MAR-84 1000 200 12
1564 WBPIR Engineer 1114 30-MAR-84 1000 200 25
2079 ATRUG Engineer 1122 30-MAR-84 1000 200 19
6792 MJMJW Engineer 1942 30-MAR-84 1000 200 15
1238 SVFRM Engineer 2107 30-MAR-84 1000 200 13
9686 GGNBI Engineer 1196 30-MAR-84 1000 200 87
1772 RKVXR Engineer 1034 30-MAR-84 1000 200 15
5466 KHELV Engineer 6323 30-MAR-84 1000 200 65
4425 WUAEG Engineer 1042 30-MAR-84 1000 200 10
14 rows selected. For Table Function or PIPELINED Table Function to work, below are the prerequisites :-
• The Collection type must be defined at the schema level.
• The Function can be defined at either schema level or package level.
