SQL Cursor Operations

Welcome to our SQL Cursor Operations tutorial! In this lesson, we'll dive deep into one of the most powerful features of SQL – cursor operations. We'll explore what cursors are, how they work, and how to use them in practical real-world examples. 🎯

What are SQL Cursors?

A cursor is a database object that holds a set of rows from a database table. It allows us to iterate through the rows and perform operations on them one by one, similar to a loop in programming languages. 📝

Why Use SQL Cursors?

Cursors are useful when dealing with complex data manipulations, such as:

Managing transactions
Executing complex operations that cannot be performed using standard SQL statements
Reading and writing data row by row

How to Create a Cursor

Let's create a simple cursor that fetches all records from a table named employees.

sql

DECLARE employee_cursor CURSOR FOR
SELECT * FROM employees;

💡 Pro Tip: Replace employees with the name of your table.

How to Open a Cursor

To open a cursor and fetch its first row, use the OPEN command.

sql

OPEN employee_cursor;

How to Fetch Rows from a Cursor

To fetch rows from a cursor, use the FETCH command. Each FETCH statement retrieves the next row.

sql

FETCH NEXT FROM employee_cursor INTO emp_id, first_name, last_name, department;

💡 Pro Tip: Replace emp_id, first_name, last_name, and department with the appropriate column names from your table.

How to Close a Cursor

After you're done processing the cursor, close it using the CLOSE command.

sql

CLOSE employee_cursor;

Handling Cursor Errors

Cursors can be declared with an FOR loop that handles errors.

sql

DECLARE employee_cursor CURSOR FOR
SELECT * FROM employees;

DECLARE done INT DEFAULT FALSE;
DECLARE emp_id INT;
DECLARE first_name VARCHAR(50);
DECLARE last_name VARCHAR(50);
DECLARE department VARCHAR(50);

OPEN employee_cursor;

loop_cursor:
LOOP
  FETCH NEXT FROM employee_cursor INTO emp_id, first_name, last_name, department;
  IF SQLSTATE '02000' THEN
    LEAVE loop_cursor;
  ELSE
    -- Process the row here
  END IF;
END LOOP loop_cursor;

CLOSE employee_cursor;

📝 Note: The SQLSTATE '02000' checks for a No Data Found error and the LEAVE statement exits the loop when it occurs.

Practical Example: Update Employee Salaries

In this example, we'll update the salaries of employees in the employees table.

sql

DECLARE employee_cursor CURSOR FOR
SELECT * FROM employees;

DECLARE done INT DEFAULT FALSE;
DECLARE emp_id INT;
DECLARE current_salary DECIMAL(10, 2);
DECLARE new_salary DECIMAL(10, 2);

OPEN employee_cursor;

loop_cursor:
LOOP
  FETCH NEXT FROM employee_cursor INTO emp_id, first_name, last_name, salary;
  IF SQLSTATE '02000' THEN
    LEAVE loop_cursor;
  ELSE
    -- Update the employee's salary
    SET new_salary = salary * 1.05;
    UPDATE employees SET salary = new_salary WHERE emp_id = emp_id;
  END IF;
END LOOP loop_cursor;

CLOSE employee_cursor;

💡 Pro Tip: In the above example, we're increasing the salary of each employee by 5%.

Quiz

We hope you enjoyed learning about SQL cursor operations! As you practice and explore, you'll find that cursors can be a powerful tool for managing complex data manipulations. Happy coding! 🚀