Compare the Difference Between Similar Terms

Difference Between

Home / Technology / IT / Database / Difference Between Explicit Cursor and Implicit Cursor

Difference Between Explicit Cursor and Implicit Cursor

April 26, 2011 Posted by Roshan Ragel

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.

Related posts:

Difference Between SQL and PL SQL Difference Between DBMS and Database Difference Between RDBMS and OODBMS Difference Between RDBMS and ORDBMS Difference Between DBMS and Data Warehouse

Filed Under: Database Tagged With: cursor, Data Manipulation Language, difference, difference between, difference between implicit and explicit cursor, DML, explicit and implicit cursor, explicit cursor, explicit cursor and implicit cursor, implicit and explicit cursor, implicit cursor, implicit cursor and explicit cursor, Oracle, PL/SQL, SQL, SQL statement, what is explicit cursor, what is implicit cursor

About the Author: Roshan Ragel

Dr.Roshan G. Ragel, is a Doctorate in Computer Science and Engineering and Member of IET (UK) and IEEE. His research interests include Micro-architectural aspects of Embedded Systems Design and their Security and Reliability issues.

Comments

  1. Joban says

    December 17, 2018 at 3:29 pm

    Exact answer

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Request Article

Featured Posts

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and SARS

Difference Between Coronavirus and SARS

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Covid 19

Difference Between Coronavirus and Covid 19

You May Like

Difference Between Samsung Galaxy S3 and HTC Velocity 4G

What is the Difference Between Alopecia Areata and Tinea Capitis

What is the Difference Between Alopecia Areata and Tinea Capitis

Difference Between Early and Late Binding

Difference Between Early and Late Binding

Difference Between Balanced Equation and Net Ionic Equation

Difference Between Balanced Equation and Net Ionic Equation

Difference Between Solvolysis and Aminolysis

Difference Between Solvolysis and Aminolysis

Latest Posts

  • What is the Difference Between Ankylosing Spondylitis and Cervical Spondylosis
  • What is the Difference Between PAC and PVC
  • What is the Difference Between Neuralgia and Neuritis
  • What is Difference Between Craniopharyngioma and Pituitary Adenoma
  • What is the Difference Between Hydrochlorothiazide and Chlorthalidone
  • What is the Difference Between Cauda Equina and Conus Medullaris Syndrome
  • Home
  • Vacancies
  • About
  • Request Article
  • Contact Us

Copyright © 2010-2018 Difference Between. All rights reserved. Terms of Use and Privacy Policy: Legal.