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().
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.
CREATE PROCEDURE procedure1 /* name */
(IN parameter1 INTEGER) /* parameters */
BEGIN /* start of block */
DECLARE variable1 CHAR(10); /* variables */
IF parameter1 = 17 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 */
Stored procedures are something new for MySQL, so naturally you'll approach them with some caution. Stored procedures are fast! What we can say is that the MySQL server takes some advantage of caching, just as prepared statements do. There is no compilation, so an SQL stored procedure won't work as quickly as a procedure written with an external language such as C. The main speed gain comes from reduction of network traffic. If you have a repetitive task that requires checking, looping, multiple statements, and no user interaction, do it with a single call to a procedure that's stored on the server.
Stored procedures are components! Suppose that you change your host language -- no
problem, the logic is in the database not the application.
Stored procedures are portable! When you write your stored procedure in SQL, you know that it will run on every platform that MySQL runs on, without obliging you to install an additional runtime-environment package, or set permissions for program execution in the operating system, or deploy different packages if you have different computer types. That's the advantage of writing in SQL rather than in an external language like Java or C or PHP.
Stored procedures are stored! If you write a procedure with the right naming
conventions, for example saying chequing_withdrawal for a bank transaction, then people who want to know about chequing can find your procedure. It's always available as 'source code' in the database itself. And it makes sense to link the data with the processes that operate on the data, as you might have heard in your programming-theory classes.
Stored procedures are migratory! MySQL adheres fairly closely to the SQL:2003
standard. Others (DB2, Mimer) also adhere. Others (Oracle, SQL Server) don't adhere
but I'll be providing tips and tools that make it easier to take code written for another DBMS and plunking it into MySQL.
CREATE PROCEDURE p1 () SELECT * FROM t;
CREATE PROCEDURE p2 ()
SELECT CURRENT_DATE, RAND() FROM t
Call Procedure
Now, to call a procedure, all you have to do is enter the word CALL and then the name of the procedure and then the parentheses. Again, the parentheses are compulsory.
CALL p1()
