Menu
I have PL-SQL Packages which return REF Cursors when data has to be retrieved as part of the CRUD operations. Would it be faster if these cursors were replaced with TABLE functions ?
Thanks
994
A cursor is really any SQL statement that runs DML (select, insert, update, delete) on your database. A ref cursor is a pointer to a result set. This is normally used to open a query on the database server, then leave it up to the client to fetch the result it needs.
Using REF CURSOR s is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application. A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database.
There is no difference between using a type declared as REF CURSOR and using SYS_REFCURSOR , because SYS_REFCURSOR is defined in the STANDARD package as a REF CURSOR in the same way that we declared the type ref_cursor .
A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result. The advantage that a ref cursor has over a plain cursor is that is can be passed as a variable to a procedure or a function.
IMO TABLE functions are more usefull if you planned to use pipelined table functions. It`s not be faster, because REF is only reference to memory. And all the work (parse, execute, fetch and etc.) will be processed out of the function that return REF Cursor. REF Cursors adds flexibility to the detriment of easy of support. That is another article from "MacLochlainns Weblog" about REF Cursors and pipelined functions - Reference Cursors - Why, when, and how?
70
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
