Oracle cursor expressions and table functions

Oracle cursor expressions are an important concept in Oracle databases. The following describes the knowledge of Oracle cursor expressions and table functions for your reference.

Oracle cursor expressions are sometimes referred to as cursor subqueues) are an element of the SQL language. Before Oracle9i, SQL and some programming environments, but not PL/SQL, support cursor expressions. Oracle9i introduces PL/SQL support for cursor expressions. For example, you can use a cursor expression in a SELECT statement that opens a PL/SQL cursor, and then perform proper control. It can also be used as real parameters of PL/SQL processes or functions, which is of great significance in combination with table functions.

Earlier versions of Oracle9i also support table functions in the initial form), while many significant improvements have been made in Oracle9i. Now, you can write table functions to immediately send rows as soon as they are computed, greatly improving the response time in the first rows example. Currently, table functions can be compiled to receive SELECT statements as input, and any number of transformations can be concatenated in the form of a chrysanthemum link, avoiding the need to store intermediate results. In addition, you can write table functions so that the computing is performed in parallel and the parallel query mechanism of Oracle can be used.

Parallel Execution of table functions means that PL/SQL functions can be used in the extraction, conversion, and loading (aka ETL) phase of Data Warehouse applications without serialization.

Cursor variable-Summary

Oracle9i Enhanced Function of batch acquisition from cursor variables of local dynamic SQL values
Use a cursor expression in PL/SQL
Use a cursor expression as the real parameter of PL/SQL Functions
"Young managers" Example
Table functions-Summary
Streamline table functions-Oracle9i added
Transfer data from a table function to the next table function-Oracle9i new
Access the "Young Managers" example again-Table Function Method
Fanout: Use table functions using the Boundary Effect
Parallel Execution of table functions-Oracle9i added
Table Function syntax based on the mode type
When writing a table function to return a pattern-level type, the syntax required to call it is somewhat lengthy. For the sake of completeness, it is demonstrated in the sample code.

Commercial benefits of table functions and cursors
Cursor expressions allow logical encapsulation for reuse in compatible queries, which improves developers' productivity and application reliability.
The table function improves the functionality and allows you to call a byte group set FROM any external data source and a byte group set composed of arbitrary calculations in the FROM List of SELECT clauses. For convenience, they can be used to define views and generate new functions.
Table functions can send rows as views-these rows come from any complex PL/SQL transformations originating from Oracle tables and therefore include other table functions-and do not need to store computed rows. This improves speed and scalability. It also improves developers' productivity and application reliability.
The input parameters of table functions enable the VIEW to be parameterized and further play the role of the VIEW. This increases code reusability and improves developers' productivity and application reliability.
A table function with reference cursor input parameters can be called together as a data source and another table function. As a result, table functions can be concatenated in the form of a chrysanthemum chain, which allows Modular programming and thus makes program design easier and improves reusability and Application robustness.
Table functions can be executed in parallel, improving the speed and scalability. Combined with the chrysanthemum chain feature, table functions are particularly suitable for Data Warehouse applications that perform extraction, conversion, and loading operations.
Fanout comes from the DML of independent transactions in table functions.
The table function allows you to query data stored in an embedded table just like associated storage. You can also query the data stored in the associated storage like the data stored in the embedded table. This is demonstrated in the example of runners training records given in the multi-tier collection code example .) This allows true independence between the data persistence storage format and the design of the data access application. You can define a VIEW on the table function, and create an instead of trigger on the VIEW to complete the screen .)
 

Use instances of Oracle stored procedures

Oracle command line custom editor vi

Implementation of oracle command line Logon

Oracle queries table space usage

Oracle sequence creation syntax