Creating and running MySQL stored functions and procedures

This guide explains how to create and execute MySQL stored functions and procedures, which can enhance database security, maintain data integrity, and improve performance.

Stored Functions

Stored functions in MySQL allow you to manipulate and process data efficiently.

Setting Up a Test Database

1. Create a test database:

CREATE DATABASE username_test;

2. Select the database:

USE username_test;

3. Create a products table:

CREATE TABLE products (
    prod_id INT NOT NULL AUTO_INCREMENT,
    prod_name VARCHAR(20) NOT NULL,
    prod_cost FLOAT NOT NULL DEFAULT 0.0,
    prod_price FLOAT NOT NULL DEFAULT 0.0,
    PRIMARY KEY(prod_id)
);

4. Insert sample data:

INSERT INTO products (prod_name, prod_cost, prod_price)
VALUES
('Basic Widget', 5.95, 8.35),
('Micro Widget', 0.95, 1.35),
('Mega Widget', 99.95, 140.00);

Creating a Stored Function

Create a function calcProfit that calculates profit by subtracting cost from price:

DELIMITER $$
CREATE FUNCTION calcProfit(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2)
BEGIN
  DECLARE profit DECIMAL(9,2);
  SET profit = price - cost;
  RETURN profit;
END$$
DELIMITER ;

Using the Stored Function

Run a query using the function:

SELECT *, calcProfit(prod_cost, prod_price) AS profit FROM products;

Sample output:

+---------+--------------+-----------+------------+--------+
| prod_id | prod_name    | prod_cost | prod_price | profit |
+---------+--------------+-----------+------------+--------+
|       1 | Basic Widget |      5.95 |       8.35 |   2.40 |
|       2 | Micro Widget |      0.95 |       1.35 |   0.40 |
|       3 | Mega Widget  |     99.95 |      140.00|  40.05 |
+---------+--------------+-----------+------------+--------+

Stored Procedures

Stored procedures differ from stored functions in that they must be invoked with the CALL statement.

Creating a Stored Procedure

Example of a basic procedure procedureTest that selects product names:

DELIMITER $$
CREATE PROCEDURE procedureTest()
BEGIN
  SELECT prod_name FROM products;
END$$
DELIMITER ;

Executing the Stored Procedure

CALL procedureTest() \G

If using phpMyAdmin, omit the \G at the end.

More Information

For full details on MySQL stored procedures and functions, see MySQL Documentation.