MySQL stored programs: functions2012-04-30 23:16:37 0 comments
Probably you are using functions such as min(), max(), count(), sum() on a regular basis. Functions written as stored programs works the in the same way (from a developer's point of view). Most important features of functions are:
they can't modify parameters,
they return a single value, not a result set,
they can be called as a part of SQL statements.
Functions consists of:
declaration, which is build from:
list of parameters,
function body, which is build from:
Before we go further please make sure you have the sakila sample database and a SQL query editor. If you don't have the database, you can download it from here. As the editor I recommend Toad for MySQL. It's free, very comfortable and have a lot of features. Now lest look at the sakila database. If you are connected to your MySQL server you should see the Object Explorer just below the Connection Manager.
Click on the functions tab and locate the inventory_in_stock function, click on it, and the click the second image from left (letter f with blue triangle) - alter the selected function. If everything went fine, you should see function code on your screen. Now I'll try comment every line of this code.
DROP FUNCTION IF EXISTS sakila.get_customer_balance;
If you're altering any stored program you have to first delete it and than create it. There is no ALTER statement that you may know from T-SQL. If you are creating function for a first time, changing its name or you have deleted it manually, you don't have to use drop statement. Thanks to IF EXISTS part even in situations that I've listed above it won't cause any errors. Long story short this line is universal template for creating and altering stored programs, so memorize it and use it.
CREATE FUNCTION sakila.`get_customer_balance`(p_customer_id INT, p_effective_date DATETIME) RETURNS decimal(5,2)
It's a very important part so let's split it into pieces:
CREATE FUNCTION sakila.`get_customer_balance` - this part is necessary. Every function have to be created and must have a name.
(p_customer_id INT, p_effective_date DATETIME) - after a function name there must be a pair of brackets. If the function don't have any parameters just leave it empty (). In this example we see that function takes as parameters two separated by a comma values: p_customer_id and p_effective_date. First one must be an integer type, second must be a type of datetime.
RETURNS decimal(5,2) - in last part we see type declaration of value returned by the function.
READS SQL DATA
This line means that some data will be retrieved from database, but nothing will be changed. Other possible values in this place are: CONTAINS SQL (function have SQL statements that don't read or modify data), NO SQL (no SQL statements at all), MODIFIES SQL DATA (data will be modified).
If your function always returns the same value for the same parameters that means it is deterministic, otherwise it is nondeterministic. Though wrong declaration can't cause an error it may mislead SQL optimizer.
BEGIN . . . END;
Every block of code must start with BEGIN statement and end with END;, so do a body of function.
#OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID
Sample comment. As you can see below it can also start after SQL statement.
DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY DECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALS DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY
Three declarations of values with its types.
SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees 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 v_overfees 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 v_payments FROM payment WHERE payment.payment_date <= p_effective_date AND payment.customer_id = p_customer_id;
Three statements retrieving data. Each of statements use parameters values p_customer_id and p_effective_data and each returning query result into a previously declared value. Beside executing statements you can use loops, conditional statements and assignments.
RETURN v_rentfees + v_overfees - v_payments;
Finally function is returning of result of addition v_rentfees and v_overfrees and subtracting v_peyments.
You can call this function using simple select query:
SELECT get_customer_balance(16, '2012-00-00 00:00:00');
First function behind you. Of course it's only a part of stored procedures complexity. Hope you've learned something and I'll have time soon to write next parts about procedures, triggers, error handling.
If you're looking a comprehensive guide strictly about SQL/PSM I recommend reading MySQL Stored Procedure Programming by Guy Harrison and Steven Feuerstein. As far as I know it's only book in this field. It was published in 2006 but and I'm not sure if latest releases of MySQL didn't added more features, but still it's a good introduction (with more that 600 pages).