This material is mostly based on the MySQL manual on Stored Procedures and Functions and also on an article in Database Journal.
MySQL distinguishes between functions (which return one value and only take inputs), and procedures (which return no values but can take output parameters). The umbrella term is routine. Functions are more limited than procedures. For example, procedures can use COMMIT and ROLLBACK, but functions can't.
You need "create routine" and "alter routine" privileges to create,
alter or drop these routines. (You also need "execute," but that is
usually automatic to the definer.) As students in 304, you have all
privileges
to your own database, so you can define routines there.
Procedures are associated with a particular database, so
there's an implicit use db at the top of the
routine.
Suppose we wanted to define a function that returns an actor's movies.
Suppose we have a variable called actorid which is the
actor's NM. The SQL query is:
SELECT `tt`,`title`,`release` FROM movie,credit WHERE actorid=credit.nm AND movie.tt=credit.tt
As a routine, it looks like (defined in mysql/routine-filmography.sql):
[an error occurred while processing this directive]
It's used like this (161 is Salma Hayek):
mysql> use scottdb; mysql> call filmography(161); +--------+-------+---------+ | tt | title | release | +--------+-------+---------+ | 120679 | Frida | 2002 | +--------+-------+---------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec) mysql>
Cool, hunh?! Imagine your scripts using routines like that, instead of constructing queries.
Let's start small. Here's simple function definition (defined in mysql/routine-pyth.sql):
[an error occurred while processing this directive]
Here's an example of its use. The first interaction just demonstrates
that this isn't any different from any built-in MySQL function, such as
power.
mysql> use scottdb; mysql> select power(2,3); +------------+ | power(2,3) | +------------+ | 8 | +------------+ 1 row in set (0.00 sec) mysql> select pyth(3,4); +-----------+ | pyth(3,4) | +-----------+ | 5 | +-----------+ 1 row in set (0.00 sec)
No matter how long the SQL statement is in a file that we load, its end is always marked (delimited) by a semi-colon. What if our function has more than one statement in it? The semi-colons will mess everything up.
The solution is to pick a different delimiter for the purpose of loading the file. The delimiter can even be a multi-character string. You can find out what the current delimiter is with the "status" command, which, fortunately, doesn't require a delimiter:
mysql> status -------------- mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (i386) using readline 5.0 Connection id: 3071 Current database: scottdb Current user: scott@sampras.wellesley.edu SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.22 Protocol version: 10 Connection: puma.wellesley.edu via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 TCP port: 3306 Uptime: 13 days 15 hours 51 min 34 sec Threads: 1 Questions: 98968 Slow queries: 0 Opens: 8 Flush tables: 1 Open tables: 64 Queries per second avg: 0.084 --------------
Let's first look at the function: (defined in mysql/routine-pyth-multi.sql):
[an error occurred while processing this directive]
Notice the use of BEGIN and END to enclose
the multiple lines of the procedure. (You might think that MySQL could
then figure out that a different delimiter treatment might be necessary,
but no....)
Here's how we use it. Note the substitution of the dollar-sign at the end of the SOURCE statement, instead of the usual semi-colon.
mysql> use scottdb; mysql> drop function if exists pyth2; Query OK, 0 rows affected (0.00 sec) mysql> delimiter $ mysql> source routine-pyth-multi.sql$ Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> select pyth2(3,4); +------------+ | pyth2(3,4) | +------------+ | 5 | +------------+ 1 row in set (0.00 sec)
Yes, this is bizarre and confusing, but if you just use the incantation above, it works.
It can be useful to have some "scratch" variables in your session. Their names begin with at-signs (@). We'll use these in a minute, but here's an example:
mysql> set @x = power(2,3); Query OK, 0 rows affected (0.00 sec) mysql> select @x; +------+ | @x | +------+ | 8 | +------+ 1 row in set (0.00 sec)
Unlike Java, Perl, and most languages you're familiar with, some programming languages allow subroutines to pass back values in the parameters, instead of just receiving values. C does this by passing in the address of a location. C++ does this by passing in a "reference" parameter. Pascal passes reference parameters by annotating them with VAR. Ada does this, if I recall correctly, by declaring such parameters to be "OUT."
So does SQL. You distinguish this by specifying whether a parameter is input (IN) or output (OUT) or both (INOUT).
Let's start small. Here's simple function definition (defined in mysql/routine-pyth-inout.sql):
[an error occurred while processing this directive]
Here's how we might use it:
mysql> drop procedure if exists pyth3; Query OK, 0 rows affected (0.00 sec) mysql> delimiter $ mysql> source routine-pyth-inout.sql$ Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call pyth3(3,4,@hyp); Query OK, 0 rows affected (0.00 sec) mysql> select @hyp; +------+ | @hyp | +------+ | 5 | +------+ 1 row in set (0.00 sec)
These are a bit cumbersome, because of the need to create variables and make a second query to retrieve the value, but they do have their applications. The MySQL pages give more detail.
Of course, if you have procedures and local variables, you want the full machinery of a programming language. MySQL provides
and more.
Note! The END IF is followed by a semi-colon! Yech.
You can print a value from your procedure by the "SELECT" statement
The example function: (defined in mysql/routine-pyth-print.sql):
[an error occurred while processing this directive]
And here's how it works:
mysql> delimiter $
mysql> source routine-pyth-print.sql$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call pyth4(3,4);
+---------------------+
| concat('a2 is ',a2) |
+---------------------+
| a2 is 9 |
+---------------------+
1 row in set (0.00 sec)
+---------------------+
| concat('b2 is ',b2) |
+---------------------+
| b2 is 16 |
+---------------------+
1 row in set (0.00 sec)
+----------------------------+
| concat('hypotenuse is ',h) |
+----------------------------+
| hypotenuse is 5 |
+----------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
When importing data into the Tanner (Tandora) database, I need to modify some data. Here are some examples:
-- Something went wrong with the calculation of the talk start/end times, so
-- this script changes them back to the start/end time of the session
DROP FUNCTION IF EXISTS sess_start;
CREATE FUNCTION sess_start( sess enum('9:15AM-10:25AM','10:45AM-11:55AM','1:30PM-2:40PM','3:00PM-4:10PM'))
RETURNS time
RETURN str_to_date(substring_index(sess,'-',1),"%h:%i%p");
DROP FUNCTION IF EXISTS sess_end;
CREATE FUNCTION sess_end( sess enum('9:15AM-10:25AM','10:45AM-11:55AM','1:30PM-2:40PM','3:00PM-4:10PM'))
RETURNS time
RETURN str_to_date(substring_index(sess,'-',-1),"%h:%i%p");
update presentation set start=sess_start(session), end=sess_end(session);
-- For creating display_names
DROP FUNCTION IF EXISTS wholename;
CREATE FUNCTION wholename( first_name text, middle_initial text, last_name text)
RETURNS text
RETURN ifnull(concat(first_name,' ',middle_initial,' ',last_name),
concat(first_name,' ',last_name));
-- It is unbelievable that, with all the sophisticated date and time
-- functions in MySQL, I can't come up with anything easier than this.
-- The trouble is that the built-in date_add function requires a full
-- date, so you can't add 10 minutes to 9:15 and get 9:25. You have
-- to add 10 minutes to '2011-10-23 9:15' but then you have to extract
-- just the time part out.
DROP FUNCTION IF EXISTS addminutes;
CREATE FUNCTION addminutes( timeofday text, mins integer)
RETURNS text
RETURN time_format(date_add(concat('2011-11-01 ',timeofday),
interval mins minute),
'%l:%i');
-- Actually, our code has the start and end time as the "time"
-- datatype, not a string, so we can omit the time formatting, which
-- somewhat simplifies this function.
DROP FUNCTION IF EXISTS addminutes;
CREATE FUNCTION addminutes( timeofday time, mins integer)
RETURNS time
RETURN sec_to_time(time_to_sec(timeofday)+mins*60);
drop table if exists start_time;
create table start_time(pid integer,gid integer, st time, et time)
select P.pid as pid,
gid,
addminutes(str_to_date(substring_index(P.session,'-',1),"%h:%i%p"),talk_len*(P.pid-gmin)) as st,
-- I'd rather just add talk_len to st, but I can't refer to that column
addminutes(str_to_date(substring_index(P.session,'-',1),"%h:%i%p"),talk_len*(P.pid-gmin+1)) as et
from presentation P
inner join presentation_admin PA using (tid)
inner join group_info G on (PA.group_id=gid);
select pid from start_time where st is null or et is null;
update presentation P inner join start_time using (pid)
set start = st, end = et;
Here's how I made my life a bit easier harder in
granting each of you the permissions to do this:
The first example routines (warning these fail) are showuser (defined in mysql/routine-showuser.sql) and proc_definer (defined in mysql/routine-proc_definer.sql):
[an error occurred while processing this directive] [an error occurred while processing this directive]
Okay, that failed, due (I think) to a mistake in the language model. What does work is to use something lower level, granting permission by modifying the underlying tables of permissions.
The example routines are: (defined in mysql/routine-showuser2.sql) and defined in mysql/routine-proc_definer2.sql):
[an error occurred while processing this directive] [an error occurred while processing this directive]
To see what routines are installed:
mysql> show procedure status; mysql> show function status;
(FYI, the real information is in the table mysql.proc.)
Write a function that returns the number of actors in a movie, given the movie TT.
Write a procedure that transfers an amount of money from one account to another, assuming the "accounts" table as show below, and given the "from" account, the "to" account, and the "amount." Ideally, it should check to see that there is enough money to transfer.
[an error occurred while processing this directive]
Try:
mysql> select title,castsize(tt) from wmdb.movie; mysql> select * from accounts; mysql> transfer(1,2,1000); mysql> select * from accounts; mysql> transfer(3,4,10); mysql> select * from accounts;
This material is mostly based on the MySQL manual on Triggers
Motivation: When certain changes are made to the database (inserts, updates, or deletes) you may want a stored procedure to be automatically run. These are called triggers, since they are executions triggered by certain events.
Example: Let's set up a table of movie "ratings" (how much people liked the movie). We're going to store the rating (1-5 stars) and the UID of the rater. Notice that it's hard to enforce that the rating is 1-5 stars. (Here's the setup code, from mysql/ratings-table.sql).
[an error occurred while processing this directive]
More capable DBMSes support "CHECK" clauses, which might look like (from mysql/ratings-table-check.sql).
[an error occurred while processing this directive]
Here's a cruel trick (from mysql/ratings-table-enum.sql).
mysql> drop table if exists ratings;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE ratings(
-> uid int,
-> tt int references wmdb.movie(tt),
-> rating enum('*','**','***','****','*****'),
-> primary key (uid,tt)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into ratings values
-> (1,120737,5), -- LotR:FotR
-> (1,167260,5), -- LotR:TT
-> (1,167261,5), -- LotR:RotK
-> (1,360717,4); -- King Kong
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from ratings;
+-----+--------+--------+
| uid | tt | rating |
+-----+--------+--------+
| 1 | 120737 | ***** |
| 1 | 167260 | ***** |
| 1 | 167261 | ***** |
| 1 | 360717 | **** |
+-----+--------+--------+
4 rows in set (0.00 sec)
mysql> -- this should not succeed, but it does, except that it's converted to 0 or the empty string
mysql> insert into ratings values (2,360717,7);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from ratings;
+-----+--------+--------+
| uid | tt | rating |
+-----+--------+--------+
| 1 | 120737 | ***** |
| 1 | 167260 | ***** |
| 1 | 167261 | ***** |
| 1 | 360717 | **** |
| 2 | 360717 | |
+-----+--------+--------+
5 rows in set (0.00 sec)
mysql> select * from ratings where rating=0;
+-----+--------+--------+
| uid | tt | rating |
+-----+--------+--------+
| 2 | 360717 | |
+-----+--------+--------+
1 row in set (0.00 sec)
mysql> -- can we use the enum as if it were numerical, including computing
mysql> -- averages and such?
mysql> select * from ratings where rating < 5;
+-----+--------+--------+
| uid | tt | rating |
+-----+--------+--------+
| 1 | 360717 | **** |
| 2 | 360717 | |
+-----+--------+--------+
2 rows in set (0.00 sec)
mysql> select avg(rating) from ratings;
+-------------+
| avg(rating) |
+-------------+
| 3.8 |
+-------------+
1 row in set (0.00 sec)
mysql> select rating,count(*) from ratings group by rating;
+--------+----------+
| rating | count(*) |
+--------+----------+
| | 1 |
| **** | 1 |
| ***** | 3 |
+--------+----------+
3 rows in set (0.00 sec)
Another approach is to use TRIGGERS. What we will do is say that anything above 5 counts as 5 and anything below 1 counts as 1.
Let's first define that as a function, which we'll call
valid_rating:
From mysql/routine-valid_rating.sql).
[an error occurred while processing this directive]
mysql> select valid_rating(3),valid_rating(0), valid_rating(7); +-----------------+-----------------+-----------------+ | valid_rating(3) | valid_rating(0) | valid_rating(7) | +-----------------+-----------------+-----------------+ | 3 | 1 | 5 | +-----------------+-----------------+-----------------+ 1 row in set (0.00 sec)
Okay, now we're ready to use a TRIGGER. This trigger will be executed whenever a record is inserted or updated. We can define it to be either BEFORE or AFTER, but let's define it to run BEFORE because we're going to define something else after.
Also, MySQL requires us to use a BEFORE trigger if we want to assign to the NEW row.
Note that MySQL requires you to have "SUPER" privilege to create a trigger,
From mysql/trigger-rating.sql).
[an error occurred while processing this directive]
Suppose that we want to store the average rating in the Movie table (or another table), so that we don't have to keep re-calculating it. Also, the ratings table will be much longer, since each movie might be rated hundreds of times. Assume searches are much more common than ratings, so it makes sense to update the other table.
From mysql/movies-plus-ratings.sql).
[an error occurred while processing this directive][an error occurred while processing this directive]