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.