8. SQL Programming

Overview

In this chapter, we'll see how we can build programs and software that reliably automate database operations, thus returning huge savings of time, effort, and money.

By the end of this chapter, you'll be able to build and execute stored procedures that automate database operations. You will be able to build and execute functions that encapsulate repeated statements in defined, structured units. Moreover, you will be equipped to build and execute triggers that automatically execute database operations when predefined events occur.

Introduction

In the previous chapter, we explored SQL subqueries, CASE statements, and views. These features offer great flexibility when we work with database resources. To use these features, and all the other SQL product query features we have seen so far, we type a SQL command into the development environment, run it, and look for the results somewhere within that environment. This approach certainly works well enough, and we relied on it as we learned about MySQL. However, it won't work for applications that rely on SQL database products as data resources. We need a product feature that can somehow automate the queries we want to run and reliably handle all the required management and overhead. Fortunately, modern SQL products, including MySQL, offer the following features to solve this problem:

• Stored procedures
• Functions
• Triggers

These features involve actual programming, and in this chapter, we'll explore the basic programming concepts. We&apos...

Programming for SQL Products – The Basics

MySQL programming, like all programming and software development, relies on a small set of core ideas. Variables are an important part of that idea set, and we'll see how they work as we begin to work with MySQL.

When we build programs in MySQL or in any other software development language, we use variables as buckets to hold information, or values, that the programs need. A variable has a name, and in most software products—including MySQL—it has an information type. In a structured, predictable way, the program can change the value of a variable based on the behavior of the program, information that comes into the program from outside of the program, or both. A variable will reliably hold the last value assigned to it. Before we look at SQL stored procedures and functions, we'll see how variables operate in MySQL.

Launch MySQL and place the following statements in the editor or the query window:

SET...

Functions

The complexity of the software we build expands as we build it. In part, this becomes unavoidable because the mass of the software itself also expands. However, we can definitely avoid the complexity of repeating blocks of code. Modern software development products give us a way to place one copy of a repeating block of code in one defined location. Then, in the software, we can replace all repeated instances of that code block with a call to that one defined copy of the code. We call this one defined block of software a function. Think of a function as a box that takes in zero or more values and returns one or more values in a structured way. A function simplifies software, and it makes the software we build much easier to maintain, repair, and enhance. If a program has the same identical code block repeated one hundred times, the same required change to all of those blocks would require the same work and testing—one hundred times. If we replace those blocks with...

Summary

In this chapter, we saw that we can build powerful, structured software with SQL stored procedures, SQL functions, and SQL triggers. These tools integrate core programming concepts with SQL machinery to build reliable, flexible solutions to complex programming problems. With these tools, we can leverage the power and potential of SQL database products. In the next chapter, we will cover the best practices for securing database resources from common potential threats.