Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Cursors

Explore how to use cursors in MySQL stored routines to iterate through result sets and process data row-by-row efficiently. Understand procedure creation, loop control, cursor declaration, and handlers to manage cursor states and finalize your queries.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
DELIMITER //
DROP PROCEDURE IF EXISTS GetCustomerOrderSummary_ByDateRange //
CREATE PROCEDURE GetCustomerOrderSummary_ByDateRange(IN start_date DATE, IN end_date DATE)
BEGIN
  -- Step 1: Declare loop flag and variables for current customer id and name
  DECLARE done INT DEFAULT 0;
  DECLARE v_customer_id INT;
  DECLARE v_customer_name VARCHAR(50);
  -- Step 2: Declare cursor over DISTINCT customers with orders in [start_date, end_date]
  DECLARE cur_customers CURSOR FOR
    SELECT DISTINCT c.CustomerID, c.CustomerName
    FROM   Customers c
    JOIN   Orders o ON o.CustomerID = c.CustomerID
    WHERE  o.OrderDate BETWEEN start_date AND end_date;
  -- Step 3: Declare NOT FOUND handler to set the loop flag
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  -- Step 4: create and reset the temporary table
  CREATE TEMPORARY TABLE IF NOT EXISTS tmp_customer_summary (
    CustomerID     INT,
    CustomerName   VARCHAR(50),
    OrdersInRange  INT,
    TotalSpent     DECIMAL(10,2)
  );
  TRUNCATE TABLE tmp_customer_summary;
  -- Step 5: OPEN cursor, LOOP and FETCH, EXIT when done, INSERT summary row (COUNT(*) and SUM(TotalAmount)), then CLOSE cursor
  OPEN cur_customers;
  read_loop: LOOP
    FETCH cur_customers INTO v_customer_id, v_customer_name;
    IF done = 1 THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO tmp_customer_summary (CustomerID, CustomerName, OrdersInRange, TotalSpent)
    SELECT v_customer_id,
           v_customer_name,
           COUNT(*),
           COALESCE(SUM(o.TotalAmount), 0)
    FROM   Orders o
    WHERE  o.CustomerID = v_customer_id
    AND    o.OrderDate BETWEEN start_date AND end_date;
  END LOOP;
  CLOSE cur_customers;
  -- Step 6: return the results
  SELECT   CustomerID, CustomerName, OrdersInRange, TotalSpent
  FROM     tmp_customer_summary
  ORDER BY TotalSpent DESC, CustomerID;
END //
//
DELIMITER ;
-- Call the procedure
CALL GetCustomerOrderSummary_ByDateRange('2025-03-01', '2025-03-10');

Explanation

The explanation of the solution code is given below:

  • Line 1: Switch the statement delimiter so the routine body can contain semicolons safely.

  • Line 3: Drop the procedure if it already exists so the new definition can be created cleanly.

  • Line 4: Create the procedure GetCustomerOrderSummary_ByDateRange with two IN parameters for the date window.

  • Line 5: Start the procedure block with BEGIN, which defines the scope for all declarations and executable statements.

  • Lines 10–12: Declare a loop flag done and local variables for the current CustomerID and CustomerName.

  • Lines 15–19: Declare a cursor that iterates distinct customers who placed at least one order in [start_date, end_date].

  • Line 22: Declare a CONTINUE handler that sets done = 1 when the cursor has no more rows.

  • Lines 25–31: Create and reset the session-scoped temporary ...