MySQL - user-defined functions (UDFs)

1. Stored Functions (written in SQL)

These are created inside MySQL using SQL code. They must return a single value.

Syntax:

DELIMITER $$

CREATE FUNCTION function_name (param1 datatype, param2 datatype, ...)
RETURNS return_datatype
DETERMINISTIC
BEGIN
    -- function body
    DECLARE result return_datatype;
    SET result = ...;  -- some calculation
    RETURN result;
END$$

DELIMITER ;

RETURNS → defines the return type.
DETERMINISTIC → means the function always returns the same output for the same input (required by MySQL).
NOT DETERMINISTIC → if the function’s result can vary.

Example 1: Simple function

DELIMITER $$

CREATE FUNCTION add_tax(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * 1.10; -- add 10% tax
END$$

DELIMITER ;

Usage:

SELECT add_tax(100);
-- Output: 110.00

Example 2: Function with multiple parameters

DELIMITER $$

CREATE FUNCTION full_name(first VARCHAR(50), last VARCHAR(50))
RETURNS VARCHAR(120)
DETERMINISTIC
BEGIN
    RETURN CONCAT(first, ' ', last);
END$$

DELIMITER ;

Usage:

SELECT full_name('John', 'Doe');
-- Output: John Doe

2. UDFs in C/C++ (compiled and loaded as plugins)

For very advanced use cases, you can write UDFs in C or C++, compile them into a shared library, and load them into MySQL with:
```
CREATE FUNCTION func_name RETURNS INTEGER SONAME 'library.so';
```
These are much less common and require server-level access.

3. Managing Functions

Show all functions:

SHOW FUNCTION STATUS WHERE Db = 'your_database';

View function definition:
```
SHOW CREATE FUNCTION function_name;
```
Remove a function:
```
DROP FUNCTION function_name;
```

Key Notes

Unlike stored procedures, functions must return a value.
Functions can be used in SELECT statements, while procedures cannot.
Permissions: You need the CREATE ROUTINE privilege to create functions.