MySQL 5.0 stored procedures tutorial

I hope this book can have a conversation with you like an expert, using simple questions and examples to let you learn what you need to know. In order to achieve this goal, I will slowly build up the concept for you from every detail, and finally I will show you a larger practical example. You may think this use case is difficult before you study it, but as long as you follow the course, I believe you can grasp it quickly.
Conventions   and   Styles   conventions and programming styles
Every time I wanted to demonstrate the actual code, I adjusted the code that appeared on the screen of the mysql client, changing the font to Courier to make it look different from plain text.
Here's an example: mysql>   DROP   FUNCTION   f;Query   OK,   0   rows   affected   (0.00   sec)
If the instance is large, you will need to comment between lines and paragraphs, and I will use "
" mysql >   CREATE   PROCEDURE   p   ()
- >   BEGIN
- >   /*   This   procedure   does   nothing   */     END;//Query   OK,   0   rows   affected   (0.00   sec)
Sometimes I'll use the example "mysql>" "And" - > "Without these systems, you can copy the code directly to the mysql client program (if what you are reading is not electronic, you can download the script from the mysql.com website). All the examples have been tested on the Suse   9.2   Linux, Mysql   5.0.3 public version.
By the time you read this book, Mysql is already available in higher editions and supports more OS, including Windows, Sparc, HP-UX. So this example will work fine on your computer. But if things still go wrong, consult with an experienced Mysql user you know for long-term support and help.
Why   MySQL   Statements   are   Legal   in   a   Procedure   Body
What MySQL statements are valid in the stored procedure body?
What SQL statements are valid in Mysql stored procedures? You can create a statement that contains INSERT,   UPDATE,DELETE,   SELECT,   DROP,   CREATE,   REPLACE, etc. The only thing you need to remember is that if the code contains MySQL extensions, the code will not be portable. In standard SQL statements: any database definition language is legal, such as

CREATE   PROCEDURE   p   ()   DELETE   FROM   t;   //
SET, COMMIT and ROLLBACK are also legal, e.g.
CREATE   PROCEDURE   p   ()   SET   @x   =   5;   //
Additional functionality of MySQL: statements in any data manipulation language will be legal.
CREATE   PROCEDURE   p   ()   DROP   TABLE   t;   //
MySQL extension: direct SELECT is also legal:
CREATE   PROCEDURE   p   ()   SELECT   'a';   //
By the way, the reason I call the ability to include an DDL statement in a stored procedure an MySQL add-on is because this is defined as a non-core, optional component in the SQL standard.
The   New   SQL   Statements   new SQL statement
Variables   variable
The directive that declares a variable in a compound statement is DECLARE.
(1)   Example   with   two   DECLARE   statements
Examples of two DECLARE statements  

CREATE   PROCEDURE   p8   ()
BEGIN
DECLARE   a   INT;
DECLARE   b   INT;
SET   a   =   5;
SET   b   =   5;
INSERT   INTO   t   VALUES   (a);
SELECT   s1   *   a   FROM   t   WHERE   s1   > =   b;
END;   //   /*   I   won't   CALL   this   */
The variables defined in the procedure are not really defined, you just define them in the BEGIN/END block.
Error   Handling   exception handling
Okay, now we're going to talk about exception handling,
  Sample   Problem:   Log   Of   Failures   When INSERT fails, I want to be able to log it in a log file The ordinary. I hope to get the wrong record. When INSERT fails, I want to record the errors
in another file Information, such as the time of the error, the cause of the error, etc. The reason I'm particularly interested in inserting is that it would violate the constraint
on foreign key associations 2.   Sample   Problem:   Log   Of   Failures   (2)
mysql >   CREATE   TABLE   t2
s1   INT,   PRIMARY   KEY   (s1))
engine=innodb;//
mysql >   CREATE   TABLE   t3   (s1   INT,   KEY   (s1),
FOREIGN   KEY   (s1)   REFERENCES   t2   (s1))
engine=innodb;//
mysql >   INSERT   INTO   t3   VALUES   (5);//
...
ERROR   1216   (23000):   Cannot   add   or   update   a   child   row:   a   foreign   key
constraint   fails (system error message shown here)
I'm going to start by creating a primary key table, and a foreign key table. We are using InnoDB, so the foreign key association check is
The open. Then when I insert a value from a non-primary key table into the foreign key table, the action fails. Of course, this can be very
Find the error number 1216.
3.   Sample   Problem:   Log   Of   Failures

CREATE   TABLE   error_log   (error_message
CHAR(80))//
The next step is to create a table that stores errors when an insertion action goes wrong.