MySQL Functions: User-defined Functions

Previous Page
Next Page

A function is a piece of code that we can store and use repeatedly. MySQL provides a long list of functions that help us do certain tasks. However, we may require making our own functions. These are called stored functions.

To create a stored function we use the CREATE FUNCTION statement. The Basic Syntax is as follows,

DELIMITER $$
CREATE FUNCTION <function_name>(
    <parameters>
)
RETURNS <datatype>
[NOT] DETERMINISTIC
BEGIN
 -- statements
END $$
DELIMITER ;

A stored function can return any type of datatype.

It can either be deterministic i.e. return the same result every time or not deterministic i.e. may return different results every time.

By default, MySQL chooses the option NOT DETERMINISTIC.

An example of a stored function is,

DELIMITER $$;
CREATE FUNCTION get_balance
     ( customer_id INT, effective_date DATETIME)
     RETURNS decimal(5,2)
     DETERMINISTIC
 BEGIN
#Some variables
   DECLARE rent_fees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY
   DECLARE overdue_fees INTEGER;      #LATE FEES FOR PRIOR RENTALS
   DECLARE payment_tot DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY


   SELECT IFNULL(SUM(film.rental_rate),0) INTO rent_fees
     FROM film, inventory, rental
     WHERE film.film_id = inventory.film_id
       AND inventory.inventory_id = rental.inventory_id
       AND rental.rental_date <= p_effective_date
       AND rental.customer_id = p_customer_id;


   SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
         ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO overdue_fees
     FROM rental, inventory, film
     WHERE film.film_id = inventory.film_id
       AND inventory.inventory_id = rental.inventory_id
       AND rental.rental_date <= p_effective_date
       AND rental.customer_id = p_customer_id;

   SELECT IFNULL(SUM(payment.amount),0) INTO payment_tot
     FROM payment
     WHERE payment.payment_date <= p_effective_date
     AND payment.customer_id = p_customer_id;

   RETURN rent_fees + overdue_fees - payment_tot;
 END

How to run a stored function in MySQL?

To run a stored function we would do:

SELECT <function_name>(<parameters>);

For example, to run the above function we would do:

select get_balance(100,'2004-01-01 09:01:11');

How to drop a function in MySQL?

To drop a stored function, we use the Drop Function statement.

DROP FUNCTION <function_name>;

Example:

DROP FUNCTION get_balance;

Check Script in MySQL

To check the script and other details of a Function, we use the show create Function statement.

show create function <function-name>;

Example:

show create function get_balance;

How to show all functions in MySQL?

To list all the functions available in MySQL we have two methods. Both yield more or less the same results.

SHOW FUNCTION STATUS
where db='sakila';

We can also do,

select * from information_Schema.routines
where routine_type= 'FUNCTION'
and routine_Schema = 'sakila';

The information_Schema table of course gives much more information.

Previous Page
Next Page