Shulou(Shulou.com)05/31 Report--

Editor to share with you the sample analysis of stored procedure definition conditions and exception handling in MySQL. I hope you will get something after reading this article. Let's discuss it together.

View the error code when calling the stored procedure

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 10 | neo |

| | 10 | neo |

| | 20 | John |

| | 30 | Lucy |

| | 40 | Larry |

| | 50 | Lilly |

| | 60 | Carlos |

| | 70 | Jason |

+-+ +

8 rows in set (0.00 sec)

Mysql > show create procedure p_test\ G

* * 1. Row *

Procedure: p_test

Sql_mode: NO_ENGINE_SUBSTITUTION

Create Procedure: CREATE DEFINER= `root` @ `localhost` PROCEDURE `p_ test` (in v_id int)

Begin

Set @ c='insert into test values (?)'

Select id into @ a from test where id=v_id

Select @ a

End

Character_set_client: utf8

Collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

Mysql > call p_test (10)

ERROR 1172 (42000): Result consisted of more than one row

Go to the official website to check the corresponding stored procedure exception code

Error: 1172 SQLSTATE: 42000 (ER_TOO_MANY_ROWS)

Message: Result consisted of more than one row

Define exceptions in stored procedures

Mysql > delimiter $$

Mysql > create procedure p_test (in v_id int)

-> begin

-> / * Declare Conditions * /

-> DECLARE too_many_rows CONDITION FOR 1172

-> / * Declare Exception Handlers, usually with set actions * /

-> / * usually with set actions, the following handler has two forms

/ * > one with begin.. End statements, and the other without * /

-> DECLARE EXIT HANDLER FOR too_many_rows

-> BEGIN

-> select 'too many rows'

-> END

-> set @ c='insert into test values'

-> select id into @ a from test where id=v_id

-> select @ a

-> end$$

Query OK, 0 rows affected (0.00 sec)

Mysql > delimiter

Mysql > call p_test (10)

+-+

| | too many rows |

+-+

| | too many rows |

+-+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

DECLARE... The HANDLER statement specifies a handle that handles one or more conditions. If one of these conditions is triggered, the specified statement is executed, either as a simple statement such as SET var_name = value, or using BEGIN. Complex statements in END format.

The handle declaration must appear after the variable or condition declaration.

The value of the handle action indicates the action performed by the handle:

CONTINUE: continue to execute existing programs

EXIT: the BEGIN that terminates the execution handle declaration. END statement, even if the condition occurs in an internal block

UNDO: currently not supported

DECLARE... The value of the HANDLER condition indicates the specific condition or category of the activation handle. It has the following form:

① mysql error code (mysql_error_code): a numeric code inside a MySQL that indicates an MySQL error code, such as 1051 marked with "unknown table":

DECLARE CONTINUE HANDLER FOR 1051

BEGIN

-- body of handler

END

Do not use MySQL error code 0 because it represents a success rather than an error condition.

② SQLSTATE [VALUE] sqlstate_value: a string of length 5 that indicates the value of SQLSTATE, for example, '42S01' is marked with "unknown table":

DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'

BEGIN

-- body of handler

END

Do not use SQLSTATE values that begin with '00', because these values represent success rather than error conditions.

③ is in DECLARE. The condition name declared in CONDITION, which can be associated with the MySQL error code or the value of SQLSTATE.

/ * declare condition * / DECLARE no_such_table CONDITION FOR 1051Placement * declare exception handling * / DECLARE CONTINUE HANDLER FOR no_such_table BEGIN-- body of handler END;/* declare condition * / DECLARE no_such_table CONDITION FOR SQLSTATE '42S02declaration alternate * declare exception handling * / DECLARE CONTINUE HANDLER FOR no_such_table BEGIN-- body of handler END

④ SQLWARNING: shorthand for the value of SQLSTATE that begins with '01'

DECLARE CONTINUE HANDLER FOR SQLWARNING

BEGIN

-- body of handler

END

⑤ NOT FOUND: an abbreviation for the value of SQLSTATE starting with '02', which is related to the context of the cursor and is used to control the database action when the cursor reaches the end of the dataset. If no row is available, the No Data condition occurs with the SQLSTATE of '02000'. To detect this condition, you need to set a handle to the NOT FOUND condition

DECLARE CONTINUE HANDLER FOR NOT FOUND

BEGIN

-- body of handler

END

⑥ SQLEXCEPTION: abbreviation of the value of SQLSTATE that does not begin with '0000', '01mm, or' 02'

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN

-- body of handler

END

Create a test table

MariaDB [test] > create table actor (actor_id int,first_name varchar (25), last_name varchar (25))

Query OK, 0 rows affected (0.14 sec)

MariaDB [test] > alter table actor add primary key (actor_id)

Query OK, 0 rows affected (0.19 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test] > desc actor

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | actor_id | int (11) | NO | PRI | NULL |

| | first_name | varchar (25) | YES | | NULL |

| | last_name | varchar (25) | YES | | NULL |

+-+ +

3 rows in set (0.00 sec)

MariaDB [test] > insert into actor (actor_id,first_name,last_name) values (100)

Query OK, 1 row affected (0.06 sec)

Create a stored procedure

MariaDB [test] > delimiter / /

MariaDB [test] > create procedure actor_insert ()

-> begin

-> set @ x = 1

-> insert into actor (actor_id,first_name,last_name) values (100djin1)

-> set @ x = 2

-> insert into actor (actor_id,first_name,last_name) values (200pr)

-> set @ x = 3

-> end//

Query OK, 0 rows affected (0.01 sec)

MariaDB [test] > delimiter

Call stored procedure to report an error

MariaDB [test] > call actor_insert ()

ERROR 1062 (23000): Duplicate entry '100' for key' PRIMARY'

MariaDB [test] > select @ x

+-+

| | @ x |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Rewrite stored procedures to add exception handling

MariaDB [test] > delimiter / /

MariaDB [test] > drop procedure actor_insert

-> / /

Query OK, 0 rows affected (0.17 sec)

MariaDB [test] > create procedure actor_insert ()

-> begin

-> declare continue handler for sqlstate '23000' set @ x2room1

-> set @ x = 1

-> insert into actor (actor_id,first_name,last_name) values (100djin1)

-> set @ x = 2

-> insert into actor (actor_id,first_name,last_name) values (200pr)

-> set @ x = 3

-> end//

Query OK, 0 rows affected (0.02 sec)

In this example, declare SQLSTATE 23000, which means skipping duplicate values in the table. Here is a description of the error in the document

Error: 1022 SQLSTATE: 23000 (ER_DUP_KEY)

Message: Can't write; duplicate key in table'% s'

MariaDB [test] > delimiter

MariaDB [test] > select * from actor

+-+

| | actor_id | first_name | last_name | |

+-+

| | 100 | James | Kevin | |

+-+

1 row in set (0.00 sec)

MariaDB [test] > select @ x

+-+

| | @ x |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Call the stored procedure again, skipping lines that repeat the primary key

MariaDB [test] > call actor_insert ()

Query OK, 0 rows affected (0.07 sec)

MariaDB [test] > select @ x2

+-+

| | @ x2 |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

MariaDB [test] > select @ x

+-+

| | @ x |

+-+

| | 3 |

+-+

1 row in set (0.00 sec)

MariaDB [test] > select * from actor

+-+

| | actor_id | first_name | last_name | |

+-+

| | 100 | James | Kevin | |

| | 200 | John | Terry |

+-+

2 rows in set (0.00 sec)

Define a HANDLER to automatically roll back a transaction. If an error occurs in a stored procedure, it will be rolled back automatically.

Create procedure sp_auto_rollback_demo ()

Begin

Declare exit handler for SQLEXCEPTION ROLLBACK

Start transaction

Insert into b select 1

Insert into b select 2

Insert into b select 1

Insert into b select 3

Commit

End

Add test error code

Delimiter / /

Create procedure sp_auto_rollback_demo ()

Begin

Declare exit handler for SQLEXCEPTION BEGIN ROLLBACK; SELECT-1; END

Start transaction

Insert into b select 1

Insert into b select 2

Insert into b select 1

Insert into b select 3

Commit

Select 1

End//

Delimiter

After reading this article, I believe you have a certain understanding of "sample analysis of stored procedure definition conditions and exception handling in MySQL". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.