MySQL 5.0 new features tutorial stored procedures: Fourth lecture

mysql| Stored Procedures | tutorials

Author: MySQL AB; translation: Chen Bong

Error Handling Exception Handling

All right, now we're going to talk about exception handling.

1. Sample Problem:log of failures Problem example: Fault logging

When an insert fails, I want to be able to record it in a log file. The sample we used to show the error handling is
The ordinary. I want to get the wrong record. When the insert fails, I want to write down the error in another file.
Information, such as the time of the error, the cause of the error. The reason I'm particularly interested in inserting is that it will violate the Foreign KEY association constraint

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 system error message is shown here)

I started to create a primary key table and a foreign key table. We are using InnoDB, so the foreign Key Association check is to hit
It's open. Then when I insert a value from a Non-key table to the Foreign key table, the action will fail. Of course, under such conditions can be very
Find the error number 1216 quickly.

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 you make an error in the Insert action.

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 for
Value = ', parameter1)];
INSERT into T3 VALUES (parameter1);
end;//

The above is our program. The first statement here declare EXIT handler is used to handle exceptions. This means that if error 1215 occurs, the program will insert a row in the Error record table. Exit means to exit the compound statement when 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 5 value does not appear in the primary key table. But no error message.
Returns because error handling is already contained in the procedure. Nothing is added to the T3 table, but the Error_log table records
With some information, this tells us that the INSERT into table T3 action failed.

Syntax for DECLARE HANDLER syntax declaring exception handling

DECLARE
{EXIT | CONTINUE}
HANDLER for
{Error-number | {SQLSTATE error-string} | Condition}
SQL statement

The above is the usage of error handling, which is a piece of code that automatically triggers when an error occurs. MySQL allows two kinds of processors, one is exit processing, we have just used this. The other is what we're going to be demonstrating, continue processing, which is similar to exit processing, except that when it is executed, the original program continues to run and the compound statement is not exported.

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 ' 23000 ' 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 the continue processing in the MySQL reference manual, which is very good, so I copy it here.
From this example we can see how continue processing works.

2. DECLARE CONTINUE Handler declaration CONTINUE exception handling

CREATE TABLE t4 (S1 int,primary key (S1));
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For SQLSTATE ' 23000 ' 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. Do you remember the MySQL error code 1216 we used earlier?
In fact, the 23000SQLSTATE is more commonly used here, when a FOREIGN KEY constraint error or a PRIMARY KEY constraint error is invoked.

3. DECLARE CONTINUE HANDLER

CREATE TABLE t4 (S1 int,primary key (S1));
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For SQLSTATE ' 23000 ' 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 executed statement of 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 ' 23000 ' SET @x2 = 1;
SET @x = 1;
INSERT into T4 VALUES (1);
SET @x = 2;
INSERT into T4 VALUES (1); <--
SET @x = 3;
end;//

The Run value 1 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 ' 23000 ' 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 @x value becomes 2.

6. DECLARE CONTINUE HANDLER Example

CREATE TABLE t4 (S1 int,primary key (S1));
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For SQLSTATE ' 23000 ' SET @x2 = 1;
SET @x = 1;
INSERT into T4 VALUES (1);
SET @x = 2;
INSERT into T4 VALUES (1); <--
SET @x = 3;
end;//

The program then tries to insert a numeric value into the primary key table again, but fails because the primary key has a uniqueness limit.

7. DECLARE CONTINUE HANDLER Example

CREATE TABLE t4 (S1 int,primary key (S1));
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
For SQLSTATE ' 23000 ' SET @x2 = 1; <--
SET @x = 1;
INSERT into T4 VALUES (1);
SET @x = 2;
INSERT into T4 VALUES (1);
SET @x = 3;
end;//

Because the insertion failed, the error handler is triggered and error handling begins. The next executed statement is an error-handling statement, @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 ' 23000 ' SET @x2 = 1;
SET @x = 1;
INSERT into T4 VALUES (1);
SET @x = 2;
INSERT into T4 VALUES (1);
SET @x = 3; <--
end;//

It's not over here, because this is continue exception handling. So after the execution returns to the failed INSERT statement, proceed to set the @x to 3 action.

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 the operation, we observe the value of @x, and we can know that it is 3 and observe the value of @x2, 1. From here you can judge that the program is running correctly, exactly according to our thinking. You can take the time to adjust the error processor, so that the check is placed in the header of the statement section, not in the wrong place, although it seems that the program is very disordered, jump to the feeling of jumping. But the code is safe and clear.

1. DECLARE CONDITION

CREATE PROCEDURE P24 ()
BEGIN
DECLARE ' Constraint violation '
CONDITION for SQLSTATE ' 23000 ';
DECLARE EXIT HANDLER for
' Constraint violation ' ROLLBACK;
START TRANSACTION;
INSERT into T2 VALUES (1);
INSERT into T2 VALUES (1);
COMMIT;
End; //

This is another example of error handling, modified on the basis of the previous one. In fact, you can give sqlstate or the wrong code a different name, and you can use your own defined name in the process. Here's how it's done: I define the table T2 as a innodb table, so the insert operation on this table is ROLLBACK (rollback), and ROLLBACK (ROLLBACK transaction) happens. Because inserting two of the same values on a primary key can cause a SQLSTATE 23000 error to occur, SQLState 23000 is a constraint error.

2. DECLARE Condition Declaration Condition

CREATE PROCEDURE P24 ()
BEGIN
DECLARE ' Constraint violation '
CONDITION for SQLSTATE ' 23000 ';
DECLARE EXIT HANDLER for
' Constraint violation ' ROLLBACK;
START TRANSACTION;
INSERT into T2 VALUES (1);
INSERT into T2 VALUES (1);
COMMIT;
End; //

This constraint error can cause rollback (ROLLBACK TRANSACTION) and SQLState 23000 errors to occur.

3. DECLARE CONDITION

Mysql> call P24 ()//
Query OK, 0 rows affected (0.28 sec)

Mysql> SELECT * from t2//
Empty Set (0.00 sec)

We call this stored procedure to see what the result is, and from the above we see that the table T2 does not insert any records. All the transactions were rolled back. That's exactly what we want.

4. DECLARE CONDITION

Mysql> CREATE PROCEDURE p9 ()
-> BEGIN
-> DECLARE EXIT HANDLER for not FOUND the BEGIN end;
-> DECLARE EXIT HANDLER for SQLEXCEPTION BEGIN;
-> DECLARE EXIT HANDLER for sqlwarning BEGIN;
-> end;//
Query OK, 0 rows Affected (0.00 sec)

Here are three pre-declared conditions: not FOUND (rows not found), SQLEXCEPTION (Error), sqlwarning (warning or comment). Because they are declared, you do not need to declare the conditions to use them. But if you're going to make this statement: "DECLARE SQLEXCEPTION CONDITION ..." You'll get an error message.

Cursors Cursors

Cursor Implementation feature Summary:

DECLARE cursor-name cursor for SELECT ...;
OPEN Cursor-name;
FETCH cursor-name into variable [, variable];
Close Cursor-name;

Now we're starting to look at the cursor. Although there is no complete implementation of the cursor method in our stored procedures, it is possible to complete the basic transaction such as declaring a cursor, opening a cursor, reading from a cursor, and closing a cursor.

1. Cursor Example

CREATE PROCEDURE P25 (out Return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR for SELECT S1 from T;
DECLARE CONTINUE HANDLER for not FOUND
SET B = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 into A;
UNTIL B = 1
End REPEAT;
Close cur_1;
SET return_val = A;
end;//

Let's look at a new example of a stored procedure that contains cursors.

2. Cursor Example

CREATE PROCEDURE P25 (out Return_val INT)
BEGIN
DECLARE a,b INT; <--
DECLARE cur_1 CURSOR for SELECT S1 from T;
DECLARE CONTINUE HANDLER for not FOUND
SET B = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 into A;
UNTIL B = 1
End REPEAT;
Close cur_1;
SET return_val = A;
end;//

This process begins by declaring three variables. Incidentally, the order is very important. You start with a variable declaration, then declare the condition, then declare the cursor, and then the error handler is declared. If you do not declare it in order, the system prompts for an error message.

3. Cursor Example

CREATE PROCEDURE P25 (out Return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR for SELECT S1 from T; <--
DECLARE CONTINUE HANDLER for not FOUND
SET B = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 into A;
UNTIL B = 1
End REPEAT;
Close cur_1;
SET return_val = A;
end;//

The second step of the program declares the cursor cur_1, which is similar to Embedded SQL if you have used embedded SQL.

4. Cursor Example

CREATE PROCEDURE P25 (out Return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR for SELECT S1 from T;
DECLARE CONTINUE HANDLER for not FOUND <--
SET B = 1; <--
OPEN cur_1;
REPEAT
FETCH cur_1 into A;
UNTIL B = 1
End REPEAT;
Close cur_1;
SET return_val = A;
end;//

Finally, the error processor declaration is made. This continue processing does not refer to SQL error codes and SQLSTATE values. It uses the not found system return value, which is the same as SQLState 02000.

5. Cursor Example

CREATE PROCEDURE P25 (out Return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR for SELECT S1 from T;
DECLARE CONTINUE HANDLER for not FOUND
SET B = 1;
OPEN cur_1; <--
REPEAT
FETCH cur_1 into A;
UNTIL B = 1
End REPEAT;
Close cur_1;
SET return_val = A;
end;//

The first executable statement of the procedure is the open cur_1, which is associated with the select S1 from T statement, and the procedure executes a select S1 from T and returns a result set.

6. Cursor Example

CREATE PROCEDURE P25 (out Return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR for SELECT S1 from T;
DECLARE CONTINUE HANDLER for not FOUND
SET B = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 into A; <--
UNTIL B = 1
End REPEAT;
Close cur_1;
SET return_val = A;
end;//

The first FETCH statement here obtains a row of values retrieved from the result set produced by SELECT, but there are multiple rows in the table T, so the statement is executed several times, of course, because the statement is inside the loop block.

7. Cursor Example

CREATE PROCEDURE P25 (out Return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR for SELECT S1 from T;
DECLARE CONTINUE HANDLER for not FOUND
SET B = 1; <--
OPEN cur_1;
REPEAT
FETCH cur_1 into A;
UNTIL B = 1
End REPEAT;
Close cur_1;
SET return_val = A;
end;//

Finally, when the MySQL fetch does not get the row, the continue processing is triggered and the variable B is assigned a value of 1.

8. Cursor Example

CREATE PROCEDURE P25 (out Return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR for SELECT S1 from T;
DECLARE CONTINUE HANDLER for not FOUND
SET B = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 into A;
UNTIL B = 1
End REPEAT;
Close cur_1; <--
SET return_val = A;
end;//

By this step until b=1 condition is true and the cycle is over. Here we can write our own code to close the cursor, or it can be executed by the system, the system will automatically close the cursor at the end of the compound statement, but it is best not to rely too much on the system's automatic shutdown behavior (this may be the same as the Java GC, not trustworthy).

9. Cursor Example

CREATE PROCEDURE P25 (out Return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR for SELECT S1 from T;
DECLARE CONTINUE HANDLER for not FOUND
SET B = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 into A;
UNTIL B = 1
End REPEAT;
Close cur_1;
SET return_val = A; <--
end;//

In this routine we assign a local variable to the output parameter so that the result is still available at the end of the process.

Cursor Example

CREATE PROCEDURE P25 (out Return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR for SELECT S1 from T;
DECLARE CONTINUE HANDLER for not FOUND
SET B = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 into A;
UNTIL B = 1
End REPEAT;
Close cur_1;
SET return_val = A;
end;//

Mysql> call P25 (@return_val)//
Query OK, 0 rows Affected (0.00 sec)
mysql> SELECT @return_val//
+-------------+
| @return_val |
+-------------+
| 5 |
+-------------+
1 row in Set (0.00 sec)

The above is the result of the procedure call. You can see that the Return_val parameter gets a value of 5, because this is the last line of the table T.
This allows you to know that the cursor is working properly and that error handling is working properly.

Characteristics of Cursor characteristics cursors

Summary:
Read Only ReadOnly Property
Not scrollable sequential read
Asensitive Sensitive

In version 5.0 of MySQL, you can only take a value from a cursor and not update it. Because the cursor is (READONLY) read-only. You can do this:

FETCH Cursor1 into Variable1;
UPDATE T1 SET column1 = ' value1 ' WHERE current of Cursor1;

Cursors are also not scrollable, allowing only the next row to be read, and not going forward or backward in the result set. The following code is wrong:

FETCH PRIOR Cursor1 into variable1;
FETCH absolute cursor1 into variable1;

It is also not allowed to perform updates transactions on a table that has been opened for operation because the cursor is (asensitive) sensitive. Because if you don't stop the update transaction, you don't know what the result will become. If you're using a innodb instead of a MyISAM storage engine, the results will be different.

Security safeguards

Summary
Privileges (1) CREATE Routine
Privileges (2) EXECUTE
Privileges (3) GRANT show routine?
Privileges (4) Invokers and Definers

Here we will discuss some issues related to privileges and security. However, since the functionality of the MySQL security measures is not complete, we will not discuss it too much.

1. Privileges CREATE Routine

GRANT CREATE Routine
On Database-name. *
To User (s)
[with GRANT OPTION];

Now we can use root.

The privilege to introduce here is create routine, which creates stored procedures and functions as well as other privileges, and creates views and tables. The root user has this privilege, along with alter routine privileges.

2. Privileges EXECUTE

GRANT EXECUTE on p to Peter
[with GRANT OPTION];

The privilege above is a privilege that determines whether you can use or execute stored procedures, which the process creator defaults to.

3. Privileges Show routine?

GRANT Show routine on db6.* to Joey
[with GRANT OPTION];

Because we already have the privilege of controlling the view: GRANT Show view. So on this basis, to ensure compatibility, grant show routine privileges may be added in the future. This is not very standard, and in writing a book, MySQL has not yet implemented this function.

4. Privileges Invokers and definers privileged callers and definitions

CREATE PROCEDURE P26 ()
SQL Security Invoker
SELECT COUNT (*) from t//
CREATE PROCEDURE p27 ()
SQL Security Definer
SELECT COUNT (*) from t//
GRANT INSERT on db5.* to Peter; //

Now let's test the SQL Security clause. Security is part of the program features we mentioned earlier. You, the root user, assigns the insertion right to Peter. Then use the Peter Landing to do a new job, we see how Peter can use the stored procedure, note: Peter does not have the Select power of table T, only the root user has.

5. Privileges Invokers and Definers

/* logged on with current_user = Peter/Use account Peter Login

Mysql> call P26 ();
ERROR 1142 (42000): Select command denied to user
' peter ' @ ' localhost ' for table ' t '
Mysql> call P27 ();
+----------+
| COUNT (*) |
+----------+
| 1 |
+----------+
1 row in Set (0.00 sec)

A failure occurs when Peter attempts to invoke a procedure that contains a calling secrecy measure p26. That's because Peter has no power over the table's select.

But it can be successful when Petre invokes a process that contains a defined secrecy measure. The reason is that Root has a select power, Peter.