Understanding MySQL variables and conditions _ MySQL

This article mainly helps you understand MySQL variables and conditions in depth. For more information, see I. Overview

Variables are often used in stored procedures. the usage of variables is an important knowledge point, especially when defining conditions.

Mysql version: 5.6

II. variable definition and assignment

# CREATE a database drop database if exists Dpro; create database DproCHARACTER SET utf8; USE Dpro; # CREATE a department table drop table if exists Employee; create table Employee (id int not null primary key comment 'primary key', name VARCHAR (20) not null comment 'personal name', depid int not null comment 'Department ID '); insert into Employee (id, name, depid) VALUES (1, 'Chen', 100), (2, 'King', 101), (3, 'zhang ', 101), (4, 'Lil', 102), (5, 'go', 103 );

Declare defines variables

In the stored procedures and functions, use declare to define variables in BEGIN... END and before the statement. Multiple variables can be defined repeatedly.

Note: the variable name defined by declare cannot contain the '@' symbol. mysql is not intuitive enough at this point, and the variable name is often mistakenly named as a parameter or field name.

DECLARE var_name [,...] type [DEFAULT value]
For example:

Drop procedure if exists Pro_Employee; DELIMITER $ create procedure Pro_Employee (IN pdepid VARCHAR (20), OUT pcount INT) reads SQL DATASQL SECURITY INVOKERBEGINDECLARE pname VARCHAR (20) DEFAULT 'Chen '; select count (id) INTO pcount FROM Employee WHERE depid = pdepid; END $ DELIMITER;

SET variable assignment

In addition to assigning values to defined variables, SET can also assign values and define new variables. the variable names defined by SET can contain the '@' symbol, the position of the SET statement is also in BEGIN .... before the END statement.

1. variable assignment

SET var_name = expr [, var_name = expr]... drop procedure if exists Pro_Employee; DELIMITER $ create procedure Pro_Employee (IN pdepid VARCHAR (20), OUT pcount INT) reads SQL DATASQL SECURITY INVOKERBEGINDECLARE pname VARCHAR (20) DEFAULT 'Chen '; SET pname = 'King'; select count (id) INTO pcount FROM Employee WHERE depid = pdepid AND name = pname; END $ DELIMITER; CALL Pro_Employee (101, @ pcount ); SELECT @ pcount;

2. Define variables by assigning values

Drop procedure if exists Pro_Employee; DELIMITER $ create procedure Pro_Employee (IN pdepid VARCHAR (20), OUT pcount INT) reads SQL DATASQL SECURITY INVOKERBEGINDECLARE pname VARCHAR (20) DEFAULT 'Chen '; SET pname = 'King'; SET @ ID = 1; select count (id) INTO pcount FROM Employee WHERE depid = pdepid AND name = pname; SELECT @ ID; END $ DELIMITER; CALL Pro_Employee (101, @ pcount );

SELECT... the value assigned by the INTO statement

The select into statement can assign a value to a variable, or assign the value to the out parameter of the stored procedure. The select into parameter of the stored procedure gives the value to the out parameter.

Drop procedure if exists Pro_Employee; DELIMITER $ create procedure Pro_Employee (IN pdepid VARCHAR (20), OUT pcount INT) reads SQL DATASQL SECURITY INVOKERBEGINDECLARE pname VARCHAR (20) DEFAULT 'Chen '; DECLARE Pid INT; select count (id) INTO Pid FROM Employee WHERE depid = pdepid AND name = pname; SELECT Pid; END $ DELIMITER; CALL Pro_Employee (101, @ pcount );

This stored procedure is to grant the value to the variable by select;

The table does not have the depid = 101 and name = 'Chen 'record.

III. conditions

The function of a condition is generally used to process a specified condition. for example, what should we do if we encounter a primary key repeat error.

Define conditions

Defining a condition is to define the name of an error state or SQL state in advance, and then you can reference the condition name for conditional processing. generally, defining conditions are rarely used, it is usually placed in the condition processing.

DECLARE condition_name CONDITION FOR condition_value condition_value:  SQLSTATE [VALUE] sqlstate_value | mysql_error_code

1. no conditions defined:

Drop procedure if exists Pro_Employee_insert; DELIMITER $ create procedure Pro_Employee_insert () modifies SQL DATASQL SECURITY INVOKERBEGINSET @ ID = 1; INSERT INTO Employee (id, name, depid) VALUES (1, 'Chen ', 100); SET @ ID = 2; insert into Employee (id, name, depid) VALUES (6, 'Chen', 100); SET @ ID = 3; END $ DELIMITER; # run the stored procedure CALL Pro_Employee_insert (); # query the variable value SELECT @ ID, @ X;

The primary key repetition error is reported. 1062 is the primary key repetition error code, and 23000 is the SQL error status.

2. define processing conditions

Drop procedure if exists Pro_Employee_insert; DELIMITER $ create procedure Pro_Employee_insert () modifies SQL DATASQL SECURITY INVOKERBEGIN # define the CONDITION name, DECLARE reprimary CONDITION FOR 1062; # reference the previously defined condition name and assign values. declare exit handler for reprimary SET @ x = 1; SET @ ID = 1; insert into Employee (id, name, depid) VALUES (1, 'Chen ', 100); SET @ ID = 2; insert into Employee (id, name, depid) VALUES (6, 'Chen', 100 ); SET @ ID = 3; END $ DELIMITER; CALL Pro_Employee_insert (); SELECT @ ID, @ X;

No error is reported during the execution of the stored procedure. However, because I defined exit, the SQL statement is terminated when an error is reported.

Next let's take a look at the differences in continue.

Drop procedure if exists Pro_Employee_insert; DELIMITER $ create procedure Pro_Employee_insert () modifies SQL DATASQL SECURITY INVOKERBEGIN # define the CONDITION name, DECLARE reprimary CONDITION FOR SQLSTATE '200 '; # reference the previously defined condition name and assign values. declare continue handler for reprimary SET @ x = 1; SET @ ID = 1; insert into Employee (id, name, depid) VALUES (1, 'Chen ', 100); SET @ ID = 2; insert into Employee (id, name, depid) VALUES (6, 'Chen', 100 ); SET @ ID = 3; END $ DELIMITER; CALL Pro_Employee_insert (); SELECT @ ID, @ X;

Here, the red mark is different from the above. the conditions defined here use the SQL status and the primary key repetition status. In addition, if you use the CONTINUE here, you will encounter an error and CONTINUE to execute.

Conditional processing

Conditional processing is the process of defining statement errors, saving the previous steps for defining the condition name.

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement handler_type:  CONTINUE| EXIT| UNDO condition_value:  SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

Handler_type: whether to continue or terminate an error. Currently, UNDO is useless.

CONTINUE: CONTINUE to execute

EXIT: terminate execution.

Condition_values: error status

SQLSTATE [VALUE] sqlstate_value: refers to the SQL error status mentioned above. for example, the duplicate state of the primary key SQLSTATE '123456'

Condition_name: name of the conditions defined above;

SQLWARNING: a shorthand FOR all SQLSTATE codes starting with 01. FOR example, declare continue handler for sqlwarning.

Not found: a shorthand for all SQLSTATE codes starting with 02.

SQLEXCEPTION: a shorthand for all SQLSTATE codes that are NOT captured by SQLWARNING or not found.

Mysql_error_code: indicates the error code. FOR example, the error code of duplicate primary keys is 1062, and declare continue handler for 1062.

Statement:

Drop procedure if exists Pro_Employee_insert; DELIMITER $ create procedure Pro_Employee_insert () modifies SQL DATASQL SECURITY INVOKERBEGIN # reference the previously defined condition name and assign values to DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @ x = 2; # START TRANSACTION after DECLARE; SET @ ID = 1; insert into Employee (id, name, depid) VALUES (7, 'Chen', 100); SET @ ID = 2; insert into Employee (id, name, depid) VALUES (6, 'Chen ', 100); SET @ ID = 3; IF @ x = 2 then rollback; else commit; end if; END $ DELIMITER; # CALL Pro_Employee_insert (); # Query SELECT @ ID, @ X;

By using SELECT @ ID and @ X, you can know that the stored procedure has been executed to the end, but the entire statement is rolled back after the stored procedure, therefore, the matching records with ID = 7 are also rolled back.

Summary

The use of variables is not only these, but the condition in the cursor is also a good function. the test just now shows that if continue uses EXIT, the statement runs "SET @ ID = 2; "The statement will not be executed, and the entire IF statement will not be rolled back, however, when CONTINE is used, it will still be executed when an error occurs. if there are many other statements, the entire rollback process will be very long. here we can use the loop, when an error occurs, immediately exit the loop and perform the if rollback operation. in the next lecture, the loop statement will be written.

The above is how to understand MySQL variables and condition _ MySQL. For more information, see PHP Chinese network (www.php1.cn )!