• Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar
  • Skip to footer

Computer Notes

Library
    • Computer Fundamental
    • Computer Memory
    • DBMS Tutorial
    • Operating System
    • Computer Networking
    • C Programming
    • C++ Programming
    • Java Programming
    • C# Programming
    • SQL Tutorial
    • Management Tutorial
    • Computer Graphics
    • Compiler Design
    • Style Sheet
    • JavaScript Tutorial
    • Html Tutorial
    • Wordpress Tutorial
    • Python Tutorial
    • PHP Tutorial
    • JSP Tutorial
    • AngularJS Tutorial
    • Data Structures
    • E Commerce Tutorial
    • Visual Basic
    • Structs2 Tutorial
    • Digital Electronics
    • Internet Terms
    • Servlet Tutorial
    • Software Engineering
    • Interviews Questions
    • Basic Terms
    • Troubleshooting
Menu

Header Right

Home » Sql » Tutorials » SQL Cursors | Types of Cursors
Next →
← Prev

SQL Cursors | Types of Cursors

By Dinesh Thakur

Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a CURSOR lets you name a work area and access its stored information. 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.

 A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

A cursor is basically a set of rows that you can access one at a time. These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

Explicit cursors must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row. Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

Example:
 
SQL> SELECT EMPNO, ENAME, JOB FROM EMP WHERE DEPTNO=20; 

The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria. As Figure shows, an explicit cursor “points” to the current row in the result set. This allows your program to process the rows one at a time.

Types of Cursors

You have lots of options in PL/SQL for executing SQL, and all of them occur as some type of cursor. Generally, there are two types of SQL that you can execute in PL/SQL.

There are two types of cursors in PL/SQL:

Implicit Cursors

When you execute DML statements like Delete, Insert, Update and Select statements, implicit statements are created to process these statements. Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN. The values of the cursor attributes always refer to the most recently executed SQL statement. Before Oracle opens the SQL cursor, the implicit cursor attributes yield NULL.

For example, when you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected. When a SELECT. .. INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.

Explicit Cursors

When you need precise control over query processing, you can explicitly declare a cursor in the declarative part of any PL/SQL block, subprogram, or package. An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. We can provide a suitable name for the cursor.

 

You’ll also like:

  1. SQL: Types of Joins
  2. SQL CREATE INDEX Statement and Types of Indexes
  3. PL/SQL Triggers | Types of Triggers
  4. SQL Constraints and Types of Constraints
  5. JSTL SQL sql:query Tag
Next →
← Prev
Like/Subscribe us for latest updates     

About Dinesh Thakur
Dinesh ThakurDinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular Computer Notes blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps.

Dinesh Thakur is a Freelance Writer who helps different clients from all over the globe. Dinesh has written over 500+ blogs, 30+ eBooks, and 10000+ Posts for all types of clients.


For any type of query or something that you think is missing, please feel free to Contact us.


Primary Sidebar

SQL Tutorials

SQL Tutorials

  • SQL - Home
  • SQL - Select
  • SQL - Create
  • SQL - View
  • SQL - Sub Queries
  • SQL - Update
  • SQL - Delete
  • SQL - Order By
  • SQL - Select Distinct
  • SQL - Group By
  • SQL - Where Clause
  • SQL - Select Into
  • SQL - Insert Into
  • SQL - Sequence
  • SQL - Constraints
  • SQL - Alter
  • SQL - Date
  • SQL - Foreign Key
  • SQL - Like Operator
  • SQL - CHECK Constraint
  • SQL - Exists Operator
  • SQL - Drop Table
  • SQL - Alias Syntax
  • SQL - Primary Key
  • SQL - Not Null
  • SQL - Union Operator
  • SQL - Unique Constraint
  • SQL - Between Operator
  • SQL - Having Clause
  • SQL - Isnull() Function
  • SQL - IN Operator
  • SQL - Default Constraint
  • SQL - Minus Operator
  • SQL - Intersect Operator
  • SQL - Triggers
  • SQL - Cursors

Advanced SQL

  • SQL - Joins
  • SQL - Index
  • SQL - Self Join
  • SQL - Outer Join
  • SQL - Join Types
  • SQL - Cross Join
  • SQL - Left Outer Join
  • SQL - Right Join
  • SQL - Drop Index
  • SQL - Inner Join
  • SQL - Datediff() Function
  • SQL - NVL Function
  • SQL - Decode Function
  • SQL - Datepart() Function
  • SQL - Count Function
  • SQL - Getdate() Function
  • SQL - Cast() Function
  • SQL - Round() Function

Other Links

  • SQL - PDF Version

Footer

Basic Course

  • Computer Fundamental
  • Computer Networking
  • Operating System
  • Database System
  • Computer Graphics
  • Management System
  • Software Engineering
  • Digital Electronics
  • Electronic Commerce
  • Compiler Design
  • Troubleshooting

Programming

  • Java Programming
  • Structured Query (SQL)
  • C Programming
  • C++ Programming
  • Visual Basic
  • Data Structures
  • Struts 2
  • Java Servlet
  • C# Programming
  • Basic Terms
  • Interviews

World Wide Web

  • Internet
  • Java Script
  • HTML Language
  • Cascading Style Sheet
  • Java Server Pages
  • Wordpress
  • PHP
  • Python Tutorial
  • AngularJS
  • Troubleshooting

 About Us |  Contact Us |  FAQ

Dinesh Thakur is a Technology Columinist and founder of Computer Notes.

Copyright © 2025. All Rights Reserved.

APPLY FOR ONLINE JOB IN BIGGEST CRYPTO COMPANIES
APPLY NOW