MySQL5.0 Stored Procedure tutorial

I hope this book will be able to talk to you like an expert, so that you can learn the required knowledge with simple questions and examples. In order to achieve this purpose, I will gradually establish concepts for everyone from every detail, and finally show you a large practical example, before learning, you may think that this case is very difficult, but as long as you follow the course, I believe you will be able to master it soon.
Conventions and Styles Conventions and programming Styles
Every time I want to demonstrate the actual code, I will adjust the code displayed on the screen of the mysql client and change the font to Courier to make them look different from the normal text.
Here is an example: mysql> drop function f; Query OK, 0 rows affected (0.00 sec)
If the instance is large, you need to add comments between some rows and paragraphs.
Mysql> create procedure p ()
-> BEGIN
->/* This procedure does nothing */END; // Query OK, 0 rows affected (0.00 sec)
Sometimes I will remove the "mysql>" and "->" systems in the example. You can copy the Code directly to the mysql client program (if you are not reading an electronic version, you can download related scripts on the mysql.com website. Therefore, all the examples have passed the test on Suse 9.2 Linux and Mysql 5.0.3 public edition.
When you read this book, Mysql has a higher version and supports more operating systems, including Windows, Linux, and HP-UX. So the example here will run normally on your computer. However, if the operation still fails, you can consult a senior 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 during Mysql storage? You can CREATE a statement that contains INSERT, UPDATE, DELETE, SELECT, DROP, CREATE, REPLACE, and so on. The only thing you need to remember is that if the Code contains the MySQL extension function, the Code cannot be transplanted. In standard SQL statements, any database definition language is legal, for example:

Create procedure p () delete from t ;//
SET, COMMIT, and ROLLBACK are also valid, for example:
Create procedure p () SET @ x = 5 ;//
Additional functions of MySQL: Statements in any data operation language are valid.
Create procedure p () drop table t ;//
MySQL expansion function: Direct SELECT is also legal:
Create procedure p () SELECT 'a ';//
By the way, I call the DDL statement feature included in the stored procedure the MySQL additional feature because it is defined as a non-core component in the SQL standard, that is, an optional component.
The New SQL Statements New SQL statement
Variables variable
The command for declaring a variable in a composite statement is DECLARE.
(1) Example with two DECLARE statements
Two DECLARE statements

Create procedure p8 ()
BEGIN
DECLARE a INT;
DECLARE B INT;
SET a = 5;
SET B = 5;
Insert into t VALUES ();
SELECT s1 * a FROM t WHERE s1> = B;
END; // * I won't CALL this */
Variables defined in the process are not really defined. You just defined them in the BEGIN/END block ).
Error Handling Exception Handling
Okay, now we want to talk about exception handling.
1. Sample Problem: Log Of Failures Problem example: Fault Record
When the INSERT operation fails, I want to record it in the log file to demonstrate the error handling examples.
Normal. I want to get an error record. When INSERT fails, I want to remember the errors in another file.
Information, such as the error time and cause. I am particularly interested in insertion because it violates the foreign key Association constraints.
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 (the error message is displayed here)
I started to create a primary key table and a foreign key table. We use InnoDB, so the foreign key Association check is
Open. Then, when I insert a value from a non-primary key table to an external key table, the Operation will fail. Of course, under this condition
Locate error 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 insert error occurs.