Techniques for Returning Multiple Results from MariaDB Stored Procedures

2024-07-27

Here are some key points to remember:

The calling program (written in a language like PHP or Python) is responsible for fetching and processing the results.
The stored procedure itself doesn't return the data directly. It executes the queries and makes the data available through other means.

Example Codes for Returning Multiple Rows from MariaDB Stored Procedures

Execute SELECT directly (no return):

CREATE PROCEDURE GetCustomerOrders(IN customer_id INT)
BEGIN
  SELECT order_id, order_date, total_amount
  FROM orders
  WHERE customer_id = customer_id;
END;

This procedure takes a customer_id as input and selects orders for that customer. However, it doesn't return anything directly. The calling program would need to execute it and then fetch the results:

// Example in PHP (assuming a mysqli connection object `$conn`)
$stmt = $conn->prepare("CALL GetCustomerOrders(?)");
$stmt->bind_param("i", $customerID);
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
  // Process each order row (e.g., order_id, order_date, total_amount)
}

$stmt->close();
$result->close();

Use a temporary table:

CREATE PROCEDURE GetCustomerOrders(IN customer_id INT)
BEGIN
  DECLARE tmp_table_name VARCHAR(64);
  SET tmp_table_name = CONCAT('customer_orders_', UUID());

  CREATE TEMPORARY TABLE tmp_table (
    order_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
  );

  INSERT INTO tmp_table (order_id, order_date, total_amount)
  SELECT order_id, order_date, total_amount
  FROM orders
  WHERE customer_id = customer_id;

  SELECT * FROM tmp_table;

  DROP TEMPORARY TABLE tmp_table;
END;

This procedure creates a temporary table named uniquely (customer_orders_ followed by a Universally Unique Identifier - UUID) to store the customer's orders. It then populates the table and selects all rows from it. Finally, it drops the temporary table.

The calling program can execute the procedure and then query the temporary table to retrieve the results:

CALL GetCustomerOrders(?);

SELECT * FROM customer_orders_<UUID>;  -- Replace <UUID> with the actual generated value

Cursors allow you to iterate through a result set fetched within the stored procedure. However, cursors can be complex to manage and have limitations compared to other methods. Here's a basic example:

CREATE PROCEDURE GetCustomerOrders(IN customer_id INT)
BEGIN
  DECLARE cur_orders CURSOR FOR SELECT order_id, order_date, total_amount
  FROM orders
  WHERE customer_id = customer_id;

  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE order_id INT;
  DECLARE order_date DATE;
  DECLARE total_amount DECIMAL(10,2);

  OPEN cur_orders;

  REPEAT
    FETCH cur_orders INTO order_id, order_date, total_amount;
    SET done = FOUND_ROWS() = 0;

    IF NOT done THEN
      -- Process each order (order_id, order_date, total_amount)
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur_orders;
END;

This procedure uses a cursor named cur_orders to iterate through the results of the SELECT statement. It declares variables to hold the fetched data and loops until there are no more rows.

Here's why cursors are not generally recommended:

Network Traffic: Cursors might require additional round trips between the client and server for fetching data.
Performance: Cursors can have performance overhead compared to simpler methods like temporary tables.
Complexity: Cursor management can be cumbersome, especially for handling errors and edge cases.

Alternatives:

Unless you have a specific need for cursor functionality within the stored procedure itself, it's generally better to use the methods mentioned earlier:

Use a temporary table: This provides a clear separation between data retrieval and processing.
Execute SELECT directly: This is simpler and often more performant.

mariadb