Variables, process control and cursors

Posted by dotbob on Sat, 05 Feb 2022 07:27:28 +0100

1, Variable

in the stored procedures and functions of MySQL database, variables can be used to store the intermediate result data of query or calculation, or output the final result data. In MySQL database, variables are divided into system variables and user-defined variables.

1.1 system variables

1.1.1 classification of system variables

variables are defined by the system, not by users, and belong to the server level. When starting the MySQL service and generating the MySQL service instance, MySQL will assign values to the system variables in the MySQL server memory, which define the properties and characteristics of the current MySQL service instance. The values of these system variables are either the default values of the parameters when compiling MySQL or the parameter values in the configuration file (such as my.ini).

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

after each MySQL client successfully connects to the MySQL server, a corresponding session will be generated. During a session, the MySQL service instance will generate the session system variables corresponding to the session in the MySQL server memory. The initial value of these session system variables is the copy of the global system variable value. The figure is as follows:

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

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

1.1.2. View system variables

1.1.2.1. View all or part of system variables

-- View all global variables
SHOW GLOBAL VARIABLES;

-- View all session variables
SHOW SESSION VARIABLES;
SHOW VARIABLES;

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

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

example:

-- View all global variables
SHOW GLOBAL VARIABLES;

-- View all session variables
SHOW SESSION VARIABLES;
SHOW VARIABLES;

-- View some system variables that meet the conditions.
SHOW GLOBAL VARIABLES LIKE '%admin_%';

-- View some session variables that meet the criteria
SHOW SESSION VARIABLES LIKE '%character_%';

1.1.2.2. View specified system variables

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

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

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

-- View the specified system variable: query the session system variable first. If not, query the global system variable
SELECT @@Variable name;

example:

-- View the value of the specified system variable
SELECT @@global.max_connections;

-- View the value of the specified session variable
SELECT @@session.pseudo_thread_id;

-- View specified system variables
SELECT @@character_set_client;

1.1.3. Modify the value of system variable

Method 1: modify MySQL configuration file and then modify the value of MySQL system variable (this method requires restarting MySQL service)
Method 2: during the operation of MySQL service, use the "set" command to reset the value of system variable
- It is valid for the current database instance. Once the mysql service is restarted or a new session is established, it will fail

-- Assign a value to a system variable
SET @@global.Variable name=Variable value;
SET GLOBAL Variable name=Variable value;


-- Assign a value to a session variable
SET @@session.Variable name=Variable value;
SET SESSION Variable name=Variable value;

example:

-- Assign a value to a system variable
SELECT @@global.max_connections;
SET @@global.max_connections = 161;
SELECT @@global.max_connections;
SET GLOBAL max_connections = 171;
SELECT @@global.max_connections;

-- Assign a value to a session variable
SELECT @@session.character_set_client;
SET @@session.character_set_client = 'gbk';
SELECT @@session.character_set_client;
SET SESSION character_set_client = 'utf8';
SELECT @@session.character_set_client;

1.2. User variables

1.2.1 classification of user variables

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

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

1.2.2. Session user variables

1.2.2.1 definition of variables

-- Mode 1:“="Or“:="
SET @User variable = value;
SET @User variables := value;

-- Mode 2:“:=" or INTO keyword
SELECT @User variable := expression [FROM Equal clause];
SELECT expression INTO @User variable  [FROM Equal clause];

example:

CREATE DATABASE IF NOT EXISTS test14;
USE test14;

CREATE TABLE employees
AS
SELECT * FROM atguigudb.employees;

CREATE TABLE departments
AS 
SELECT * FROM atguigudb.departments;

SET @num1 = 1;
SET @num2 := 2;
SET @sum := @num1+@num2;


SELECT @count := COUNT(*) FROM employees;
SELECT AVG(salary) INTO @avg_salary FROM employees;

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

SELECT @User variable

example:

SELECT @sum;
SELECT @count;
SELECT @avg_salary;

1.2.3 local variables

Definition: you can use the DECLARE statement to define a local variable
Scope: just define its begin End is valid, that is, it can only be used in stored procedures or stored functions
Location: can only be placed in begin End, and can only be placed in the first sentence

BEGIN
	-- Declaration of local variables, must be declared in the first line, if not DEFAULT Clause, the initial value is NULL
	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 the value of a local variable
	SELECT Variable 1,Variable 2,Variable 3;
END

example:

USE test14;

DELIMITER $

CREATE PROCEDURE test_var()
BEGIN
	-- Declare local variables
	DECLARE a,b INT DEFAULT 0;
	DECLARE employee_name VARCHAR(25);
	
	-- Assign values to local variables
	SET a = 1;
	SET b := 2;
	SELECT last_name INTO employee_name FROM employees WHERE employee_id = 100;
	
	-- Query local variables
	SELECT a,b,employee_name;
END $

DELIMITER ;

CALL test_var();

1.2.4 compare session user variables with local variables

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

2, Define conditions and handlers

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

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

2.1 definition conditions

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

Description of error code:

MySQL_error_code and sqlstate_value can represent MySQL errors.
- MySQL_error_code is a numeric type error code.
- sqlstate_value is a string type error code with a length of 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:

-- Method 1: use MySQL_error_code
DECLARE Field_Not_Be_Null CONDITION FOR 1048;
-- Mode 2: use sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';

2.3 definition processing procedure

DECLARE Treatment method HANDLER FOR Error type processing statement;

Processing method: the processing method has three values: CONTINUE, EXIT and UNDO.
- CONTINUE: indicates that if an error is encountered, it will not be processed and execution will CONTINUE.
- EXIT: EXIT immediately in case of an error.
- UNDO: indicates that the previous operation is withdrawn after an error is encountered. MySQL does not support such operations for the time being.
The error type (i.e. condition) can have the following values:
- SQLSTATE 'string error code': indicates SQLSTATE with length of 5_ Error code of type value;
- MySQL_error_code: error code of matching value type;
- Error Name: indicates declare The name of the error condition defined by the condition.
- SQLWARNING: matches all SQLSTATE error codes starting with 01;
- NOT FOUND: matches all SQLSTATE error codes starting with 02;
- SQLEXCEPTION: matches all SQLSTATE error codes not captured by SQLWARNING or NOT FOUND;
Processing statement: if one of the above conditions occurs, the corresponding processing method is adopted and the specified processing statement is executed. The statement can be a simple statement like "SET variable = value", or it can use begin Compound statement written by end.

example:

-- Method 1: capture sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

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

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

-- Method 4: use SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

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

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

2.4 case resolution

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

USE test14;

DELIMITER //

CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
	-- Define handler
	-- Processing method 1
	-- DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;
	-- Treatment method 2
	-- DECLARE CONTINUE HANDLER FOR SQLSTATE '2300' SET @proc_value = -1;
	-- Treatment method 3
	DECLARE Field_Not_Be_Null CONDITION FOR 1048;
	DECLARE EXIT HANDLER FOR Field_Not_Be_Null SET @proc_value = -1;
	
	SET @x = 1;
	UPDATE employees SET email = NULL WHERE last_name = 'Abel';
	SET @x = 2;
	UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
	SET @x = 3;
END //

DELIMITER ;

CALL UpdateDataNoCondition();
SELECT @x,@proc_value;

3, Process control

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

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

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

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

3.1 IF of branch structure

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

Execute the corresponding statement according to whether the result of the expression is TRUE or FALSE. Here, the content in "[]" is optional.
Features: ① different expressions correspond to different operations; ② they are used in begin end

example:

USE test14;

DELIMITER $

CREATE PROCEDURE test_if()
BEGIN
	DECLARE age INT DEFAULT 20;
	
	IF age > 40
		THEN SELECT 'Middle aged and elderly';
	ELSEIF age >18
		THEN SELECT 'Young adults';
	ELSE 
		SELECT 'baby';
	END IF;
END $

DELIMITER ;

CALL test_if();

3.2 CASE of branch structure

Syntax structure of CASE statement 1:

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

example:

USE test14;

DELIMITER $

CREATE PROCEDURE test_case01()
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 value';
	END CASE;
END $

DELIMITER ;

CALL test_case01();

Syntax structure of CASE statement 2:

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

example:

USE test14;

DELIMITER $

CREATE PROCEDURE test_case02()
BEGIN
	DECLARE var INT DEFAULT 10;
	
	CASE 
		WHEN var > 100 THEN SELECT 'Three digit';
		WHEN var >= 10 THEN SELECT 'Double digit';
		ELSE SELECT 'Single digit';
	END CASE;
END $

DELIMITER ;

CALL test_case02();

3.3 LOOP of cycle structure

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

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

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

example:

USE test14;

DELIMITER //

CREATE PROCEDURE test_loop()
BEGIN
	DECLARE num INT DEFAULT 1;
	
	loop_label:LOOP
		SET num = num+1;
		IF num >= 10 THEN LEAVE loop_label;
		END IF;
	END LOOP loop_label;
	
	SELECT num;
		
END //

DELIMITER ;

CALL test_loop();

3.4 WHILE of cycle structure

WHILE statement creates a circular procedure with conditional judgment. WHILE when executing a statement, first judge the specified expression. If it is true, execute the statement in the loop, otherwise exit the loop.

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

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

example:

USE test14;

DELIMITER $

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

DELIMITER ;

CALL test_while();

3.5 REPEAT of cycle structure

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

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

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

example:

USE test14;

DELIMITER //

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

DELIMITER ;

CALL test_repeat();

Compare three cycle structures:

The names of these three loops can be omitted, but if a loop control statement (LEAVE or ITERATE) is added to the loop, the name must be added.
LOOP: generally used to implement a simple "dead" LOOP
WHILE: judge first and then execute
REPEAT: execute first and then judge. Execute unconditionally at least once

3.6. LEAVE statement of jump statement

LEAVE statement: it can be used in a loop statement or in a program body wrapped with BEGIN and END to indicate the operation of jumping out of the loop or the program body.

LEAVE Tagnames;

The label parameter represents the flag of the loop. LEAVE and begin End or loop are used together.

example:

USE test14;

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 ;

CALL leave_begin(1);

USE test14;

DELIMITER //

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

DELIMITER ;

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

3.7. ITERATE statement of jump statement

ITERATE statement: it can only be used in LOOP statements (LOOP, REPEAT and WHILE statements) to restart the LOOP and turn the execution order to the beginning of the statement segment.

ITERATE label

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

example:

USE test14;

DELIMITER //

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

DELIMITER ;

DROP PROCEDURE test_iterate;

CALL test_iterate();

4, Cursor

4.1. What is a cursor (or cursor)

although we can also return a record through the filter conditions WHERE and HAVING, or the keyword LIMIT that limits the returned record, we cannot locate a record forward, backward, or arbitrarily locate a record in the result set like a pointer and process the recorded data. At this time, the cursor can be used. Cursor, which provides a flexible operation mode, enables us to locate each record in the result set and operate on the data in the pointed record. Cursors enable SQL, a collection oriented language, to have the ability of process oriented development.

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

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

4.2 steps of using cursor

Cursors must be declared before declaring handlers, and variables and conditions must also be declared before declaring cursors or handlers.
If we want to use cursors, we generally need to go through four steps. The syntax for using cursors may vary slightly from DBMS to DBMS.

The first step is to declare the cursor

DECLARE cursor_name CURSOR FOR select_statement;

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

DECLARE cursor_name CURSOR IS select_statement;

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

Step 2: open the cursor
after we define the cursor, if we want to use the cursor, we must open the cursor first. When the cursor is opened, the query result set of the SELECT statement will be sent to the cursor workspace to prepare for the subsequent cursor to read the records in the result set one by one.

OPEN cursor_name

Step 3: use cursor (get data from cursor)

FETCH cursor_name INTO var_name [, var_name] ...

The function of this sentence is to use cursor_name this cursor to read the current row and save the data to var_ In the variable name, the cursor pointer points to the next line
If the data row read by the cursor has multiple column names, assign values to multiple variable names after the INTO keyword.
var_ The name must be defined before declaring the cursor.
The number of fields in the query result set of the cursor must be consistent with the number of variables after INTO. Otherwise, MySQL will prompt an error when the stored procedure is executed.

Step 4: close the cursor
if there is OPEN, there will be CLOSE, that is, OPEN and CLOSE the cursor. When we finish using the cursor, we need to CLOSE the cursor. Because the cursor will occupy system resources, if it is not closed in time, the cursor will remain until the end of the stored procedure, affecting the efficiency of system operation. Closing the cursor will release the system resources occupied by the cursor. After closing the cursor, we can no longer retrieve the data rows in the query results. If we need to retrieve, we can only OPEN the cursor again.

CLOSE cursor_name

4.3 examples

USE test14;

-- Create stored procedure"get_count_by_limit_total_salary"，
-- statement IN parameter limit_total_salary,DOUBLE Type; statement OUT parameter total_count,INT type
-- The function can accumulate the salary values of several employees with the highest salary until the total salary reaches limit_total_salary Parameter value to return the accumulated number of people to total_count
DELIMITER //

CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
	DECLARE sum_salary DOUBLE DEFAULT 0.0;
	DECLARE emp_salary DOUBLE;
	DECLARE emp_count INT DEFAULT 0;
	
	-- 1.declare cursor 
	DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
	
	-- 2.Open cursor
	OPEN emp_cursor;
	
	REPEAT 
		-- 3.Use cursor
		FETCH emp_cursor INTO emp_salary;
		SET sum_salary = sum_salary+emp_salary;
		SET emp_count = emp_count+1;
		UNTIL sum_salary >= limit_total_salary
	END REPEAT; 
	
	SET total_count = emp_count;
	
	-- 4.Close cursor
	CLOSE emp_cursor;
END //

DELIMITER ;

DROP PROCEDURE get_count_by_limit_total_salary;

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

4.4 summary

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

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

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

5, New feature of MySQL 8.0 - persistence of global variables

in MySQL database, global variables can be set through SET GLOBAL statement. Variable values set using the SET GLOBAL statement will only take effect temporarily. After the database restarts, the server will read the default value of the variable from the MySQL configuration file. MySQL version 8.0 adds the command SET PERSIST.

SET PERSIST global max_connections = 1000;

MySQL will save the configuration of this command to mysqld auto In CNF file, the file will be read at the next startup, and the default configuration file will be overwritten with the configuration in it.

6, Exercises

-- 1.preparation 
CREATE DATABASE test14_var_cur; 
USE test14_var_cur; 

CREATE TABLE employees 
AS
SELECT * FROM atguigudb.employees; 

CREATE TABLE departments 
AS
SELECT * FROM atguigudb.departments; 

SET GLOBAL log_bin_trust_function_creators = 1;

-- 2. Return without parameters:Create function get_count(),Returns the number of employees in the company
DELIMITER //

CREATE FUNCTION get_count() RETURNS INT
BEGIN 
	DECLARE emp_count INT;
	
	SELECT COUNT(*) INTO emp_count FROM employees;
	
	RETURN emp_count;
END //

DELIMITER ;

SELECT get_count();

-- 3. Participation and return:Create function ename_salary(),Returns the salary of an employee based on its name 
DELIMITER $

CREATE FUNCTION ename_salary(emp_name VARCHAR(25)) RETURNS DOUBLE
BEGIN
	SET @emp_salary = 0;
	
	SELECT salary INTO @emp_salary FROM employees WHERE last_name = emp_name;
	
	RETURN @emp_salary;
END $

DELIMITER ;

SELECT ename_salary('Abel');
SELECT @emp_salary;

-- 4.. Create function dept_salary() ,Return the average salary of the department according to the Department name 
DELIMITER //

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

DELIMITER ;

SELECT dept_salary('Marketing');

-- 5. Create function add_float()，Implement the incoming two float，Returns the sum of the two
DELIMITER $

CREATE FUNCTION add_float(value1 FLOAT,value2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE sum_value FLOAT;
	
	SET sum_value = value1+value2;
	
	RETURN sum_value;
END $

DELIMITER ;

SET @v1 = 13.2;
SET @v2 = 35.7;
SELECT add_float(@v1,@v2);

-- 6. Create function test_if()and test_case()，Achieve the incoming score, if the score>90,return A，If achievement>80,return B，If achievement>60,return C，Otherwise return D 
-- Requirements: use separately if Structure and case Structure realization 
DELIMITER //

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

DELIMITER ;

SELECT test_if(56);

DELIMITER $

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

DELIMITER ;

SELECT test_case(97);

-- 7. Create stored procedure test_if_pro()，Pass in salary value,
-- If the salary value<3000,Delete the employee whose salary is this value, if 3000 <= Wage value <= 5000,The salary of the employee who modifies this salary value will be increased by 1000, otherwise, the salary will be increased by 500 
DELIMITER //

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

DELIMITER ;

CALL test_if_pro(24000);

SELECT * FROM employees;

-- 8. Create stored procedure insert_data(),The passed in parameter is IN of INT Type variable insert_count,Realization direction admin Batch insert in table insert_count Records
CREATE TABLE admin(
	id INT PRIMARY KEY AUTO_INCREMENT,
	user_name VARCHAR(25) NOT NULL,
	user_password VARCHAR(35) NOT NULL
);

SELECT * FROM admin;

DELIMITER $

CREATE PROCEDURE insert_data(IN insert_count INT)
BEGIN 
	DECLARE init_count INT DEFAULT 0;
	
	WHILE init_count <= insert_count DO
		INSERT INTO admin(user_name,user_password) 
		VALUES(CONCAT('sakura-',init_count),ROUND(RAND()*1000000));
		SET init_count = init_count+1;
	END WHILE;
END $

DELIMITER ;

CALL insert_data(100);

SELECT * FROM admin;

-- 9.Create stored procedure update_salary()，
-- Parameter 1 is IN of INT Type variable dept_id，Represent department id；Parameter 2 is IN of INT Type variable change_salary_count，Indicates the number of employees whose salary is to be adjusted.
-- Query assignment id Employee information of the Department, according to salary In ascending order, according to hire_date Before adjustment change_salary_count The salary of an employee.
/*
	hire_date				salary 
	hire_date < 1995			salary = salary*1.2 
	hire_date >=1995 and hire_date <= 1998	salary = salary*1.15 
	hire_date > 1998 and hire_date <= 2001 	salary = salary *1.10 
	hire_date > 2001 			salary = salary * 1.05
*/
DELIMITER //

CREATE PROCEDURE update_salary(IN dept_id INT,IN change_salary_count INT)
BEGIN 
	DECLARE emp_id INT;
	DECLARE emp_hire_date DATE;
	DECLARE init_count INT DEFAULT 1;
	DECLARE add_salary_rate DOUBLE;
	
	DECLARE emp_cursor CURSOR 
	FOR 
	SELECT employee_id,hire_date 
	FROM employees
	WHERE department_id = dept_id 
	ORDER BY salary ASC;
	
	OPEN emp_cursor;
	
	WHILE init_count <= change_salary_count DO
		FETCH emp_cursor INTO emp_id,emp_hire_date;
		
		IF YEAR(emp_hire_date) < 1995
			THEN SET add_salary_rate = 1.2;
		ELSEIF YEAR(emp_hire_date) <= 1998
			THEN SET add_salary_rate = 1.15;
		ELSEIF YEAR(emp_hire_date) <= 2001
			THEN SET add_salary_rate = 1.10;
		ELSE
			SET add_salary_rate = 1.05;
		END IF;
		
		UPDATE employees SET salary = salary * add_salary_rate WHERE employee_id = emp_id;
		SET init_count = init_count+1;
	END WHILE;
	
	CLOSE emp_cursor;
END //

DELIMITER ;

SELECT * FROM employees WHERE department_id = 50 ORDER BY salary ASC;
CALL update_salary(50,3);
SELECT * FROM employees WHERE department_id = 50 ORDER BY salary ASC;

Programmer Think

Variables, process control and cursors

1, Variable

1.1 system variables

1.1.1 classification of system variables

1.1.2. View system variables

1.1.2.1. View all or part of system variables

1.1.2.2. View specified system variables

1.1.3. Modify the value of system variable

1.2. User variables

1.2.1 classification of user variables

1.2.2. Session user variables

1.2.2.1 definition of variables

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

1.2.3 local variables

1.2.4 compare session user variables with local variables

2, Define conditions and handlers

2.1 definition conditions

2.3 definition processing procedure

2.4 case resolution

3, Process control

3.1 IF of branch structure

3.2 CASE of branch structure

3.3 LOOP of cycle structure

3.4 WHILE of cycle structure

3.5 REPEAT of cycle structure

3.6. LEAVE statement of jump statement

3.7. ITERATE statement of jump statement

4, Cursor

4.1. What is a cursor (or cursor)

4.2 steps of using cursor

4.3 examples

4.4 summary

5, New feature of MySQL 8.0 - persistence of global variables

6, Exercises

Hot Topics