MySQL variables, process control and cursors

In the stored procedures and functions of MySQL database, variables can be used to store the intermediate result data of query or calculation, or output the final result data.

In MySQL database, variables are divided into system variables and user-defined variables.

1.1 system variables

1.1.1 classification of system variables

Variables are defined by the system, not by users, and belong to the server level. When starting the MySQL service and generating the MySQL service instance, MySQL will assign values to the system variables in the MySQL server memory, which define the properties and characteristics of the current MySQL service instance. The values of these system variables are either the default values of the parameters when compiling MySQL or the parameter values in the configuration file (such as my.ini, etc.). You can use the website MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables View the system variables of MySQL documents.

System variables are divided into global system variables (Global keyword needs to be added) and session system variables (session keyword needs to be added). Sometimes global system variables are referred to as global variables, and sometimes session system variables are referred to as local variables. If you do not write, the default session level is. Static variables (their values cannot be dynamically modified with set during MySQL service instance running) belong to special global system variables.

After each MySQL client successfully connects to the MySQL server, a corresponding session will be generated. During a session, the MySQL service instance will generate session system variables corresponding to the session in the MySQL server memory. The initial values of these session system variables are copies of the global system variable values. As shown below:

The global system variable is valid for all sessions (connections), but cannot be restarted across multiple sessions
The session system variable is valid only for the current session (connection). During a session, the modification of a session system variable value by the current session will not affect the value of the same session system variable in other sessions.
The modification of a global system variable value in session 1 will result in the modification of the same global system variable value in session 2.

In mysql, some system variables can only be global, such as max_connections is used to limit the maximum number of connections to the server; Some system variable scopes can be both global and session, such as character_set_client is used to set the character set of the client; Some system variables can only be scoped to the current session, such as pseudo_thread_id is used to mark the MySQL connection ID of the current session.

1.1.2. View system variables

View all or some system variables

#View all global variables
SHOW GLOBAL VARIABLES;

#View all session variables
SHOW SESSION VARIABLES;
or
SHOW VARIABLES;

#View some system variables that meet the criteria.
SHOW GLOBAL VARIABLES LIKE '%identifier %';

#View some session variables that meet the criteria
SHOW SESSION VARIABLES LIKE '%identifier %';

give an example:

#Query global system variables
SHOW GLOBAL VARIABLES;

#Query session system variable
SHOW SESSION VARIABLES;
SHOW VARIABLES; #Default query session system variable

#Query some system variables
SHOW GLOBAL VARIABLES LIKE 'admin_%';

SHOW SESSION VARIABLES LIKE 'character_%';
SHOW VARIABLES LIKE 'character_%'; #Default query session system variable

View specified system variables

As a MySQL coding specification, system variables in mysql start with two '@', where '@ @ global' is only used to mark global system variables and '@ @ session' is only used to mark session system variables. "@ @" first marks the session system variable. If the session system variable does not exist, mark the global system variable.

#View the value of the specified system variable
SELECT @@global.Variable name;

#View the value of the specified session variable
SELECT @@session.Variable name;
#perhaps
SELECT @@Variable name;

give an example:

SELECT @@global.max_connections;

#Error: Variable 'max_connections' is a GLOBAL variable
SELECT @@session.max_connections;

SELECT @@session.character_set_client;
SELECT @@global.character_set_client;
SELECT @@session.pseudo_thread_id;

SELECT @@character_set_client; #First query the session system variable, and then query the global variable

Modify the value of the system variable

Sometimes, the database administrator needs to modify the default value of system variables to modify the properties and characteristics of the current session or MySQL service instance. Specific methods:

Method 1: modify the MySQL configuration file, and then modify the value of the MySQL system variable (this method requires restarting the MySQL service)

Method 2: during MySQL service operation, use the "set" command to reset the value of the system variable

#Assign a value to a system variable
#Mode 1:
SET @@global.Variable name=Variable value;
#Mode 2:
SET GLOBAL Variable name=Variable value;


#Assign a value to a session variable
#Mode 1:
SET @@session.Variable name=Variable value;
#Mode 2:
SET SESSION Variable name=Variable value;

give an example:

#Global system variables:
#Mode 1:
SET @@global.max_connections = 161;
SELECT @@global.max_connections;
#Mode 2:
SET GLOBAL max_connections = 171;
#Global system variable: the modification is only valid for the current database instance. Once the MySQL service is restarted, it will fail

#Session system variable:
#Mode 1:
SET @@session.character_set_client = 'gbk';
SELECT @@session.character_set_client;
#Mode 2:
SET SESSION character_set_client = 'utf8mb3';
#It is valid for the current session. Once the session is ended and a new session is established, it is invalid

1.2 user scalar

1.2.1 classification of user variables

User variables are defined by users themselves. As the MySQL coding specification, user variables in MySQL begin with an "@". According to the scope, it is divided into session user variables and local variables.

Session user variable: the scope is the same as the session variable and is only valid for the current session.
Local variable: valid only in BEGIN and END statement blocks. Local variables can only be used in stored procedures and functions.

1.2.2 session user variables

Definition of variables

#Method 1: "=" or ": ="
SET @User variable = value;
SET @User variable := value;

#Method 2: ": =" or INTO keyword
SELECT @User variable := expression [FROM Equal clause];
SELECT expression INTO @User variable  [FROM Equal clause];

View the values of user variables (view, compare, calculate, etc.)

SELECT @User variable

1.2.3 local variables

Definition: you can define a local variable using the DECLARE statement

Scope: valid only in begin... End that defines it

Position: can only be placed in begin... End, and can only be placed in the first sentence

BEGIN
	#Declare local variables
	DECLARE Variable name 1 variable data type [DEFAULT Variable defaults];
	DECLARE Variable name 2,Variable name 3,... Variable data type [DEFAULT Variable defaults];

	#Assign values to local variables
	SET Variable name 1 = value;
	SELECT value INTO Variable name 2 [FROM clause];

	#View the value of a local variable
	SELECT Variable 1,Variable 2,Variable 3;
END

1. Define variables

DECLARE Variable name type [default value];  # If there is no DEFAULT clause, the initial value is NULL

2. Variable assignment

Method 1: generally used to assign simple values

SET Variable name=value;
SET Variable name:=value;

Method 2: generally used to assign field values in a table

SELECT Field name or expression INTO Variable name FROM surface;

3. Use variables (view, compare, calculate, etc.)

SELECT Local variable name;

give an example:

#give an example:
#Create stored procedure
DELIMITER //

CREATE PROCEDURE test_var()
BEGIN
	#Declare local variables
	DECLARE a INT DEFAULT 0;
	DECLARE b INT;
	#declare a,b int default 0;
	DECLARE emp_name VARCHAR(25);
	
	#assignment
	SET a := 1;
	SET b = 2;
	SELECT last_name INTO emp_name FROM employees WHERE employee_id = 101;
	
	#use
	SELECT a, b, emp_name;
END //

DELIMITER ;

#Call stored procedure
CALL test_var();

#Example 1: declare local variables and assign values to employees in the employees table_ Last with ID 102_ Name and salary
DELIMITER //

CREATE PROCEDURE test_pro()
BEGIN
	DECLARE emp_name VARCHAR(25);
	DECLARE sal DOUBLE(10, 2) DEFAULT 0.0;
	
	SELECT last_name, salary INTO emp_name, sal
	FROM employees
	WHERE employee_id = 102;
	
	SELECT emp_name, sal;
END //

DELIMITER ;

CALL test_pro;

#Example 2: declare two variables, sum and print (implemented by session user variables and local variables respectively)
#Method 1: session user variable
SET @v1 = 10;
SET @v2 = 20;
SET @result = @v1 + @v2;
SELECT @result;

#Mode 3: local variables
DELIMITER //

CREATE PROCEDURE add_value()
BEGIN
	DECLARE value1, value2, `sum` INT DEFAULT 0;
	
	SET value1 = 10;
	SET value2 = 110;
	SET `sum` = value1 + value2;
	
	SELECT `sum`;
END //

DELIMITER ;

CALL add_value;

#Example 3: create a stored procedure "different_salary" to query the salary gap between an employee and his leader, and use the IN parameter emp_id receive employee id,
#Use OUT parameter dif_salary outputs salary gap results.
DELIMITER //

CREATE PROCEDURE different_salary(IN emp_id INT, OUT dif_salary DOUBLE)
BEGIN
	DECLARE emp_sal DOUBLE DEFAULT 0.0;
	DECLARE mgr_sal DOUBLE DEFAULT 0.0;
	DECLARE mgr_id INT DEFAULT 0;
	
	SELECT salary,manager_id INTO emp_sal,mgr_id FROM employees WHERE employee_id = emp_id;
	
	SELECT salary INTO mgr_sal FROM employees WHERE employee_id = mgr_id;
	
	SET dif_salary = mgr_sal - emp_sal;
END //

DELIMITER ;

SET @emp_id = 103;
CALL different_salary(@emp_id, @dif_salary);
SELECT @dif_salary;

1.2.4 comparing session user variables with local variables

	Scope	Define location	grammar
Session user variable	Current session	Anywhere in the conversation	Add the @ symbol without specifying the type
local variable	Define it in BEGIN END	First sentence of BEGIN END	Generally, you do not need to add @, and you need to specify the type

2. Define conditions and handlers

The definition condition is to define the problems that may be encountered during the execution of the program in advance. The handler defines the processing method that should be taken when encountering problems, and ensures that the stored procedure or function can continue to execute when encountering warnings or errors. This can enhance the ability of the stored program to deal with problems and avoid the abnormal stop of the program.

Description: defining conditions and handlers are supported in stored procedures and stored functions.

2.1 definition conditions

The definition condition is to name the error code in MySQL, which helps to make the stored program code clearer. It associates an error name with the specified error condition. This name can then be used in the DECLARE HANDLER statement that defines the handler.

The define statement is used to define conditions. The syntax format is as follows:

DECLARE Error name CONDITION FOR Error code (or error condition)

Description of error code:

MySQL_error_code and sqlstate_value can represent MySQL errors.
- MySQL_error_code is a numeric type error code.
- sqlstate_value is a string type error code of length 5.
For example, in ERROR 1418 (HY000), 1418 is MySQL_error_code, 'HY000' is sqlstate_value.
For example, in ERROR 1142 (42000), 1142 is MySQL_error_code, '42000' is sqlstate_value.

give an example:

#Example 1: define the "Field_Not_Be_NULL" error name and the error type that violates the non NULL constraint in MySQL
#It corresponds to "ERROR 1048 (23000)".
#Method 1: use MySQL_error_code
DECLARE Filed_Not_Be_NULL CONDITION FOR 1048;
#Method 2: use sqlstate_value
DECLARE Filed_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

#Example 2: define the "ERROR 1148(42000)" error with the name command_not_allowed. 
#Method 1: use MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;
#Method 2: use sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';

2.2. Define processing procedures

You can define special handlers for certain types of errors that occur during SQL execution. When defining a handler, the syntax of the DECLARE statement is as follows:

DECLARE Treatment method HANDLER FOR Error type processing statement

Processing method: the processing method has three values: CONTINUE, EXIT and UNDO.
- CONTINUE: indicates that if an error is encountered, it will not be processed and execution will CONTINUE.
- EXIT: EXIT immediately in case of an error.
- UNDO: indicates that the previous operation is withdrawn after an error is encountered. MySQL does not support such operations for the time being.
The error type (i.e. condition) can have the following values:
- SQLSTATE 'string error code': represents SQLSTATE with length of 5_ Error code of type value;
- MySQL_error_code: error code of matching value type;
- Error 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;
Processing statement: if one of the above conditions occurs, the corresponding processing method is adopted and the specified processing statement is executed. The statement can be a simple statement like "SET variable = value" or a composite statement written with begin... End.

give an example:

#give an example:
#Method 1: capture sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

#Method 2: capture mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';

#Method 3: define the condition first, and then call
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';

#Method 4: use SQLWARNING
DECLARE CONTINUE HANDLER FOR SQLWARNING SET @info = 'ERROR';

#Method 5: use NOT FOUND
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';

#Method 6: use SQLEXCEPTION
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

2.4 examples

#Redefine the stored procedure to reflect the error handler
DELIMITER //

CREATE PROCEDURE UpdateDataNoCondition()
	BEGIN
		#Declaration handler
		#Treatment method 1:
		DECLARE CONTINUE HANDLER FOR 1048 SET @prc_value = -1;
		#Treatment method 2:
		#declare continue handler for sqlstate '23000' set @prc_value = -1;
		
		SET @x = 1;
		UPDATE employees SET email = NULL WHERE last_name = 'Abel';
		SET @x = 2;
		UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
		SET @x = 3;
	END //

DELIMITER ;

CALL UpdateDataNoCondition();#x = 3

#Example 2
#Create a stored procedure named "InsertDataWithCondition"

#preparation
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;

DESC departments;

ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE(department_id);

#Define stored procedures
DELIMITER //

CREATE PROCEDURE InsertDataWithCondition()
	BEGIN		
		SET @x = 1;
		INSERT INTO departments(department_name) VALUES('test');
		SET @x = 2;
		INSERT INTO departments(department_name) VALUES('test');
		SET @x = 3;
	END //

DELIMITER ;

#call
#Error code: 1062
#Duplicate entry '0' for key 'departments.uk_dept_name'
CALL InsertDataWithCondition();
SELECT @x; #x=2

#Delete this stored procedure
DROP PROCEDURE InsertDataWithCondition;

#Redefining stored procedures (considering wrong handlers)
DELIMITER //

CREATE PROCEDURE InsertDataWithCondition()
	BEGIN		
		#processing program
		#Mode 1
		#declare exit handler for 1062 set @pro_value = -1;
		#Mode 1
		#declare exit handler for sqlstate '23000' set @pro_value = -1;
		#Mode 3
		DECLARE duplicate_entry CONDITION FOR 1062;
		DECLARE EXIT HANDLER FOR duplicate_entry SET @pro_value = -1;
		
		SET @x = 1;
		INSERT INTO departments(department_name) VALUES('test');
		SET @x = 2;
		INSERT INTO departments(department_name) VALUES('test');
		SET @x = 3;
	END //

DELIMITER ;

#call
CALL InsertDataWithCondition();

SELECT @x, @pro_value;

3. Process control

It is impossible to solve complex problems through one SQL statement. We need to perform multiple SQL operations. The function of process control statement is to control the execution order of SQL statements in stored procedures. It is an essential part for us to complete complex operations. As long as the procedure is executed, the process is divided into three categories:

Sequential structure: the program is executed from top to bottom
Branch structure: the program selects and executes according to conditions, and selects one of two or more paths for execution
Loop structure: when the program meets certain conditions, it repeatedly executes a group of statements

There are three main types of process control statements for MySQL. Note: it can only be used to store programs.

Conditional judgment statement: IF statement and CASE statement
LOOP statements: LOOP, WHILE, and REPEAT statements
Jump statements: ITERATE and LEAVE statements

3.1 branch structure

3.1.1,IF

The syntax structure of the IF statement is:

IF Expression 1 THEN Operation 1
[ELSEIF Expression 2 THEN Operation 2]......
[ELSE operation N]
END IF

Execute the corresponding statement according to whether the result of the expression is TRUE or FALSE. Here, the content in "[]" is optional.

Features: ① different expressions correspond to different operations
② Use in begin end

③ ELSEIF is connected

give an example:

#Example 1
DELIMITER //

CREATE PROCEDURE test_if()
BEGIN	
	/*
	#Case 1
	#Declare local variables
	declare stu_name varchar(25);
	
	if stu_name is null 
		then select 'stu_name is null';
	end if;
	*/	
	
	/*	
	#Case 2: one out of two
	declare email varchar(25) default 'Tom@qq.com';
	
	if email is null 
		then select 'email is null';
	else
		select 'email is not null';
	end if;
	*/
	
	#Case 3: select one more
	DECLARE age INT DEFAULT 20;
	
	IF age > 40
		THEN SELECT 'Middle aged and elderly';
	ELSEIF age > 18
		THEN SELECT 'Young adults';
	ELSEIF age > 8
		THEN SELECT 'teenagers';
	ELSE
		SELECT 'baby';
	END IF;
	
END //

DELIMITER ;

#call
CALL test_if();

DROP PROCEDURE test_if;

#Example 2: declare the stored procedure "update_salary_by_eid1" and define the IN parameter emp_id, enter the employee number.
#Judge that if the employee's salary is less than 8000 yuan and the employment time is more than 5 years, the salary will be increased by 500 yuan; Otherwise, it will remain the same.
DELIMITER //

CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN
	DECLARE emp_sal DOUBLE;#Employee's salary
	DECLARE hire_year DOUBLE;#Years of employment
	
	SELECT salary, DATEDIFF(CURDATE(),hire_date)/365 INTO emp_sal,hire_year 
	FROM employees WHERE employee_id = emp_id;
	
	IF emp_sal < 8000 AND hire_year > 5
		THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
	END IF;
END //

DELIMITER ;

SET @emp_id = 
CALL update_salary_by_eid1(104);

SELECT * FROM employees WHERE employee_id = 104;

DROP PROCEDURE update_salary_by_eid1;

#Example 3: declare the stored procedure "update_salary_by_eid2" and define the IN parameter emp_id, enter the employee number.
#Judge that if the employee's salary is less than 9000 yuan and has been on the job for more than 5 years, the salary will be increased by 500 yuan; Otherwise, the salary will be increased by 100 yuan.
DELIMITER //

CREATE PROCEDURE update_salary_by_eid2(IN emp_id INT)
BEGIN
	DECLARE emp_sal DOUBLE;
	DECLARE hire_year DOUBLE;
	
	SELECT salary, DATEDIFF(CURDATE(), hire_date) INTO emp_sal, hire_year
	FROM employees
	WHERE employee_id = emp_id;
	
	IF emp_sal < 9000 AND hire_year >= 5
		THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
	ELSE
		UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
	END IF;
END //

DELIMITER ;

CALL update_salary_by_eid2(104);

SELECT * FROM employees WHERE employee_id IN (103,104);

DROP PROCEDURE update_salary_by_eid2;

#Example 4: declare the stored procedure "update_salary_by_eid3" and define the IN parameter emp_id, enter the employee number.
#Judge that if the employee's salary is less than 9000 yuan, the salary will be updated to 9000 yuan; If the salary is greater than or equal to 9000 yuan and
#If it is less than 10000 but the bonus ratio is NULL, the bonus ratio will be updated to 0.01; Other salary increases are 100 yuan.
DELIMITER //

CREATE PROCEDURE update_salary_by_eid3(IN emp_id INT)
BEGIN
	#Declare variable
	DECLARE emp_sal DOUBLE;
	DECLARE bonus DOUBLE;
	
	SELECT salary, commission_pct INTO emp_sal, bonus
	FROM employees
	WHERE employee_id = emp_id;
	
	IF emp_sal < 9000
		THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
	ELSEIF emp_sal < 10000 AND bonus IS NULL
		THEN UPDATE employees SET commission_pct = 0.01 WHERE employee_id = emp_id;
	ELSE
		UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
	END IF;
END //

DELIMITER ;

CALL update_salary_by_eid3(104);

SELECT * FROM employees WHERE employee_id IN (102,103,104);

3.1.2,CASE

Syntax structure of CASE statement 1:

#Case 1: similar to switch
CASE expression
WHEN Value 1 THEN Result 1 or statement 1(If it is a statement, you need to add a semicolon) 
WHEN Value 2 THEN Result 2 or statement 2(If it is a statement, you need to add a semicolon)
...
ELSE result n Or statement n(If it is a statement, you need to add a semicolon)
END [case](If it's on begin end Need to add case，If you put it select (not required later)

Syntax structure of CASE statement 2:

#Case 2: similar to multiple if
CASE 
WHEN Condition 1 THEN Result 1 or statement 1(If it is a statement, you need to add a semicolon) 
WHEN Condition 2 THEN Result 2 or statement 2(If it is a statement, you need to add a semicolon)
...
ELSE result n Or statement n(If it is a statement, you need to add a semicolon)
END [case](If it's on begin end Need to add case，If you put it select (not required later)

give an example:

#Example 1: basic use
DELIMITER //

CREATE PROCEDURE test_case()
BEGIN
	/*
	#Demo 1:case..when..then
	declare var int default 2;
	
	case var
		when 1 then select 'var = 1';
		when 2 then select 'var = 2';
		when 3 then select 'var = 3';
		else select 'other value';
	end case;
	*/
	
	#Demo 2: case when... Then
	DECLARE var1 INT DEFAULT 10;
	
	CASE 
	WHEN var1 >= 100 THEN SELECT 'Three digit';
	WHEN var1 >= 10 THEN SELECT 'Double digit';
	ELSE SELECT 'Single digit';
	END CASE;
END //

DELIMITER ;

CALL test_case();

DROP PROCEDURE test_case;

#Example 2: declare the stored procedure "update_salary_by_eid4" and define the IN parameter emp_id, enter the employee number.
#Judge that if the employee's salary is less than 9000 yuan, the salary will be updated to 9000 yuan; If the salary is more than or equal to 9000 yuan and less than 10000 yuan,
#However, if the bonus ratio is NULL, the bonus ratio will be updated to 0.01; Other salary increases are 100 yuan.
DELIMITER //

CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT)
BEGIN
	#Declaration of local variables
	DECLARE emp_sal DOUBLE;
	DECLARE bonus DOUBLE;
	
	SELECT salary, commission_pct INTO emp_sal, bonus
	FROM employees
	WHERE employee_id = emp_id;
	
	CASE
	WHEN emp_sal < 9000 
		THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
	WHEN emp_sal < 10000 AND bonus IS NULL 
		THEN UPDATE employees SET commission_pct = 0.01 WHERE employee_id = emp_id;
	ELSE 
		UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
	END CASE;
END //

DELIMITER ;

CALL update_salary_by_eid4(103);
CALL update_salary_by_eid4(104);
CALL update_salary_by_eid4(105);

SELECT * FROM employees WHERE employee_id IN (103,104,105);

DROP PROCEDURE update_salary_by_eid4;

#Example 3: declare stored procedure update_salary_by_eid5, define the IN parameter emp_id, enter the employee number.
#Judge the employment years of the employee. If it is 0 years, the salary will increase by 50; If it is one year, the salary will increase by 100;
#If it is 2 years, the salary will increase by 200; If it is 3 years, the salary will increase by 300; If it is 4 years, the salary will increase by 400; Other salary increases of 500.
DELIMITER //

CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)
BEGIN
	DECLARE hire_year DOUBLE;
	
	SELECT TRUNCATE(DATEDIFF(CURDATE(), hire_date)/365, 0) INTO hire_year
	FROM employees WHERE employee_id = emp_id;
	
	CASE hire_year
	WHEN 0 THEN UPDATE employees SET salary = salary + 50 WHERE employee_id = emp_id;
	WHEN 1 THEN UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
	WHEN 2 THEN UPDATE employees SET salary = salary + 200 WHERE employee_id = emp_id;
	WHEN 3 THEN UPDATE employees SET salary = salary + 300 WHERE employee_id = emp_id;
	WHEN 4 THEN UPDATE employees SET salary = salary + 400 WHERE employee_id = emp_id;
	ELSE UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
	END CASE;
END //

DELIMITER ;

CALL update_salary_by_eid5(101);

SELECT * FROM employees;

3.2 circulation structure

Compare three cycle structures:

1. The names of these three loops can be omitted, but if a loop control statement (LEAVE or ITERATE) is added to the loop, the name must be added.

2. LOOP: generally used to implement a simple "dead" LOOP

WHILE: judge first and then execute

REPEAT: execute first and then judge. Execute unconditionally at least once

3.2.1,LOOP

LOOP statements are used to repeat certain statements. The statements in LOOP are executed repeatedly until the LOOP is exited (using the LEAVE clause) and jump out of the LOOP process.

The basic format of the LOOP statement is as follows:

[loop_label:] LOOP
	Statement executed in a loop
END LOOP [loop_label]

Where, loop_label indicates the label name of the LOOP statement. This parameter can be omitted.

give an example:

DELIMITER //

CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
	DECLARE avg_sal DOUBLE;
	DECLARE loop_count INT DEFAULT 0;
	
	#Get the average salary of employees
	SELECT AVG(salary) INTO avg_sal FROM employees;
	
	loop_1:LOOP
		IF avg_sal > 12000
			THEN LEAVE loop_1;
		END IF;
		
		UPDATE employees SET salary = salary * 1.1;
		
		SELECT AVG(salary) INTO avg_sal FROM employees;
		
		SET loop_count = loop_count + 1;
		
	END LOOP loop_1;
	
	SET num = loop_count;
		
END //

DELIMITER ;

SET @num = 0;
CALL update_salary_loop(@num);
SELECT @num;

SELECT AVG(salary) FROM employees;

3.2.2,WHILE

WHILE statementcreates a circular procedure with conditional judgment. WHILE when executing a statement, first judge the specified expression. If it is true, execute the statement in the loop, otherwise exit the loop. The basic format of WHILE statement is as follows:

[while_label:] WHILE Cycle condition  DO
	Circulatory body
END WHILE [while_label];

while_label is the label name of the WHILE statement; If the result of the loop condition is true, the statement or statement group in the WHILE statement is executed until the loop condition is false and exits the loop.

give an example:

#Example 1:
DELIMITER //

CREATE PROCEDURE test_while()
BEGIN
	DECLARE num INT DEFAULT 1;
	
	WHILE num < 10 DO
		SET num = num + 1;
	END WHILE;
	
	SELECT num;
END //

DELIMITER;

CALL test_while();

#Example 2: when the market environment is bad, the company decides to temporarily reduce everyone's salary in order to tide over the difficulties.
#Declare the stored procedure "update_salary_while()", declare the OUT parameter num, and output the number of cycles.
#In the stored procedure, the salary will be reduced by 90%. Until the average salary of the whole company
#End at 5000. And count the number of cycles.
DELIMITER //

CREATE PROCEDURE update_salary_while(OUT num INT)
BEGIN
	DECLARE avg_sal DOUBLE;
	DECLARE while_count INT DEFAULT 0;
		
	SELECT AVG(salary) INTO avg_sal FROM employees;
	
	WHILE avg_sal > 5000 DO
		UPDATE employees SET salary = salary * 0.9;
		
		SET while_count = while_count + 1;
		
		SELECT AVG(salary) INTO avg_sal FROM employees;
	END WHILE;
	
	SET num = while_count;
END //

DELIMITER ;

CALL update_salary_while(@num);
SELECT @num;

3.2.3,REPEAT

REPEAT statement creates a loop procedure with conditional judgment. Unlike WHILE loop, REPEAT loop will first execute a loop, and then judge the expression in UNTIL. If the conditions are met, exit, that is, END REPEAT; If the condition is not met, the loop will continue to execute UNTIL the exit condition is met.

The basic format of REPEAT statement is as follows:

[repeat_label:] REPEAT
　　　　Statement of loop body
UNTIL Conditional expression to end the loop
END REPEAT [repeat_label]

repeat_label is the label name of REPEAT statement, which can be omitted; A statement or group of statements within a REPEAT statement is repeated until expr_condition is true.

give an example:

#Example 1
DELIMITER //

CREATE PROCEDURE test_repeat()
BEGIN
	DECLARE num INT DEFAULT 1;
	
	REPEAT
		SET num = num + 1;
	
		UNTIL num >= 10
		
	END REPEAT;
	
	SELECT num;
END //

DELIMITER ;

CALL test_repeat();


#Example 2: when the market environment became better, the company decided to give everyone a raise in order to reward them.
#Declare the stored procedure "update_salary_repeat()", declare the OUT parameter num, and output the number of cycles.
#In the stored procedure, the salary is increased by 1.15 times. Until the average of the whole company
#The salary reaches 13000. And count the number of cycles.
DELIMITER //

CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGIN
	DECLARE avg_sal DOUBLE;
	DECLARE repeat_count INT DEFAULT 0;
	
	SELECT AVG(salary) INTO avg_sal FROM employees;
	
	REPEAT
		SET repeat_count = repeat_count + 1;
		
		UPDATE employees SET salary = salary * 1.15;
		
		SELECT AVG(salary) INTO avg_sal FROM employees;
		
		UNTIL avg_sal >= 13000
		
	END REPEAT;
	
	SET num = repeat_count;
END //

DELIMITER ;

CALL update_salary_repeat(@num);
SELECT @num;

SELECT AVG(salary) FROM employees;

3.3. Jump statement

3.3.1,LEAVE

LEAVE statement: it can be used in a loop statement or in a program body wrapped with BEGIN and END to indicate the operation of jumping out of the loop or the program body. If you have experience in using process oriented programming language, you can understand LEAVE as break.

The basic format is as follows:

LEAVE Tagnames

Where the label parameter represents the flag of the loop. LEAVE is used with begin... End or loop.

give an example:

DELIMITER //

CREATE PROCEDURE leave_while(OUT num INT)
BEGIN
	DECLARE while_count INT DEFAULT 0;
	DECLARE avg_sal DOUBLE;
	
	SELECT AVG(salary) INTO avg_sal FROM employees;
	
	while_1:WHILE TRUE DO
		IF avg_sal <= 10000
			THEN LEAVE while_1;
		END IF;
		
		UPDATE employees SET salary = salary * 0.9;
		
		SET while_count = while_count + 1;
		
		SELECT AVG(salary) INTO avg_sal FROM employees;
		
	END WHILE;
	
	SET num = while_count;
END //

DELIMITER ;

CALL leave_while(@num);
SELECT @num;
SELECT AVG(salary) FROM employees;

3.3.2,ITERATE

ITERATE statement: it can only be used in LOOP statements (LOOP, REPEAT and WHILE statements) to restart the LOOP and turn the execution order to the beginning of the statement segment. If you have experience in using process oriented programming language, you can understand ITERATE as continue, which means "LOOP again".

The basic format of the statement is as follows:

ITERATE label

The label parameter indicates the flag of the loop. The ITERATE statement must precede the loop flag.

give an example:

/*
Example: define the local variable num, with the initial value of 0. Perform the num + 1 operation in the loop structure.

- If num < 10, continue the loop;
- If num > 15, exit the loop structure;
*/
DELIMITER //

CREATE PROCEDURE test_iterate()
BEGIN
	DECLARE num INT DEFAULT 0;
	
	loop_1:LOOP
		SET num = num + 1;
		
		IF num < 10
			THEN ITERATE loop_1;
		ELSEIF num > 15
			THEN LEAVE loop_1;
		END IF;
		
		SELECT 'test';
	
	END LOOP;
END //

DELIMITER ;

CALL test_iterate();

4. Cursor

4.1. What is a cursor (or cursor)

Although we can also return a record through the filter conditions WHERE and HAVING, or the keyword LIMIT that limits the returned record, we cannot locate a record forward, locate a record backward, or arbitrarily locate a record in the result set like a pointer, and process the recorded data.

At this time, the cursor can be used. Cursor, which provides a flexible operation mode, enables us to locate each record in the result set and operate on the data in the pointed record. Cursors give SQL, a collection oriented language, the ability of process oriented development.

In SQL, a cursor is a temporary database object that can point to a data row pointer stored in a database table. Here, the cursor acts as a pointer. We can operate the data row by operating the cursor.

Cursors in MySQL can be used in stored procedures and functions.

4.2 steps of using cursor

Cursors must be declared before declaring handlers, and variables and conditions must also be declared before declaring cursors or handlers.

If we want to use cursors, we generally need to go through four steps. The syntax for using cursors may vary slightly from DBMS to DBMS.

4.2.1 declare cursor

In MySQL, the DECLARE keyword is used to DECLARE cursors. The basic syntax is as follows:

DECLARE cursor_name CURSOR FOR select_statement;

This syntax applies to MySQL, SQL Server, DB2, and MariaDB. If Oracle or PostgreSQL is used, it needs to be written as:

DECLARE cursor_name CURSOR IS select_statement;

To use the SELECT statement to obtain the result set of data, and the traversal of the data has not started yet, SELECT here_ Statement represents a SELECT statement that returns a result set used to create a cursor.

4.2.2 open cursor

The syntax for opening a cursor is as follows:

OPEN cursor_name

After we define the cursor, if we want to use the cursor, we must open the cursor first. When the cursor is opened, the query result set of the SELECT statement will be sent to the cursor workspace to prepare for the subsequent cursor to read the records in the result set one by one. Note: the number of fields in the query result set of the cursor must be consistent with the number of variables after INTO. Otherwise, MySQL will prompt an error when the stored procedure is executed.

4.2.3 use cursor

The syntax is as follows:

FETCH cursor_name INTO var_name [, var_name] ...

The purpose of this sentence is to use cursor_name this cursor to read the current row and save the data to var_ In the variable name, the cursor pointer points to the next line. If the data row read by the cursor has multiple column names, assign values to multiple variable names after the INTO keyword.

Note: VAR_ The name must be defined before declaring the cursor.

Note: the number of fields in the query result set of the cursor must be consistent with the number of variables after INTO. Otherwise, MySQL will prompt an error when the stored procedure is executed.

4.2.4 close cursor

CLOSE cursor_name

If there is OPEN, there will be CLOSE, that is, OPEN and CLOSE cursors. When we finish using the cursor, we need to CLOSE the cursor. Because the cursor will occupy system resources, if it is not closed in time, the cursor will remain until the end of the stored procedure, affecting the efficiency of system operation. Closing the cursor will release the system resources occupied by the cursor.

After closing the cursor, we can no longer retrieve the data rows in the query results. If we need to retrieve, we can only open the cursor again.

4.3 examples

#For example, create a stored procedure "get_count_by_limit_total_salary()" and declare the IN parameter limit_ total_ salary，
#DOUBLE type; Declare the OUT parameter total_count, INT type. The function can accumulate the salary values of several employees with the highest salary,
#Until the total salary reaches the limit_ total_ The value of salary parameter returns the accumulated number of people to total_count. 
DELIMITER //

CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE, OUT total_count INT)
BEGIN
	#Declare local variables
	DECLARE sum_sal DOUBLE DEFAULT 0.0;#Record the total accumulated wages
	DECLARE emp_sal DOUBLE; #Record the salary of each employee
	DECLARE emp_count INT DEFAULT 0; #Record the cumulative number of people
	
	#declare cursor 
	DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
	
	#Open cursor
	OPEN emp_cursor;
	
	REPEAT
		#Use cursor
		FETCH emp_cursor INTO emp_sal;
		
		SET sum_sal = sum_sal + emp_sal;
		SET emp_count = emp_count + 1;
		
		UNTIL sum_sal >= limit_total_salary
		
	END REPEAT;
	
	SET total_count = emp_count;
	
	#4. Close the cursor
	CLOSE emp_cursor;
	
END //

DELIMITER ;

CALL get_count_by_limit_total_salary(200000,@total_count);
SELECT @total_count;

4.5 summary

Cursor is an important function of MySQL, which provides a perfect solution for reading the data in the result set one by one. Compared with realizing the same function at the application level, cursors can be used in stored programs, which is more efficient and simpler.

But at the same time, it will also bring some performance problems. For example, in the process of using cursors, data rows will be locked. In this way, when there is a large amount of business concurrency, it will not only affect the efficiency between businesses, but also consume system resources and cause insufficient memory. This is because cursors are processed in memory.

Suggestion: form the habit of closing after use, so as to improve the overall efficiency of the system.

5. A new feature of MySQL 8.0 -- persistence of global variables

In MySQL database, global variables can be set through SET GLOBAL statement. For example, to set the limit of server statement timeout, you can set the system variable max_execution_time to achieve:

SET GLOBAL MAX_EXECUTION_TIME=2000;

Variable values set using the SET GLOBAL statement take effect only temporarily. After the database restarts, the server will read the default value of the variable from the MySQL configuration file. In MySQL version 8.0, the setparse ` ` command is added. For example, set the maximum number of connections to the server to 1000:

SET PERSIST global max_connections = 1000;

MySQL will save the configuration of this command to the mysqld-auto.cnf file in the data directory. This file will be read at the next startup, and the default configuration file will be overwritten with the configuration.

6. Practice

Exercise 1: Variables

#Exercise 1: Variables
#0. Preparation
CREATE DATABASE test16_pro_func;

USE test16_pro_func; 

CREATE TABLE employees 
AS
SELECT * FROM atguigudb.`employees`; 
 
CREATE TABLE departments 
AS
SELECT * FROM atguigudb.`departments`; 
 
#Return without parameters 
#1. Create function get_count() returns the number of employees in the company 
SET GLOBAL log_bin_trust_function_creators = 1;
 
DELIMITER //
 
CREATE FUNCTION get_count()
RETURNS INT
 
BEGIN
	#Declare local variables
	DECLARE emp_count INT;
	
	SELECT COUNT(*) INTO emp_count FROM employees;
	
	RETURN emp_count;
END //
 
DELIMITER ;
 
SELECT get_count();
 
#There are parameters and returns 
#2. Create function ename_salary(), returns the salary of the employee according to the employee's name
DELIMITER //
 
CREATE FUNCTION ename_salary(emp_name VARCHAR(25))
RETURNS DOUBLE
BEGIN
	SET @sal = 0;
	
	SELECT salary INTO @sal FROM employees WHERE last_name = emp_name;
	
	RETURN @sal;
END //
 
DELIMITER ;

SELECT ename_salary('Abel');
 
#3. Create the function dept_sal(), returns the average salary of the department according to the Department name
DELIMITER //

CREATE FUNCTION dept_sal(dept_name VARCHAR(15))
RETURNS DOUBLE
BEGIN
	DECLARE avg_sal DOUBLE;
	
	SELECT AVG(salary) INTO avg_sal
	FROM employees e JOIN departments d
	ON e.department_id = d.department_id
	WHERE d.department_name = dept_name;
	
	RETURN avg_sal;
END //

DELIMITER ;

SELECT dept_sal('Marketing');

DROP FUNCTION dept_sal;
 
 
#4. Create function add_float(), pass in two floats and return the sum of them
DELIMITER //

CREATE FUNCTION add_float(a FLOAT, b FLOAT)
RETURNS FLOAT
BEGIN
	DECLARE sum_val FLOAT;
	
	SET sum_val = a + b;
	
	RETURN sum_val;
END //

DELIMITER ;

SET @a := 1.1; 
SET @b = 6.8;
SELECT add_float(@a, @b);

Exercise 2: process control

#Exercise 2: process control
#1. Create function test_if_case(), which realizes the incoming score. If the score is > 90, it returns A; if the score is > 80, it returns B; if the score is > 60, it returns C; otherwise, it returns D
#Requirements: use if structure and case structure respectively

#Mode 1: IF
DELIMITER //

CREATE FUNCTION test_if_case1(score DOUBLE)
RETURNS CHAR
BEGIN
	DECLARE score_level CHAR;
	
	IF score > 90
		THEN SET score_level = 'A';
	ELSEIF score > 80
		THEN SET score_level = 'B';
	ELSEIF score > 60
		THEN SET score_level = 'C';
	ELSE
		SET score_level = 'D';
	END IF;
	
	RETURN score_level;
END //

DELIMITER ;

SELECT test_if_case1(56);

DROP FUNCTION test_if_case1;
#Mode 2: CASE
DELIMITER //

CREATE FUNCTION test_if_case1(score DOUBLE)
RETURNS CHAR
BEGIN
	DECLARE score_level CHAR;
	
	CASE
	WHEN score > 90
		THEN SET score_level = 'A';
	WHEN score > 80
		THEN SET score_level = 'B';
	WHEN score > 60
		THEN SET score_level = 'C';
	ELSE
		SET score_level = 'D';
	END CASE;
	
	RETURN score_level;
END //

DELIMITER ;

SELECT test_if_case1(76);

#2. Create stored procedure test_if_pro(), pass in the salary value. If the salary value is less than 3000, delete the employee whose salary is this value,
# If 3000 < = salary value < = 5000, the salary of the employee who modifies this salary value will increase by 1000, otherwise, the salary will increase by 500
DELIMITER //

CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN 
	IF sal < 3000
		THEN DELETE FROM employees WHERE salary = sal;
	ELSEIF sal <= 5000
		THEN UPDATE employees SET salary = salary + 1000 WHERE salary = sal;
	ELSE
		UPDATE employees SET salary = salary + 500 WHERE salary = sal;
	END IF;
END //

DELIMITER ;

SELECT * FROM employees;

CALL test_if_pro(2800);

#3. Create stored procedure insert_data(), pass IN INT type variable insert with parameter IN_ Count, which can be added to the admin table
#Batch insert_count records 
CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(25) NOT NULL,
user_pwd VARCHAR(35) NOT NULL
);

SELECT * FROM admin;

DELIMITER //

CREATE PROCEDURE insert_data(IN insert_count INT)
BEGIN
	#Declare variable
	DECLARE init_count INT DEFAULT 1;
	
	WHILE init_count <= insert_count DO
		INSERT INTO admin(user_name, user_pwd) VALUES(CONCAT('zhang-', init_count), ROUND(RAND()*1000000));
		
		SET init_count = init_count + 1;
	END WHILE;
END //

DELIMITER ;

CALL insert_data(5);

DROP PROCEDURE insert_data;

Exercise 3: using cursors

#Exercise 3: using cursors
#Create stored procedure update_salary(), INT type variable dept with parameter 1 as IN_ id, Department id;
#Parameter 2 is INT type variable change of IN_ sal_ Count, indicating the number of employees whose salary is to be adjusted. Query the employee information of the specified id department,
#In ascending order of salary, according to hire_date, change before adjustment_ sal_ The salary of count employees is as follows.
/*
hire_date		salary
<1995			*1.2
>=1995 && <= 1998	*1.15
>1998 && <= 2001	*1.10
>2001			*1.05
*/

DELIMITER //

CREATE PROCEDURE update_salary(IN dept_id INT, IN change_sal_count INT)
BEGIN
	#Declare variable
	DECLARE emp_id INT ;#Record employee id
	DECLARE emp_hire_date DATE; #Record the employee's employment time
	DECLARE init_count INT DEFAULT 1; #Used to represent the initialization condition of the loop structure
	DECLARE add_sal_rate DOUBLE ; #Record the percentage of salary increase

	#declare cursor 
	DECLARE emp_cur CURSOR FOR 
	SELECT employee_id, hire_date
	FROM employees 
	WHERE department_id = dept_id 
	ORDER BY salary ASC;
	
	#Use cursor
	OPEN emp_cur;
	
	WHILE init_count <= change_sal_count DO
		#Open cursor
		FETCH emp_cur INTO emp_id, emp_hire_date;
		
		#Percentage of salary increase
		IF (YEAR(emp_hire_date) < 1995)
			THEN SET add_sal_rate = 1.2;
		ELSEIF(YEAR(emp_hire_date) <= 1998)
			THEN SET add_sal_rate = 1.15;
		ELSEIF(YEAR(emp_hire_date) <= 2001)
			THEN SET add_sal_rate = 1.10;
		ELSE
			SET add_sal_rate = 1.05;
		END IF;
		
		#Salary increase operation
		UPDATE employees SET salary = salary * add_sal_rate
		WHERE employee_id = emp_id;
		
		#Update of iteration conditions
		SET init_count = init_count + 1;
	END WHILE;
	
	#Close cursor
	CLOSE emp_cur;
END //

DELIMITER ;

SELECT employee_id, hire_date, salary
FROM employees 
WHERE department_id = 50
ORDER BY salary ASC;

CALL update_salary(50, 3);

Keywords: Database MySQL

Added by thepriest on Sun, 05 Dec 2021 05:47:54 +0200

Programming VIP

MySQL variables, process control and cursors

1. Variable

1.1 system variables

1.1.1 classification of system variables

1.1.2. View system variables

1.2 user scalar

1.2.1 classification of user variables

1.2.2 session user variables

1.2.3 local variables

1.2.4 comparing session user variables with local variables

2. Define conditions and handlers

2.1 definition conditions

2.2. Define processing procedures

2.4 examples

3. Process control

3.1 branch structure

3.1.1,IF

3.1.2,CASE

3.2 circulation structure

3.2.1,LOOP

3.2.2,WHILE

3.2.3,REPEAT

3.3. Jump statement

3.3.1,LEAVE

3.3.2,ITERATE

4. Cursor

4.1. What is a cursor (or cursor)

4.2 steps of using cursor

4.2.1 declare cursor

4.2.2 open cursor

4.2.3 use cursor

4.2.4 close cursor

4.3 examples

4.5 summary

5. A new feature of MySQL 8.0 -- persistence of global variables

6. Practice

Exercise 1: Variables

Exercise 2: process control

Exercise 3: using cursors

Popular Keywords