Using a stored procedure, we can wrap multiple SQL statements into a single unit that provides the integrity and consistency in which the SQL statements are executed. Assume that there are multiple developers performing the same set of tasks again and again, executing the same set of statements one at a time, in the same or a different order based on the developer's preferences. The process can be consolidated by putting these statements into a stored procedure. This single unit can be thoroughly tested for maintaining data integrity and executing consistency across different scenarios. Another reason why the stored procedures are preferred over a set of multiple SQL statements is the performance boost that the stored procedures provide. To build a stored procedure, we would need two things at a minimum, the first being the name of the stored procedure and the second being the body or the content inside the stored procedure. Let's write a simple...

Stored routines are similar to stored procedures; both of them contain a block of SQL statements. There are a few differences such as a stored routine cannot return a result set and that a stored routine has to return a value and therefore, not preferred over stored procedures. Stored routines are invoked using the SELECT statement and can interchangeably be called as functions. The SHA1 function that we are using is a system-built stored routine to generate hashes for strings. Let's build a simple stored routine that would return the full name of the student when a username is passed in as a parameter, as shown in the following screenshot:

We begin by using the CREATE FUNCTION DDL command, appended by the name of the function, to create the function. This function takes the student's username as a parameter and returns the full name of the student, if the student exists. In...

In the last few sections, we have looked at different scenarios for adding, updating, and deleting data. These are considered to be common operations in an everyday environment. However, what if we would like to watch certain events and use these events to perform another operation? As in, have an audit table that keeps a track of the user on whom an operation was performed or, assuming that there is a limit on the number of students that can register for a course, subtract that number whenever a student registers for the course. In our case, let's take a look at how an audit table can be used to keep track of different operations on a user. MariaDB provides the TRIGGER statement, which is a chain reaction that is set off in response to a SQL DML operation such as INSERT, UPDATE, or DELETE. To track these changes, let's build an audit table that would hold the person who performed the operation, the time when the operation was made, the type of the operation...

In this chapter, we have covered a lot of advanced concepts such as altering tables, working with indexes and column characteristics, working with stored procedures, working with stored routines, and working with triggers. MariaDB offers many more advanced concepts and the concepts discussed in this chapter will lay a good foundation to acquire a better understanding of the more advanced database concepts. Now that we have talked about a few advanced concepts of MariaDB, let's switch gears and dive in to understand a popular programming paradigm called object-oriented programming and understand its implementation in PHP 5.