Chapter 11. Stored Procedures

A stored procedure is something that is both stored (that is, it's a database object) and procedural (that is, it can contain constructs like IF, WHILE, BEGIN/END). Like a procedure in any language, an SQL stored procedure can accept parameters, declare and set variables , and return scalar values. A stored procedure may contain one or more SQL statements, including SELECT, so it can return result sets too. Listing 11-1 shows an example of a stored procedure declaration, in SQL Standard syntax.

Portability

MySQL does not support stored procedures. All gains shown in this chapter are for only seven DBMSs. Also, because stored procedures were around long before the SQL Standard got around to requiring them, every DBMS uses slightly different syntax to define stored procedures. A discussion of the differences is beyond the scope of this book.

Listing 11-1 Stored procedure declaration, SQL standard syntax

 CREATE PROCEDURE       /* always CREATE PROCEDURE or FUNCTION */   Sp_proc1             /* typically names begin with Sp_ */ (param1 INT)           /* parenthesized parameter list */ MODIFIES SQL DATA      /* SQL data access characteristic */ BEGIN   DECLARE num1 INT;    /* variable declaration */   IF param1 <> 0 THEN  /* IF statement */      SET param1 = 1;   /* assignment statement */   END IF;              /* terminates IF block */   UPDATE Table1 SET    /* ordinary SQL statement */     column1 = param1; END