MySQL variables and Conditions

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

variable definition and assignment

#创建数据库DROP DATABASE IF EXISTS dpro; CREATE DATABASE dprocharacter 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'name', depid INT not NULL COMMENT'Department ID') INSERT into Employee (id,name,depid) VALUES (1,'Chen', -),(2,'Wang',101),(3,'Zhang',101),(4,'Li',102),(5,'Guo',103);

Declare defining variables

Defining variables through declare in stored procedures and functions at begin ... End, and before the statement. And you can define multiple variables by repeating them.

Note: The variable name defined by declare cannot be signed with the ' @ ' symbol, and MySQL does not make it intuitive at this point, and often the variable name is incorrectly called 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 ( c3>), out Pcount INT) READS SQL datasql SECURITY invokerbeginDECLARE pname VARCHAR (DEFAULT ' Chen ') c6>; SELECT COUNT (ID) into Pcount from Employee WHERE depid=pdepid; end$ $DELIMITER;

Set Variable Assignment

In addition to assigning values to variables that have already been defined, you can specify assignments and define new variables, and the variable names defined by the set can have the ' @ ' symbol, and the position of the SET statement is also at begin .... Before the statement between end.

1. Assigning values to variables

SET var_name = expr [, var_name = expr] ...

DROP PROCEDURE IF EXISTS pro_employee;delimiter $ $CREATE PROCEDURE pro_employee (in Pdepid VARCHAR ( c2>), out Pcount INT) READS SQL datasql SECURITY invokerbegin DECLARE pnameVARCHAR (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. Defining variables by assigning values

DROP PROCEDURE IF EXISTS pro_employee;delimiter $ $CREATE PROCEDURE pro_employee (in Pdepid VARCHAR ( -), out Pcount INT) READS SQL datasql SECURITY invokerbegindeclare pname VARCHAR ( -) DEFAULT'Chen'; SET pname='Wang'; 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 ... Into statement assignment

You can assign a value to a variable by using the SELECT INTO statement, or it can be assigned to a stored procedure's out parameter, and the above stored procedure, select INTO, assigns the value to the out parameter.

DROP PROCEDURE IF EXISTS pro_employee;delimiter $ $CREATE PROCEDURE pro_employee (in Pdepid VARCHAR ( c2>), out Pcount INT) READS SQL datasql SECURITY invokerbegindeclare pname VARCHAR  c6> ';  DECLARE Pid INT; SELECT COUNT (ID) into the Pid from Employee WHERE depid=pdepid and name=pname; SELECT Pid; end$ $DELIMITER; Call Pro_employee (101, @pcount);

This stored procedure is a SELECT into that assigns a value to a variable;

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

conditions

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

Defining conditions

Define the condition is to define a certain error state or the name of the SQL state, and then you can refer to the condition of the name of the conditional processing, the definition of conditions generally used relatively little, usually directly placed in the conditional 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 datasql SECURITY Invokerbeginset @ID=1INSERT into Employee (id,name,depid) VALUES (1,'Chen', -); SET @ID=2INSERT into Employee (id,name,depid) VALUES (6,'Chen', -); SET @ID=3; end$ $DELIMITER; #执行存储过程CALL Pro_employee_insert (); #查询变量值SELECT @ID, @X;

A duplicate error is reported for the primary key, where 1062 is the primary key duplicate error code, and 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 datasql SECURITY invokerbegin# defines the condition name,DECLARE reprimary CONDITION for1062; #引用前面定义的条件名称并做赋值处理DECLARE EXIT HANDLER for reprimary SET @x =1; SET @ID=1INSERT into Employee (id,name,depid) VALUES (1,'Chen', -); SET @ID=2INSERT into Employee (id,name,depid) VALUES (6,'Chen', -); 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 ended up with an error in SQL.

Next look at the difference between continue

DROP PROCEDURE IF EXISTS pro_employee_insert;delimiter $ $CREATE PROCEDURE pro_employee_insert () modifies SQL datasql SECURITY invokerbegin# Define condition name, DECLARE reprimary CONDITION for SQLSTATE ' 23000 '; #引用前面定义的条件名称并做赋值处理DECLARE CONTINUE HANDLER for reprimary SET @x=1; SET @ID=1INSERT into Employee (id,name,depid) VALUES (1,'Chen', -); SET @ID=2INSERT into Employee (id,name,depid) VALUES (6,'Chen', -); SET @ID=3; end$ $DELIMITER; Call Pro_employee_insert (); SELECT @ID, @X;

Where the red flag is different from the above, where the condition is defined using the SQL State, and the primary key is repeated, and the use of continue is to continue to run down the error.

Conditional processing

Conditional processing is the process of defining the error between statements, eliminating the previous step of defining a condition name.

DECLAREHandler_type Handler forCondition_value[,...]sp_statement Handler_type:CONTINUE| EXIT|UNDO condition_value:sqlstate[VALUE]Sqlstate_value|Condition_name|sqlwarning|  notFOUND|SQLEXCEPTION|Mysql_error_code

Handler_type: The error is to continue down or terminate, and Undo is not yet available.

CONTINUE: Continue to execute down

EXIT: Terminate execution

Condition_values: Error status

SQLSTATE [VALUE] sqlstate_value: 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 codes that begin with 01, for example: DECLARE CONTINUE HANDLER for sqlwarning.

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

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

Mysql_error_code: Is the error code, such as the primary key duplicate 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 datasql SECURITY invokerbegin 
 #引用前面定义的条件名称并做赋值处理DECLARE CONTINUE HANDLER for sqlexception  SET @x=2 ; 
  #开始事务必须在DECLARE之后  
 start TRANSACTION;  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 (); #查询 SELECT @ID,@X;

With the Select @ID, @x can know that the stored procedure has been executed to the last, but since the stored procedure has been rolled back after the entire statement has been rolled back, the Id=7 's qualifying record is also rolled back.

Summary

The use of variables is not only these, the condition in the cursor is also a good function, just the test is continue if you use Exit statement execution "SET @ID = 2;" Do not go down, the subsequent if is not executed the entire statement will not be rolled back, but the use of contine when an error occurs or will go down if the following statement is still a lot of the whole rollback process will be very long, here can take advantage of the loop, when an error occurs immediately after exiting the loop to perform the subsequent if rollback operation, In the next section, the circular statement will be written, welcome attention.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly give the link. Welcome to the exchange of discussions

MySQL variables and Conditions