Understanding Mysql variables and Conditions _mysql

I. Overview

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

MySQL Version: 5.6

Definition and assignment of variables

#创建数据库
DROP DATABASE IF EXISTS dpro;
CREATE DATABASE dpro
CHARACTER SET UTF8
;

Use Dpro;

#创建部门表
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
(id INT not NULL PRIMARY key COMMENT ' primary key ',
 name VARCHAR not null COMMENT ' person name ',
 de PID INT not NULL COMMENT ' Department id '
);

INSERT into Employee (id,name,depid) VALUES (1, ' Chen ', 100), (2, ' Wang ', 101), (3, ' Zhang ', 101), (4, ' Lee ', 102), (5, ' Guo ', 103);

Declare define Variables

Define variables in stored procedures and functions by declare in the BEGIN ... End and before the statement. And you can define multiple variables by repeating

Note: Declare definition of variable names can not take the ' @ ' symbol, MySQL does not do this in the intuitive, often variable names will be wrong to the 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 (m), out Pcount INT)
reads SQL DATA
SQL security Invoker
BEGIN
DECLARE pname VARCHAR () DEFAULT ' Chen ';
SELECT COUNT (ID) into Pcount from Employee WHERE depid=pdepid;

end$$
DELIMITER;

Set Variable Assignment

Set, in addition to assigning values to already-defined variables, you can specify assignments and define new variables, and the set-defined variable names can take the ' @ ' symbol, and the SET statement's position is also at the beginning .... End before the statement.

1. Variable Assignment value

SET var_name = expr [, var_name = expr] ...
DROP PROCEDURE IF EXISTS pro_employee;
DELIMITER $$
CREATE PROCEDURE pro_employee (in Pdepid VARCHAR (m), out Pcount INT)
reads SQL DATA
SQL security Invoker
BEGIN
DECLARE pname VARCHAR () DEFAULT ' Chen ';
SET pname= ' King ';
SELECT COUNT (ID) into Pcount from Employee WHERE depid=pdepid and Name=pname;

end$$
DELIMITER;

Call Pro_employee (@pcount);
 SELECT @pcount;

2. Define variables by assigning values

DROP PROCEDURE IF EXISTS pro_employee;
DELIMITER $$
CREATE PROCEDURE pro_employee (in Pdepid VARCHAR (m), out Pcount INT)
reads SQL DATA
SQL security Invoker
BEGIN
DECLARE pname VARCHAR () 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 (@pcount);

SELECT ... Into statement assignment

The SELECT INTO statement assigns a value to a variable, or it can assign a value to the out parameter of the stored procedure, where the stored procedure select into is to assign the value to the out parameter.

DROP PROCEDURE IF EXISTS pro_employee;
DELIMITER $$
CREATE PROCEDURE pro_employee (in Pdepid VARCHAR (m), out Pcount INT)
reads SQL DATA
SQL security Invoker
BEGIN
DECLARE pname VARCHAR () DEFAULT ' Chen ';
DECLARE Pid INT;
SELECT COUNT (ID) into the Pid from Employee WHERE depid=pdepid and Name=pname;
SELECT Pid;

end$$
DELIMITER;

Call Pro_employee (@pcount);

The stored procedure is select into to assign the value to a variable;

There is no record of depid=101 and Name= ' Chen ' in the table.

Third, the conditions

The role of the condition is generally used in the processing of the specified conditions, such as we encounter the primary key repeated error after how to deal with.

Define criteria

Defining a condition is to define a certain error state or the name of the SQL state beforehand, and then you can refer to the condition name to do the conditional processing, the definition condition generally uses relatively few, generally will place directly in the condition processing inside.

DECLARE condition_name condition for condition_value
 
condition_value:
  SQLSTATE [value] Sqlstate_value
 | mysql_error_code

1. No conditions are defined:

DROP PROCEDURE IF EXISTS pro_employee_insert;
DELIMITER $$
CREATE PROCEDURE pro_employee_insert ()
modifies SQL DATA
SQL security invoker
BEGIN
SET @ID =1;
INSERT into Employee (id,name,depid) VALUES (1, ' Chen ', MB);
SET @ID =2;
INSERT into Employee (id,name,depid) VALUES (6, ' Chen ', MB);
SET @ID =3;

end$$
DELIMITER;

#执行存储过程 call
Pro_employee_insert ();

#查询变量值
SELECT @ID, @x;

Error Reporting duplicate primary key, where 1062 is the primary key duplicate error code, 23000 is the SQL Error state

2. Define processing conditions

DROP PROCEDURE IF EXISTS pro_employee_insert;
DELIMITER $$
CREATE PROCEDURE pro_employee_insert ()
modifies SQL DATA
SQL security invoker
BEGIN
#定义条件名称,
DECLARE reprimary CONDITION for 1062;
#引用前面定义的条件名称并做赋值处理
DECLARE EXIT HANDLER for reprimary SET @x=1;
SET @ID =1;
INSERT into Employee (id,name,depid) VALUES (1, ' Chen ', MB);
SET @ID =2;
INSERT into Employee (id,name,depid) VALUES (6, ' Chen ', MB);
SET @ID =3;

end$$
DELIMITER;

Call Pro_employee_insert ();

SELECT @ID, @x;

There was no error in the steps to execute the stored procedure, but because I defined exit, I terminated execution when I encountered the error SQL.

Next look at the difference between continue

DROP PROCEDURE IF EXISTS pro_employee_insert;
DELIMITER $$
CREATE PROCEDURE pro_employee_insert ()
modifies SQL DATA
SQL security invoker
BEGIN
#定义条件名称,
DECLARE reprimary CONDITION for SQLSTATE ' 23000 ';
#引用前面定义的条件名称并做赋值处理
DECLARE CONTINUE HANDLER for reprimary SET @x=1;
SET @ID =1;
INSERT into Employee (id,name,depid) VALUES (1, ' Chen ', MB);
SET @ID =2;
INSERT into Employee (id,name,depid) VALUES (6, ' Chen ', MB);
SET @ID =3;

end$$
DELIMITER;

Call Pro_employee_insert ();

SELECT @ID, @x;

Where the red is marked is different from the above, where the condition is defined using the SQL state and the primary key duplication state, and here is the use of continue is encountered errors continue to proceed.

Conditional Processing

Conditional processing is the process of defining errors between statements, eliminating the previous step of defining a 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: The error is to continue to execute or terminate, the current undo is useless.

CONTINUE: Continue down the execution

EXIT: Abort execution

Condition_values: Error status

SQLSTATE [VALUE] Sqlstate_value: This is the SQL Error state mentioned earlier, such as primary key repeat state SQLSTATE ' 23000 '

Condition_name: The definition condition name mentioned above;

SQLWarning: is shorthand for all SQLSTATE code that starts with 01, for example: DECLARE CONTINUE HANDLER for sqlwarning.

Not FOUND: is shorthand for all SQLSTATE codes that start with 02.

SQLEXCEPTION: is shorthand for all SQLSTATE code that is not captured by sqlwarning or not found.

Mysql_error_code: Is the error code, such as a duplicate of the primary key error code is 1062,declare CONTINUE HANDLER for 1062

Statement:

DROP PROCEDURE IF EXISTS pro_employee_insert;
DELIMITER $$
CREATE PROCEDURE pro_employee_insert ()
modifies SQL DATA
SQL security invoker
BEGIN

#引用前面定义的条件名称并做赋值处理
DECLARE CONTINUE HANDLER for SQLEXCEPTION SET @x=2;
#开始事务必须在DECLARE之后
START TRANSACTION;
SET @ID =1;
INSERT into Employee (id,name,depid) VALUES (7, ' Chen ', MB);
SET @ID =2;
INSERT into Employee (id,name,depid) VALUES (6, ' Chen ', MB);
SET @ID =3;

IF @x=2 THEN
 ROLLBACK;
ELSE
 COMMIT;
End IF; 

end$$
DELIMITER;

#执行存储过程 call
Pro_employee_insert ();
#查询
SELECT @ID, @x;

With the Select @ID, @x can know that the stored procedure has been executed to the last, but because the entire statement was rolled back after the stored procedure, the id=7 records were rolled back.

Summarize

The use of variables is not only these, the conditions in the cursor is also a good function, just testing is continue if you use Exit statement execution "SET @ID = 2;" is not executed, the following if is not executed the entire statement will not be rolled back, but the use of contine when there is an error will still go down if there are a lot of words after the whole rollback process will be very long, where you can use the loop, when there is an error immediately exit the loop to perform the following if rollback operation, The next circular statement will be written, welcome attention.