
- DBMS Tutorial
- DBMS - Home
- DBMS - Overview
- DBMS - Architecture
- DBMS - Data Models
- DBMS - Data Schemas
- DBMS - Data Independence
- Entity Relationship Model
- DBMS - ER Model Basic Concepts
- DBMS - ER Diagram Representation
- DBMS - Generalization, Aggregation
- Relational Model
- DBMS - Codd's Rules
- DBMS - Relational Data Model
- DBMS - Relational Algebra
- DBMS - ER to Relational Model
- DBMS- SQL Overview
- Relational Database Design
- DBMS - Database Normalization
- DBMS - Database Joins
- Storage and File Structure
- DBMS - Storage System
- DBMS - File Structure
- Indexing and Hashing
- DBMS - Indexing
- DBMS - Hashing
- Transaction And Concurrency
- DBMS - Transaction
- DBMS - Concurrency Control
- DBMS - Deadlock
- Backup and Recovery
- DBMS - Data Backup
- DBMS - Data Recovery
- DBMS Useful Resources
- DBMS - Quick Guide
- DBMS - Useful Resources
- DBMS - Discussion
Cursors in Oracle DBMS
When a SQL statement is executed in Oracle, the temporary context area is created. This area contains all the relevant information relating to the statement and its execution. The cursor is a pointer to this context area and allows the PL/SQL program to control this area.
There are two types of Cursors.
- Implicit Cursors
- Explicit Cursors
Let us begin with Implicit Cursors −
Implicit Cursors
Whenever an SQL statement is executed, the implicit cursors are automatically created. This happens if there is no explicit cursor for the particular statement. Implicit cursors cannot be controlled by the programmers.
There are many different attributes for Implicit Cursors. Some of them are −
%FOUND
If one or more records were fetched successfully with commands such as INSERT, UPDATE, DELETE etc., then it returns TRUE. Otherwise it returns FALSE.
%NOTFOUND
This is the direct opposite of %FOUND. If one or more records were fetched successfully with commands such as INSERT, UPDATE, DELETE etc., then it returns FALSE. Otherwise it returns TRUE.
%ROWCOUNT
This returns the number of rows that are affected by different commands such as INSERT, UPDATE, DELETE etc.
%ISOPEN
This returns TRUE if the cursor is open and false otherwise. However, for implicit cursors, the value is always FALSE because the cursor is closed immediately after executing its instruction.
Explicit Cursors
While implicit cursors were automatically created, explicit cursors are specifically created by the programmers. There definition is provided in the declaration section of the PL/SQL block.
Creating an explicit cursor has the following steps −
Declare Cursor
The cursor is declared as follows. Here, the cursor is c_student −
CURSOR c_student IS Select Stu_ID,Stu_Name from Student;
Open Cursor
The cursor is opened as follows −
OPEN c_student;
Fetch Cursor
One row at a time is accessed while fetching the cursor. Fetching the cursor is done as follows −
FETCH c_student INTO c_stuID, c_stuName;
Close Cursor
The allocated memory is released when the cursor is closed. This is done as follows −
CLOSE c_student;
- Related Articles
- How to display open cursors in Oracle?
- Functions in Oracle DBMS
- Block of PL/SQL in Oracle DBMS
- Difference between oracle golden gate and oracle active guard in the oracle
- How to set different type of cursors using CSS?
- Difference between Oracle 11g and Oracle 12c
- Oracle Bones
- Overview of Packages in Oracle
- Oracle Database Connection in Python
- How to PIVOT results in Oracle?
- How to UNPIVOT results in Oracle?
- Deadlocks in DBMS
- Cascadeless in DBMS
- Checkpoints in DBMS
- Constraints in DBMS
