Implicit vs Explicit Cursors. Static vs Dynamic Cursors.

Life Cycle of DB Cursors. Implicit vs Explicit, Static vs Dynamic Cursors.

What are Database Cursors?

Oracle RDBMS assigns a private area to contain related information and the data returned or affected by a SQL statement when executed from a PL/SQL block. A Cursor is a mechanism of naming that private area so that the contents of which can be manipulated programmatically.

A Cursor in its simplest form can be thought of as a pointer to the records in database table or a virtual table represented by the result of a SELECT statement (in case of JOIN for example). A sample Cursor declaration/definition:

DECLARE

 CURSOR sample_cur /*... explicit cursor ...*/
 IS
 SELECT T2.description
 FROM SampleTable1 T1, SampleTable2 T2
 WHERE T1.id = T2.id

 /*...declaring a variable to hold data...*/
 sample_var VARCHAR2(50);

BEGIN

 /*... open the cursor if not opened already ...*/
 IF NOT sample_cur%ISOPEN
 THEN
  OPEN sample_cur;
 END IF;

 /*... fetching data from a cursor into a variable ...*/
 FETCH sample_cur INTO sample_var;

 /*... once finished using the cursor then close it ...*/
 CLOSE sample_cur;

END;

Once defined/declared a Cursor can be OPENed, FETCHed, and finally CLOSEd quite easily using the obvious looking commands. You can put the FETCH inside a loop if you wish to iterate through all the rows returned by the cursor. This of course holds true only for Explicit Cursors as these operations are (and can only be) performed implicitly by the PL/SQL engine for Implicit Cursors. Read more about the type of Cursors in the subsequent sections.

Life Cycle of a Cursor. How PL/SQL Engine executes a Cursor?

The PL/SQL Engine performs the following operations (either automatically or as specified in the program depending upon whether the cursor is implicit or explicit) during the entire life cycle of a database cursor:

• PARSE: making sure that the statement is valid and determining the execution plan. Implicitly done by the engine.
• BIND: associating values with placeholders in the SQL statement which is implicitly done by the engine for static SQL and needs to be specified explicitly for dynamic SQL.
• OPEN: acquiring memory for the cursor, initializing the cursor pointer and making the SQL statement ready to be executed. Implicitly done by the engine in case of an Implicit Cursor otherwise it's required to be specified for Explicit Cursors.
• EXECUTE: executing the SQL statement in the engine and seting the pointer to point just above the first row. Can't be specified explicitly.
  
• FETCH: retrieving the next row from the cursor's ResultSet. In case of an Explicit Cursor, this operation doesn't raise an exception/error when it reaches the end of the ResultSet. So you need to explicitly have some code making sure that the end of the ResultSet has reached.
• CLOSE: closing the cursor and releasing all memory used by it. Once close you can of course not use the cursor anymore. Implicit Cursors are automaticaly closed by the engine, but Explicit Cursors require the programmer to close them explicitly.

Implicit vs Explicit Cursors

For every INSERT, UPDATE, and DELETE in your code the PL/SQL engine creates implicit cursors and the developer doesn't require to create these cursors explicitly. In fact they can't create them explicitly even if they want to. Such cursors are called Implicit Cursors. If you have a SELECT statement returning only a single row then also an implicit cursors is created automatically. Implicit cursors are OPENed, FETCHed, and CLOSEd automatically and they don't offer any programmatic control.

For SELECT statements returning more than one rows you need to create cursors explicitly to iterate through all the returned rows/records. Such cursors are called Explicit Cursors. You can however create explicit cursors on those SELECT statement as well which return only a single row. It's advisable to do so if you're using PL/SQL Release 2.2 or earlier (read the reason below). But, if you're heavily re-using the same SELECT query then you would probably be better off with the implicit one for single-row SELECT statements.

Drawbacks of Implicit Cursors

• Less Control: Implicit Cursors can't be OPENed, FETCHed, or CLOSEd explicitly and hence they give less programming control.
• Less Efficient: In addition, they are comparatively less efficient than the explicit cursors (at least theoretically). The reason why it is slightly slow is because an implicit cursor runs as a SQL statement and Oracle SQL is ANSI-standard which requires a single-row query to not only fetch the first row, but also to perform a second fetch to ensure if more than one rwo will be returned by that query or not (in which case TOO_MANY_ROWS excepion will be raised by the PL/SQL engine). In case of an explicit cursor there would be only one FETCH in such a situation. But, this holds true only till PL/SQL Release 2.2 as in PL/SQL Release 2.3 implicit cursors got optimized and now they run slightly faster than the corresponding explcit cursor. In addition there are more chances that an implicit cursor would be re-used in the application and if that happens then the parsing time can be escaped as it might lie in pre-parsed state in the shared memory. So implicit cursors may enhace the overall performance of the application in case you're using PL/SQL 2.3 or later releases.
• Vulnerable to Data Errors: Less programmatic control on implicit cursors makes them more vulnerable to data errors as you can't really OPEN/FETCH/CLOSE them at will. For explicit cursors you may have custom validations in place and decide accordingly. Example: suppose you have a query returning only a single row and you are using an implicit cursor for that, but what if the same query returns morethan one rows in future. Implicit cursor will raise TOO_MANY_ROWS exception which may or may not like depending upon your application requirements.

Difference between Static Cursors and Dynamic Cursors

• 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 Cursor: A Database Cursor is called a Static Cursor if it captures the snapshot of data only at the time when the ResultSet (or Recordset in case of MS SQL Server) is created with no further DB interaction afterwards. And hence a Static Cursor will be unaware of any data changes made into the database after the ResultSet has been created. A Static Cursor facilitates scrolling through the static ResultSet and it supports both absolute and relative positioning. Reason being, the ResultSet is static and hence the cursor can always be sure of the position of all the records/rows. Relative positioning can be specified in terms of offsets from the current, top or bottom rows.

Dynamic Cursor: A Dynamic Cursor is the one which reflects all the data changes made into the database as and when they happen. This may require continuous re-ordering of data in the ResultSet due to the database changes and hence it's much more expensive than a Static Cursor. The possible re-ordering of records in the ResultSet doesn't allow a Dynamic Cursor to support absolute positioning as it can't be sure of the absolute position of a record/row in the ResultSet. Hence a Dynamic Cursor only supports relative positioning. Furthermore a Dynamic Cursor doesn't support Bookmarks for the obvious reasons.

Liked the article? You may like to Subscribe to this blog for regular updates. You may also like to follow the blog to manage the bookmark easily and to tell the world that you enjoy GeekExplains. You can find the 'Followers' widget in the rightmost sidebar.