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

Editor to share with you the example analysis of process control and cursors in MySQL. I hope you will get something after reading this article. Let's discuss it together.

Anyone who has studied or known programming languages knows that no programming language can be done with just one or two sentences of code.

The function of process control statements is to control the execution order of statements in the process of program operation, 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

Preparatory work:

Create the database and two tables, and insert the data:

Create database dbtest16;use dbtest16;create table employees asselect * from atguigudb.employees;create table departmentsasselect * from atguigudb.departments;select * from employees;select * from departments

Branching structure

Branch, that is, choose one of the two. There are two main forms of branching structure in SQL:

IF

CASE

IF statement

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: single judgment

Delimiter / / create procedure test_if () begin # declares the local variable declare stu_name varchar (15); if stu_name is null then select 'stu_name is null'; end if;end//delimiter; call test_if ()

Example 2: choose one of the two

Delimiter / / create procedure test_if2 () begin declare email varchar (25); if email is null then select 'email is null'; else select' email is not null'; end if;end//delimiter; call test_if2 ()

Example 3: choose one more

Delimiter / / create procedure test_if3 () begin declare age int default 20; if age > 40 then select 'middle-aged'; elseif age > 18 then select 'young adult' Elseif age > 10 then select 'teenagers'; else select 'children'; end if;end//delimiter; call test_if3 ()

Scenario 1: 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 variable declare sal double; # record employee salary declare hiredate date; # record entry date # query assignment select salary,hire_date into sal,hiredate from employees where employee_id = emp_id; # judge condition and modify if sal

< 8000 and datediff(now(),hiredate)/365 >

5 then update employees set salary = salary + 500where employee_id = emp_id; end if;end//delimiter;set @ emp_id # salary of employee No. 104 before update: select salary,employee_id from employees where employee_id = @ emp_id;# update salary: call update_salary_by_eid1 (@ emp_id) # check the salary of employee No. 104 again: select salary,employee_id from employees where employee_id = @ emp_id

Scenario 2: 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 variable declare sal double; # record employee salary declare hiredate date; # record entry date # query assignment select salary,hire_date into sal,hiredate from employees where employee_id = emp_id; # judge condition and modify if sal

< 9000 and datediff(now(),hiredate)/365 >

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;# take 103104 employees as an example # salary before update: select salary,employee_id from employees where employee_id in (103104) # Update salary: call update_salary_by_eid2 (103); call update_salary_by_eid2 (104); # query employee's salary again: select salary,employee_id from employees where employee_id in (103104)

Scenario example 3: 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 variable declare sal double; # record employee salary declare emp_commission_pct double; # record bonus ratio # query assignment select salary,commission_pct into sal,emp_commission_pct from employees where employee_id = emp_id; # judge condition and modify if sal

< 9000 then update employees set salary = 9000 where employee_id = emp_id; elseif sal < 10000 and emp_commission_pct 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;# 以102,103,104员工为例# 更新前员工工资情况：select salary,employee_id,commission_pct from employees where employee_id in (102,103,104);# 更新工资：call update_salary_by_eid3(102);call update_salary_by_eid3(103);call update_salary_by_eid3(104);# 再次查询员工工资情况：select salary,employee_id,commission_pct from employees where employee_id in (102,103,104); 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后面不需要） 举例一：case ... when ... then ... delimiter //create procedure test_case()begin declare var int default 2; case var when 1 then select 'var = 1'; when 2 then select 'var = 2'; when 3 then select 'var = 3'; else select 'other'; end case;end //delimiter ;call test_case(); 举例二：case when ... then ... delimiter //create procedure test_case2()begin declare var1 int default 10; case when var1 >

= 100 then select 'three digits'; when var1 > = 10 then select 'double digits'; else select 'single digits'; end case;end / / delimiter; call test_case2 ()

Scenario 1:-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 proportion 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 variable declare sal double; # record employee salary declare emp_commission_pct double; # record bonus ratio # query assignment select salary,commission_pct into sal,emp_commission_pct from employees where employee_id = emp_id; # judge condition and modify case when sal

< 9000 then update employees set salary = 9000 where employee_id = emp_id; when sal < 10000 and emp_commission_pct 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;# 以103,104,105员工为例# 更新前员工工资情况：select salary,employee_id,commission_pct from employees where employee_id in (103,104,105);# 更新工资：call update_salary_by_eid3(103);call update_salary_by_eid3(104);call update_salary_by_eid3(105);# 再次查询员工工资情况：select salary,employee_id,commission_pct from employees where employee_id in (103,104,105); 场景举例二：-- 声明存储过程update_salary_by_eid5，定义IN参数emp_id，输入员工编号。 -- 判断该员工的入职年限，如果是0年，薪资涨50； -- 如果是1年，薪资涨100； -- 如果是2年，薪资涨200； -- 如果是3年，薪资涨300； -- 如果是4年，薪资涨400； -- 其他的涨薪500。 delimiter //create procedure update_salary_by_eid5(in emp_id int)begin # 声明变量 declare sal double; # 记录员工工资 declare hire_year double; # 记录入职日期 # 查询赋值 select salary,round(datediff(now(),hire_date)/365) into sal,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;# 以107员工为例# 更新前员工工资情况：select salary,employee_id,hire_date from employees where employee_id = 107;# 更新工资：call update_salary_by_eid5(107);# 再次查询员工工资情况：select salary,employee_id,hire_date from employees where employee_id = 107; # 针对场景二，此种写法略显不足，重复的书写相同的更新语句，# 其实观察下来也就金额不同，可以有改进改进如下：delimiter //create procedure update_salary_by_eid6(in emp_id int)begin # 声明变量 declare sal double; # 记录员工工资 declare hire_year double; # 记录入职日期 declare add_sal double; # 保存更新的金额 # 查询赋值 select salary,round(datediff(now(),hire_date)/365) into sal,hire_year from employees where employee_id = emp_id; # 判断条件并修改 case hire_year when 0 then set add_sal = 50; when 1 then set add_sal = 100; when 2 then set add_sal = 200; when 3 then set add_sal = 300; when 4 then set add_sal = 400; else set add_sal = 500; end case; # 根据当前add_sal值修改 update employees set salary = salary + add_sal where employee_id = emp_id;end//delimiter;# 以108员工为例# 更新前员工工资情况：select salary,employee_id,hire_date from employees where employee_id = 108;# 更新工资：call update_salary_by_eid5(108);# 再次查询员工工资情况：select salary,employee_id,hire_date from employees where employee_id = 108; 循环结构 有时候我们需要重复的执行某条语句，而借助循环结构可以很好地实现。在MySQL中我们可以有三种方式实现循环： LOOP WHILE REPEAT 凡是循环结构都遵循的四要素： 1.初始化条件 2.循环条件 3.循环体 4.迭代条件 LOOP语句 LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。 LOOP语句的基本格式如下： [loop_label:] LOOP 循环执行的语句END LOOP [loop_label] 其中，loop_label表示LOOP语句的标注名称，该参数可以省略。 举例一： delimiter //create procedure test_loop()begin # 声明变量 declare num int default 1; soberw:loop # 重新赋值 set num = num + 1; if num >

= 10 then leave soberw; end if; end loop soberw; # View num select num;end / / delimiter; call test_loop ()

Example 2:-when the market environment gets better, the company decides to give you a raise in order to reward you. Declare the stored procedure "update_salary_loop ()", declare the OUT parameter num, and output the number of loops. Realize the cycle in the stored procedure to give everyone a salary increase, which is 1.1 times that of the original. 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 variable # record keeping average salary declare avg_sal double default 0; # record cycles declare count int default 0; # get current average salary select avg (salary) into avg_sal from employees Soberw:loop # end condition if avg_sal > = 12000 then leave soberw; end if # Update salary update employees set salary = salary * 1.1; # ensure that the current average salary is the latest select avg (salary) into avg_sal from employees # number of records set count = count + 1; end loop soberw; # returns num set num = count;end / / delimiter; call update_salary_loop (@ num); select @ num;select avg (salary) from employees

WHILE statement

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: WHILE statement example. If the I value is less than 10:00, the loop process will be repeated.

Delimiter / / create procedure test_while () begin # initialization condition declare i int default 1; # Loop condition while I

< 10 do # 循环体略 #迭代条件 set i = i + 1; end while; select i;end//delimiter ;call test_while(); 举例二： -- 市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。 -- 声明存储过程"update_salary_while()"，声明OUT参数num，输出循环次数。 -- 存储过程中实现循环给大家降薪，薪资降为原来的90%。 -- 直到全公司的平均薪资达到5000结束。 -- 并统计循环次数。 delimiter //create procedure update_salary_while(out num int)begin # 声明变量 # 记录保存平均薪资 declare avg_sal double default 0; # 记录循环次数 declare count int default 0; # 获取当前平均薪资 初始化条件 select avg(salary) into avg_sal from employees; #循环条件 soberw:while avg_sal >

5000 do # cycle body # Update salary update employees set salary = salary * 0.9; # number of records set count = count + 1 # iteration condition # guarantee that the current average salary is the latest select avg (salary) into avg_sal from employees; end while soberw # return num set num = count;end / / delimiter; call update_salary_while (@ num); select @ num;select avg (salary) from employees

REPEAT statement

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; call test_repeat ()

Example 2:-when the market environment gets better, the company decides to give you a raise 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 everyone 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 variable # record keeping average salary declare avg_sal double default 0; # record cycles declare count int default 0 # get the current average salary initialization condition select avg (salary) into avg_sal from employees # cycle condition soberw:repeat # cycle body # Update salary update employees set salary = salary * 1.15 # number of records set count = count + 1 # iteration condition # guarantee that the current average salary is the latest select avg (salary) into avg_sal from employees Until avg_sal > = 13000 end repeat soberw; # returns num set num = count;end / / delimiter; call update_salary_repeat (@ num); select @ num;select avg (salary) from employees

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

Jump statement

Jump statements can help us better control the loop.

LEAVE 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 ()" that declares 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) soberw:begin if num 2 then select max (salary) from employees;end if; select count (1) from employees;end//delimiter; call leave_begin (2); call leave_begin (- 1)

Example 2:-when the market environment is not good, 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, 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; declare count int default 0; select avg (salary) into avg_sal from employees; soberw:while true do if (avg_sal 15, then exit the loop structure

Delimiter / / create procedure test_iterate () begin declare num int default 0; soberw:loop set num = num + 1; if num

< 10 then iterate soberw; end if; if num >

15 then leave soberw; end if; end loop soberw; select num;end//delimiter; call test_iterate ()

Vernier

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.

To use cursors:

1) declare the cursor DECLARE cursor_name CURSOR FOR select_statement

2) Open the cursor OPEN cursor_name

3) use cursors (get data from cursors) FETCH cursor_name INTO var_name [, var_name].

4) close the cursor CLOSE cursor_name

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.

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.

For example:-- create a stored procedure "get_count_by_limit_total_salary ()",-- declare the IN parameter limit_total_salary,DOUBLE type;-- 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,-- return the accumulated number to total_count.

Delimiter / / create procedure get_count_by_limit_total_salary (in limit_total_salary double,out total_count int) begin # Save salary and declare sum_sal double default 0; # Save cumulative declare count int default 0; # define individual salary declare emp_salary double default 0 # define cursor declare cursor_sal cursor for select salary from employees order by salary desc; # Open cursor open cursor_sal; # use cursor while sum_sal < limit_total_salary do fetch cursor_sal into emp_salary Set sum_sal = sum_sal + emp_salary; set count = count + 1; end while; # closes the cursor close cursor_sal; # assigns total_count the value set total_count = count;end//delimiter; set @ limit_total_salary = 200000 Call get_count_by_limit_total_salary (@ limit_total_salary,@total_count); select @ total_count

After reading this article, I believe you have some understanding of "sample Analysis of process Control and cursors in MySQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.