MySQL– Variables, flow control, and cursors

1. The variable

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

In the 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. During MySQL service startup and MySQL service instance generation, MySQL assigns values to system variables in MySQL server memory that define properties and characteristics of the current MySQL service instance. The values of these system variables are either the default values of the parameters when MySQL is compiled, or the parameter values in configuration files (such as my.ini, etc.). You can through https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html to check the MySQL documentation system variables.

System variables are divided into global system variables (the global keyword needs to be added) and session system variables (the session keyword needs to be added). Sometimes the global system variables are referred to as global variables, and sometimes the session system variables are called local variables. If no, the default session level. Static variables (whose values cannot be dynamically changed using set during the running of a MySQL service instance) are special global system variables.

After each MySQL client successfully connects to the MySQL server, a corresponding session is generated. During a session, the MySQL service instance generates session system variables corresponding to the session in MySQL server memory. The initial values of these session system variables are copies of the values of the global system variables. The diagram below:

  • Global system variables are valid for all sessions (connections), butCannot restart across
  • Session system variables are valid only for the current session (connection). During a session, the modification of a session system variable does not affect the value of the same session system variable in other sessions.
  • A change in the value of a global system variable in session 1 causes a change in the value of the same global system variable in session 2.

In MySQL, some system variables must be global. For example, max_connections is used to limit the maximum number of connections to the server. Some system variable scopes can be both global and session. For example, character_set_client is used to set the character set of the client. Some system variables can only be scoped to the current session, for example, 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; orSHOW VARIABLES;
Copy the code
View the partial system variables that meet the criteria.
SHOW GLOBAL VARIABLES LIKE '% identifier %';

View the partial session variables that meet the criteria
SHOW SESSION VARIABLES LIKE '% identifier %';
Copy the code

For example:

SHOW GLOBAL VARIABLES LIKE 'admin_%';
Copy the code
  • View the specified system variable

As the 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. “@@” marks the session system variable first, and if the session system variable does not exist, it marks the global system variable.

Check the value of the specified system variable
SELECT@@global. Variable name;# check the value of the specified session variable
SELECT@@session. Variable name;# or
SELECT@@ Variable name;Copy the code
  • Modify the value of a system variable

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

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

Method 2: When the MySQL service is running, run the set command to reset the values of system variables

Assign to a system variable
# Method 1:
SET@@global. Variable name = variable value;# Method 2:
SET GLOBALVariable name = variable value;Assign to a session variable
# Method 1:
SET@@session. Variable name = variable value;# Method 2:
SET SESSIONVariable name = variable value;Copy the code

For example:

SELECT @@global.autocommit;
SET GLOBAL autocommit=0;
Copy the code
SELECT @@session.tx_isolation;
SET @@session.tx_isolation='read-uncommitted';
Copy the code
SET GLOBAL max_connections = 1000;
SELECT @@global.max_connections;
Copy the code

1.2 User Variables

1.2.1 Classification of user variables

User variables are user-defined. As the MySQL coding specification, user variables in MySQL start with an “@”. According to different scope, it can be divided into session user variable and local variable.

  • Session user variables: The scope, like session variables, is only valid for the current connected session.

  • Local variables: Valid only in BEGIN and END 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 [FROMDengZi];SELECTexpressionINTO@user variable [FROMDengZi];Copy the code
  • View the value of a user variable (view, compare, calculate, etc.)
SELECT@user variableCopy the code
  • For example,
SET @a = 1;

SELECT @a;
Copy the code
SELECT @num: =COUNT(*) FROM employees;

SELECT @num;
Copy the code
SELECT AVG(salary) INTO @avgsalary FROM employees;

SELECT @avgsalary;
Copy the code
SELECT @big;  When you view an undeclared variable, you get a NULL value
Copy the code

1.2.3 Local variables

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

Scope: just define it in BEGIN… The END of the effective

Position: can only be placed in BEGIN… “END” and only in the first sentence

BEGIN
	Declare local variables
	DECLAREThe variable name1Variable data type [DEFAULTVariable default value];DECLAREThe variable name2The variable name3. Variable data type [DEFAULTVariable default value];# Assign values to local variables
	SETThe variable name1= value;SELECT 值 INTOThe variable name2 [FROMClause];Check the value of the local variable
	SELECTvariable1, the variable2, the variable3;
END

Copy the code

1. Define variables

DECLAREVariable name type [defaultValue];If there is no DEFAULT clause, the initial value is NULL
Copy the code

For example:

DECLAREmyparamINT　DEFAULT 100;
Copy the code

2. Variable assignment

Method 1: Generally used to assign simple values

SETVariable name = value;SETVariable name := value;Copy the code

Method 2: Generally used to assign field values in the table

SELECTField name or expressionINTOThe variable nameFROMTable;Copy the code

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

SELECTLocal variable name;Copy the code

Example 1: Declare a local variable and assign the value to last_name and salary of the employees table where employee_id is 102

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 ;
Copy the code

Example 2: Declare two variables, sum and print them (using session user variables and local variables respectively)

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

SELECT @sum;
Copy the code
# Method 2: Use local variables
DELIMITER //

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

DELIMITER ;
Copy the code

Example 3: Create the stored procedure “different_salary” to query the salary difference between an employee and his boss. Use the IN parameter emp_id to receive the employee ID, and use the OUT parameter dif_salary to output the salary difference result.

# the 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);


# check
SELECT @diff_sal;
Copy the code

1.2.4 Comparing session user variables with local variables

Scope definition Location syntax Session user variable The @ sign is added anywhere in the current session. The type is not specifiedCopy the code

2. Define conditions and handlers

A condition defines in advance the problems that may be encountered during the execution of a program. A handler defines what should be done if a problem is encountered and ensures that the stored procedure or function can continue to execute if it encounters warnings or errors. This can enhance the ability of the stored program to handle problems and prevent the program from stopping unexpectedly.

Note: Defining conditions and handlers are supported in stored procedures and stored functions.

2.1 Case Analysis

Example: Create a stored procedure named “UpdateDataNoCondition”. 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 ;
Copy the code

Calling a 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)

Copy the code

And you can see that the at sign x variable has a value of 1. Combined with the SQL statement code of the stored procedure, it can be concluded that conditions and handlers are not defined in the stored procedure. When an ERROR occurs in the SQL statement executed in the stored procedure, the MySQL database will throw an error and exit the current SQL logic.

2.2 Defining Conditions

Defining conditions is to name error codes in MySQL to help store program code more clearly. It associates an error name with a specified error condition. This name can then be used in DECLARE HANDLER statements that define handlers.

Define conditions using DECLARE statements in the following syntax:

DECLAREError name CONDITIONFORError code (or error condition)Copy the code

Error code description:

  • MySQL_error_codeandsqlstate_valueCan represent MySQL error.
    • MySQL_error_code is the numeric type error code.
    • Sqlstate_value is a string error code of length 5.
  • For example, in ERROR 1418 (HY000), 1418 is MySQL_error_code and ‘HY000’ is SQLSTATE_value.
  • For example, in ERROR 1142 (42000), 1142 is MySQL_error_code and ‘42000’ is SQLSTATE_value.

Example 1: Define the Field_Not_Be_NULL ERROR name to correspond to ERROR type 1048 (23000) in MySQL.

# use MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;

# use sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';
Copy the code

Example 2: Define “ERROR 1148(42000)” as command_not_allowed.

# use MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;

# use sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
Copy the code

2.3 Defining handlers

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

DECLAREhandlingHANDLER FORError type handling statementCopy the code
  • handling: The processing mode can be CONTINUE, EXIT, or UNDO.
    • CONTINUE: indicates that an error occurs and the execution continues.
    • EXIT: Exits immediately if an error occurs.
    • UNDO: Indicates that the previous operation is reversed after an error occurs. MySQL does not support this operation.
  • Wrong type(conditions) can have the following values:
    • SQLSTATE 'String error code': represents an error code of type SQLSTATE_value of length 5.
    • MySQL_error_code: matching numeric type error code;
    • Wrong name: DECLARE… The name of the error CONDITION defined by 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 that were NOT caught by SQLWARNING or NOT FOUND;
  • Processing statement: If any of the above conditions occurs, the corresponding processing mode is adopted and the specified processing statement is executed. Statements can be like”SET variable = value“Simple statements like this can also be usedBEGIN ... ENDCompound statement written.

There are several ways to define a handler, as follows:

# method 1: capture sqlSTATE_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

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

# method 3: Define the condition and call it later
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';
Copy the code

2.4 Case Resolution

In the stored procedure, define a handler that captures the SQLSTATE_value value, and when you encounter a MySQL_error_code value of 1048, execute the CONTINUE operation and set the @proc_value value 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 ;
Copy the code

Call procedure:

mysql> CALLUpdateDataWithCondition(); Query OK, 0 rows affected (0.01sec) mysql>SELECT @x,@proc_value;
+------+-------------+
| @x   | @proc_value |
+------+-------------+| | | - 1 + 3------+-------------+
1 row in set (0.00 sec)

Copy the code

For example:

Create a stored procedure named “InsertDataWithCondition” as follows.

In the stored procedure, define a handler that captures the SQLSTATE_value, and when it encounters a SQLSTATE_value of 23000, perform an EXIT operation and set the @proc_value value to -1.

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

ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE(department_id);
Copy the code
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 ;
Copy the code

Calling a stored procedure:

mysql> CALLInsertDataWithCondition(); Query OK, 0 rows affected (0.01sec) mysql>SELECT @x,@proc_value;
+------+-------------+
| @x   | @proc_value |
+------+-------------+| | | - 1 + 2------+-------------+
1 row in set (0.00 sec)

Copy the code

3. Process control

Solving complex problems cannot be done in a single SQL statement; we need to perform multiple SQL operations. The function of flow control statement is to control the execution sequence of SQL statements in the stored procedure. It is an essential part for us to complete complex operations. As long as the program is executed, the flow falls into three broad categories:

  • Sequential structure: Programs are executed from top to bottom
  • Branching structure: The program selects one of two or more paths for execution based on conditions
  • Loop structure: Executes a set of statements repeatedly if a program meets certain conditions

There are three main types of flow control statements for MySQL. Note: Can only be used for stored programs.

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

3.1 IF of branching structure

  • The syntax of an IF statement is:
IF (1, 1) THEN (1, 2) THEN (2, 2) N] [the ELSE operationEND IF
Copy the code

Executes a statement depending on whether the result of the expression is TRUE or FALSE. The contents of “[]” are optional.

  • Features: ① Different expressions correspond to different operations. ② Used in begin end

  • Example 1:

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

END IF;
Copy the code

  • Example 2: Declare the stored procedure update_salary_by_eid1, define the IN parameter emp_id, and enter the employee id. If the salary of the employee is less than 8000 yuan and the employee has been working for more than 5 years, the salary will be increased by 500 yuan; Otherwise it stays 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 ;
Copy the code

  • Example 3: Declare the stored procedure update_salary_by_eid2, define the IN parameter emp_id, and enter the employee id. If the salary of the employee is less than 9000 yuan and the employee has been working for more than 5 years, the salary will be increased by 500 yuan; Otherwise you get a 100 yuan raise.

    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 ;
Copy the code

  • Example 4: Declare the stored procedure update_salary_by_eid3, define the IN parameter emp_id, and enter the employee id. If the salary of the employee 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, but the bonus ratio is NULL, the bonus ratio will be updated to 0.01; Others get a raise of 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 ;
Copy the code

3.2 CASE of branch structure

Syntax structure of CASE statement 1:

# Case 1: Similar to switchCASE expression WHEN (1) THEN (1) THEN (2) THEN (2) THEN (2) THEN (2)... ELSE result n or statement n(semicolon if statement)END [caseIf it is placed inbegin endNeed to addcase, if placed inselectNot needed in the back)Copy the code

Syntax structure of CASE statement 2:

# Case 2: Similar to multiple IfsCASE WHEN condition 1 THEN condition 1 THEN condition 2 THEN condition 2 THEN condition 2 THEN condition 2... ELSE result n or statement n(semicolon if statement)END [caseIf it is placed inbegin endNeed to addcase, if placed inselectNot needed in the back)Copy the code
  • Example 1:

Use the first form of the CASE flow control statement to determine that val is equal to 1, equal to 2, or both.

CASE val WHEN 1 THENSELECT 'val is 1'; WHEN 2 THENSELECT 'val is 2'; ELSESELECT 'val is not 1 or 2';
END CASE;
Copy the code
  • Example 2:

Use the second form of the CASE flow control statement to determine whether val is null, 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;
Copy the code
  • Example 3: Declare the stored procedure update_salary_by_eid4, define the IN parameter emp_id, and enter the employee id. If the salary of the employee 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, but the bonus ratio is NULL, the bonus ratio will be updated to 0.01; Others get a raise of 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 ;
Copy the code
  • Example 4: Declare the stored procedure update_salary_by_eid5, define the IN parameter emp_id, and enter the employee number. Judge the employee’s working years, if it is 0 years, the salary will increase 50 years; If it’s one year, it’s 100; If it’s 2 years, it’s 200; If it’s three years, it’s 300; If it’s 4 years, it’s 400; The rest get a $500 raise.
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 ;
Copy the code

3.3 LOOP structure of LOOP

LOOP statements are used to execute certain statements repeatedly. The statements inside the LOOP are repeated until the LOOP exits (using the LEAVE clause), breaking out of the LOOP.

The basic format of the LOOP statement is as follows:

[loop_label:] LOOP Indicates the statement executed by the LOOPEND LOOP [loop_label]
Copy the code

Loop_label indicates the label name of the LOOP statement. This parameter can be omitted.

Example 1:

The LOOP statement is used for the LOOP operation, and the LOOP is repeated if the id value is less than 10.

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;
Copy the code

Example 2: When the market was getting 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 print the number of loops. In the storage process, the salary is raised by 1.1 times. Until the company’s average salary 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 ;
Copy the code

3.4 WHILE for loop structure

The WHILE statement creates a loop with a conditional judgment. When executing a statement, WHILE evaluates the specified expression. If true, the statement inside the loop is executed; otherwise, the loop exits. The basic format of a WHILE statement is as follows:

[while_label:] Condition for a WHILE loopDOThe loop bodyEND WHILE [while_label];
Copy the code

While_label specifies the label name of the WHILE statement. If the loop condition result is true, the statement or group of statements inside the WHILE is executed until the loop condition is false, exiting the loop.

Example 1:

An example of a WHILE statement that repeats the loop if the value of I is less than 10:

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();
Copy the code

Example 2: In order to tide over the bad market, the company decided to temporarily reduce everyone’s salary. Declare the stored procedure “update_salary_while()”, declare the OUT parameter num, and print the number of loops. Save the process to implement a loop to reduce everyone’s salary, salary down to 90% of the original. Until the company-wide average salary 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 ;
Copy the code

3.5 REPEAT of the loop structure

The REPEAT statement creates a loop with a conditional judgment. Unlike the WHILE loop, the REPEAT loop first executes a loop, then evaluates the expression in UNTIL, and exits if the condition is met, that is, END REPEAT; If the condition is not met, the loop continues until the exit condition is met.

The basic format of the REPEAT statement is as follows:

[REPEAT_label :] condition expression of REPEAT body statement UNTIL to end loopEND REPEAT [repeat_label]
Copy the code

Repeat_label is the label name of REPEAT statement, and this parameter can be omitted. The statement or group of statements within the 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 ;
Copy the code

Example 2: When the market was getting 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 print the number of loops. Save the process to achieve a cycle to raise everyone’s salary, salary increased by 1.15 times. Until the company-wide average salary reaches 13,000. 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 ;
Copy the code

Compare the three cycle structures:

1. The name of all three loops can be omitted, but it must be added if a loop control statement (LEAVE or ITERATE) is added. 2. LOOP: Generally used to implement a simple “dead” LOOP WHILE: judge first and then execute REPEAT: Judge first and then execute at least once unconditionally

3.6 Jump Statement LEAVE statement

LEAVE statement: Can be used inside a loop or in a program wrapped in BEGIN and END to indicate an operation that breaks out of the loop or the program body. If you have experience with procedural programming languages, you can interpret LEAVE as break.

The basic format is as follows:

LEAVE tag nameCopy the code

Where, the label parameter represents the label of the loop. LEAVE and BEGIN… END or loop are used together.

** Example 1: ** Create the stored procedure leave_begin() and declare the IN parameter num of type INT. To the BEGIN… END with the tag name and BEGIN… END uses the IF statement to determine the value of the num argument.

  • If num<=0, use the LEAVE statement to exit BEGIN… The END;
  • Select * from employees where num=1;
  • Select * from employees where num=2;
  • If num>2, query the highest salary in the “employees” table.

Query the total number of people in the “employees” table after the IF statement.

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 ;
Copy the code

Example 2:

When the market is bad, the company decides to temporarily reduce everyone’s salary in order to tide over the difficult situation. Declare the stored procedure “leave_while()”, declare the OUT parameter num, output the number of cycles, use the WHILE loop 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;Keep track of the average salary
	DECLARE while_count INT DEFAULT 0; # Record the number of cycles
	
	SELECT AVG(salary) INTO avg_sal FROM employees; # 1 Initialization conditions
	
	while_label:WHILE TRUE DO  #② Cyclic conditions
		
		# 3. The loop body
		IF avg_sal <= 10000 THEN
			LEAVE while_label;
		END IF;
		
		UPDATE employees SET salary  = salary * 0.9;
		SET while_count = while_count + 1;
		
		#④ Iteration conditions
		SELECT AVG(salary) INTO avg_sal FROM employees;
	
	END WHILE;
	
	# assignment
	SET num = while_count;

END //

DELIMITER ;
Copy the code

3.7 Jump statement ITERATE statement

ITERATE: Can only be used in LOOP statements (LOOP, REPEAT, WHILE) to restart the LOOP, reversing the execution order to the beginning of the statement. If you have experience with procedural programming languages, you can understand ITERATE as continue, which means “to ITERATE again.”

The basic statement format is as follows:

ITERATE label
Copy the code

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

For example, define the local variable num with an initial value of 0. The num + 1 operation is performed in the loop structure.

  • If num < 10, the loop continues;
  • If num > 15, exit the loop;
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 'Silicon Valley: Making it easy to learn technology';
	
	END LOOP my_loop;

END //

DELIMITER ;
Copy the code

4. The cursor

4.1 What is a Cursor?

While it is possible to filter WHERE and HAVING, or to LIMIT the return record keyword, to return a record, there is no way to locate a record forward, backward, or arbitrary in a result set and process the recorded data.

At this point, you can use the cursor. Cursors, a data structure that provides a flexible way to locate each record in a result set and manipulate the data in the pointed record. Cursors give procedural development capabilities to a collection-oriented language like SQL.

In SQL, a cursor is a temporary database object that points to a pointer to a row of data stored in a database table. Here the cursor acts as a pointer, and we can manipulate rows by manipulating the cursor.

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

For example, we queried the employees table to see which employees earn more than 15,000:

SELECT employee_id,last_name,salary FROM employees
WHERE salary > 15000;
Copy the code

Here we can use the cursor to manipulate the data row, as shown in the figure where the cursor is in the record “108”, or we can scroll the cursor across the result set to point to any row in the result set.

4.2 Using cursor Steps

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

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

First, declare the cursor

MySQL uses the DECLARE keyword to DECLARE cursors. The basic syntax is as follows:

DECLARE cursor_name CURSOR FOR select_statement; 
Copy the code

This syntax applies to MySQL, SQL Server, DB2, and MariaDB. If you are using Oracle or PostgreSQL, write:

DECLARE cursor_name CURSOR IS select_statement;
Copy the code

To get a result set of data using a SELECT statement that has not yet started iterating through the data, where select_Statement represents a SELECT statement that returns a result set used to create a cursor.

Such as:

DECLARE cur_emp CURSOR FOR 
SELECT employee_id,salary FROM employees;
Copy the code
DECLARE cursor_fruit CURSOR FOR 
SELECT f_name, f_price FROM fruits ;
Copy the code

Step 2, open the cursor

The syntax for opening a cursor is as follows:

OPEN cursor_name
Copy the code

After we have defined the cursor, if we want to use it, we must first open the cursor. When the cursor is opened, the query result set of the SELECT statement is sent to the cursor workspace in preparation for subsequent cursors to read the records in the result set one by one.

OPENcur_emp;Copy the code

Step 3, use the cursor (retrieve data from the cursor)

The syntax is as follows:

FETCH cursor_name INTO var_name [, var_name] ...
Copy the code

Read the current row using the cursor cursor_name and save the data to the variable var_name. The cursor pointer points to the next row. If the cursor reads a row with more than one column name, assign to more than one variable name after the INTO keyword.

Note: Var_name must be defined before the cursor is declared.

FETCHcur_emp INTO emp_id, emp_sal;Copy the code

Note: The number of fields in the cursor query result set must be the same as 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
Copy the code

If you have OPEN, you have CLOSE, which is to OPEN and CLOSE the cursor. We need to close the cursor when we are done with it. Because cursors occupy system resources, if not closed in time, cursors will remain until the end of the stored procedure, affecting the efficiency of system operation. Closing a cursor releases system resources occupied by the cursor.

After the cursor is closed, we can no longer retrieve the rows in the query result, only to open the cursor again if we need to retrieve it.

CLOSEcur_emp;Copy the code

For example, 4.3

Create a stored procedure “get_count_by_limit_total_salary()” and declare IN parameter limit_total_salary, type DOUBLE; Declare the OUT parameter total_count of type INT. The limit_total_salary () function increments the salaries of the highest employees until the total salary reaches the limit_total_salary value, and returns the increments 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 cumulative gross salary
	DECLARE cursor_salary DOUBLE DEFAULT 0; Record a certain salary value
	DECLARE emp_count INT DEFAULT 0; # count the number of loops
	Define the cursor
	DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
	Open the cursor
	OPEN emp_cursor;
	
	REPEAT
		# Use cursor (retrieve 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 the cursor
	CLOSE emp_cursor;
	
END //

DELIMITER ;
Copy the code

4.5 summary

Cursor is an important feature of MySQL, which provides a perfect solution for reading data in a result set item by item. Cursors can be used in stored programs, which are more efficient and simpler than implementing the same functionality at the application level.

However, some performance problems may also occur. For example, when cursors are used, data rows are locked. In this case, the efficiency between services is affected, system resources are consumed, and memory is insufficient because cursors are processed in memory.

Tip: Get in the habit of turning it off when you’re done to improve the overall efficiency of your system.

Added: New in MySQL 8.0 – persistence of global variables

In MySQL databases, GLOBAL variables can be SET using the SET GLOBAL statement. For example, setting a timeout limit for server statements can be done by setting the system variable max_execution_time:

SET GLOBAL MAX_EXECUTION_TIME=2000;
Copy the code

Variable values SET using the SET GLOBAL statement are only temporary. After the database restarts, the server reads the default values of the variables from the MySQL configuration file. The SET PERSIST command has been added to MySQL 8.0. For example, set the maximum number of connections to 1000:

SET PERSIST global max_connections = 1000;
Copy the code

MySQL saves the configuration of the command to the mysqld-auto-. CNF file in the data directory. The next startup will read the file and use the configuration in the file to overwrite the default configuration file.

For example:

Look at the value of the global variable max_connections 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)
Copy the code

Set the value of the global variable max_connections:

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

MySQL > select * from max_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)
Copy the code