Explicit Cursor vs Implicit Cursor
When it comes to databases, a cursor is a control structure that allows traversing over the records in a database. A cursor provides a mechanism to assign a name to a SQL select statement and then it can be used to manipulate the information within that SQL statement. Implicit cursors are automatically created and used every time a Select statement is issued in PL/SQL, when there is no explicitly defined cursor. Explicit cursors, as the name suggests, are defined explicitly by the developer. In PL/SQL an explicit cursor is actually a named query defined using the key word cursor.
What is Implicit Cursor?
Implicit cursors are automatically created and used by Oracle each time a select statement is issued. If an implicit cursor is used, the Database Management System (DBMS) will perform the open, fetch and close operations automatically. Implicit cursors should be used only with SQL statements that return a single row. If the SQL statement returns more than one row, using an implicit cursor will introduce an error. An implicit cursor is automatically associated with each Data Manipulation Language (DML) statements, namely INSERT, UPDATE and DELETE statements. Also, an implicit cursor is used to process SELECT INTO statements. When fetching data using implicit cursors NO_DATA_FOUND exception can be raised when the SQL statement returns no data. Furthermore, implicit cursors can raise TOO_MANY_ROWS exceptions when the SQL statement returns more than one row.
What is Explicit Cursor?
As mentioned earlier, explicit cursors are queries defined using a name. An explicit cursor can be thought of as a pointer to a set of records and the pointer can be moved forward within the set of records. Explicit cursors provide the user the complete control over opening, closing and fetching data. Also, multiple rows can be fetched using an explicit cursor. Explicit cursors can also take parameters just like any function or procedure so that the variables in the cursor can be changed each time it is executed. In addition, explicit cursors allow you to fetch a whole row in to a PL/SQL record variable. When using an explicit cursor, first it needs to be declared using a name. Cursor attributes can be accessed using the name given to cursor. After declaring, cursor needs to be opened first. Then fetching can be started. If multiple rows need to be fetched, the fetching operation needs to be done inside a loop. Finally, the cursor needs to be closed.
Difference Between Explicit Cursor and Implicit Cursor
The main difference between the implicit cursor and explicit cursor is that an explicit cursor needs to be defined explicitly by providing a name while implicit cursors are automatically created when you issue a select statement. Furthermore, multiple rows can be fetched using explicit cursors while implicit cursors can only fetch a single row. Also NO_DATA_FOUND and TOO_MANY_ROWS exceptions are not raised when using explicit cursors, as opposed to implicit cursors. In essence, implicit cursors are more vulnerable to data errors and provide less programmatic control than explicit cursors. Also, implicit cursors are considered less efficient than explicit cursors.
Leave a Reply