Table Procedure
A table procedure is a row-producing database procedure that can be invoked in the FROM clause of a SELECT statement.
A table procedure reference uses the following syntax:
proc_name ([param_name=]param_spec {,[param_name=]param_spec})
The parameter expressions can reference columns from other tables or views in the FROM clause, effectively representing joins between the tables and the table procedures. Columns of the result rows of the table procedures can also be referenced anywhere in the query.
The following query example shows the table procedure tproc1() with the correlation name tp.
SELECT a.c1, b.d1, tp.r5 FROM table1 a, table2 b, tproc1 (p1 = 19, p2 = 'pqr',
p3 = a.c2) tp
WHERE a.c4 = b.d3 AND b.d6 = tp.r2;
Its parameter list contains a reference to column c2 of table1 and the WHERE clause has an equijoin between columns d6 of table2 and the result column r2 of tproc1().
The following query example shows the creation of a view from a table procedure in which some, but not all, parameters are specified:
CREATE VIEW tpv1 AS SELECT * FROM tproc2 (p3 = 25);