MySQL5.0 new feature tutorial stored procedure: Lecture 4 _ MySQL

ErrorHandling has handled the exception. now we want to talk about exception handling. sampleProblem: LogOfFailures problem example: when an INSERT failure occurs in a fault record, I hope to record it in the log file. This example shows the error handling problem. I want to get an error record. When INSERT fails, I want Error Handling exception Handling
　　
Okay, now we want to talk about exception handling.
　　
　　 1. Sample Problem: Log Of Failures Problem example: fault record
　　
When INSERT fails, I want to record it in the log file to demonstrate the error handling example. I want to get an error record. When the INSERT operation fails, I want to record the error information in another file, 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 enabled. Then, when I insert a value from a non-primary key table to an external key table, the operation will fail. Of course, the error code 1216 can be quickly found under such conditions.
　　
　　 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.
　　
　　 4. Sample Problem: Log Of Errors
　　
Create procedure p22 (parameter1 INT)
BEGIN
　　
Declare exit handler for 1216
Insert into error_log VALUES
(CONCAT ('Time: ', current_date,
'. Foreign Key Reference Failure
Value = ', parameter1 ));
Insert into t3 VALUES (parameter1 );
END ;//
　　
The above is our program. The first statement declare exit handler is used to handle exceptions. It means that if error 1215 occurs, this program inserts a row in the error record table. EXIT means to EXIT the compound statement after the action is successfully submitted.
　　
　　 5. Sample Problem: Log Of Errors
　　
CALL p22 (5 )//
　　
Calling this stored procedure will fail, which is normal because the value 5 does not appear in the primary key table. However, no error message is returned because the error processing is included in the process. Nothing is added to table t3, but some information is recorded in table error_log, which tells us that the INSERT into table t3 action fails.
　　
Declare handler syntax statement syntax for exception handling
　　
DECLARE
{EXIT | CONTINUE}
HANDLER
{Error-number | {SQLSTATE error-string} | condition}
SQL statement
　　
The above is the usage of error handling, that is, a piece of code automatically triggered when a program fails. MySQL allows two processors, one of which is EXIT processing, which we just used. The other is what we will demonstrate. the CONTINUE processing is similar to the EXIT processing. The difference is that after it is executed, the original main program continues to run, so this composite statement has no EXIT.
　　
　　 1. declare continue handler example CONTINUE processing example
　　
Create table t4 (s1 int, primary key (s1 ));//
Create procedure p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For sqlstate '200' SET @ x2 = 1;
SET @ x = 1;
Insert into t4 VALUES (1 );
SET @ x = 2;
Insert into t4 VALUES (1 );
SET @ x = 3;
END ;//
　　
This is an example of CONTINUE processing in MySQL Reference Manual. This example is very good, so I copied it here. Through this example, we can see how the CONTINUE processing works.
　　
　　 2. declare continue handler declares CONTINUE exception handling
　　
Create table t4 (s1 int, primary key (s1 ));//
Create procedure p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For sqlstate '200' SET @ x2 = 1; <--
SET @ x = 1;
Insert into t4 VALUES (1 );
SET @ x = 2;
Insert into t4 VALUES (1 );
SET @ x = 3;
END ;//
　　
This time I will define a handler for the SQLSTATE value. Remember the MySQL error code 1216 we used earlier? As a matter of fact, the 23000SQLSTATE is more commonly used. when a foreign key constraint error or a primary key constraint error occurs, it is called.
　　
　　 3. DECLARE CONTINUE HANDLER
　　
Create table t4 (s1 int, primary key (s1 ));//
Create procedure p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For sqlstate '200' SET @ x2 = 1;
SET @ x = 1; <--
Insert into t4 VALUES (1 );
SET @ x = 2;
Insert into t4 VALUES (1 );
SET @ x = 3;
END ;//
　　
The first statement executed in this stored procedure is "SET @ x = 1 ".
　　
　　 4. declare continue handler example
　　
Create table t4 (s1 int, primary key (s1 ));//
Create procedure p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For sqlstate '200' SET @ x2 = 1;
SET @ x = 1;
Insert into t4 VALUES (1 );
SET @ x = 2;
Insert into t4 VALUES (1); <--
SET @ x = 3;
END ;//
　　
Value 1 after running is inserted into the primary key table.
　　
　　 5. DECLARE CONTINUE HANDLER
　　
Create table t4 (s1 int, primary key (s1 ));//
Create procedure p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For sqlstate '200' SET @ x2 = 1;
SET @ x = 1;
Insert into t4 VALUES (1 );
SET @ x = 2; <--
Insert into t4 VALUES (1 );
SET @ x = 3;
END ;//
　　
Then the value of @ x is changed to 2.
　　
　　 6. declare continue handler example
　　
Create table t4 (s1 int, primary key (s1 ));//
Create procedure p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For sqlstate '200' SET @ x2 = 1;
SET @ x = 1;
Insert into t4 VALUES (1 );
SET @ x = 2;
Insert into t4 VALUES (1); <--
SET @ x = 3;
END ;//
　　
Then, the program tries to insert a value into the primary key table again, but fails because the primary key has a unique limit.
　　
　　 7. declare continue handler example
　　
Create table t4 (s1 int, primary key (s1 ));//
Create procedure p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For sqlstate '200' SET @ x2 = 1; <--
SET @ x = 1;
Insert into t4 VALUES (1 );
SET @ x = 2;
Insert into t4 VALUES (1 );
SET @ x = 3;
END ;//
　　
The error processing program is triggered due to insertion failure and error processing starts. The next statement to be executed is the statement for error handling. @ x2 is set to 2.
　　
　　 8. declare continue handler example
　　
Create table t4 (s1 int, primary key (s1 ));//
Create procedure p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For sqlstate '200' SET @ x2 = 1;
SET @ x = 1;
Insert into t4 VALUES (1 );
SET @ x = 2;
Insert into t4 VALUES (1 );
SET @ x = 3; <--
END ;//
　　
This is not the end, because this is a CONTINUE exception handling. Therefore, after executing the failed insert statement, continue to set @ x to 3.
　　
　　 9. declare continue handler example
　　
Mysql> CALL p23 ()//
Query OK, 0 rows affected (0.00 sec)
Mysql> SELECT @ x, @ x2 //
　　
+ ------ +
| @ X | @ x2 |
+ ------ +
| 3 | 1 |
+ ------ +
1 row in set (0.00 sec)
　　
After running the process, we observe the value of @ x. We are sure that it is 3. observe the value of @ x2, which is 1. From this point, we can determine that the program runs correctly, which is based entirely on our ideas. You can take some time to adjust the error processor so that the check can be placed at the beginning of the statement segment, rather than at the place where errors may occur, although it seems like the program is messy and it just jumps. However, such code is safe and clear.
　　
　　 1. DECLARE CONDITION
　　
Create procedure p24 ()
BEGIN
DECLARE 'straint violation'
Condition for sqlstate '200 ';
DECLARE EXIT HANDLER
'Straint violation' ROLLBACK;
Start transaction;
Insert into t2 VALUES (1 );
Insert into t2 VALUES (1 );
COMMIT;
END ;//
　　
This is another example of error handling, which was modified on the previous page. In fact, you can give other names of SQLSTATE or error code, and you can use your own name in processing. The following shows how it is implemented: I define table t2 as an InnoDB table, so the insert operations on this table will be ROLLBACK (ROLLBACK) and ROLLBACK (ROLLBACK Transaction) it also happens. Because inserting two identical values for the primary key will cause the SQLSTATE 23000 error. here, SQLSTATE 23000 is a constraint error.
　　
　　 2. declare condition statement conditions
　　
Create procedure p24 ()
BEGIN
DECLARE 'straint violation'
Condition for sqlstate '200 ';
DECLARE EXIT HANDLER
'Cons