Mysql

MySQL 5.0 Stored Procedures
MySQL 5.0 New Features Series – Part 1

A MySQL® Technical White Paper
Peter Gulutzan
March, 2005

Copyright © 2005, MySQL AB

Table of Contents
Introduction ....................................................................................................3 A Definition and an Example........................................................................3 Why Stored Procedures ................................................................................4 Why MySQL Statements are Legal in a Procedure Body...........................8 Characteristics Clauses ..............................................................................10Parameters....................................................................................................13 The New SQL Statements ...........................................................................15 (Scope ...........................................................................................................16 Loops.............................................................................................................21 Error Handling..............................................................................................29 Cursors .........................................................................................................35 Security .........................................................................................................41 Functions ......................................................................................................43Metadata........................................................................................................44 Details ...........................................................................................................48 Style...............................................................................................................52 Tips when writing longroutines.................................................................63 Bugs ..............................................................................................................64 Feature Requests .........................................................................................65 Resources.....................................................................................................65Conclusion....................................................................................................66 About MySQL ...............................................................................................66

Copyright © 2005, MySQL AB

Page 2

Introduction
This book is for long-time MySQL users who want to know "what's new" in version 5. The short answer is "stored procedures,triggers, views, information_schema". The long answer is the “MySQL 5.0 New Features” series, and this book is the first in the series. What I'm hoping to do is make this look like a hands-on session where you, as if you're working it out yourself on your keyboard, can walk through sample problems. To do this, I'll go through each little item, building up slowly. By the end, I'll be showing largerroutines that do something useful, something that you might have thought was tough.

Conventions and Styles
Whenever I want to show actual code, such as something that comes directly from the screen of my mysql client program, I switch to a Courier font, which looks different from the regular text font. For example: mysql> DROP FUNCTION f; Query OK, 0 rows affected (0.00 sec) When the example islarge and I want to draw attention to a particular line or phrase, I highlight it with a double underline and a small arrow on the right of the page. For example: mysql> CREATE PROCEDURE p () -> BEGIN -> /* This procedure does nothing */ -> END;// Query OK, 0 rows affected (0.00 sec)

” and “->” prompts so that you can cut the examples and paste them into your copy of the mysql client program. (If...