Difference between Static Cursor Vs Dynamic Cursor in oracle

Hi Can any body de开发者_如何学Goscribe, what are the differences between static cursor and dynamic cursor with examples.

•A Static Cursor doesn't reflect data changes made to the DB once the ResultSet has been created whereas a Dynamic Cursor reflects the changes as and when they happen.

•A Static Cursor is much more performant than a Dynamic Cursor as it doesn't require further interaction with the DB server.

•A static cursor supports both Relative and Absolute Positioning whereas a Dynamic Cursor supports only Relative Positioning.

•A Static Cursor can be used for Bookmarking purposes as the data returned is static whereas a Dynamic Cursor can't be used for the same

Static

The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened. Static cursors detect few or no changes, but consume relatively few resources while scrolling.

Dynamic

Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor.

From http://technet.microsoft.com/en-us/library/ms191179.aspx

Check ref cursors. For example code, check here. The difference is ofcourse in the ability and implementation.

static cursor refers always one work area associated with cursor. where as ref cursor refers different work area in memory. ref cursor is used to declare a cursor with out select statement.