MariaDB DO Statement Explained: Alternatives for Procedural Control Flow

1. Stored Procedures
   MariaDB supports stored procedures, which are essentially named blocks of SQL statements grouped together to perform a specific task. These procedures can be executed by calling their name, similar to a function. You can achieve programmatic logic and conditional execution within stored procedures.

2. Control Flow Statements (Limited)
   While SQL itself is a declarative language focused on what data to retrieve or manipulate, MariaDB offers a few control flow statements within procedures. These include IF...THEN...ELSE for conditional execution and WHILE loops for repetitive tasks.

Here are some resources for further exploration:

• Control Flow Statements in MariaDB: Information on these is typically scattered within documentation for stored procedures. You can search for it within the MariaDB documentation.

---

DELIMITER //

CREATE PROCEDURE check_stock(IN product_id INT)
BEGIN
  DECLARE stock_count INT;

  SELECT stock FROM products WHERE product_id = product_id INTO stock_count;

  IF stock_count > 0 THEN
    SELECT CONCAT('Product (', product_id ,') in stock!');
  ELSE
    SELECT CONCAT('Product (', product_id ,') is out of stock!');
  END IF;
END //

DELIMITER ;

1. We define a delimiter (//) to differentiate the stored procedure code from regular SQL statements.
2. The CREATE PROCEDURE statement creates a new procedure named check_stock.
3. It takes an IN parameter product_id of type integer.
4. Inside the procedure, we declare a variable stock_count to store the retrieved stock level.
5. We use a SELECT statement to fetch the stock for the given product_id and store it in stock_count.
6. The IF...THEN...ELSE statement checks if stock_count is greater than 0.

• If false, it displays a message indicating the product is out of stock.
• If true, it displays a message indicating the product is in stock.

1. Finally, the procedure ends with the END // statement.
2. We reset the delimiter back to ; using DELIMITER ;.

Usage

CALL check_stock(123);

This would call the procedure with product ID 123 and display the appropriate message based on the stock level.

This example simulates a simple "DO loop" functionality using a WHILE loop in a stored procedure.

DELIMITER //

CREATE PROCEDURE update_prices(IN raise_percent DECIMAL(5,2))
BEGIN
  DECLARE product_id INT;
  DECLARE price DECIMAL(10,2);
  DECLARE cursor_products CURSOR FOR SELECT product_id, price FROM products;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;

  OPEN cursor_products;
  SET finished = FALSE;

  WHILE finished = FALSE DO
    FETCH NEXT FROM cursor_products INTO product_id, price;
    IF finished THEN LEAVE WHILE; END IF;

    SET price = price * (1 + raise_percent/100);

    UPDATE products SET price = price WHERE product_id = product_id;
  END WHILE;

  CLOSE cursor_products;
END //

DELIMITER ;

1. We define a stored procedure update_prices that takes a decimal parameter raise_percent for the price increase.
2. We declare variables for product ID, price, and a cursor to iterate through products.
3. A DECLARE CONTINUE HANDLER is used to handle the "not found" scenario when the cursor reaches the end. It sets a flag finished to TRUE.
4. We open the cursor to retrieve product IDs and prices.
5. The WHILE loop continues as long as finished is FALSE.
6. Inside the loop, we fetch the next product details using FETCH NEXT.
7. The LEAVE WHILE statement exits the loop when there are no more products.
8. We calculate the new price with the provided percentage increase.
9. An UPDATE statement updates the price for the current product in the products table.
10. Finally, we close the cursor and the procedure ends.

Usage

CALL update_prices(5.00);

This would call the procedure and increase all product prices by 5%.

---

Stored Procedures

• As shown in the previous examples, Stored Procedures are the most versatile alternative. You can achieve programmatic logic, conditional execution (like IF statements), and looping (using WHILE loops) within these procedures.

Control Flow Statements (Limited)

• MariaDB offers a few control flow statements within stored procedures:
  • IF...THEN...ELSE for conditional execution (used in the previous examples).
  • WHILE loops for repetitive tasks (used in the previous examples).
  • CASE statements for multi-way conditional branching.

SET Statements with Logic

• In some simple cases, you can achieve limited logic using SET statements with conditional expressions. However, this approach can become cumbersome for complex scenarios.

Choosing the Right Alternative

• For basic looping within a stored procedure, WHILE loops provide a structured approach.
• For simple conditional branching within a single statement, IF statements might be suitable.
• For complex logic or repetitive tasks, Stored Procedures are the best option.

• Choose the approach that best balances readability, maintainability, and complexity for your specific needs.
• Stored Procedures offer the most flexibility but require more code compared to simpler control flow statements.