MariaDB Crash Course - Ben Forta [69]
* * *
Tip: Use Views for Retrieval
As a rule, use views for data retrieval (SELECT statements) and not for updates (INSERT, UPDATE, and DELETE).
* * *
Summary
Views are virtual tables. They do not contain data, but they contain queries that retrieve data as needed, instead. Views provide a level of encapsulation around MariaDB SELECT statements and can be used to simplify data manipulation, as well as to reformat or secure underlying data.
23. Working with Stored Procedures
In this chapter, you learn what stored procedures are, why they are used, and how they are used. You also look at the basic syntax for creating and using them.
Understanding Stored Procedures
Most of the SQL statements that we’ve used thus far are simple in that they use a single statement against one or more tables. Not all operations are that simple—often, multiple statements are needed to perform a complete operation. For example, consider the following scenario:
• To process an order, checks must be made to ensure that items are in stock.
• If items are in stock, they need to be reserved so they are not sold to anyone else, and the available quantity must be reduced to reflect the correct amount in stock.
• Any items not in stock need to be ordered; this requires some interaction with the vendor.
• The customer needs to be notified as to which items are in stock (and can be shipped immediately) and which are back ordered.
This is obviously not a complete example, and it is even beyond the scope of the example tables that we have been using in this book, but it will suffice to help make a point. Performing this process requires many MariaDB statements against many tables. In addition, the exact statements that need to be performed and their order are not fixed; they can (and will) vary according to which items are in stock and which are not.
How would you write this code? You could write each of the statements individually and execute other statements conditionally, based on the result. You’d have to do this every time this processing was needed (and in every application that needed it).
Or you could create a stored procedure. Stored procedures are simply collections 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.
Why Use Stored Procedures
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 created 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 business 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 (unintentional 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 performance. 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