MySQL cursor detailed introduction

1. What is a cursor?

Although we can also return a record through the screening conditions WHERE and HAVING, or the keyword LIMIT to limit the returned records, we cannot locate a record forward or backward in the result set like a pointer, or隨意定位到某一條記錄and process the data of the record.

At this time, you can use the cursor. The cursor provides a flexible operation mode, which allows us to locate each record in the result set and operate the data in the pointed record. Cursors give SQL, a set-oriented language, the ability to develop procedures.

In SQL, a cursor is a temporary database object that can point to a row of data stored in a database table. Here the cursor充當了指針的作用. We can operate the data row by operating the cursor MySQL中游標可以在存儲過程和函數中使用

For example, we query the employees table to find out which employees have a salary higher than 15,000:

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

Here we can use the cursor to operate the data rows. As shown in the figure, the row where the cursor is located is the record of "108". We can also scroll the cursor on the result set to point to any row in the result set.

2. How to use cursors

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

1. Declare a cursor

Syntax format:

DECLARE cursor name CURSOR FOR query statement;

To use the SELECT statement to obtain the data result set, but at this time, the data has not yet been traversed. The query statement here represents the SELECT statement,返回一個用于創建游標的結果集

Example:

DECLARE emp_cur CURSOR FOR 
SELECT employee_id,salary FROM employees;

2. Open the cursor

Syntax format:

OPEN cursor name;

After we define the cursor, if we want to use the cursor, we must open the cursor first. When the cursor is opened, the query result set of the SELECT statement will be sent to the cursor workspace, preparing for the subsequent cursor逐條讀取.

3. Use cursor

Syntax format:

FETCH cursor_name INTO var_name [, var_name] ...

The function of this statement is to use游標名to read the current row, save the data to the variable var_name, and point the cursor to the next row. If the data row read by the cursor has multiple column names, you can assign them to multiple variable names after the INTO keyword.

Note: var_name must be defined before declaring the cursor.

Example:

FETCH emp_cur INTO emp_id, emp_sal;

Note: The number of fields in the cursor query result set must be consistent with the number of variables after INTO.

4. Close the cursor

Syntax format:

CLOSE cursor name;

When we are done using the cursor we need to close it. Because the cursor will占用系統資源, if it is not closed in time, the cursor will remain until the stored procedure ends , 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 data rows in the query results. If we need to retrieve them, we can only open the cursor again.

3. Code Examples

#Create a stored procedure "get_count_by_limit_total_salary()",
#Declare the IN parameter limit_total_salary, which is of DOUBLE type; declare the OUT parameter total_count, which is of INT type.
#The function can add up the salary values ​​of the employees with the highest salary.
#Until the total salary reaches the value of the limit_total_salary parameter, 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 count_emp INT DEFAULT 0; #Used to record the number of people, the default value is 0
	DECLARE sum_sal DOUBLE DEFAULT 0; #Record total salary DECLARE one_sal DOUBLE DEFAULT 0; #Record one person's salary	
	#Declare a cursor to sort the queried salary result set from high to low DECLARE emp_cur CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
	
	OPEN emp_cur; #Open the cursor	
	# Use loop statements to traverse until the total salary meets the requirements of the question REPEAT
		#Start using the cursor FETCH emp_cur INTO one_sal;
		
		SET sum_sal = sum_sal + one_sal;
		SET count_emp = count_emp + 1;
		
		UNTIL sum_sal >= limit_total_salary
		
	END REPEAT;
	CLOSE emp_cur; #Close the cursor	
	#Return the result to total_count
	SELECT count_emp INTO total_count;
END $
DELIMITER ;

CALL get_count_by_limit_total_salary(30000,@count);
SELECT @count;

Thinking analysis: First create a stored procedure, and then declare the variables you need according to the actual situation to meet the requirements of the question. The cursor should be created in steps: open the cursor, use the cursor and finally close the cursor; pay attention to using the cursor in a loop to improve the simplicity of the code.

4. Summary

Cursor is an important function of MySQL, which provides a perfect solution for逐條讀取. Compared with implementing the same functionality at the application level, cursors can be used in stored procedures, which is more efficient and the program is simpler.

However, it also brings some performance issues. For example, when using the cursor, the data rows will be加鎖. When the business concurrency is large, this will not only affect the efficiency between businesses, but also消耗系統資源and cause insufficient memory. This is because the cursor is processed in memory.

Recommendation: Make a habit of closing things after use to improve the overall efficiency of the system.

Love at the end: The use of cursors combines the knowledge points related to stored procedures, process control, and user-defined variables that have been learned before. It is not difficult to just look at the cursor knowledge point. You should review the knowledge points you have learned before and achieve a thorough understanding.

This is the end of this article about the detailed introduction of MySQL cursor. For more relevant MySQL cursor content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!