A table function is a function that returns a collection of rows, which can be queried like a database table. You can use a table function in the FROM clause of a query.
Note
Since OceanBase Database V4.2.5 BP5, table functions support complex data types.
Example
CREATE FUNCTION func_normal RETURN table_type AS
v_result table_type := table_type();
BEGIN
v_result.EXTEND;
v_result(1) := obj_type(1, 'A');
RETURN v_result;
END;
Parameters
| Parameter | Description |
|---|---|
| CREATE FUNCTION func_normal | Declares the creation of a function named func_normal. |
| RETURN table_type | Specifies the return type of the function as the custom table type table_type. |
| v_result | A local variable of type table_type (nested table or array type). |
| table_type() | Initializes an empty table instance, similar to a constructor. |
| v_result.EXTEND | Extends the table capacity. |
| v_result(1) := obj_type(1, 'A') | Creates an instance of the custom object type obj_type. |
| RETURN v_result | Returns the filled table variable, which must match the declared type. |
Here is a complete example of using a table function:
Define an object type.
obclient> CREATE OR REPLACE TYPE obj_type AS OBJECT ( id NUMBER, name VARCHAR2(50) );Define a table type.
obclient> CREATE OR REPLACE TYPE table_type IS TABLE OF obj_type;Call the table function.
obclient> SELECT * FROM TABLE(func_normal());