SQL Cursor

SQL Cursor ek database object hota hai jo result set (query ka output) par row-by-row operations karne ki suvidha deta hai. Cursors ka istemal tab hota hai jab aapko query ke result set par iterative operations karne ki zaroorat hoti hai. Cursors ko samajhne ke liye kuch key concepts hain:

Types of Cursors:

1. Implicit Cursors:

   • Ye automatic generate hote hain SQL statements ke execution ke dauran.
   • Jab aap ek single SELECT statement execute karte hain, to SQL engine automatically ek implicit cursor create karta hai.

2. Explicit Cursors:

   • Ye user-defined hote hain aur inhe manually create, open, fetch, aur close karna hota hai.
   • Explicit cursors ka istemal tab hota hai jab aapko complex logic ya multiple rows par operations perform karna ho.

Cursor ke Operations:

1. Declare a Cursor:

   • Cursor declare karte samay aap specify karte hain ki kaunsa result set aap cursor ke liye use karna chahte hain.

   
   DECLARE cursor_name CURSOR FOR
   SELECT column1, column2 FROM table_name WHERE condition;
   

2. Open the Cursor:

   • Cursor ko open karne par, SQL server result set ko fetch karne ke liye ready ho jata hai.

   OPEN cursor_name;

3. Fetch from the Cursor:

   • Fetch operation ke madhyam se aap cursor se ek row ya rows ko retrieve kar sakte hain.

   
   FETCH NEXT FROM cursor_name INTO variable1, variable2;
   

4. Process the Fetched Data:

   • Jab aap data fetch karte hain, to aap us data par kuch operations (jaise insert, update, ya delete) perform kar sakte hain.

5. Close the Cursor:

   • Jab aapka kaam khatam ho jata hai, to cursor ko close karna zaroori hota hai taaki resources release ho sake.

   
   CLOSE cursor_name;
   

6. Deallocate the Cursor:

   • Cursor ko deallocate karne se woh memory release hoti hai jo cursor use kar raha tha.

   
   DEALLOCATE cursor_name;
   

Example:

Yahan ek example diya gaya hai jo cursor ka istemal dikhata hai:

DECLARE @employee_name NVARCHAR(100);

DECLARE employee_cursor CURSOR FOR
SELECT name FROM Employees WHERE department = 'Sales';

OPEN employee_cursor;

FETCH NEXT FROM employee_cursor INTO @employee_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @employee_name;  -- Ya koi aur processing
    FETCH NEXT FROM employee_cursor INTO @employee_name;
END

CLOSE employee_cursor;
DEALLOCATE employee_cursor;

Advantages of Cursors:

1. Row-by-Row Processing: Cursors aapko result set par row-by-row processing karne ki suvidha dete hain.
2. Complex Operations: Aap complex operations perform kar sakte hain jo simple SQL statements se sambhav nahi hote.

Disadvantages of Cursors:

1. Performance Overhead: Cursors ka istemal performance ko affect kar sakta hai, kyunki ye resources ko hold karte hain aur iterative processing karte hain.
2. Complexity: Cursors ko use karna kabhi-kabhi complex ho sakta hai, aur inhe sahi tarike se manage karna zaroori hai.

Conclusion:

Cursors SQL me ek powerful tool hain, lekin inka istemal kabhi-kabhi performance issues la sakta hai. Jab bhi sambhav ho, set-based operations ko prefer karna chahiye, lekin jab row-by-row processing ki zaroorat ho, tab cursors ka istemal faydemand ho sakta hai.