Shulou(Shulou.com)05/31 Report--

Today, I would like to share with you the relevant knowledge points about variables, process control and cursors in MySQL. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article.

1. Variable

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

In MySQL database, variables are divided into system variables and user-defined variables. [related recommendation: mysql video tutorial]

1.1 system variables 1.1.1 Classification of system variables

Variables are defined by the system, not user-defined, and belong to the server level. During the process of starting the MySQL service and generating the MySQL service instance, MySQL will assign values to the system variables in the memory of the MySQL server, 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, and so on. You can view the system variables of the MySQL document at https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html.

System variables are divided into global system variables (need to add global keyword) and session system variables (need to add session keywords). Sometimes global system variables are referred to as global variables, and sometimes session system variables are called local variables. If you do not write, the default session level. Static variables (their values cannot be dynamically modified using set while the MySQL service instance is running) are special global system variables.

After each MySQL client successfully connects to the MySQL server, it produces a corresponding session. During a session, the MySQL service instance generates session system variables corresponding to the session in MySQL server memory, and the initial value of these session system variables is a copy of the global system variable value. As shown below:

The global system variable is valid for all sessions (connections), but cannot be restarted across

The session system variable is valid only for the current session (connection). During a session, changes made by the current session to the value of a session system variable do not affect the value of the same session system variable for other sessions.

The modification of the value of a global system variable by session 1 results in the modification of the same global system variable in session 2.

Some system variables in MySQL can only be global, for example, max_connections is used to limit the maximum number of connections to the server; some system variables scope can be both global and session, such as the character set used by character_set_client to set the client; and some system variables can only be the current session, such as the MySQL connection ID used by pseudo_thread_id to mark the current session.

1.1.2 View system variabl

View all or some of the 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%'

For example:

SHOW GLOBAL VARIABLES LIKE 'admin_%'

View the specified system variabl

As the MySQL coding specification, system variables in MySQL begin with two "@", where "@ @ global" is used only to mark global system variables, and "@ @ session" is used only to mark session system variables. "@ @" marks the session system variable first, or the global system variable if the session system variable does not exist.

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

Modify the value of the system variable

Sometimes, the database administrator needs to modify the default value of the system variable in order 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 a restart of the MySQL service)

Method 2: use the "set" command to reset the value of the system variable while the MySQL service is running

# assign a value to a system variable # mode 1:SET @ @ global. Variable name = variable value; # mode 2:SET GLOBAL variable name = variable value; # assign to a session variable # mode 1:SET @ @ session. Variable name = variable value; # mode 2:SET SESSION variable name = variable value

For example:

SELECT @ @ global.autocommit;SET GLOBAL autocommit=0;SELECT @ @ session.tx_isolation;SET @ @ session.tx_isolation='read-uncommitted';SET GLOBAL max_connections = 1000 X select @ @ global.max_connections;1.2 user variable 1.2.1 user variable classification

User variables are user-defined, and as the MySQL coding specification, user variables in MySQL start with an "@". According to the different scope of action, it is divided into session user variables and local variables.

Session user variable: the scope, like the session variable, is valid only for the current connection session.

Local variables: valid only in BEGIN and END statement blocks. Local variables can only be used in stored procedures and functions.

1.2.2 session user variabl

Definition of variables

# Mode 1: "=" or "=" SET @ user variable = value; SET @ user variable: = value; # method 2: ": =" or INTO keyword SELECT @ user variable: = expression [FROM clause]; SELECT expression INTO @ user variable [FROM clause]

View the values of user variables (view, compare, operate, 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 local variable with a null value of 1.2.3

Definition: you can use the declare statement to define a local variable

Scope: just defining its BEGIN... Valid in END

Location: can only be placed on BEGIN. In END, and only in the first sentence

BEGIN # declares local variables DECLARE variable name 1 variable data type [DEFAULT variable default]; DECLARE variable name 2, variable name 3. Variable data type [DEFAULT variable default]; # assign values to local variables SET variable name 1 = value; SELECT value INTO variable name 2 [FROM clause]; # View values of local variables SELECT variable 1, variable 2, variable 3

1. Define variable

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

For example:

DECLARE myparam INT DEFAULT 100

two。 Variable assignment

Method 1: generally used to assign simple values

SET variable name = value; SET variable name: = value

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

SELECT field name or expression INTO variable name FROM table

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

SELECT local variable name

Example 1: declare a local variable and assign it to last_name and salary in the employees table with employee_id 102, respectively

DELIMITER / / CREATE PROCEDURE set_value () BEGIN DECLARE emp_name VARCHAR (25); DECLARE sal DOUBLE (10Lab 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 (using session user variables and local variables, respectively)

# method 1: use the user variable SET @ CREATE PROCEDURE add_value 1 setSet @ sum=@m+@n;SELECT @ sum;# mode 2: use the local variable DELIMITER / / CREATE PROCEDURE add_value () BEGIN # local variable DECLARE m INT DEFAULT 1; DECLARE n INT DEFAULT 3; DECLARE SUM INT; SET SUM = missun; 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 to receive the employee id, and use the OUT parameter dif_salary to output the salary gap result.

# declare DELIMITER / / CREATE PROCEDURE different_salary (IN emp_id INT,OUT dif_salary DOUBLE) BEGIN # declare the local variable DECLARE emp_sal,mgr_sal DOUBLE DEFAULT 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 position call different_salary (@ emp_id,@diff_sal); # View SELECT @ diff_sal 1.2.4 compare session user variables with local variables scope definition location syntax session user variables add the @ symbol anywhere in the current session You don't need to specify a type local variable to define the first sentence of BEGIN END in its BEGIN END. Generally, you don't need to add @, you need to specify type 2. Define conditions and handlers

The definition condition is to define in advance the problems that may be encountered in the execution of the program, the handler defines the way to deal with the problem, and ensures that the stored procedure or function can continue to execute in the event of a warning or error. This can enhance the ability of the stored program to deal with problems and prevent the program from stopping running abnormally.

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

2.1 case study

Case study: 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

Call the stored procedure:

Mysql > CALL UpdateDataNoCondition (); ERROR 1048 (23000): Column 'email' cannot be nullmysql > SELECT @ x sec + | @ x | +-+ | 1 | +-+ 1 sec)

As you can see, the value of the @ x variable is 1. Combined with the SQL statement code that creates the stored procedure, it can be concluded that the condition and handler are not 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 and exit the current SQL logic and no longer continue to execute downward.

2.2 define conditions

The definition condition is to name the error code in MySQL, which helps to store the program code more clearly. 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 condition is defined using the declare statement, and the syntax format is as follows:

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

Description of the error code:

Both MySQL_error_code and sqlstate_value can represent errors in MySQL.

MySQL_error_code is the 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 that the error name of "Field_Not_Be_NULL" corresponds to the error type of "ERROR 1048 (23000)" that violates non-null constraints in MySQL.

# using MySQL_error_codeDECLARE Field_Not_Be_NULL CONDITION FOR 1048th # using sqlstate_valueDECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000'

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

# using MySQL_error_codeDECLARE command_not_allowed CONDITION FOR 1148th # defining handlers using sqlstate_valueDECLARE command_not_allowed CONDITION FOR SQLSTATE '42000 candidate 2.3

You can define special handlers for some type of error that occurs during SQL execution. When defining a handler, the syntax for using the DECLARE statement is as follows:

DECLARE handling HANDLER FOR error type handling statement

Processing method: there are 3 values for processing method: CONTINUE, EXIT, UNDO.

CONTINUE: indicates that if you encounter an error, you will not handle it and continue execution.

EXIT: means to quit immediately if you encounter an error.

UNDO: indicates that the previous operation is withdrawn after an error is encountered. This operation is not supported in MySQL for the time being.

The error type (that is, condition) can have the following values:

SQLSTATE 'string error code': represents an error code of type sqlstate_value of length 5

MySQL_error_code: match numeric type error code

Error name: indicates DECLARE... The name of the error condition defined by CONDITION.

SQLWARNING: matches all SQLSTATE error codes that start with 01

NOT FOUND: matches all SQLSTATE error codes that start with 02

SQLEXCEPTION: matches all SQLSTATE error codes that are 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 as simple as "SET variable = value" or using BEGIN. A compound statement written by END.

There are several ways to define handlers, as follows:

# method 1: capture sqlstate_valueDECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @ info =' NO_SUCH_TABLE';# method 2: capture mysql_error_valueDECLARE 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 inter declare CONTINUE HANDLER FOR NO_SUCH_TABLE SET @ info =' NO_SUCH_TABLE';# method 4: use SQLWARNINGDECLARE EXIT HANDLER FOR SQLWARNING SET @ info = 'ERROR' # method 5: use NOT FOUNDDECLARE EXIT HANDLER FOR NOT FOUND SET @ info = 'NO_SUCH_TABLE';# method 6: use SQLEXCEPTIONDECLARE EXIT HANDLER FOR SQLEXCEPTION SET @ info =' ERROR';2.4 case to solve

In the stored procedure, define the handler, capture the sqlstate_ value, perform the CONTINUE operation when the MySQL_error_ code value is 1048, and set the value of @ proc_value to-1.

DELIMITER / / CREATE PROCEDURE UpdateDataNoCondition () BEGIN # defines the 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

Calling procedure:

Mysql > CALL UpdateDataWithCondition (); Query OK, 0 rows affected (.01 sec) mysql > SELECT @ x camera procuring valueworthiness copyright copyright + | @ x | @ proc_value | +-+-+ | 3 |-1 | +-+-+ 1 row in set (0.00 sec)

For example:

Create a stored procedure named "InsertDataWithCondition" with the following code.

In the stored procedure, define a handler, capture the sqlstate_ value, EXIT when you encounter a sqlstate_ value of 23000, and set the value of @ proc_value to-1.

# preparation CREATE TABLE departmentsASSELECT * FROM atguigudb.`departments`; ALTER TABLE departmentsADD 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 the stored procedure:

Mysql > CALL InsertDataWithCondition (); Query OK, 0 rows affected (0.01 sec) mysql > SELECT @ x camera procuring valueworthiness copyright copyright + | @ x | @ proc_value | +-+-+ | 2 |-1 | +-+-+ 1 row in set (0.00 sec) 3. Process control

Solving complex problems cannot be done with a single 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, which is an indispensable part of us to complete complex operations. As long as the program is executed, the process is divided into three main categories:

Sequential structure: the program is executed from top to bottom

Branch structure: the program selects execution according to conditions, selecting one of two or more paths for execution.

Loop structure: the program repeatedly executes a set of statements under certain conditions

There are three main types of process control statements for MySQL. Note: 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 branched structure

The syntax structure of the IF statement is:

IF expression 1 THEN Action 1 [ELSEIF expression 2 THEN Action 2]... [ELSE Operation N] END IF

Execute the corresponding statement for TRUE or FALSE based on the result of the expression. The content in "[]" here is optional.

Features: ① different expressions correspond to different operations ② is 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", define the IN parameter emp_id, and enter the employee number. Judge that if the salary of the employee is less than 8000 yuan and the entry 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 + 500WHERE employee_id = emp_id; END IF;END / / DELIMITER

Example 3: declare the stored procedure "update_salary_by_eid2", define the IN parameter emp_id, and enter the employee number. Judge that if the salary of the employee is less than 9000 yuan and the entry time is more than 5 years, the salary will rise by 500 yuan; otherwise, the salary will rise 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 + 500WHERE employee_id = emp_id; ELSE UPDATE employees SET salary = salary + 100WHERE employee_id = emp_id; END IF;END / / DELIMITER

Example 4: declare the stored procedure "update_salary_by_eid3", define the IN parameter emp_id, and enter the employee number. If the salary of the employee 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 yuan; other salary increases of 9000 yuan.

DELIMITER / / CREATE PROCEDURE update_salary_by_eid3 (IN emp_id INT) BEGIN DECLARE emp_salary DOUBLE; DECLARE bonus DECIMAL (3Power2); 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 CASE 语句的语法结构1： #情况一：类似于switchCASE 表达式WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号) WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)...ELSE 结果n或语句n(如果是语句，需要加分号)END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要） CASE 语句的语法结构2： #情况二：类似于多重ifCASE WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)...ELSE 结果n或语句n(如果是语句，需要加分号)END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要） 举例1： 使用CASE流程控制语句的第1种格式，判断val值等于1、等于2，或者两者都不等。 CASEval　　　WHEN 1 THEN SELECT 'val is 1';　　　WHEN 2 THEN SELECT 'val is 2';　　　ELSE SELECT 'val is not 1 or 2';END CASE; 举例2： 使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于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", define the IN parameter emp_id, and enter the employee number. If the salary of the employee 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 yuan; other salary increases of 9000 yuan.

DELIMITER / / CREATE PROCEDURE update_salary_by_eid4 (IN emp_id INT) BEGIN DECLARE emp_sal DOUBLE; DECLARE bonus DECIMAL (3Power2); 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= 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 with cyclic structure

The WHILE statement creates a loop procedure with conditional judgment. When WHILE executes the statement, it first judges the specified expression. If it is true, it executes the statement within the loop, otherwise it exits the loop. The basic format of the WHILE statement is as follows:

[while_label:] WHILE cycle conditional DO cycle body END WHILE [while_label]

While_label marks the name of the WHILE statement; if the loop condition turns out to be true, the statement or statement group within the WHILE statement is executed until the loop condition is false and exits the loop.

Example 1:

Example of a WHILE statement, where the I value is less than 10:00, the loop will be repeated 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 test_while(); 举例2： 市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。声明存储过程"update_salary_while()"，声明OUT参数num，输出循环次数。存储过程中实现循环给大家降薪，薪资降为原来的90%。直到全公司的平均薪资达到5000结束。并统计循环次数。 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 with cyclic structure

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

The basic format of the REPEAT statement is as follows:

[repeat_label:] statement in the body of the REPEAT loop UNTIL conditional expression END REPEAT to end the loop [repeat_label]

Repeat_label is the name of the REPEAT statement, and this parameter can be omitted; statements or statement groups within the REPEAT statement are 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 ten end / / DELIMITER

Example 2: when the market environment gets better, the company decides to give you a raise in salary in order to reward you. Declare the stored procedure "update_salary_repeat ()", declare the OUT parameter num, and output the number of loops. Realize the cycle in the stored procedure to give you a salary increase, which is 1.15 times that of the original. 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 kinds of cycle structures:

1. All three loops can omit their names, but if a loop control statement (LEAVE or ITERATE) is added to the loop, they must be added. 2. LOOP: generally used to implement simple "dead" loop WHILE: judge first and then execute REPEAT: execute first and then judge, execute at least once unconditionally

3.6 LEAVE statement of jump statement

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

The basic format is as follows:

LEAVE tag name

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

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

If num2, query the maximum salary for the "employees" table.

The total number of people querying the "employees" table at the end of the IF statement.

DELIMITER / / CREATE PROCEDURE leave_begin (IN num INT) begin_label: BEGIN IF num 15 THEN LEAVE my_loop;END IF; SELECT 'Silicon Valley: there is no hard technology to learn'; 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 criteria WHERE and HAVING, or the keyword LIMIT that qualifies the return record, we cannot locate a record forward or backward like a pointer in the result set, or randomly locate a record and process the recorded data.

At this point, cursors can be used. Cursors, a data structure that provides a flexible operation that allows us to locate each record in the result set and manipulate the data in the pointed record. Cursors make SQL, a collection-oriented language, capable of process-oriented development.

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

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

For example, we queried the employees in the employees datasheet who earned more than 15000:

SELECT employee_id,last_name,salary FROM employeesWHERE salary > 15000

Here we can manipulate data rows through cursors, as shown in the figure. At this time, the row of the cursor is a record of "108". We can also scroll the cursor over the result set and point to any row in the result set.

4.2 steps to use cursors

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

If we want to use cursors, we usually need to go through four steps. The syntax for using cursors may be slightly different in different DBMS.

First, declare the cursor

In MySQL, you use the DECLARE keyword to declare cursors, and the basic form of syntax is as follows:

DECLARE cursor_name CURSOR FOR select_statement

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

DECLARE cursor_name CURSOR IS select_statement

To use the SELECT statement to get the data result set, and you haven't started traversing the data at this point, select_statement represents the SELECT statement that returns a result set for creating cursors.

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 two, open the cursor

The syntax for opening the cursor is as follows:

OPEN cursor_name

After we have defined the cursor, if we want to use the cursor, 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.

OPEN cur_emp

The third step is to use cursors (get data from cursors)

The syntax is as follows:

FETCH cursor_name INTO var_name [, var_name]...

The purpose of this sentence is to use the cursor cursor_name to read the current row and save the data to the variable var_name, with the cursor pointing to the next line. If the data row read by the cursor has more than one column name, assign a value to multiple variable names after the INTO keyword.

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

FETCH cur_emp INTO emp_id, emp_sal

Note: the number of fields in the query result set of the cursor 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

With OPEN, there will be CLOSE, that is, opening and closing cursors. We need to close the cursor when we have finished using it. 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 the system. The operation of closing the cursor releases the system resources occupied by the cursor.

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

An example of CLOSE cur_emp;4.3

Create the stored procedure "get_count_by_limit_total_salary ()", declare the IN parameter limit_total_salary,DOUBLE type, and declare the OUT parameter total_count,INT type. The function can accumulate the salary value of several employees with the highest salary until the sum of the salary reaches the value of the limit_total_salary parameter, and the cumulative number is returned 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 total salary DECLARE cursor_salary DOUBLE DEFAULT 0; # record a wage value DECLARE emp_count INT DEFAULT 0; # record number of cycles # define cursor DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC # Open cursors OPEN emp_cursor; REPEAT # use cursors (get data from cursors) 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

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

But at the same time, it will also bring some performance problems, such as locking data rows when using cursors, so that when business concurrency is large, it will not only affect the efficiency between businesses, but also consume system resources, resulting in insufficient memory. This is because cursors are processed in memory.

Suggestion: get into the habit of shutting down after use, so as to improve the overall efficiency of the system.

Add: new feature of MySQL 8.0-persistence of global variables

In the MySQL database, global variables can be set through the SET GLOBAL statement. For example, setting the server statement timeout limit can be achieved by setting the system variable max_execution_time:

SET GLOBAL MAX_EXECUTION_TIME=2000

The value of a variable set using the SET GLOBAL statement takes effect only temporarily. After the database is restarted, the server reads the default values of the variables from the MySQL configuration file. The SET PERSIST command has been added to MySQL version 8.0. For example, set the maximum number of connections to the server to 1000:

SET PERSIST global max_connections = 1000

MySQL saves the configuration of the command to the mysqld-auto.cnf file in the data directory, which is read the next time you start, and overwrites the default configuration file with the configuration.

For example:

Check the value of the global variable max_connections, and the result is 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 the value of the global variable max_connections:

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

Restart the MySQL server and query the value of max_connections again:

Mysql > show variables like'% max_connections%' +-+-+ | Variable_name | Value | +-+-+ | max_connections | 1000 | mysqlx_max_connections | 1000 | +-+-+ 2 rows in set 1 warning (0.00 sec) and above are all the contents of the article "variables, process Control and how to use cursors in MySQL" Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.