The best mysql-16 in history (variable, process control, cursor)

Posted by xshanelarsonx on Thu, 09 Dec 2021 11:04:29 +0100

Variable, process control, cursor

                                     Personal blog: www.xiaobeigua.icu

1. Variables

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 https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html 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 viewing 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:

SHOW GLOBAL VARIABLES LIKE 'admin_%';

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;

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:

SELECT @@global.autocommit;
SET GLOBAL autocommit=0;

SELECT @@session.tx_isolation;
SET @@session.tx_isolation='read-uncommitted';

SET GLOBAL max_connections = 1000;
SELECT @@global.max_connections;

1.2 user variables

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 connected 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

give an example

SET @a = 1;

SELECT @a;

SELECT @num := COUNT(*) FROM employees;

SELECT @num;

SELECT AVG(salary) INTO @avgsalary FROM employees;

SELECT @avgsalary;

SELECT @big;  #When you view an undeclared variable, you get a NULL value

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

give an example:

DECLARE　myparam　INT　DEFAULT 100;

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;

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

DELIMITER //

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

DELIMITER ;

Example 2: declare two variables, sum and print (implemented by session user variables and local variables respectively)

#Method 1: use user variables
SET @m=1;
SET @n=1;
SET @sum=@m+@n;

SELECT @sum;

#Method 2: use local variables
DELIMITER //

CREATE PROCEDURE add_value()
BEGIN
	#local variable
	DECLARE m INT DEFAULT 1;
	DECLARE n INT DEFAULT 3;
	DECLARE SUM INT;
	
	SET SUM = m+n;
	SELECT SUM;
END //

DELIMITER ;

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 receives the employee id and uses the OUT parameter dif_salary outputs salary gap results.

#statement
DELIMITER //

CREATE PROCEDURE different_salary(IN emp_id INT,OUT dif_salary DOUBLE)
BEGIN
	#Declare local variables
	DECLARE emp_sal,mgr_sal DOUBLE DEFAULT 0.0;
	DECLARE mgr_id INT;
	
	SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
	SELECT manager_id INTO 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 ;

#call
SET @emp_id = 102;
CALL different_salary(@emp_id,@diff_sal);


#see
SELECT @diff_sal;

1.2.4 comparing session user variables with local variables

			  Scope					Define location				  grammar
 Session user variable	  Current session				   Anywhere in the conversation				plus@Symbol, no type specified
 local variable	   Define its BEGIN END in 		BEGIN END The first sentence		  Generally do not add@,Type needs to be specified

2. Define conditions and handling procedures

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 case analysis

Case study: create a stored procedure named "updatedatacondition". The code is as follows:

DELIMITER //

CREATE PROCEDURE UpdateDataNoCondition()
	BEGIN
		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 stored procedure:

mysql> CALL UpdateDataNoCondition();
ERROR 1048 (23000): Column 'email' cannot be null

mysql> SELECT @x;
+------+
| @x   |
+------+
|   1  |
+------+
1 row in set (0.00 sec)

As you can see, the value of the @x variable is 1. Combined with the SQL statement code for creating the stored procedure, it can be concluded that there are no conditions and handlers defined in the stored procedure, and when the SQL statement executed in the stored procedure reports an error, the MySQL database will throw an error, exit the current SQL logic, and do not continue to execute downward.

2.2 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.

Example 1: define the error name of "Field_Not_Be_NULL", which corresponds to the error type of "ERROR 1048 (23000)" in MySQL that violates non null constraints.

#Using MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;

#Using sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

Example 2: define the "ERROR 1148(42000)" error with the name command_not_allowed.

#Using MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;

#Using sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';

2.3 definition handler

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.

There are several ways to define the handler. The code is as follows:

#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 EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

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

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

2.4 case resolution

In the stored procedure, define a handler to capture sqlstate_value, when MySQL is encountered_ error_ When the code value is 1048, the CONTINUE operation is performed and @ proc_ The value of value is set to - 1.

DELIMITER //

CREATE PROCEDURE UpdateDataNoCondition()
	BEGIN
		#Define handler
		DECLARE CONTINUE HANDLER FOR 1048 SET @proc_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 procedure:

mysql> CALL UpdateDataWithCondition();
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @x,@proc_value;
+------+-------------+
| @x   | @proc_value |
+------+-------------+
|    3 |       	 -1  |
+------+-------------+
1 row in set (0.00 sec)

give an example:

Create a stored procedure named "InsertDataWithCondition". The code is as follows.

In the stored procedure, define a handler to capture sqlstate_value value, when sqlstate is encountered_ When the value is 23000, the EXIT operation is executed and @ proc_ The value of value is set to - 1.

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

ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE(department_id);

DELIMITER //

CREATE PROCEDURE InsertDataWithCondition()
	BEGIN
		DECLARE duplicate_entry CONDITION FOR SQLSTATE '23000' ;
		DECLARE EXIT HANDLER FOR duplicate_entry SET @proc_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 stored procedure:

mysql> CALL InsertDataWithCondition();
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @x,@proc_value;
+------+-------------+
| @x   | @proc_value |
+------+-------------+
|    2 |       	 -1  |
+------+-------------+
1 row in set (0.00 sec)

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 IF of branch structure

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; ② they are used in begin end

Example 1:

IF val IS NULL 
	THEN SELECT 'val is null';
ELSE SELECT 'val is not null';

END 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_salary DOUBLE;
	DECLARE hire_year DOUBLE;

	SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;

	SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year
	FROM employees WHERE employee_id = emp_id;

	IF emp_salary < 8000 AND hire_year > 5
	THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
	END IF;
END //


DELIMITER ;

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_salary DOUBLE;
	DECLARE hire_year DOUBLE;

	SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;

	SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year
	FROM employees WHERE employee_id = emp_id;

	IF emp_salary < 8000 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 ;

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 less than 10000 yuan, but the bonus proportion is NULL, the bonus proportion 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 emp_salary DOUBLE;
	DECLARE bonus DECIMAL(3,2);

	SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
	SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;

	IF emp_salary < 9000
		THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
	ELSEIF emp_salary < 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 ;

3.2 CASE of branch structure

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)

Example 1:

Use the first format of the CASE process control statement to judge whether the val value is equal to 1, 2, or both.

CASE val
　　　WHEN 1 THEN SELECT 'val is 1';
　　　WHEN 2 THEN SELECT 'val is 2';
　　　ELSE SELECT 'val is not 1 or 2';
END CASE;

Example 2:

Use the second format of the CASE process control statement to judge whether val is empty, less than 0, greater than 0 or equal to 0.

CASE
	WHEN val IS NULL THEN SELECT 'val is null';
	WHEN val < 0 THEN SELECT 'val is less than 0';
	WHEN val > 0 THEN SELECT 'val is greater than 0';
	ELSE SELECT 'val is 0';
END CASE;

**Example 3: * * 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 greater than or equal to 9000 yuan and less than 10000 yuan, 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_eid4(IN emp_id INT)
BEGIN
	DECLARE emp_sal DOUBLE;
	DECLARE bonus DECIMAL(3,2);

	SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
	SELECT commission_pct INTO 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 ;

Example 4: 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 emp_sal DOUBLE;
	DECLARE hire_year DOUBLE;

	SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
	
	SELECT ROUND(DATEDIFF(CURDATE(),hire_date)/365) 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 ;

3.3 LOOP of cycle structure

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.

Example 1:

LOOP statement is used for LOOP operation. When the id value is less than 10, the LOOP process will be repeated.

DECLARE id INT DEFAULT 0;
add_loop:LOOP
	SET id = id +1;
	IF id >= 10 THEN LEAVE add_loop;
	END IF;

END LOOP add_loop;

**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_loop()", declare the OUT parameter num, and output the number of cycles. In the stored procedure, the loop is realized to increase everyone's salary, which is 1.1 times the original salary. Until the average salary of the whole company reaches 12000. And count the number of cycles.

DELIMITER //

CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
	DECLARE avg_salary DOUBLE;
	DECLARE loop_count INT DEFAULT 0;
	
	SELECT AVG(salary) INTO avg_salary FROM employees;
	
	label_loop:LOOP
		IF avg_salary >= 12000 THEN LEAVE label_loop;
		END IF;
		
		UPDATE employees SET salary = salary * 1.1;
		SET loop_count = loop_count + 1;
		SELECT AVG(salary) INTO avg_salary FROM employees;
	END LOOP label_loop;
	
	SET num = loop_count;

END //

DELIMITER ;

3.4 WHILE of cycle structure

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.

Example 1:

In the example of WHILE statement, when the i value is less than 10, the loop process will be executed repeatedly, and the code is as follows:

DELIMITER //

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

DELIMITER ;
#call
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 reaches 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 ;

3.5 REPEAT of cycle structure

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.

Example 1:

DELIMITER //

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

DELIMITER ;

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 salary of the whole company 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
		UPDATE employees SET salary = salary * 1.15;
		
		SET repeat_count = repeat_count + 1;
		
		SELECT AVG(salary) INTO avg_sal FROM employees;
	UNTIL avg_sal >= 13000
	END REPEAT;
	
	SET num = repeat_count;
		
END //

DELIMITER ;

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.6 LEAVE statement of jump statement

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.

Example 1: create a stored procedure "leave_begin()" and declare the IN parameter num of INT type. Tag BEGIN... END and use the IF statement IN BEGIN... END to determine the value of the num parameter.

If num < = 0, use the LEAVE statement to exit BEGIN... END;
If num=1, query the average salary in the employees table;
If num=2, query the minimum salary in the "employees" table;
If num > 2, query the maximum salary in the "employees" table.

After the IF statement, query the total number of employees in the "employees" table.

DELIMITER //

CREATE PROCEDURE leave_begin(IN num INT)

	begin_label: BEGIN
		IF num<=0 
			THEN LEAVE begin_label;
		ELSEIF num=1 
			THEN SELECT AVG(salary) FROM employees;
		ELSEIF num=2 
			THEN SELECT MIN(salary) FROM employees;
		ELSE 
			SELECT MAX(salary) FROM employees;
		END IF;
		
		SELECT COUNT(*) FROM employees;
	END //


DELIMITER ;

Example 2:

When the market environment is bad, the company decided to temporarily reduce everyone's salary in order to tide over the difficulties. Declare the stored procedure "leave_while()", declare the OUT parameter num and output the number of cycles. Use the WHILE cycle in the stored procedure to reduce everyone's salary to 90% of the original salary until the average salary of the whole company is less than or equal to 10000, and count the number of cycles.

DELIMITER //
CREATE PROCEDURE leave_while(OUT num INT)

BEGIN 
	#
	DECLARE avg_sal DOUBLE;#Record average salary
	DECLARE while_count INT DEFAULT 0; #Record the number of cycles
	
	SELECT AVG(salary) INTO avg_sal FROM employees; #① Initialization condition
	
	while_label:WHILE TRUE DO  #② Cycle condition
		
		#③ Circulatory body
		IF avg_sal <= 10000 THEN
			LEAVE while_label;
		END IF;
		
		UPDATE employees SET salary  = salary * 0.9;
		SET while_count = while_count + 1;
		
		#④ Iterative condition
		SELECT AVG(salary) INTO avg_sal FROM employees;
	
	END WHILE;
	
	#assignment
	SET num = while_count;

END //

DELIMITER ;

3.7 ITERATE statement of jump statement

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.

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;
	
	my_loop:LOOP
		SET num = num + 1;
	
		IF num < 10 
			THEN ITERATE my_loop;
		ELSEIF num > 15 
			THEN LEAVE my_loop;
		END IF;
	
		SELECT 'Shang Silicon Valley: let the world have no difficult technologies';
	
	END LOOP my_loop;

END //

DELIMITER ;

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.

For example, we queried the employees whose salary is higher than 15000 in the employees data table:

SELECT employee_id,last_name,salary FROM employees
WHERE salary > 15000;

Here, we can operate the data row through the cursor, as shown in the figure. At this time, the row where the cursor is located is the record of "108". We can also scroll the cursor on the result set and point to any row in the result set.

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.

The first step is to declare the 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.

For example:

DECLARE cur_emp CURSOR FOR 
SELECT employee_id,salary FROM employees;

DECLARE cursor_fruit CURSOR FOR 
SELECT f_name, f_price FROM fruits ;

Step 2: open the 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.

OPEN　cur_emp ;

Step 3: use the cursor (get data from the 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.

FETCH　cur_emp INTO emp_id, emp_sal ;

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.

Step 4: close the 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.

CLOSE　cur_emp;

4.3 examples

Create the 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 sum_salary DOUBLE DEFAULT 0;  #Record the accumulated total salary
	DECLARE cursor_salary DOUBLE DEFAULT 0; #Record a salary value
	DECLARE emp_count INT DEFAULT 0; #Record the number of cycles
	#Define cursor
	DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
	#Open cursor
	OPEN emp_cursor;
	
	REPEAT
		#Use cursor (get data from cursor)
		FETCH emp_cursor INTO cursor_salary;
		
		SET sum_salary = sum_salary + cursor_salary;
		SET emp_count = emp_count + 1;
		
		UNTIL sum_salary >= limit_total_salary
	END REPEAT;
	
	SET total_count = emp_count;
	#Close cursor
	CLOSE emp_cursor;
	
END //

DELIMITER ;

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.

Supplement: 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.
MySQL version 8.0 adds the SET PERSIST command. 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.

give an example:

View global variable Max_ The values of connections are as follows:

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

Set global variable Max_ Value of connections:

mysql> set persist max_connections=1000;
Query OK, 0 rows affected (0.00 sec)

Restart the MySQL server and query Max again_ Value of connections:

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 1000  |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

Topics: Database MySQL

Programmer Think

The best mysql-16 in history (variable, process control, cursor)

Variable, process control, cursor

1. Variables

1.1 system variables

1.1.1 classification of system variables

1.1.2 viewing system variables

1.2 user variables

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 handling procedures

2.1 case analysis

2.2 definition conditions

2.3 definition handler

2.4 case resolution

3. Process control

3.1 IF of branch structure

3.2 CASE of branch structure

3.3 LOOP of cycle structure

3.4 WHILE of cycle structure

3.5 REPEAT of cycle structure

3.6 LEAVE statement of jump statement

3.7 ITERATE statement of jump statement

4. Cursor

4.1 what is a cursor (or cursor)

4.2 steps of using cursor

4.3 examples

4.5 summary

Supplement: a new feature of MySQL 8.0 - persistence of global variables

Hot Topics