What is Cursor? Why do we need cursor? Example.

 

Q. What is a Cursor?

Ans:

A cursor is a pointer/handle to the result set of a query in PL/SQL.

It lets us process rows one by one (row-by-row iteration).

Think of it like:

👉 SQL gives you a whole table of results.

👉 A cursor lets you pick each row, work with it, then move to the next.

Q. Why Do We Need Cursors?

Ans:

SQL by itself works in sets (all rows at once).

Example:

UPDATE students SET grade='A' WHERE marks > 90.

→ These updates all qualifying rows in one go. 

But PL/SQL is procedural. 

Sometimes you need to:

1. Fetch one row, process it, make a decision

2. Then move to next row, apply a different logic

👉 In such cases, we require cursors.

Example Without Cursor (Problem Case)

Suppose we have a table Students (roll_no, name, marks)

Now we want to give grades like:

Marks ≥ 90 → Grade 'A'

Marks 75–89 → Grade 'B'

Marks < 75 → Grade 'C'

We cannot write one simple SQL UPDATE because logic is conditional per row.

Notes in Detail:

Problem Statement:

Cursors: (All types: Implicit, Explicit, Cursor FOR Loop, Parameterized Cursor)

Write a PL/SQL block of code using parameterized Cursor that will merge the data available in the newly created table N_RollCall with the data available in the table O_RollCall. If the data in the first table already exist in the second table, then that data should be skipped.

Note: Instructor will frame the problem statement for writing PL/SQL block using all types of Cursors in line with above statement.

Algorithm:

Let's start the Implementation of above problem statement as follows:

1. We need to create two tables N_RollCall (Rno, Sname, Per) and O_RollCall (Rno, Sname, Per) with same attributes.

2. Insert the 3-4 records inside the O_RollCall table after that Insert 4-8 records inside the N_RollCall table out of which 3 records keep same as it is inside the O_RollCall tables.

3. First try to understand what Implicit Cursor is and how it works?

4. Second try to implement Explicit Cursor.

5. Third try to implement Cursor FOR Loop example.

6. Forth try to implement Parameterized Cursor that will merge the data available in the newly created table N_RollCall with the data available in the table O_RollCall.

6.1 Apply the logic: If the data in the first table already exist in the second table, then that data should be skipped.

Implementation:

1. We need to create two tables N_RollCall (Rno, Sname, Per) and O_RollCall (Rno, Sname, Per) with same attributes.

2. Insert the 3-4 records inside the O_RollCall table after that Insert 4-8 records inside the N_RollCall table out of which 3 records keep same as it is inside the O_RollCall tables.

Here, if we observe carefully in N_RollCall table two records 101 and 103 already present inside the O_RollCall table. so, by skipping these two records we want to merge remaining two records 102 and 104 inside the O_RollCall table.

3. First try to understand what Implicit Cursor is and how it works?

4. Second try to implement Explicit Cursor.