Cursors

A cursor is a pointer to this temporary work area (context area) created in the system memory, when a SQL statement is executed. Temporary work area is used to store the data retrieved from the database and manupulate this data. It can hold more than one row but can process one row at a time.

There are two different Types of cursors
- Implicit Cursors 
- Explicit Cursors

Implicit Cursors : Implicit cursors are automatically created by oracle whenever SQL statement is executed. At the time of DML operations like insert, update and delete these cursors hold the data that needs to be inserted.
Here when you write a query completely table data will be processed in the cursor area.

Explicit Cursors : Explicit Cursors are created by users. They are used to display multiple records using program or procedure. 
Here when you write a query only the requred columns data will be processed in the cursor area.
It should be defined in the declaration section of the PLSQL block and created on select statement.
Fetch operation can get only 1 record because pointer is on the first record inorder to fetch all the records 'Loop' is used.

4 steps to write a cursor
- Declare Cursor
- Open Cursor
- Fetch data from Cursor
- Close Cursor

Cursor Properties
- <cursor name> %ISOPEN 
- <cursor name> %FOUND
- <cursor name> %NOT FOUND
- <cursor name> %ROWCOUNT


** Write a program to loop through each employee and print their salary.

DECLARE @EmpID INT, @EmpName VARCHAR(50), @Salary DECIMAL(10,2);

-- Step 1: Declare the cursor

DECLARE Employee_Cursor CURSOR FOR

SELECT EmpID, EmpName, Salary

FROM Employee;

-- Step 2: Open the cursor

OPEN Employee_Cursor;

-- Step 3: Fetch the first row

FETCH NEXT FROM Employee_Cursor INTO @EmpID, @EmpName, @Salary;

-- Step 4: Loop through rows

WHILE @@FETCH_STATUS = 0

BEGIN

    PRINT 'Employee ID: ' + CAST(@EmpID AS VARCHAR) +

          ', Name: ' + @EmpName +

          ', Salary: ' + CAST(@Salary AS VARCHAR);

    -- Fetch next row

    FETCH NEXT FROM Employee_Cursor INTO @EmpID, @EmpName, @Salary;

END

-- Step 5: Close and deallocate cursor

CLOSE Employee_Cursor;

DEALLOCATE Employee_Cursor;