atitit MySQL stored procedure captures all exceptions, as well as logging exception information

atitit mysql stored procedure captures all exceptions, as well as logging exception information

1.1. exception Handling mode exit continue undo Mode 1

1.2. capturing all exceptions using DECLARE continue HANDLER for SqlException 1

1.3. capturing specific exceptions using HANDLER for ErrorCode 2

1.4. logging exceptions to the Log table, getting exception codes and exception information 2

1.5. throwing a custom exception 3

2. Catch SQL Custom thrown exception in program language 3

2.1. Code 3

3. collection of loop loop while repeat mode 4

3.1. Loop using Loop mode for collections is the simplest, while and repeat are troublesome stores. Loop mode automatically handles the end of the collection. 4

3.2. While mode Loop collection: You need to define a CONTINUE HANDLER for not FOUND 4

3.3. Reference 5

1.1. exception Handling mode exit continue undo Mode

by default,the MySQL exception mechanism is exit mode, and an error exits directly.

When we are dealing with a collection loop, we need to catch the exception, log the logs, and continue to execute.

1.2. Catch all exceptions using DECLARE continue HANDLER for SqlException

compared to the exception ofThe JS language, the exception capture ofSQL is coarse, can only catch an exception within an SP, can not be accurate to a few lines of code ...

BEGIN

#Routine body goes here ...

DECLARE a varchar (102);   DECLARE code varchar (102); DECLARE msg varchar (102);

DECLARE b varchar (100); DECLARE n int;

# #catch

DECLARE continue HANDLER for SqlException

Cat1:begin

GET Diagnostics CONDITION 1

Code = returned_sqlstate, msg = Message_text;

SELECT code,msg;

#exit All

#leave CAT1;

END;

Set n=1;

# while n<5 do

Lable:loop

Call Exthrow ();

Set n=n+1;

Select ' In loop ';

If N>3 Then

Select ' N>3 ';

Leave lable;

End If;

#end while;

End Loop;

END

1.3. capturing specific exceptions using HANDLER for ErrorCode1.4. logging exceptions to the Log table, getting exception codes and exception information

GET Diagnostics CONDITION 1

Code = returned_sqlstate, msg = Message_text;

SELECT code,msg;

1.5. throw a custom exception

#Routine body goes here ...

SIGNAL SQLSTATE ' HY000 ' SET message_text = "extag_1";

2. Catch SQL custom thrown exception in program language

2.1. Code

BEGIN

#Routine body goes here ...

SIGNAL SQLSTATE ' HY000 ' SET message_text = "extag_1";

END

{

"@type": "Java.lang.RuntimeException",

"Cause": {

"@type": "Java.sql.SQLException",

"ErrorCode": 1644,

"Localizedmessage": "Extag_1 query:call exthrow Parameters: []",

"Message": "Extag_1 query:call exthrow Parameters: []",

"Nextexception": {

"ErrorCode": 1644,

"Localizedmessage": "Extag_1",

"Message": "Extag_1",

"SQLState": "HY000",

"SQLState": "HY000",

"StackTrace": [{

"Localizedmessage": "Java.sql.SQLException:extag_1 query:call exthrow Parameters: []",

"Message": "Java.sql.SQLException:extag_1 query:call exthrow Parameters: []",

"StackTrace": [{

3. collection of loop loop while repeat mode 3.1. looping through the collection is the simplest way to use loop mode, while and repeat are troublesome stores. Loop mode automatically handles the end of the collection.

BEGIN

#Routine body goes here ...

DECLARE a varchar (102);

DECLARE b varchar (100);   DECLARE n int; DECLARE rs_finished int;

DECLARE cursor_name cursor FOR select id,identity from System_passport ORDER BY id desc limit 3;

OPEN cursor_name;

Lable:loop

Fetch cursor_name into a A, b;

Select a, B;

End LOOP;

END

3.2. while mode loop collection: You need to define a CONTINUE HANDLER for not FOUND

BEGIN

#Routine body goes here ...

DECLARE a varchar (102);

DECLARE b varchar (100);   DECLARE n int; DECLARE rs_finished int;

DECLARE cursor_name cursor FOR select id,identity from System_passport ORDER BY id desc limit 5;

DECLARE CONTINUE HANDLER for not FOUND SET rs_finished=1;

#set rs_finished=0;

Set N=1;select ' open cursor bef ';

OPEN cursor_name;

Select ' Open cursor after ';

Fetch cursor_name into a A, b;

Select a, B;

Select rs_finished; # is NULL

While rs_finished are null do

Select a, B;

Fetch cursor_name into a A, b;

End while;

END

3.3. References

GET DIAGNOSTIC Statement - Blog Channel - CSDN.NET.html

Atitit MySQL database custom exception capture and processing in Java recommended standards and specifications

author :: nickname : old Wow's paw Claw of Eagle idol iconoclast image-smasher

Bird King "Bird Catcher Kok devout pious religious defender Defender of the Faith. Caracalla Red Cloak Caracalla Red Cloak King of the Beast

abbreviation:: Emir attilax Akbar Emile Atiraca

Full Name:: Emir Attilax Akbar bin Mahmud bin Attila bin Solomon bin Adam Al Rapanui Emile Atilax Akbaben Maham Solomon Ben Adam Arrapano Iraq

Common name: Etila (Ayron), Email:[email protected]

title:Head of Uke headquarters , founder of global Grid project,

Uke Friendship Association President UKE Hunting Association President Emir Uke Tribe chief Grand Chief,

Uke , Minister of Religious and Cultural Integration Affairs, Vice-Chairman of the Uke Reformation Commission

Uke System and chairman of the major conference committee,uke Security Department Chief Brigade , uke System Inspection Committee vice president,

Uke chief CTO Software Division Director of Technical department deputy Director of Research and Development department Director of Product department deputy Manager Project department deputy manager uke Science and Technology Research Institute Dean UK E software training Master

Uke Polynesia District chain Head of the region of Tonga Kingdom director of the Uke Kerr Glen, chain head, Line Is. District,uke Bouvet and South Georgia and South Sandwich Is. regional chain head

Uke, chairman of the Association of Software Standardization , Vice president of Uke Database and storage Standardization Association

Uke , founder of Uke Hospital and medical School, vice president of lifelong education school

UEC , President of Uecip , director of uke Document retrieval Department, head of Department of Image processing machine Vision

Uke, chief Dean of the School of image processing and machine vision

Uke Outdoor Sports Association chairman of the resort chief Mayor Uke Press editor

reprint Please indicate source:Attilax 's column http://blog.csdn.net/attilax

http://www.cnblogs.com/attilax/

--atiend V9

atitit MySQL stored procedure captures all exceptions, as well as logging exception information