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).