MySQL defines the exception and exception handling methods, and MySQL defines the handling methods

MySQL defines the exception and exception handling methods, and MySQL defines the handling methods

In MySQL, specific exceptions must be handled. These exceptions can be associated with errors and general process control in subprograms. Defining exceptions is a problem encountered during the execution of the program in advance. Exception Handling defines the handling methods to be taken when a problem occurs, it also ensures that stored procedures or functions can continue to be executed in case of errors or warnings.

1. Exception definition 1.1 syntax

DECLARE condition_name condition for [condition_type];

1.2 description

Condition_nameParameter indicates the name of the exception;

Condition_typeThe parameter indicates the type of the condition. condition_type consists of SQLSTATE [VALUE] sqlstate_value | mysql_error_code:

• Both sqlstate_value and mysql_error_code can indicate MySQL errors;
• Sqlstate_value is a string-type error code with a length of 5;
• Mysql_error_code is a numeric error code;

1.3 example

Define "ERROR 1148 (42000)" and name it command_not_allowed. You can use either of the following methods:

// Method 1: Use sqlstate_value

DECLARE command_not_allowed condition for sqlstate '2013 ';

// Method 2: Use mysql_error_code

DECLARE command_not_allowed condition for 1148;

2. Custom Exception Handling 2.1 Exception Handling syntax

DECLARE handler_type handler for condition_value [,...] sp_statement

2.2 parameter description

Handler_type:CONTINUE | EXIT | UNDO

• Handler_type is the error handling method, and the parameter is one of three values;
• CONTINUE indicates that an error is not handled and the execution continues;
• EXIT indicates to EXIT immediately when an error occurs;
• UNDO indicates that the previous operation is recalled after an error occurs. MySQL does not support rollback at the moment;
  

Condition_value:SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | not found | SQLEXCEPTION | mysql_error_code

• Condition_value indicates the error type;
• SQLSTATE [VALUE] sqlstate_value is a string error VALUE that contains five characters;
  
• Condition_name indicates the name of the error CONDITION defined by declare condition;
• SQLWARNING matches all SQLSTATE error codes starting with 01;
• Not found matches all SQLSTATE error codes starting with 02;
• SQLEXCEPTION matches all SQLSTATE error codes NOT captured by SQLWARNING or not found;
• Error Code of mysql_error_code matching value type;

2.3 exception capture method

// Method 1: capture sqlstate_value exceptions

// This method captures the sqlstate_value value. If the sqlstate_value is "42S02", perform the CONTINUE operation and output the "NO_SUCH_TABLE" information.

Declare continue handler for sqlstate '42s02 'SET @ info = 'no _ SUCH_TABLE ';

// Method 2: Catch an error in mysql_error_code

// This method captures the value of mysql_error_code. If the value of mysql_error_code is 1146, perform the CONTINUE operation and output "NO_SUCH_TABLE" information;

Declare continue handler for 1146 SET @ info = 'no _ SUCH_TABLE ';

// Method 3: first define conditions and then capture exceptions

DECLARE no_such_table condition for 1146;

Declare continue handler for NO_SUCH_TABLE SET @ info = 'no _ SUCH_TABLE ';

// Method 4: capture exceptions using SQLWARNING

Declare exit handler for sqlwarning set @ info = 'error ';

// Method 5: Use not found to capture exceptions

Declare exit handler for not found set @ info = 'no _ SUCH_TABLE ';

// Method 6: capture exceptions using SQLEXCEPTION

Declare exit handler for sqlexception set @ info = 'error ';

3 comprehensive example

Create a table and set the primary key of the table. If you do not define exception handling or exception handling, you can see where to proceed.

show databases;use wms;create table location(location_id int primary key,location_name varchar(50));

Example 1: do not define exceptions

DELIMITER //CREATE PROCEDURE handlerInsertNoException()BEGIN/*DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;*/SET @x=1;INSERT INTO location VALUES (1,'Beijing');SET @x=2;INSERT INTO location VALUES (1,'Wuxi');SET @x=3;END;//DELIMITER ;

Call the stored procedure and result:

mysql> call handlerInsertNoException();ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> select @x;+------+| @x   |+------+|    2 |+------+1 row in set (0.00 sec)mysql> select * from location;+-------------+---------------+| location_id | location_name |+-------------+---------------+|           1 | Beijing       |+-------------+---------------+1 row in set (0.00 sec) 

Note: Before example 2, clear the table data and log out again to avoid the influence of the client variable @ x. For details, see the first point in the conclusion.

mysql> truncate table location;Query OK, 0 rows affected (0.04 sec)mysql> select * from location;Empty set (0.00 sec)mysql> exit;Byedavid@Louis:~$ mysql -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 53Server version: 5.5.38-0ubuntu0.14.04.1 (Ubuntu)mysql> use wms;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from location;Empty set (0.00 sec)mysql> select @x;+------+| @x   |+------+| NULL |+------+1 row in set (0.00 sec)

Example 2: Define the exception handling case:

DELIMITER //CREATE PROCEDURE handlerInsertWithException()BEGINDECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;SET @x=1;INSERT INTO location VALUES (1,'Beijing');SET @x=2;INSERT INTO location VALUES (1,'Wuxi');SET @x=3;END;//DELIMITER ;

Call the stored procedure and result:

mysql> CALL handlerInsertWithException();Query OK, 0 rows affected (0.09 sec)mysql> select @x;+------+| @x   |+------+|    3 |+------+1 row in set (0.00 sec)

Description and conclusion:

1. in MySQL, @ var_name indicates the user variable, which is assigned a value using the SET statement. The user variable is related to the connection. The variables defined by one client cannot be seen or used by other clients. When the client exits, all variables connected to the client are automatically released.

2. In example 1, because the exception statement "" is commented, inserting the same primary key to the table triggers an exception and uses the default EXIT path; check that @ x returns 2 at this time, indicating that the following INSERT statement is exited if it is not executed.

3. If exception handling is defined, the execution will continue as the exception is defined. However, only the first data is inserted into the table, at this time, the user variable @ x = 3 indicates that the execution has reached the end;

Bytes -----------------------------------------------------------------------------------------------------------------------------

If you encounter any problems during your attempt or my code is incorrect, please correct me. Thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: reprinted, please indicate the source!

How do I define error handling in mysql stored procedures?

Declare is used to define variables and keywords such as common processing and declaration. Before the emergence of the mysql stored procedure, declare is a weakness. We often use declare to define local variables. I often use set to define variables (although global, it is convenient ). After a stored procedure appears, the standard processing definition of declare becomes a very powerful tool that can be used to add some very powerful error processing mechanisms for the stored procedure. The first thing to mention is that if you want to define a varchar variable in declare, you must specify the maximum length of the parameter, that is, declare varchar (20 ). here we will not go into the small details of the parameters used to define them. We will mainly study DECLARE Condition, DECLARE HandlerDECLARE Condition, and DECLARE Handler to handle errors. In terms of function, DECLARE Condition occurs earlier and features are relatively simple. It can trigger a name by using an error number or SQLSTATE, to put it bluntly, replace a name with an error number. In this way, we do not need to remember a large number of errors. Its standard syntax can be found in the notes of mysql. DECLARE condition_name condition for condition_valuecondition_value: Example: DECLARE errname condition for sqlstate '20160301' will return the SQLSTATE information 23000 error name is errname this name can be called by our absolute drama DECLARE Handler, in DECLARE Handler, you can define the error handling method. You can use begin and end to mark the statement block, and you can use rollback separately. The processing process can also be defined as continuing to execute and interrupting the storage process. Standard Syntax: DECLARE handler_type handler for condition_value [,...] sp_statementhandler_type: Process of processing. CONTINUE continues to execute unfinished stored procedures until the end. (Commonly used, default) | if EXIT is incorrect, the system automatically jumps out of the corresponding begin and does not execute subsequent statements. Condition_value: The processing trigger condition SQLSTATE [VALUE] sqlstate_value Needless to say. For the most common error definitions, check the Error List by yourself. | Condition_name the name errnmae we just defined is used here. | SQLWARNING: This is too easy to use. It indicates an error starting with error code 01. It is equivalent to an incorrect wildcard. | Not found is similar to above, starting from 02. | SQLEXCEPTION errors that are not included in the preceding two sections can be used for triggering. That is to say, if an error is triggered, the error conditions will be defined as SQLWARNING + SQLEXCEPTION. | Mysql_error_code error number, which is different from the first one, but can also be found in the error list, which is commonly used by me. Example DECLARE errname condition for sqlstate '20170301 '; for errors that cause error 23000, define the name errnameDECLARE continue handler for errname. When errname occurs, perform the following BEGIN statement to start set @ x = 1; set @ x = 1

How does mysql define throwing an error so that external programs can capture it?

If you are using a Windows operating system, you may encounter the following error message:
「 "0X ????????" The memory of the instruction reference "0x00000000". The memory cannot be "read" or "written" and the application is closed.
If you ask some experts, the answer is often "Windows is so unstable" and so on. In fact, this error is not necessarily caused by Windows instability. This article briefly analyzes the general causes of such errors.
1. failed to check memory allocation by Application
When a program needs a piece of memory to store data, it needs to apply using the "function" provided by the operating system. If the memory is allocated successfully, the function will return the newly opened memory zone address to the application, and the application can use this memory address. This is "dynamic memory allocation", and the memory address is also the "cursor" in programming 」. The memory is not always used, and sometimes the memory allocation fails. When the allocation fails, the system function returns a value of 0. The returned value "0" does not indicate a newly enabled cursor. Instead, the system sends a notification to the application notifying you of an error. As an application, check whether the returned value is 0 after each memory application. If yes, it means that a fault has occurred and some measures should be taken to save the problem, this enhances the robustness of the program 」. If the application does not check this error, it considers this value as an available cursor for it according to "thinking inertia" and continues to use this memory in subsequent execution. The real 0-address memory zone stores the most important "Interrupt Descriptor Table" in computer systems and is definitely not allowed to be used by applications. In an operating system without a protection mechanism (such as DOS), writing data to this address will immediately become a machine, and in a robust operating system, such
Windows and so on, this operation will be immediately captured by the system's protection mechanism, the result is that the operating system forces the application to close the error, to prevent the error from expanding. In this case, the above "write memory" error occurs and the referenced memory address is "0x00000000 」. There are many causes of memory allocation failure, such as insufficient memory and mismatched system function versions. Therefore, this allocation failure is often seen after the operating system has been in use for a long time and has installed a variety of applications (including virus programs that inadvertently "Install ), after a large number of system parameters and system files are changed.
2. The application cited abnormal memory cursor due to its own BUG
In applications that use dynamic allocation, sometimes such a situation occurs: The program tries to read and write a piece of "should be available" memory, but somehow, the expected available cursor has expired. It may be that the program "forgot" to request distribution to the operating system, or the program may have logged out the memory at some time and "did not pay attention. The logged-out memory is reclaimed by the system and its access rights do not belong to this application. Therefore, read/write operations also trigger the system's protection mechanism, the only end of an attempt to "violate the law" is to be terminated and all resources are recycled. Laws in the computer world are much more effective and harsh than humans! Such cases all belong to the bugs of the program. You can often reproduce errors in a specific operation order. Invalid cursor may not always be 0, so the memory address in the error prompt is not set to "0x00000000", but other random numbers. If the system frequently mentions error prompts, the following suggestions may be explained.
:
1. check whether a trojan or virus exists in the system. Such programs tend to change the system irresponsibly to control the system,
As a result, the operating system is abnormal. In general, information security awareness should be strengthened, and the executable procedures with unknown sources should never be curious.
2. Update the operating system so that the operating system installer can copy the correct version of the system file and correct system parameters.
Sometimes the operating system itself also has bugs. You must install the officially released upgrade program.
3. Try the new version of the application.
Mode:
Remove virtual memory
Answer:
So far, this is positive, that is, if it does not happen again when the next cold day arrives, it means this is the main cause.
Append:
If you use
We recommend that you delete all *. PF files under the WINDOWS \ PREFETCH directory after the Ghost recovers OS because you need to make the remaining full text of win...>