Difference Between Explicit Cursor and Implicit Cursor
Coming to databases, in IT parlance, a cursor is a control structure which permits the user to traverse over the records in a data base. A cursor offers a mechanism to allocate a name to an SQL select statement which can then be utilized to maneuver the information within that SQL statement. There are however two cursors known as Implicit cursors and Explicit cursors. Implicit cursors are created automatically and used every time a Select statement is issued in PL/SQL, when there is no explicitly defined cursor. Explicit cursors on the other hand, as the name implies are classified clearly by the developer. In PL/SQL, an explicit cursor is in fact a named query characterized using the keyword cursor.
Every time a select statement is issued, implicit cursors are automatically created and used by Oracle. However, in case, an implicit cursor is used, DBMS or the Database Management System will execute the open, fetch and close operations automatically. Implicit cursors are supposed to be used only with SQL statements that return a single row. If the SQL statement returns more than one row, the usage of an implicit cursor will introduce an error. Implicit cursors are robotically connected with each DML or Data Manipulation Language statements, such as INSERT, UPDATE and DELETE statements. An implicit cursor is usually used to process SELECT INTO statements. When extracting data using implicit cursors NO_DATA_FOUND exception can be raised when the SQL statement returns no data. Further, implicit cursors can raise TOO_MANY_ROWS exceptions when the SQL statement returns more than one row.
As indicated earlier, explicit cursors are queries defined using a name. An explicit cursor can be considered as a pointer to a set of records and the pointer can be moved forward within the set of records. Explicit cursors also offer the user the complete control over opening, closing and fetching data. Further, multiple rows can be obtaine using an explicit cursor. Explicit cursors also have the ability to take parameters just like any function or procedure so that the variables in the cursor can be altered each time it is executed. Additionally, explicit cursors permit the user to fetch a whole row in to a PL/SQL record variable. While using an explicit cursor, it first needs to be confirmed using a name. Cursor characteristics can be accessed using the name given to cursor. After declaration, the cursor needs to be opened first. Then fetching can be begun. If multiple rows need to be fetched, the fetching operation is required to be done inside a loop. Ultimately, the cursor needs to be closed.
The main difference between the implicit cursor and the explicit cursor is that an explicit cursor requires to be defined explicitly by providing a name while implicit cursors are automatically created, when the user issues a select statement. Further, 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 while using explicit cursors, as against implicit cursors. In essence, implicit cursors are more susceptible to data errors and therefore provide less programmatic control than explicit cursors. Implicit cursors are considered to be less efficient than explicit cursors.