Pipelined table functions can be used to convert data, and data transfer between pipelined table functions is supported.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Convert data by using a pipelined table function
A pipelined table function with cursor variables can receive any arguments just like a regular function. You can specify cursor variables in a pipelined table function to obtain input rows and use the PIPE ROW statement to transmit the converted one or more rows to the caller. If the FETCH and PIPE ROW statements are contained in the LOOP statement, the function can convert multiple input rows.
Transfer data between pipelined table functions
The cursor variable arguments of a pipelined table function can be cursor variables or CURSOR expressions. The function is more efficient with CURSOR expressions. If the SELECT statement passes a CURSOR expression to the function, the referenced cursor is opened when the function is started and is closed when the function is completed.
Obtain the result of a pipelined table function
You can associate a named cursor with the query that calls a pipelined table function to obtain the results of the function. Such cursors have no special acquisition semantics, and cursor variables have no special assignment semantics.
When a pipelined table function acts as an aggregate function, it accepts a set of input rows and returns a single result. You can use the SELECT statement to select the function result.
Perform DML operations on the result of a pipelined table function
The "table" returned by a pipelined table function cannot be the target table in a DELETE, INSERT, UPDATE, or MERGE statement. However, you can create a view for the table and create an INSTEAD OF trigger in the view. For more information about the INSTEAD OF trigger, see CREATE TRIGGER.