Chapter 8: Stored Procedures and Functions

Creating and using stored procedures

A stored procedure is a collection of SQL statements that is saved in the database and can be called by name. Stored procedures can simplify complex operations and improve database performance. Each database system has its own syntax for them. The examples in this chapter use MySQL’s. Here’s how to create one:

DELIMITER //

CREATE PROCEDURE sp_get_users_by_age (IN p_age INT)
BEGIN
    SELECT * FROM users WHERE age = p_age;
END //

DELIMITER ;

This creates a stored procedure called sp_get_users_by_age that takes an input parameter, p_age. The procedure runs a SELECT query on the users table and returns every row where the age column matches the input parameter.

The parameter is named p_age, not age, on purpose. Inside a procedure, MySQL reads a name that matches both a parameter and a column as the parameter, so WHERE age = age would compare the parameter with itself and return every row.

The DELIMITER lines are for the mysql command-line client. The procedure body contains semicolons, so the client is told to treat // as the end of a statement until the procedure has been created, and then switched back to ;.

To run the stored procedure, call it by name:

CALL sp_get_users_by_age(25);

This returns all users aged 25.

Creating and using user-defined functions

A user-defined function is similar to a stored procedure, but it returns a single value instead of executing a collection of statements. You can use user-defined functions to perform calculations or manipulate data. Here’s how to create one:

DELIMITER //

CREATE FUNCTION get_total_sales_by_user (p_user_id INT)
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE total_sales INT;
    SELECT SUM(sales_amount) INTO total_sales FROM sales WHERE user_id = p_user_id;
    RETURN total_sales;
END //

DELIMITER ;

This creates a user-defined function called get_total_sales_by_user that takes an input parameter, p_user_id. The function runs a SELECT query on the sales table to add up all the sales amounts for that user. It stores the result in a local variable, total_sales, and returns it.

A function’s parameters are always inputs, so unlike a procedure’s they don’t take the IN keyword. READS SQL DATA tells MySQL that the function reads tables but doesn’t change them. MySQL asks for a characteristic like this when binary logging is on, which it is by default.

To use the function, call it in a SELECT statement:

SELECT user_id, get_total_sales_by_user(user_id) as total_sales FROM sales GROUP BY user_id;

This returns a list of user IDs, each with its total sales amount.