Over the last days, i’ve dealing with the challenge of develop some database functions whose need to process some million rows to analyze the whole dataset. As a basic tool, a store procedure may save you several time and a lot of effort.
What is a stored procedure?
A stored procedure is a procedure (like a subprogram in a regular computing language) that is stored (in the database). Correctly speaking, MySQL supports “routines” and there are two kinds of routines: stored procedures which you call, or functions whose return values you use in other SQL statements the same way that you use pre-installed MySQL functions like pi() or now(). I’ll use the word “stored procedures” more frequently than “routines” because it’s what we’ve used in the past, and what people expect us to use.
How is a stored procedure composed?
A stored procedure has a name, a parameter list, and an SQL statement, which can contain many more SQL statements. There is new syntax for local variables, error handling, loop control, and IF conditions. Here is an example of a statement that creates a stored procedure.
1 2 3 4 5 6 7 8 9 10 11
CREATE PROCEDURE procedure1 /* name */ (IN parameter1 INTEGER) /* parameters */ BEGIN /* start of block */ DECLARE variable1 CHAR(10); /* variables */ IF parameter1 = 20 THEN /* start of IF */ SET variable1 = 'birds'; /* assignment */ ELSE SET variable1 = 'beasts'; /* assignment */ END IF; /* end of IF */ INSERT INTO table1 VALUES (variable1);/* statement */ END /* end of block */
In order to give away some examples, we need to create a test database and a basic table to work with, then populate with some arbitrary data.
1 2 3 4 5 6
> CREATE DATABASE testdb; > USE testdb; > CREATE TABLE test_table(v1 INT, v2 FLOAT); > INSERT INTO test_table VALUES(1,4.0); > INSERT INTO test_table VALUES(2,5.2); > INSERT INTO test_table VALUES(3,6.7);
Picking a delimiter
The delimiter is the character or string of characters that you’ll use to tell the mysql client that you’ve finished typing in an SQL statement. For ages, the delimiter has always been a semicolon. That causes a problem because, in a stored procedure, one can have many statements, and every one must end with a semicolon.
Along this post, i will use
Thus, a pretty basic procedure may look like this:
CREATE PROCEDURE p1 () SELECT * FROM test_table; //
and to execute it we use:
A bit more complex procedure may receive a number as parameter and we can return such number plus the first column, and the same number times the second column, like this:
1 2 3
DELIMITER // CREATE PROCEDURE p2 (IN NUMBER INT) SELECT v1,v2,NUMBER,v1+NUMBER, v2 * NUMBER FROM test_table; //
Once you start comprehending these statements, you can start creating your own procedures to automatize daily tasks such as selecting specific client’s data on a table, printing sums, averages, and many more. In the next post i will talk about CURSORS and LOOPS, whose greatly increase some other tasks which need on memory data between rows.