MySQL Stored Procedure Programming

Chapter 5. Using SQL in Stored Programming

While we can use the MySQL stored program language to perform traditional programming tasks, in reality almost all stored programs will engage in an interaction with the database through the execution of SQL statements. This chapter focuses on how you can use SQL within your stored programs.

In this chapter we'll look at the various ways in which you can use SQL inside of stored programs:

• Simple (non-SELECT) SQL statements that do not return a result set can be freely embedded within stored procedures and functions.

• A SELECT statement that returns only a single row can pass its result INTO local variables.

• A SELECT statement that returns multiple rows can form the basis for a cursor that allows you to loop through each row, taking whatever action you deem appropriate for that row.

• Any SELECT statement can be included in a stored procedure (but not in a stored function) "unbound" by an INTO clause or a CURSOR statement. The result set from such a SQL statement will be returned to the calling program (but not, alas, to a calling stored procedure).

• SQL statements can be prepared dynamically using MySQL server-side prepared statements (in stored procedures only).

Using Non-SELECT SQL in Stored Programs

When we include a SQL statement that does not return a result set—such as an UPDATE, INSERT, or SET statement—within a stored program, it will execute exactly as it would if it were executed in some other context (such as if it were called from PHP ...