MySQL Stored Procedure Programming (2009)

Part I. Stored Programming Fundamentals

This first part of the book introduces the MySQL stored program language and provides a detailed description of the language structure and usage. Chapter 1 asks the fundamental questions: Where did the language come from? What is it good for? What are the main features of the language?Chapter 2 is a tutorial that is designed to get you started with the language as quickly as possible; it shows you how to create basic stored programs of each type and provides interactive examples of major language functions. Chapters 3 through 6 describe the MySQL stored program language in detail: how to work with variables, how to implement conditional and iterative control structures, how SQL can be used within the language, and how errors can be handled.

Chapter 1, Introduction to MySQL Stored Programs

Chapter 2, MySQL Stored Programming Tutorial

Chapter 3, Language Fundamentals

Chapter 4, Blocks, Conditional Statements, and Iterative Programming

Chapter 5, Using SQL in Stored Programming

Chapter 6, Error Handling

Chapter 1. Introduction to MySQL Stored Programs

When MySQL first emerged into the IT world in the mid-1990s, it had few of the characteristics normally associated with commercial relational databases. Features such as transactional support, subqueries, views, and stored procedures were conspicuously absent. Subsequent releases provided most of the missing features, and now—with the introduction of stored procedures, functions, and triggers in MySQL 5 (as well as updateable views and a data dictionary)—the feature gap between MySQL and other relational database systems is narrow indeed.

The introduction of stored programs (our generic term for stored procedures, functions, and triggers) has significance beyond simply winning a features war with competitive database systems. Without stored programs, MySQL cannot claim full compliance with a variety of standards, including ANSI/ISO standards that describe how a DBMS should execute stored programs. Furthermore, judicious use of stored programs can lead to greater database security and integrity and can improve overall application performance and maintainability. We outline these advantages in greater detail later in this chapter.

In short, stored programs—procedures, functions, and triggers—add significantly to the capabilities of MySQL, and a working knowledge of stored programming should be an essential skill for the MySQL professional.

This chapter introduces the MySQL stored program language, its origins, and its capabilities. It also offers a guide to additional resources for MySQL stored program developers and some words of overall development advice.

What Is a Stored Program?

A database stored program—sometimes called a stored module or a stored routine—is a computer program (a series of instructions associated with a name) that is stored within, and executes within, the database server. The source code and (sometimes) any compiled version of the stored program are almost always held within the database server's system tables as well. When the program is executed, it is executed within the memory address of a database server process or thread.

There are three major types of MySQL stored programs:

Stored procedures

Stored procedures are the most common type of stored program. A stored procedure is a generic program unit that is executed on request and that can accept multiple input and output parameters.

Stored functions

Stored functions are similar to stored procedures , but their execution results in the return of a single value. Most importantly, a stored function can be used within a standard SQL statement, allowing the programmer to effectively extend the capabilities of the SQL language.

Triggers

Triggers are stored programs that are activated in response to, or are triggered by, an activity within the database. Typically, a trigger will be invoked in response to a DML operation (INSERT, UPDATE, DELETE) against a database table. Triggers can be used for data validation or for the automation of denormalization.

Tip

Other databases offer additional types of stored programs , including packages and classes, both of which allow you to define or collect multiple procedures and functions within a single, named context. MySQL does not currently support such structures—in MySQL, each stored program is a standalone entity.

Throughout this book, we are going to use the term stored programs to refer to stored procedures, functions, and triggers , and the term stored program language to refer to the language used to write these programs. Most of the facilities in the stored program language are applicable across procedures, functions, and triggers; however, both functions and triggers have strict limitations on the language features that may be used with them. Thus, we dedicate a chapter to each of these program types in which we explain these limitations.

Why Use Stored Programs?

Developers have a multitude of programming languages from which to choose. Many of these are not database languages, which means that the code written in these languages does not reside in, nor is it managed by, a database server. Stored programs offer some very important advantages over more general-purpose languages, including:

§ The use of stored programs can lead to a more secure database.

§ Stored programs offer a mechanism to abstract data access routines, which can improve the maintainability of your code as underlying data structures evolve.

§ Stored programs can reduce network traffic, because the program can work on the data from within the server, rather than having to transfer the data across the network.

§ Stored programs can be used to implement common routines accessible from multiple applications—possibly using otherwise incompatible frameworks—executed either within or from outside the database server.

§ Database-centric logic can be isolated in stored programs and implemented by programmers with more specialized, database experience.

§ The use of stored programs can, under some circumstances, improve the portability of your application.

While this is an impressive list of advantages (many of which will be explored in greater detail in this book), we do not recommend that you immediately move all your application logic into stored programs. In today's rich and complex world of software technology, you need to understand the strengths and weaknesses of each possible element in your software configuration, and figure out how to maximize each element. We spend most of Chapter 12 evaluating how and where to apply MySQL stored programs.

The bottom line is that, used correctly, stored programs—procedures, functions, and triggers—can improve the performance, security, maintainability, and reliability of your applications.

Subsequent chapters will explore how to construct MySQL stored programs and use them to best advantage. Before plunging into the details, however, let's look at how the technology developed and take a quick tour of language capabilities.

A Brief History of MySQL

MySQL has its roots in an in-house (non-SQL) database system called Unireg used by the Swedish company TcX that was first developed in the 1980s and optimized for data warehousing. The author of Unireg, Michael "Monty" Widenius, added a SQL interface to Unireg in 1995, thus creating the first version of MySQL. David Axmark, from Detron HB , approached Monty proposing to release MySQL to the world under a "dual licensing" model that would allow widespread free use, but would still allow for commercial advantage. Together with Allan Larsson, David and Monty became the founders of the MySQL company.

The first widely available version of MySQL was 3.11, which was released in mid-1996. Adoption of MySQL grew rapidly—paralleling the adoption of other related open source technologies. By the year 2005, MySQL could lay claim to over 6 million installations of the MySQL database.

Version 3 of MySQL, while suitable for many types of applications (particularly read-intensive web applications), lacked many of the features normally considered mandatory in a relational database. For instance, transactions, views, and subqueries were not initially supported.

However, the MySQL system was designed to support a particularly extensible data access architecture, in which the SQL layer was decoupled from the underlying data and file access layer. This allowed custom "storage engines" to be employed in place of—or in combination with—the native ISAM (Indexed Sequential Access Method) -based MySQL engine. The Berkeley-DB (BDB ) database (from Sleepycat Software ) was integrated as an optional storage engine in version 3.23.34 in early 2001. BDB provided MySQL with its initial transaction processing capability. At about the same time, the open source InnoDB storage engine became available and quickly became a natively available option for MySQL users.

The 4.0 release in early 2002 fully incorporated the InnoDB option, making transactions easily available for all MySQL users, and also added improved replication capabilities. The 4.1 release in early 2004 built on the 4.0 release and included—among many other improvements—support for subqueries and Unicode character sets.

With the 5.0 release of MySQL in late 2005, MySQL took an important step closer to functional parity with commercial RDBMS systems; it introduced stored procedures , functions, and triggers , the addition of a data dictionary (the SQL-standard INFORMATION_SCHEMA), and support for updateable views.

The 5.1 release, scheduled for the second half of 2006, will add important factilities such as an internal scheduler, table partitioning, row-based replication, and many other significant enhancements.

MySQL Stored Procedures, Functions, and Triggers

MySQL chose to implement its stored program language within the MySQL server as a subset of the ANSI SQL:2003 SQL/PSM (Persistent Stored Module) specification. What a mouthful! Essentially, MySQL stored programs—procedures, functions, and triggers—comply with the only available open standard for these types of programs—the ANSI standard.

Many MySQL and open source aficionados had been hoping for a stored program language implementation based on an open source language such as PHP or Python. Others anticipated a Java?-based implementation. However, by using the ANSI specification—the same specification adopted within IBM's DB2 database—MySQL has taken advantage of years of work done by the ANSI committee, which included representatives from all of the major RDBMS companies.

The MySQL stored program language is a block-structured language (like Pascal) that includes familiar commands for manipulating variables, implementing conditional execution, performing iterative processing, and handling errors. Users of existing stored program languages, such as Oracle's PL/SQL or SQL Server's Transact-SQL, will find features of the language very familiar. Programmers familiar with other languages, such as PHP or Java, might consider the language somewhat simplistic, but they will find that it is easy to learn and that it is well matched to the common requirements of database programming.

A Quick Tour

Let's look at a few quick examples that demonstrate some key elements of both the structure and the functionality of MySQL's stored program language. For a full tutorial, see Chapter 2.

Integration with SQL

One of the most important aspects of MySQL's stored program language is its tight integration with SQL. You don't need to rely on intermediate software "glue," such as ODBC (Open DataBase Connectivity) or JDBC (Java DataBase Connectivity), to construct and execute SQL statements in your stored program language programs. Instead, you simply write the UPDATE, INSERT, DELETE, and SELECT statements directly into your code, as shown in Example 1-1.

Example 1-1. Embedding SQL in a stored program

1 CREATE PROCEDURE example1( )

2 BEGIN

3 DECLARE

l_book_count INTEGER;

5 SELECT COUNT(*)

6 INTO l_book_count

7 FROM books

8 WHERE author LIKE '%HARRISON,GUY%';

10 SELECT CONCAT('Guy has written (or co-written) ',

11 l_book_count ,

12 ' books.');

14 -- Oh, and I changed my name, so...

15 UPDATE books

16 SET author = REPLACE (author, 'GUY', 'GUILLERMO')

17 WHERE author LIKE '%HARRISON,GUY%';

19 END

Let's take a more detailed look at this code in the following table:

Line(s)	Explanation
1	This section, the header of the program, defines the name (example1) and type (PROCEDURE) of our stored program.
2	This BEGIN keyword indicates the beginning of the program body, which contains the declarations and executable code that constitutes the procedure. If the program body contains more than one statement (as in this program), the multiple statements are enclosed in a BEGIN-END block.
3	Here we declare an integer variable to hold the results of a database query that we will subsequently execute.
5-8	We run a query to determine the total number of books that Guy has authored or coauthored. Pay special attention to line 6: the INTO clause that appears within the SELECT serves as the "bridge" from the database to the local stored program language variables.
10-12	We use a simple SELECT statement (e.g., one without a FROM clause) to display the number of books. When we issue a SELECT without an INTO clause, the results are returned directly to the calling program. This is a non-ANSI extension that allows stored programs to easily return result sets (a common scenario when working with SQL Server and other RDBMSs).
14	This single-line comment explains the purpose of the UPDATE.
15-17	Guy has decided to change the spelling of his first name to "Guillermo"— he's probably being stalked by fans of his Oracle book—so we issue an UPDATE against the books table. We take advantage of the built-in REPLACE function to locate all instances of "GUY" and replace them with "GUILLERMO".

Control and Conditional Logic

Of course, real-world applications are full of complex conditions and special cases, so you are unlikely to be able to simply execute a series of SQL statements. The stored program language offers a full range of control and conditional statements so that we can control which lines of our programs actually run under a given set of circumstances. These include:

IF and CASE statements

Both of these statements implement conditional logic with different structures. They allow you to express logic such as "If the page count of a book is greater than 1000, then . . . ".

A full complement of looping and iterative controls

These include the simple loop, the WHILE loop, and the REPEAT UNTIL loop.

Example 1-2, a procedure that pays out the balance of an account to cover outstanding bills, demonstrates some of the control statements of MySQL.

Example 1-2. Stored procedure with control and conditional logic

1 CREATE PROCEDURE pay_out_balance

2 (account_id_in INT)

4 BEGIN

6 DECLARE l_balance_remaining NUMERIC(10,2);

8 payout_loop:LOOP

9 SET l_balance_remaining = account_balance(account_id_in);

11 IF l_balance_remaining < 1000 THEN

12 LEAVE payout_loop;

14 ELSE

15 CALL apply_balance(account_id_in, l_balance_remaining);

16 END IF;

18 END LOOP;

20 END

Let's take a more detailed look at this code in the following table:

Line(s)	Explanation
1-3	This is the header of our procedure; line 2 contains the parameter list of the procedure, which in this case consists of a single incoming value (the identification number of the account).
6	Declare a variable to hold the remaining balance for an account.
8-18	This simple loop (named so because it is started simply with the keyword LOOP, as opposed to WHILE or REPEAT) iterates until the account balance falls below 1000. In MySQL, we can name the loop (line 8, payout_loop), which then allows us to use the LEAVE statement (see line 12) to terminate that particular loop. After leaving a loop, the MySQL engine will then proceed to the next executable statement following the END LOOP; statement (line 18).
9	Call the account_balance function (which must have been previously defined) to retrieve the balance for this account. MySQL allows you to call a stored program from within another stored program, thus facilitating reuse of code. Since this program is a function, it returns a value and can therefore be called from within a MySQL SET assignment.
11-16	This IF statement causes the loop to terminate if the account balance falls below $1,000. Otherwise (the ELSE clause), it applies the balance to the next charge. You can construct much more complex Boolean expressions with ELSEIF clauses, as well.
15	Call the apply_balance procedure. This is an example of code reuse; rather than repeating the logic of apply_balance in this procedure, we call a common routine.

Stored Functions

A stored function is a stored program that returns a single value and that can be used whenever a built-in function can be used—for example, in a SQL statement. Example 1-3 returns the age of a person in years when provided with a date of birth.

Example 1-3. A stored function to calculate age from date of birth

1 CREATE FUNCTION f_age (in_dob datetime) returns int

2 NO SQL

3 BEGIN

4 DECLARE l_age INT;

5 IF DATE_FORMAT(NOW( ),'00-%m-%d') >= DATE_FORMAT(in_dob,'00-%m-%d') THEN

6 -- This person has had a birthday this year

7 SET l_age=DATE_FORMAT(NOW( ),'%Y')-DATE_FORMAT(in_dob,'%Y');

8 ELSE

9 -- Yet to have a birthday this year

10 SET l_age=DATE_FORMAT(NOW( ),'%Y')-DATE_FORMAT(in_dob,'%Y')-1;

11 END IF;

12 RETURN(l_age);

END;

Let's step through this code in the following table:

Lines(s)	Explanation
1	Define the function: its name, input parameters (a single date), and return value (an integer).
2	This function contains no SQL statements. There's some controversy about the use of this clause —see Chapters 3 and 10 for more discussion.
4	Declare a local variable to hold the results of our age calculation.
5-11	This IF-ELSE-END IF block checks to see if the birth date in question has occurred yet this year.
7	If the birth date has, in fact, passed in the current year, we can calculate the age by simply subtracting the year of birth from the current year.
10	Otherwise (i.e., the birth date is yet to occur this year), we need to subtract an additional year from our age calculation.
12	Return the age as calculated to the calling program.

We can use our stored function wherever a built-in function would be permitted—within another stored program, in a SET statement, or, as shown in Example 1-4, within a SQL statement.

Example 1-4. Using a stored function within a SQL statement (continued)

mysql> SELECT firstname,surname, date_of_birth, f_age(date_of_birth) AS age

-> FROM employees LIMIT 5;

+-----------+---------+---------------------+------+

+-----------+---------+---------------------+------+

| LUCAS | FERRIS | 1984-04-17 07:04:27 | 21 |

| STAFFORD | KIPP | 1953-04-22 06:04:50 | 52 |

| GUTHREY | HOLMES | 1974-09-12 08:09:22 | 31 |

| TALIA | KNOX | 1966-08-14 11:08:14 | 39 |

| JOHN | MORALES | 1956-06-22 07:06:14 | 49 |

+-----------+---------+---------------------+------+

When Things Go Wrong

Even if our programs have been thoroughly tested and have no bugs, user input can cause errors to occur in our code. The MySQL stored program language offers a powerful mechanism for handling errors. In Example 1-5, we create a procedure that creates new product codes or—if the product code already exists—updates it with a new name. The procedure detects an attempt to insert a duplicate value by using an exception handler. If the attempt to insert fails, the error is trapped and an UPDATE is issued in place of the INSERT. Without the exception handler, the stored program execution is stopped, and the exception is passed back unhandled to the calling program.

Example 1-5. Error handling in a stored program

1 CREATE PROCEDURE sp_product_code

2 (in_product_code VARCHAR(2),

3 in_product_name VARCHAR(30))

5 BEGIN

7 DECLARE l_dupkey_indicator INT DEFAULT 0;

8 DECLARE duplicate_key CONDITION FOR 1062;

9 DECLARE CONTINUE HANDLER FOR duplicate_key SET l_dupkey_indicator =1;

11 INSERT INTO product_codes (product_code, product_name)

12 VALUES (in_product_code, in_product_name);

14 IF l_dupkey_indicator THEN

15 UPDATE product_codes

16 SET product_name=in_product_name

17 WHERE product_code=in_product_code;

18 END IF;

20 END

Let's take a more detailed look at the error-handling aspects of this code:

Line(s)	Explanation
1-4	This is the header of the stored procedure, accepting two IN parameters: product code and product name.
7	Declare a variable that we will use to detect the occurrence of a duplicate key violation. The variable is initialized with a value of 0 (false); subsequent code will ensure that it gets set to a value of 1 (true) only if a duplicate key violation takes place.
8	Define a named condition, duplicate_key, that is associated with MySQL error 1062. While this step is not strictly necessary, we recommend that you define such conditions to improve the readability of your code (you can now reference the error by name instead of by number).
9	Define an error handler that will trap the duplicate key error and then set the value of the variable l_dupkey_indicator to 1 (true) if a duplicate key violation is encountered anywhere in the subsequent code.
11-12	Insert a new product with the user-provided code and name.
14	Check the value of the l_dupkey_indicator variable. If it is still 0, then the INSERT was successful and we are done. If the value has been changed to 1 (true), we know that there has been a duplicate key violation. We then run the UPDATE statement in lines 15-17 to change the name of the product with the specified code.

Error handling is a critical aspect of writing robust, maintainable MySQL stored programs. Chapter 6 takes you on an extensive tour of the various error-handling mechanisms in MySQL stored programs.

Triggers

A trigger is a stored program that is automatically invoked in response to an event within the database. In the MySQL 5 implementation, triggers are invoked only in response to DML activity on a specific table. The trigger can automatically calculate derived or denormalized values. Example 1-6 shows a trigger that maintains such a derived value; whenever an employee salary is changed, the value of the contrib_401K column is automatically set to an appropriate value.

Example 1-6. Trigger to maintain a derived column value

1 CREATE TRIGGER employees_trg_bu

2 BEFORE UPDATE ON employees

3 FOR EACH ROW

4 BEGIN

5 IF NEW.salary <50000 THEN

6 SET NEW.contrib_401K=500;

7 ELSE

8 SET NEW.contrib_401K=500+(NEW.salary-50000)*.01;

9 END IF;

10 END

The following table explains this fairly simple and short trigger:

Line(s)	Explanation
1	A trigger has a unique name. Typically, you will want to name the trigger so as to reveal its nature. For example, the "bu" in the trigger's name indicates that this is a BEFORE UPDATE trigger.
2	Define the conditions that will cause the trigger to fire. In this case, the trigger code will execute prior to an UPDATE statement on the employees table.
3	FOR EACH ROW indicates that the trigger code will be executed once for each row being affected by the DML statement. This clause is mandatory in the current MySQL 5 trigger implementation.
4-10	This BEGIN-END block defines the code that will run when the trigger is fired.
5-9	Automatically populate the contrib_401K column in the employees table. If the new value for the salary column is less than 50000, the contrib._401K column will be set to 500. Otherwise, the value will be calculated as shown in line 8.

There is, of course, much more that can be said about the MySQL stored program language—which is why you have hundreds more pages of material to study in this book! These initial examples should, however, give you a good feel for the kind of code you will write with the stored program language, some of its most important syntactical elements, and the ease with which you can write—and read—the stored program language code.

Resources for Developers Using Stored Programs

The introduction of stored programs in MySQL 5 is a significant milestone in the evolution of the MySQL language. For any new technology to be absorbed and leveraged fully, users of that technology need lots of support and guidance in how best to utilize it. Our objective is to offer in this book complete and comprehensive coverage of the MySQL stored program language.

We are certain, however, that you will need help in other ways, so in the following sections we describe additional resources that either complement this book (by providing information about other MySQL technologies) or provide community-based support or late-breaking news. In these sections we provide quick summaries of many of these resources. By taking full advantage of these resources, many of which are available either free or at a relatively low cost, you will greatly improve the quality of your MySQL development experience—and your resulting code.

Books

Over the years, the MySQL series from O'Reilly has grown to include quite a long list of books. Here we list some of the books currently available that we feel could be pertinent to the MySQL stored program developer, as well as relevant books from other publishers. Please check out the MySQL area of the O'Reilly OnLAMP web site (http://www.onlamp.com/onlamp/general/mysql.csp) for more complete information.

MySQL Stored Procedure Programming, by Guy Harrison with Steven Feuerstein

This is the book you are holding now (or maybe even viewing online). This book was designed to be a complete and comprehensive guide to the MySQL stored program language. However, this book does not attempt complete coverage of the MySQL server, the SQL language, or other programming languages that you might use with MySQL. Therefore, you might want to complement this book with one or more other topics from the O'Reilly catalog or even—heaven forbid—from another publisher!

MySQL in a Nutshell, by Russell Dyer

This compact quick-reference manual covers the MySQL SQL language, utility programs, and APIs for Perl, PHP, and C. This book is the ideal companion for any MySQL user (O'Reilly).

Web Database Applications with PHP and MySQL, by Hugh Williams and David Lane

This is a comprehensive guide to creating web-based applications using PHP and MySQL. It covers PEAR (PHP Extension and Application Repository) and provides a variety of complete case studies (O'Reilly).

MySQL, by Paul DuBois

This classic reference—now in its third edition—is a comprehensive reference to MySQL development and administration. The third edition includes prerelease coverage of MySQL 5.0, including some information about stored procedures, functions, and triggers (SAMS).

High Performance MySQL, by Jeremy Zawodny and Derek Balling

This book covers the construction of high-performance MySQL server environments, along with how you can tune applications to take advantage of these environments. The book focuses on optimization, benchmarking, backups, replication, indexing, and load balancing (O'Reilly).

MySQL Cookbook, by Paul DuBois

This cookbook provides quick and easily applied recipes for common MySQL problems ranging from program setup to table manipulation and transaction management to data import/export and web interaction (O'Reilly).

Pro MySQL, by Michael Krukenberg and Jay Pipes

This book covers many advanced MySQL topics, including index structure, internal architecture, replication, clustering, and new features in MySQL 5.0. Some coverage of stored procedures, functions, and triggers is included, although much of the discussion is based on early MySQL 5 beta versions (APress).

MySQL Design and Tuning, by Robert D. Schneider

This is a good source of information on advanced development and administration topics, with a focus on performance (MySQL Press).

SQL in a Nutshell, by Kevin Kline, et al.

MySQL stored procedures, functions, and triggers rely on the SQL language to interact with database tables. This is a reference to the SQL language as implemented in Oracle, SQL Server, DB2, and MySQL (O'Reilly).

Learning SQL, by Alan Beaulieu

This book provides an excellent entry point for those unfamiliar with SQL. It covers queries, grouping, sets, filtering, subqueries, joins, indexes, and constraints, along with exercises (O'Reilly).

Internet Resources

There are also some excellent web sites available to MySQL programmers, including some areas devoted to stored programming. You should also make sure to look at the web site for this book (described in the Preface) for updates, errata, and other MySQL information.

MySQL

MySQL AB offers the most comprehensive collection of white papers, documentation, and forums on MySQL in general and MySQL stored programming in particular. Start at http://www.mysql.com. We outline some specific areas later.

MySQL Developer Zone

http://dev.mysql.com/ is the main entry point for MySQL programmers. From here you can easily access software downloads, online forums, white papers, documentation, and the bug-tracking system.

MySQL online documentation

The MySQL reference manual—including sections on stored procedures, functions, and triggers—is available online at http://dev.mysql.com/doc/. You can also download the manual in various formats from here, or you can order various selections in printed book format athttp://dev.mysql.com/books/mysqlpress/index.html.

MySQL forums

MySQL forums are great places to discuss MySQL features with others in the MySQL community. The MySQL developers are also frequent participants in these forums. The general forum index can be found at http://forums.mysql.com/. The stored procedure forum includes discussions of both procedures and functions, and there is a separate forum for triggers.

MySQL blogs

There are many people blogging about MySQL nowadays, and MySQL has consolidated many of the most significant feeds on the Planet MySQL web site at http://www.planetmysql.org/.

MySQL stored routines library

Giuseppe Maxia initiated this routine library, which collects general-purpose MySQL 5 stored procedures and functions. The library is still young, but already there are some extremely useful routines available. For example, you will find routines that emulate arrays, automate repetitive tasks, and perform crosstab manipulations. Check it out at http://savannah.nongnu.org/projects/mysql-sr-lib/.

O'Reilly's OnLAMP MySQL section

O'Reilly hosts the OnLAMP site, which is dedicated to the LAMP stack (Linux, Apache, MySQL, PHP/Perl/Python) of which MySQL is such an important part. OnLAMP includes numerous MySQL articles, which you can find at http://www.onlamp.com/onlamp/general/mysql.csp.

Some Words of Advice for Developers

By definition, everyone is new to the world of MySQL stored program development, because stored programs are themselves new to MySQL. However, Guy and Steven have both had plenty of experience in stored program development within other relational databases. Steven, in particular, has been a key figure in the world of Oracle PL/SQL (Oracle's stored program language) development for more than a decade. We hope that you will find it helpful if we share some advice with you on how you can work more effectively with this powerful MySQL programming language.

Don't Be in Such a Hurry!

We are almost always working under tight deadlines, or playing catch-up from one setback or another. We have no time to waste, and lots of code to write. So let's get right to it—right?

Wrong. If we dive too quickly into the depths of code construction, slavishly converting requirements to hundreds, thousands, or even tens of thousands of lines of code, we will end up with a total mess that is almost impossible to debug and maintain. Don't respond to looming deadlines with panic; you are more likely to meet those deadlines if you do some careful planning.

We strongly encourage you to resist these time pressures and make sure to do the following before you start a new application, or even a specific program in an application:

Construct test cases and test scripts before you write your code

You should determine how you want to verify a successful implementation before you write a single line of a program. By doing this, you are more likely to get the interface of your program correct and be able to thoroughly identify what it is your program needs to do.

Establish clear rules for how developers will write the SQL statements in the application

In general, we recommend that individual developers not write a whole lot of SQL. Instead, those single-row queries and inserts and updates should be "hidden" behind prebuilt and thoroughly tested procedures and functions (this is called data encapsulation). These programs can be optimized, tested, and maintained much more effectively than SQL statements (many of them quite similar) scattered throughout your code.

Establish clear rules for how developers will handle exceptions in the application

If you don't set standards, then everyone will handle errors their own way or not at all, creating software chaos. The best approach to take is to centralize your error-handling logic in a small set of procedures, which hide all the details of how an error log is kept, determine how exceptions are raised and propagated up through nested blocks, and more. Make sure that all developers use these programs and do not write their own complicated, time-consuming, and error-prone error-handling code.

Use "stepwise refinement" (a.k.a. top-down design) to limit the complexity of the requirements you must deal with at any given time

We are usually tasked with implementing very complex requirements. If you try to "do it all" in one big stored program, it will rapidly devolve into spaghetti code that even you will not be able to understand later. Break your big challenges into a sequence of smaller challenges, and then tackle those more manageable problems with reasonably sized programs. If you use this approach, you will find that the executable sections of your modules are shorter and easier to understand, which makes your code easier to maintain and enhance over time.

These are just a few of the important things to keep in mind before you start writing all that code. Just remember: in the world of software development, haste not only makes waste, it virtually guarantees a generous offering of bugs and lost weekends.

Don't Be Afraid to Ask for Help

Chances are, if you are a software professional, you are a smart and well-educated individual. You studied hard, you honed your skills, and now you make a darn good living writing code. You can solve almost any problem you are handed, and that makes you proud.

Unfortunately, your success can also make you egotistical, arrogant, and reluctant to seek out help when you are stumped (we think we are supposed to know all the answers). This dynamic is one of the most dangerous and destructive aspects of software development.

Software is written by human beings; it is important, therefore, to recognize that human psychology plays a key role in software development. The following is an example.

Joe, the senior developer in a team of six, has a problem with his program. He studies it for hours, with increasing frustration, but cannot figure out the source of the bug. He wouldn't think of asking any of his peers to help because they all have less experience than he does. Finally, though, he is at wits' end and gives up. Sighing, he picks up his phone and touches an extension: "Sandra, could you come over here and take a look at my program? I've got a problem I can't figure out." Sandra stops by and, with the quickest glance at Joe's program, points out what should have been obvious to him long ago. Hurray! The program is fixed, and Joe expresses gratitude, but in fact he is secretly embarrassed.

Thoughts like "Why didn't I see that?" and "If I'd only spent another five minutes doing my own debugging I would have found it" run though Joe's mind. This is understandable but misguided. The bottom line is that we are often unable to identify our own problems because we are too close to our own code. Sometimes, all we need is a fresh perspective, the relatively objective view of someone with nothing at stake. It has nothing to do with seniority, expertise, or competence.

Besides, Sandra isn't going to think poorly of Joe. Instead, by asking her for help, Joe has made her feel better about herself, and so both members of the development team benefit.

We strongly suggest that you establish the following guidelines in your organization:

Reward admissions of ignorance

Hiding what you don't know about an application or its code is very dangerous. Develop a culture in which it is OK to say "I don't know" and encourages the asking of lots of questions.

Ask for help

If you cannot figure out the source of a bug in 30 minutes, immediately ask for help. You might even set up a "buddy system," so that everyone is assigned a person who is expected to be asked for assistance. Don't let yourself (or others in your group) go for hours banging your head against the wall in a fruitless search for answers.

Set up a formal peer code review process

Don't let any code go to QA (Quality Assurance) or production without being read and critiqued (in a positive, constructive manner) by other developers in your group.

Take a Creative, Even Radical Approach

We all tend to fall into ruts, in almost every aspect of our lives. Humans are creatures of habit: you learn to write code in one way; you assume certain limitations about a product; you turn aside possible solutions without serious examination because you just know it can't be done. Developers become downright prejudiced about their own programs, and often not in positive ways. They are often overheard saying things like:

§ "It can't run any faster than that; it's a pig."

§ "I can't make it work the way the user wants; that'll have to wait for the next version."

§ "If I were using X or Y or Z product, it would be a breeze. But with this stuff, everything is a struggle."

But the reality is that your program can almost always run a little faster. And the screen can, in fact, function just the way the user wants it to. And although each product has its limitations, strengths, and weaknesses, you should never have to wait for the next version. Isn't it so much more satisfying to be able to tell your therapist that you tackled the problem head-on, accepted no excuses, and crafted a solution?

How do you do this? Break out of the confines of your hardened views and take a fresh look at the world (or maybe just the walls of your cubicle). Reassess the programming habits you've developed. Be creative—step away from the traditional methods, from the often limited and mechanical approaches constantly reinforced in our places of business.

Try something new: experiment with what may seem to be a radical departure from the norm. You will be surprised at how much you will learn and grow as a programmer and problem solver. Over the years, we have surprised ourselves over and over with what is really achievable when we stopped saying "You can't do that!" and instead simply nodded quietly and wondered to ourselves: "Now, if we do it this way, what will happen ...?"

Conclusion

In this chapter, we took you on a whirlwind tour of the MySQL relational database and the new MySQL stored program language. We also provided you with some useful resources and added some general words of advice that we hope you find useful.

In the next chapter, we'll provide a more comprehensive tutorial that will really get you started with MySQL stored procedures, functions, and triggers.