Creating Stored Functions in MySQL

Course: MySQL Training Course

Topic: Creating Stored Functions in MySQL

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Stored Functions in MySQL

A stored function in MySQL is a named database routine that accepts input parameters, performs calculations or other supported SQL operations, and returns a single value. The function definition is stored in the database server and can be reused in SQL statements wherever an appropriate expression is allowed.

Stored functions are useful when the same calculation or business rule needs to be applied repeatedly. Instead of writing the same formula in multiple queries, developers can create one function and call it whenever required.

For example, an online shopping application may need to calculate discounts, taxes, product prices after discounts, employee bonuses, or customer reward points. These calculations can be placed inside stored functions and reused across different queries.

MySQL provides built-in functions such as UPPER(), LOWER(), ROUND(), and CONCAT(). A stored function allows developers to create their own reusable functions for application-specific requirements.

Example of a Repeated Calculation

Suppose a company calculates a 10 percent discount on a product price. The calculation can be written directly in a query:

SELECT product_price * 10 / 100 AS discount_amount
FROM products;

If the same calculation is needed in multiple queries, a stored function can centralize the formula and make it easier to maintain.

2. Learning Objectives

After completing this topic, learners should be able to:

• Define stored functions in MySQL.
• Understand why stored functions are used.
• Differentiate stored functions from stored procedures.
• Understand the syntax of the CREATE FUNCTION statement.
• Declare input parameters and return data types.
• Use the RETURN statement to return a value.
• Create functions using calculations, conditions, and local variables.
• Call stored functions in SELECT statements and other suitable expressions.
• Inspect and remove stored functions.
• Understand deterministic characteristics and data-access declarations.
• Identify common errors when creating stored functions.
• Apply stored functions to practical database scenarios.

3. What Is a Stored Function?

A stored function is a stored routine created using the CREATE FUNCTION statement. It contains a function name, optional input parameters, a required return data type, and a body that calculates or retrieves a value.

Unlike a stored procedure, a stored function must declare the type of value it returns. Its body must contain a RETURN statement that returns a value compatible with the declared type.

Key Characteristics

• Reusable: The same function can be called from multiple SQL statements.
• Returns a value: A stored function returns one scalar value per invocation.
• Accepts input parameters: Parameters supply values used in calculations or queries.
• Stored on the database server: Its definition is maintained as a database object.
• Supports procedural logic: The function body can use supported constructs such as local variables and conditional statements.
• Can be used in expressions: A function can be called in suitable SELECT, WHERE, and other expression contexts, subject to MySQL restrictions.
• Subject to restrictions: Stored functions have rules concerning result sets, transaction statements, and other operations.

4. Stored Function vs Built-In Function

MySQL includes many built-in functions, but developers can also create stored functions to implement application-specific calculations.

5. Basic Syntax of CREATE FUNCTION

The CREATE FUNCTION statement defines a new stored function in MySQL.

General Syntax

CREATE FUNCTION function_name(
    parameter_name data_type
)
RETURNS return_data_type
[characteristics]
BEGIN
    -- Declare variables if required
    -- Perform calculations or supported SQL operations
    RETURN value;
END;

The square brackets around [characteristics] indicate optional syntax elements. They should not be typed literally in the SQL statement.

Explanation of the Syntax

For a function with a single RETURN expression, a compound BEGIN...END block may not be necessary. However, compound blocks are useful when the function needs local variables, conditions, or multiple statements.

6. Understanding the RETURNS Clause

The RETURNS clause is mandatory in a stored function. It specifies the data type of the value the function returns.

Examples of Return Data Types

RETURNS INT
RETURNS DECIMAL(10,2)
RETURNS VARCHAR(100)
RETURNS DATE
RETURNS DATETIME

The chosen data type should match the nature of the result.

Choose the return type carefully. MySQL may convert a returned value to the declared type, and invalid conversions or overflow can produce warnings or errors depending on the SQL mode.

7. Creating Your First Stored Function

Consider a function that accepts a person's name and returns a greeting. This is a simple example for learning the basic syntax.

Step 1: Create the Function

CREATE FUNCTION SayHello(p_name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
NO SQL
RETURN CONCAT('Hello, ', p_name, '!');

Step 2: Call the Function

SELECT SayHello('Aarav') AS greeting;

Expected Result

greeting
----------------
Hello, Aarav!

Explanation

• SayHello is the function name.
• p_name is an input parameter.
• VARCHAR(50) defines the parameter's data type and length.
• RETURNS VARCHAR(100) specifies the type of value returned.
• CONCAT() joins the greeting text with the supplied name.
• RETURN sends the calculated string back to the caller.
• DETERMINISTIC declares that the same input produces the same result.
• NO SQL declares that the function contains no SQL statements.

Because this function contains no internal semicolon-delimited statements, the DELIMITER command is unnecessary in a client that can submit the full definition as one statement.

8. Using DELIMITER When Creating Functions

When a function contains a compound statement block with several semicolon-terminated statements, the MySQL command-line client commonly uses the DELIMITER command to change the statement terminator temporarily.

Example

DELIMITER //

CREATE FUNCTION SquareNumber(p_number INT)
RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
    RETURN p_number * p_number;
END //

DELIMITER ;

In this example, the client uses // to mark the end of the complete CREATE FUNCTION statement. The semicolon inside the function body remains available to terminate the RETURN statement.

Important: DELIMITER is a command handled by clients such as the MySQL command-line client. It is not a SQL statement that the server stores as part of the function. When using an application driver or a graphical database tool, follow that tool's method for submitting stored function definitions.

9. Creating a Function for Mathematical Calculations

Stored functions are commonly used to perform mathematical operations. They can reduce repeated formulas and help keep business calculations consistent.

Example: Calculating the Square of a Number

DELIMITER //

CREATE FUNCTION CalculateSquare(p_number INT)
RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
    DECLARE v_result INT;

    SET v_result = p_number * p_number;

    RETURN v_result;
END //

DELIMITER ;

Calling the Function

SELECT CalculateSquare(5) AS square_result;

Expected Result

square_result
-------------
25

Explanation

• The function accepts an integer parameter named p_number.
• A local variable named v_result stores the calculated result.
• The SET statement assigns the square of the input number.
• The RETURN statement sends the result back to the caller.

For larger integer values, consider whether the multiplication could exceed the range of INT. Choose a suitable numeric type when the input range requires it.

10. Creating a Function to Calculate a Discount

Discount calculations are common in e-commerce and retail applications. A stored function can implement a consistent discount formula.

Example

DELIMITER //

CREATE FUNCTION CalculateDiscount(
    p_price DECIMAL(10,2),
    p_discount_rate DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE v_discount DECIMAL(10,2);

    SET v_discount =
        p_price * p_discount_rate / 100;

    RETURN v_discount;
END //

DELIMITER ;

Call the Function

SELECT CalculateDiscount(2000.00, 15.00)
AS discount_amount;

Expected Result

discount_amount
---------------
300.00

The function calculates 15 percent of 2000.00, resulting in a discount amount of 300.00.

Example: Calculating the Final Price

DELIMITER //

CREATE FUNCTION CalculateFinalPrice(
    p_price DECIMAL(10,2),
    p_discount_rate DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE v_discount DECIMAL(10,2);

    SET v_discount =
        p_price * p_discount_rate / 100;

    RETURN p_price - v_discount;
END //

DELIMITER ;

Call the function:

SELECT CalculateFinalPrice(2000.00, 15.00)
AS final_price;

Expected result:

final_price
-----------
1700.00

These examples demonstrate the formula. A production discount function should also define valid price and discount-rate ranges and handle negative values according to the application's business rules.

11. Creating a Function with Conditional Logic

A stored function can use conditional statements to return different values depending on the supplied input.

IF Statement Syntax

IF condition THEN
    statements;
ELSEIF another_condition THEN
    statements;
ELSE
    statements;
END IF;

Example: Assigning a Grade

DELIMITER //

CREATE FUNCTION GetGrade(p_marks INT)
RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL
BEGIN
    IF p_marks >= 90 AND p_marks <= 100 THEN
        RETURN 'A';
    ELSEIF p_marks >= 75 AND p_marks < 90 THEN
        RETURN 'B';
    ELSEIF p_marks >= 50 AND p_marks < 75 THEN
        RETURN 'C';
    ELSEIF p_marks >= 0 AND p_marks < 50 THEN
        RETURN 'Needs Improvement';
    ELSE
        RETURN 'Invalid Marks';
    END IF;
END //

DELIMITER ;

Calling the Function

SELECT
    GetGrade(95) AS grade_1,
    GetGrade(82) AS grade_2,
    GetGrade(60) AS grade_3,
    GetGrade(35) AS grade_4,
    GetGrade(120) AS grade_5;

Expected Result

This example illustrates how a function can validate a value and return a category. The conditions are arranged so each valid score falls into exactly one category.

12. Creating a Function with Local Variables

A local variable is a temporary variable declared within a stored function. It stores intermediate values used during execution.

Syntax

DECLARE variable_name data_type DEFAULT default_value;

In a compound block, local variables must be declared before executable statements in that block.

Example: Calculating Annual Salary

DELIMITER //

CREATE FUNCTION CalculateAnnualSalary(
    p_monthly_salary DECIMAL(10,2)
)
RETURNS DECIMAL(12,2)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE v_annual_salary DECIMAL(12,2);

    SET v_annual_salary = p_monthly_salary * 12;

    RETURN v_annual_salary;
END //

DELIMITER ;

Execute the function:

SELECT CalculateAnnualSalary(45000.00)
AS annual_salary;

Expected result:

annual_salary
-------------
540000.00

The local variable stores the calculated annual salary before the function returns it.

13. Creating a Function That Uses a Table

A stored function can read database data and return a calculated value. For example, a function can retrieve an employee's salary and return an annual salary amount.

Unlike a procedure, a stored function cannot return a result set directly. When reading data into a local variable, use a supported technique such as SELECT ... INTO and return the scalar result.

Step 1: Create a Sample Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    monthly_salary DECIMAL(10,2) NOT NULL
);

Step 2: Insert Sample Records

INSERT INTO employees
    (employee_id, employee_name, monthly_salary)
VALUES
    (101, 'Aarav Sharma', 45000.00),
    (102, 'Diya Patel', 40000.00),
    (103, 'Rohan Mehta', 50000.00);

Step 3: Create the Function

DELIMITER //

CREATE FUNCTION GetAnnualSalary(
    p_employee_id INT
)
RETURNS DECIMAL(12,2)
READS SQL DATA
BEGIN
    DECLARE v_monthly_salary DECIMAL(10,2);

    SELECT monthly_salary
    INTO v_monthly_salary
    FROM employees
    WHERE employee_id = p_employee_id;

    RETURN v_monthly_salary * 12;
END //

DELIMITER ;

Step 4: Call the Function

SELECT GetAnnualSalary(101) AS annual_salary;

Expected result for employee 101:

annual_salary
-------------
540000.00

The READS SQL DATA characteristic indicates that the function reads data. This declaration describes the routine's intended data access; it is not a replacement for appropriate security controls.

Important Considerations

• The employee ID should identify a matching record.
• If the SELECT ... INTO statement finds no row, MySQL raises a NOT FOUND condition.
• If the query returns more than one row, MySQL raises an error.
• The function should have a defined policy for missing employee records.
• Use appropriate numeric precision for salary and annual salary values.

14. Handling Missing Records in Stored Functions

A function that reads from a table should account for situations where the requested record does not exist. One approach is to use a NOT FOUND handler and return NULL when no employee record is found.

Example

DELIMITER //

CREATE FUNCTION SafeGetAnnualSalary(
    p_employee_id INT
)
RETURNS DECIMAL(12,2)
READS SQL DATA
BEGIN
    DECLARE v_monthly_salary DECIMAL(10,2) DEFAULT NULL;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_monthly_salary = NULL;

    SELECT monthly_salary
    INTO v_monthly_salary
    FROM employees
    WHERE employee_id = p_employee_id;

    IF v_monthly_salary IS NULL THEN
        RETURN NULL;
    END IF;

    RETURN v_monthly_salary * 12;
END //

DELIMITER ;

Test the function with an existing employee and a missing employee:

SELECT SafeGetAnnualSalary(101) AS existing_employee_salary;

SELECT SafeGetAnnualSalary(999) AS missing_employee_salary;

The first call returns the calculated annual salary. The second returns NULL if employee 999 does not exist.

Note: Returning NULL is one possible design choice. In some applications, a missing record should be reported through a separate validation step or an error condition. Choose a behavior that clearly communicates the result to the caller.

15. Calling Stored Functions in SQL Statements

A stored function is called by writing its name with the required arguments. It does not use the CALL statement used for stored procedures.

15.1 Calling a Function in SELECT

SELECT CalculateSquare(8) AS result;

15.2 Using a Function in a Calculation

SELECT CalculateDiscount(1500.00, 10.00)
       + CalculateDiscount(800.00, 5.00)
       AS total_discount;

15.3 Using a Function in a WHERE Clause

A function can be used in a WHERE expression when its return type and behavior are appropriate.

SELECT employee_id, employee_name, monthly_salary
FROM employees
WHERE GetAnnualSalary(employee_id) > 500000;

This query calls the function for rows being evaluated. If the function queries the same table or performs substantial work, this approach can add overhead. Consider whether the calculation can be expressed directly in the query for better performance.

15.4 Using a Function in ORDER BY

SELECT employee_id, employee_name, monthly_salary
FROM employees
ORDER BY GetAnnualSalary(employee_id) DESC;

This sorts employees by the annual salary calculated by the function. As with WHERE, repeated function evaluation can affect query performance.

16. Stored Functions vs Stored Procedures

Stored functions and stored procedures are both stored routines, but they have different invocation and return behaviors.

17. Understanding DETERMINISTIC and NOT DETERMINISTIC

MySQL allows stored functions to declare whether they are deterministic. This characteristic describes whether the function always returns the same result for the same input parameters.

DETERMINISTIC

A function declared DETERMINISTIC is expected to return the same result for the same inputs, assuming its logic does not depend on changing external or database state.

CREATE FUNCTION AddNumbers(
    p_first INT,
    p_second INT
)
RETURNS INT
DETERMINISTIC
NO SQL
RETURN p_first + p_second;

For the same pair of input values, this function returns the same sum.

NOT DETERMINISTIC

A function should be declared NOT DETERMINISTIC when its result may vary for the same explicit inputs, such as when it uses current time or random values.

CREATE FUNCTION GetCurrentTimestamp()
RETURNS DATETIME
NOT DETERMINISTIC
NO SQL
RETURN NOW();

The function returns the current date and time, so its result can change between invocations.

Important: MySQL relies on the function creator's declaration and does not fully verify that a function is truly deterministic. Choose the characteristic honestly because it can affect acceptance of routine definitions, binary logging considerations, and optimizer behavior.

18. Understanding SQL Data-Access Characteristics

Stored functions can include characteristics that describe how they interact with SQL data. These characteristics communicate the intended behavior of the routine.

These declarations are advisory characteristics in MySQL. They do not override the restrictions that apply to stored functions. In particular, do not assume that declaring MODIFIES SQL DATA makes all data-modifying statements permissible inside a function.

19. Restrictions on Stored Functions

Stored functions are subject to restrictions because they may be invoked as part of larger SQL statements. Understanding these restrictions helps prevent creation errors and unexpected behavior.

• No direct result sets: A stored function cannot return a result set using an ordinary SELECT statement without INTO.
• Return a scalar value: The function must return a value compatible with its declared return type.
• No OUT or INOUT parameters: Function parameters are input parameters.
• No explicit transaction control: A stored function cannot contain statements that perform explicit or implicit commit or rollback.
• Restrictions on modifying data: Some data-modification patterns are prohibited or restricted, including changes to a table already being used by the statement that invoked the function.
• No USE statement: A stored routine cannot change the selected database using USE inside its body.
• No recursive stored functions: MySQL stored functions cannot call themselves recursively.
• Routine access rules apply: Creation and execution depend on suitable privileges and security settings.

Check the documentation for the MySQL version in use before designing a function that reads or modifies data. A stored procedure may be a better choice when the task requires result sets, transaction control, or a multi-step workflow.

20. Creating and Managing Stored Functions

MySQL provides statements for creating, inspecting, and removing stored functions.

20.1 Create a Function

CREATE FUNCTION AddNumbers(
    p_a INT,
    p_b INT
)
RETURNS INT
DETERMINISTIC
NO SQL
RETURN p_a + p_b;

20.2 View the Function Definition

SHOW CREATE FUNCTION AddNumbers;

This displays the stored definition and characteristics of the function, subject to the current account's privileges.

20.3 List Stored Functions

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'
  AND ROUTINE_SCHEMA = DATABASE();

This query lists functions associated with the current database that are visible through the metadata.

20.4 Remove a Function

DROP FUNCTION AddNumbers;

To avoid an error when the function does not exist, use:

DROP FUNCTION IF EXISTS AddNumbers;

Dropping a function removes its definition. Check dependent queries, routines, and application code before removing a function used elsewhere.

21. Replacing or Updating a Stored Function

When the logic of an existing stored function needs to change, the function definition must be updated using a supported MySQL workflow.

MySQL provides ALTER FUNCTION for certain function characteristics, but it does not provide a general ALTER FUNCTION syntax for replacing the routine body. A common workflow is to drop and recreate the function, taking care to preserve the definition and account for dependencies.

Example Workflow

DROP FUNCTION IF EXISTS CalculateSquare;

DELIMITER //

CREATE FUNCTION CalculateSquare(p_number INT)
RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
    RETURN p_number * p_number;
END //

DELIMITER ;

Before replacing a function in a production database:

• Save the existing definition.
• Identify queries and routines that use the function.
• Test the replacement in a development environment.
• Confirm that the return type and function name remain compatible with callers.
• Plan deployment and rollback steps.
• Verify the recreated function using SHOW CREATE FUNCTION.

22. Common Errors When Creating Stored Functions

23. Best Practices for Creating Stored Functions

• Use descriptive function names such as CalculateDiscount and GetAnnualSalary.
• Give each function one clear responsibility.
• Choose an appropriate return data type and numeric precision.
• Use input parameters instead of hard-coded values.
• Declare local variables with meaningful names when intermediate values are needed.
• Use deterministic declarations only when the function truly has deterministic behavior.
• Declare SQL data-access characteristics accurately.
• Validate inputs such as rates, quantities, prices, and identifiers.
• Handle missing records and unexpected conditions deliberately.
• Avoid unnecessary database queries inside functions called for many rows.
• Review query execution plans when functions are used in WHERE or ORDER BY clauses.
• Prefer straightforward SQL expressions when they are clearer or more efficient than calling a function repeatedly.
• Do not use a stored function when a stored procedure better fits the task.
• Use least-privilege access and review DEFINER and SQL SECURITY settings.
• Store function definitions in version control and deploy them through repeatable scripts.
• Test normal values, boundary values, invalid inputs, and missing records.

24. Mini Project: Product Pricing Calculator

Project objective: Create a stored function that calculates the final selling price of a product after applying a discount.

Project Requirements

1. Create a products table with product ID, product name, and product price.
2. Insert at least five sample products.
3. Create a stored function that accepts a price and a discount rate.
4. Calculate the discount amount.
5. Return the final price after discount.
6. Call the function for several products or example values.
7. Validate discount rates and handle invalid inputs according to defined business rules.
8. Document the function's parameters, return type, and expected results.

Step 1: Create the Products Table

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    product_price DECIMAL(10,2) NOT NULL
);

Step 2: Insert Sample Products

INSERT INTO products
    (product_id, product_name, product_price)
VALUES
    (1, 'Keyboard', 1500.00),
    (2, 'Mouse', 800.00),
    (3, 'Monitor', 12000.00),
    (4, 'Headphones', 2500.00),
    (5, 'Webcam', 3500.00);

Step 3: Create the Function

DELIMITER //

CREATE FUNCTION ProductFinalPrice(
    p_price DECIMAL(10,2),
    p_discount_rate DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGIN
    IF p_price < 0
       OR p_discount_rate < 0
       OR p_discount_rate > 100 THEN
        RETURN NULL;
    END IF;

    RETURN p_price -
        (p_price * p_discount_rate / 100);
END //

DELIMITER ;

This learning example returns NULL for negative prices or discount rates outside the range of 0 to 100. A production application may prefer to reject invalid values through a separate validation step or use an appropriate error-handling design.

Step 4: Calculate Final Prices

SELECT
    product_name,
    product_price,
    ProductFinalPrice(product_price, 10.00)
        AS final_price
FROM products;

Expected Result

The query uses the same function for each product row, demonstrating how a stored function can provide a consistent calculation across a table.

25. Practice Exercises

1. Create a stored function that adds two integers and returns their sum.
2. Create a function that calculates the square of a number.
3. Create a function that converts a monthly salary into an annual salary.
4. Create a function that calculates a discount amount from a price and percentage.
5. Create a function that returns a product's final price after discount.
6. Create a function that returns a grade based on marks.
7. Create a function that accepts a name and returns a greeting.
8. Create a function that retrieves an employee's salary using an employee ID.
9. Modify a function to handle a missing employee record.
10. Explain the purpose of the RETURNS clause.
11. Explain why a stored function cannot use an OUT parameter.
12. Use SHOW CREATE FUNCTION to inspect a stored function definition.
13. Use INFORMATION_SCHEMA.ROUTINES to list functions in the current database.
14. Explain the difference between DETERMINISTIC and NOT DETERMINISTIC.
15. Describe two situations in which a stored procedure is more appropriate than a stored function.

26. Interview Questions and Answers

Q1. What is a stored function in MySQL?

A stored function is a named database routine that accepts input parameters, performs supported operations, and returns a scalar value using RETURN.

Q2. Which statement is used to create a stored function?

The CREATE FUNCTION statement is used to define a stored function.

Q3. What is the purpose of the RETURNS clause?

The RETURNS clause declares the data type of the value the function returns.

Q4. What is the purpose of the RETURN statement?

RETURN supplies the value returned by the function to the calling expression.

Q5. How do you call a stored function?

Call the function by name within a suitable SQL expression, for example, SELECT CalculateSquare(5);.

Q6. What is the difference between a stored function and a stored procedure?

A stored function returns a scalar value and can be used in suitable expressions. A stored procedure is invoked using CALL and can return result sets or values through OUT and INOUT parameters.

Q7. Can a stored function have OUT parameters?

No. MySQL stored function parameters are input parameters. OUT and INOUT modes are supported for stored procedures.

Q8. What does DETERMINISTIC mean?

It declares that a function returns the same result for the same input parameters, assuming its behavior does not depend on changing state or other varying factors.

Q9. What does READS SQL DATA mean?

It declares that the function reads SQL data without modifying it.

Q10. Can a stored function return multiple rows?

No. A stored function returns a scalar value rather than a result set. A stored procedure is appropriate when a query must return rows directly.

Q11. Why is DELIMITER used?

In clients that support it, DELIMITER changes the command terminator so semicolons inside a compound function definition do not prematurely terminate the outer CREATE statement.

Q12. How do you view a stored function's definition?

Use SHOW CREATE FUNCTION function_name; with a valid function name and appropriate privileges.

Q13. How do you remove a stored function?

Use DROP FUNCTION function_name; or DROP FUNCTION IF EXISTS function_name;.

Q14. Can a stored function contain an IF statement?

Yes. A stored function can use supported conditional logic, including IF...THEN...ELSE, within a compound statement block.

Q15. Can a stored function use local variables?

Yes. Local variables can be declared with DECLARE inside an appropriate compound statement block.

Q16. What happens if SELECT ... INTO returns no rows?

MySQL raises a NOT FOUND condition. The function should handle the condition if missing records are expected.

Q17. Can a stored function modify database data?

MySQL imposes restrictions on data-modifying statements inside stored functions. The function must comply with those restrictions, and a stored procedure may be more appropriate for write-oriented operations.

Q18. Why can a stored function affect query performance?

A function may be evaluated repeatedly when used for many rows. If it performs additional queries or complex calculations, execution can become more expensive.

Q19. What is the purpose of a local variable?

A local variable stores an intermediate value during function execution.

Q20. What are the best practices for stored functions?

Use clear names, appropriate return types, accurate characteristics, input validation, suitable error handling, efficient queries, least-privilege security, and repeatable deployment scripts.

27. Quick Revision Notes

• A stored function is created using CREATE FUNCTION.
• A stored function must declare a RETURNS data type.
• The function body must return a compatible value using RETURN.
• Function parameters are input parameters.
• Stored functions are invoked by name in suitable expressions, not using CALL.
• BEGIN and END group multiple statements in a compound block.
• DECLARE creates local variables and handlers where permitted.
• IF statements support conditional logic.
• DETERMINISTIC describes functions expected to return the same result for the same inputs.
• READS SQL DATA describes a function that reads data.
• A stored function cannot return a result set directly.
• Stored functions cannot contain explicit or implicit transaction commit or rollback statements.
• SHOW CREATE FUNCTION displays the function definition.
• DROP FUNCTION removes a stored function.
• Input validation, testing, security, and performance reviews are important for reliable functions.

28. Important MySQL Commands

29. Conclusion

Creating stored functions is an important MySQL skill for building reusable calculations and database logic. A stored function accepts input parameters, processes values using supported SQL and procedural statements, and returns a scalar result through RETURN.

Stored functions can be used for discount calculations, salary computations, data transformations, grading logic, and other repeated operations. Their effective use requires an appropriate return type, accurate characteristics, careful input validation, clear handling of missing records, and awareness of MySQL's stored-function restrictions.

By practicing CREATE FUNCTION, RETURN, local variables, conditional statements, and function calls in SQL expressions, learners can build a strong foundation for developing maintainable MySQL database applications.

For further learning, visit the JustAcademy MySQL Training Course or register for a course demo.

30. Additional Learning Resources

• MySQL Official Documentation: CREATE PROCEDURE and CREATE FUNCTION
• MySQL Official Documentation: Stored Routine Syntax
• MySQL Official Documentation: Stored Programs and Views
• JustAcademy MySQL Training Course
• JustAcademy Course Demo Registration