MySQL Stored Function

MySQL DROP FUNCTION

The DROP FUNCTION statement in MySQL is used to delete a stored function from the database. It permanently removes the function definition, and any subsequent attempts to call the function will result in an error.

Syntax

Create a Stored Function

CREATE FUNCTION function_name ([parameters])
RETURNS data_type
[DETERMINISTIC | NOT DETERMINISTIC]
[CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]
BEGIN
    -- Function body
    RETURN value;
END;

• function_name: Name of the function.
• parameters: Input parameters for the function.
• data_type: The data type of the value returned by the function.
• Determinism:
  • DETERMINISTIC: Function always produces the same result for the same input.
  • NOT DETERMINISTIC: Function may produce different results for the same input.
• SQL Containment:
  • CONTAINS SQL: Contains SQL but does not read or modify data.
  • NO SQL: Does not contain any SQL statements.
  • READS SQL DATA: Reads data but does not modify it.
  • MODIFIES SQL DATA: Reads and modifies data.

Example

1. Basic Function

Create a function to calculate the square of a number:

DELIMITER $$

CREATE FUNCTION calculate_square(num INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN num * num;
END$$

DELIMITER ;

• Usage:

  SELECT calculate_square(5); -- Output: 25
  

2. Function with Multiple Parameters

Create a function to calculate the discount price of a product:

DELIMITER $$

CREATE FUNCTION calculate_discount(price DECIMAL(10, 2), discount_rate DECIMAL(5, 2))
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    RETURN price - (price * discount_rate / 100);
END$$

DELIMITER ;

• Usage:

  SELECT calculate_discount(100, 15); -- Output: 85.00
  

3. Function Using Conditional Logic

Create a function to categorize a number as Positive, Negative, or Zero:

DELIMITER $$

CREATE FUNCTION categorize_number(num INT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    IF num > 0 THEN
        RETURN 'Positive';
    ELSEIF num < 0 THEN
        RETURN 'Negative';
    ELSE
        RETURN 'Zero';
    END IF;
END$$

DELIMITER ;

• Usage:

  SELECT categorize_number(-5); -- Output: Negative
  

Calling a Function

You can call a stored function in SQL statements, such as SELECT, WHERE, or ORDER BY.

SELECT calculate_square(10) AS square;

SELECT * FROM products
WHERE calculate_discount(price, discount_rate) < 50;

Manage Stored Functions

View Functions

List all functions in the current database:

SHOW FUNCTION STATUS WHERE Db = 'database_name';

View Function Details

Get the CREATE FUNCTION statement for a specific function:

SHOW CREATE FUNCTION function_name;

Drop a Function

Delete a stored function when it's no longer needed:

DROP FUNCTION function_name;

Considerations

1. Privileges:

   • You need the CREATE ROUTINE privilege to create functions and ALTER ROUTINE or DROP privilege to modify or delete them.

2. Return Value:

   • A function must include a RETURN statement to specify the value it produces.

3. Deterministic vs. Non-Deterministic:

   • Use DETERMINISTIC for functions with predictable output to optimize performance.

4. Side Effects:

   • Functions are not designed to modify database data. Use stored procedures for tasks that involve data changes.

Use Cases

• Encapsulate Logic:

  • Simplify complex calculations or transformations used frequently in queries.

• Reusable Code:

  • Avoid repetition by defining reusable functions for common tasks.

• Data Transformation:

  • Format or process data directly within SQL queries (e.g., formatting dates or strings).

Example: Reusable Utility Function

Create a function to convert temperature from Celsius to Fahrenheit:

DELIMITER $$

CREATE FUNCTION celsius_to_fahrenheit(celsius DECIMAL(5, 2))
RETURNS DECIMAL(5, 2)
DETERMINISTIC
BEGIN
    RETURN (celsius * 9 / 5) + 32;
END$$

DELIMITER ;

Usage:

SELECT celsius_to_fahrenheit(25); -- Output: 77.00

Conclusion

MySQL stored functions are a powerful way to encapsulate reusable logic and simplify complex SQL queries. With proper planning and implementation, functions can significantly enhance your database's efficiency and maintainability. Let me know if you'd like help with creating or optimizing stored functions!