Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 40 ยท Stored Programs and Triggers

Stored Procedures in MariaDB

Write MariaDB stored procedures with IN, OUT and INOUT parameters, IF, CASE and loops, error handlers, SIGNAL and transactions, then call, inspect and drop them safely.

A stored procedure is a named block of SQL statements stored in the database and run with CALL. Unlike a function it returns no value in an expression; it can take input and output parameters, run several statements, open transactions, return result sets and change data. Typical uses: a multi-step operation that must be atomic (place an order, transfer money), a nightly maintenance job, or an API for applications that should not touch tables directly.

MariaDB syntax runs on a MariaDB server; the in-browser engine has no stored procedures. So the procedure code is read-only here, and each part has a runnable stand-in written as plain SQL that does the same steps.

Creating and calling a procedure

SQL
DELIMITER //

CREATE PROCEDURE raise_salary(IN p_department INT, IN p_percent DECIMAL(5,2))
BEGIN
  UPDATE employees
  SET salary = ROUND(salary * (1 + p_percent / 100))
  WHERE department_id = p_department;

  SELECT ROW_COUNT() AS employees_updated;
END //

DELIMITER ;

CALL raise_salary(3, 4.5);

CALL can be run from any client or driver. A SELECT inside the procedure sends a result set to the caller, which is how a report procedure returns data. Procedures are stored per database; call another database's procedure with CALL other_db.proc().

Management commands:

SQL
SHOW PROCEDURE STATUS WHERE Db = 'shop';
SHOW CREATE PROCEDURE raise_salary;
DROP PROCEDURE IF EXISTS raise_salary;
CREATE OR REPLACE PROCEDURE raise_salary(...) ...;   -- MariaDB 10.1.3+

Parameters: IN, OUT, INOUT

Mode Meaning
IN (default) value goes in; changes inside the procedure are not visible to the caller
OUT starts as NULL; the procedure sets it and the caller reads it afterwards
INOUT value goes in and the changed value comes back
SQL
DELIMITER //
CREATE PROCEDURE order_stats(IN p_customer INT, OUT p_orders INT, OUT p_spent DECIMAL(12,2))
BEGIN
  SELECT COUNT(*), COALESCE(SUM(total), 0)
    INTO p_orders, p_spent
  FROM orders
  WHERE customer_id = p_customer;
END //
DELIMITER ;

CALL order_stats(7, @n, @amount);
SELECT @n AS orders, @amount AS spent;

OUT arguments are passed as user variables (@n). Give parameters a prefix such as p_ so they never clash with column names.

Flow control and loops

SQL
IF v_total > 1000 THEN ... ELSEIF v_total > 100 THEN ... ELSE ... END IF;

CASE v_status WHEN 'new' THEN ... WHEN 'paid' THEN ... ELSE ... END CASE;

SET i = 1;
WHILE i <= 5 DO
  INSERT INTO numbers VALUES (i);
  SET i = i + 1;
END WHILE;

REPEAT ... UNTIL condition END REPEAT;

my_loop: LOOP
  ...
  IF done THEN LEAVE my_loop; END IF;     -- exit
  ITERATE my_loop;                         -- next round
END LOOP my_loop;

Loops that write one row at a time are slow. A single set-based statement (INSERT ... SELECT, a recursive CTE, a seq_1_to_100 sequence table) is nearly always faster. Use a loop only when each round depends on the previous one.

SQL Stand-in: generate rows without a loop

Press Run (or Ctrl + Enter) to see the result here.

Transactions and error handling

The reason for many procedures is to make several changes all-or-nothing. Declare a handler that rolls back when anything fails:

SQL
DELIMITER //
CREATE PROCEDURE place_order(IN p_customer INT, IN p_product INT, IN p_qty INT, OUT p_order_id INT)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;                       -- re-raise the original error to the caller
  END;

  START TRANSACTION;

  UPDATE products SET stock = stock - p_qty
  WHERE id = p_product AND stock >= p_qty;

  IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Not enough stock';
  END IF;

  INSERT INTO orders (customer_id, ordered_on) VALUES (p_customer, CURDATE());
  SET p_order_id = LAST_INSERT_ID();

  INSERT INTO order_lines (order_id, product_id, quantity)
  VALUES (p_order_id, p_product, p_qty);

  COMMIT;
END //
DELIMITER ;

Points worth knowing:

  • DECLARE EXIT HANDLER FOR SQLEXCEPTION runs the handler and leaves the block; CONTINUE HANDLER resumes after the failing statement. Handlers can match SQLSTATE '23000', a numeric code such as 1062 (duplicate key), SQLWARNING or NOT FOUND.
  • SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...' raises your own error ('45000' means "unhandled user-defined exception"). RESIGNAL re-raises the current one, and GET DIAGNOSTICS reads details.
  • The WHERE stock >= p_qty condition makes check-and-change a single atomic statement, so two buyers cannot both take the last item.
  • InnoDB tables are transactional; MyISAM tables ignore ROLLBACK.

Runnable stand-in with the same steps as a script, so you can watch the atomicity (the ROLLBACK stands in for the failing branch):

SQL Stand-in: all-or-nothing stock update

Press Run (or Ctrl + Enter) to see the result here.

The guarded UPDATE is the heart of the pattern: the second order fails quietly and leaves the data consistent. In MariaDB the procedure would signal an error instead.

Calling procedures from code

PHP
$stmt = $pdo->prepare('CALL order_stats(?, @n, @spent)');
$stmt->execute([7]);
$row = $pdo->query('SELECT @n AS orders, @spent AS spent')->fetch();

Use prepared statements for the arguments. Procedures that return result sets can leave extra result sets pending on some drivers, so consume or close them before the next query.

Security and maintenance

  • SQL SECURITY DEFINER (default) lets users run a procedure that touches tables they cannot access: grant EXECUTE and nothing else (see users, grants and roles).
  • Put procedures in version control as migration files, not only in the database.
  • Debug with SELECT statements printing variables, or log into a table; MariaDB also has SHOW PROCEDURE CODE only when built with debugging.
  • Avoid dynamic SQL built by concatenating strings; if you must, use PREPARE stmt FROM @sql; EXECUTE stmt USING @a; DEALLOCATE PREPARE stmt; with ? placeholders.

Common mistakes

  • Parameter names equal to column names (WHERE id = id).
  • Forgetting that DECLARE order is variables, conditions, cursors, handlers.
  • Starting a transaction in the procedure while the caller already started one: START TRANSACTION commits the caller's transaction implicitly.
  • Row-by-row loops where one set-based statement would do.
  • Using SELECT ... INTO when the query returns more than one row (error) or none (warning, variable unchanged).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How do you run a stored procedure?

    Procedures are invoked with CALL. Functions are used inside expressions.
  2. 2. Which parameter mode lets the procedure send a value back to the caller without needing an input value?

    OUT parameters start as NULL and the value set inside the procedure is visible to the caller afterwards.
  3. 3. What does DECLARE EXIT HANDLER FOR SQLEXCEPTION typically contain in a transactional procedure?

    The handler undoes the partial work and re-raises the error so the caller knows the operation failed.
  4. 4. Why is UPDATE products SET stock = stock - 1 WHERE id = 5 AND stock >= 1 safer than reading the stock first?

    There is no gap between checking and changing in which another session could take the last item.

Summary

  • CREATE PROCEDURE + CALL; parameters can be IN, OUT or INOUT.
  • Use IF, CASE, WHILE, LOOP sparingly; prefer set-based SQL.
  • Handlers, SIGNAL and transactions give all-or-nothing operations.
  • Grant EXECUTE to give applications a narrow interface; keep source code in version control.

Next: Triggers