Database Reference
In-Depth Information
Or you could create a stored procedure. Stored procedures are simply collec-
tions of one or more MariaDB statements saved for future use. You can think
of them as batch files, although in truth they are more than
that.
Now that you know what stored procedures are, why use them? There are
many reasons, but here are the primary ones:
■
To simplify complex operations (as seen in the previous example) by
encapsulating processes into a single easy-to-use
unit.
■
To ensure data integrity by not requiring that a series of steps be cre-
ated over and over. If all developers and applications use the same
(tried and tested) stored procedure, the same code will be used by all.
An extension of this is to prevent errors. The more steps that need
to be performed, the more likely it is that errors will be introduced.
Preventing errors ensures data consistency.
■
To simplify change management. If tables, column names, or busi-
ness logic (or just about anything) changes, only the stored procedure
code needs to be updated, and no one else needs even to be aware that
changes were made.
An extension of this is security. Restricting access to underlying data
via stored procedures reduces the chance of data corruption (uninten-
tional or otherwise).
■
To improve performance, as stored procedures typically execute
quicker than do individual SQL statements.
■
There are MariaDB language elements and features available only
within single requests. Stored procedures can use these to write code
that is more powerful
and flexible. (We see an example of this in the
next chapter.)
In other words, there are three primary benefits—simplicity, security, and per-
formance. Obviously all are important. Before you run off to turn all your SQL
code into stored procedures, here's the downside:
■
Stored procedures tend to be more complex to write than basic SQL
statements, and writing them requires a greater degree of skill and
experience.
