Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Stored Functions

Explore how to define and use stored functions within MySQL stored routines. Understand creating local variables, loading query results into variables, and implementing aggregate functions with COALESCE and SUM. Gain skills to write reusable functions for complex queries and optimize data retrieval for advanced interview problems.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
-- Create the stored function
DELIMITER $$
CREATE FUNCTION fn_customer_total_delivered(p_customer_id INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_total DECIMAL(10,2);
    SELECT COALESCE(SUM(o.TotalAmount), 0.00)
    INTO v_total
    FROM Orders AS o
    WHERE o.CustomerID = p_customer_id
      AND o.DeliveryStatus = 'Delivered';
    RETURN v_total;
END$$
DELIMITER ;
-- Use the function for VIP customers
SELECT   c.CustomerID,
         c.CustomerName,
         fn_customer_total_delivered(c.CustomerID) AS TotalDeliveredAmount
FROM     Customers AS c
WHERE    c.CustomerTier = 'VIP'
ORDER BY TotalDeliveredAmount DESC;

Explanation

The explanation of the solution code is given below:

  • Line 2: The DELIMITER $$ command switches the statement terminator from the default semicolon to $$ so that the function body can contain semicolons without prematurely ending the CREATE FUNCTION statement.

  • Lines 4–7: The CREATE FUNCTION fn_customer_total_delivered(p_customer_id INT) definition creates a stored function that accepts a single input parameter p_customer_id of type INT. The RETURNS DECIMAL(10,2) clause specifies that the function returns a numeric value with two decimal places. The DETERMINISTIC attribute documents that the function always produces the same result for the same p_customer_id, and READS SQL DATA states that the function only reads from tables instead of modifying them.

  • Line 9: The DECLARE v_total DECIMAL(10,2); statement defines a local variable v_total inside the function. This variable will hold the aggregated delivered total for the given p_customer_id before it is returned.

  • Lines 11–15: The SELECT COALESCE(SUM(o.TotalAmount), 0.00) query aggregates the TotalAmount from the Orders table for rows where ...