How to write the cursor in mysql

The method of writing cursor in mysql: first declare the cursor; then open the cursor, the code is [OPEN cursor_name]; then capture the cursor; finally close the cursor, the code is [CLOSE cursor_name].

The operating environment of this tutorial: Windows 7 system, MySQL version 5.8. This method is suitable for all brands of computers.

More related free learning recommendations: mysql tutorial(Video)

How to write cursor in mysql:

1, What is a cursor

In a stored procedure or function, you can use the cursor to loop through the result set. The following is my personal opinion. The cursor is similar to the auto-incrementing variable i in the Java loop.

2. The use of the cursor

The use of the cursor includes the following three steps:

1. Declare the cursor

Format: DECLARE cursor_name CURSOR FOR select_statement;

Cursor_name: cursor name, set by the user, it is best to know the name.

　　select_statement: Complete query statement, column names in the query table (detailed explanation of the case below).

2. Turn on the cursor

cursor_name: The cursor name when declared.

Format: OPEN cursor_name;

3. Capture cursor

Format: FETCH cursor_name INTO var_name...;（ ...means there can be multiple)

Cursor_name: The cursor name when declared.

　var_name: Custom variable name. (Detailed explanation of the case later)

4. Close the cursor

Format: CLOSE cursor_name;

cursor_name: The cursor name when declared.

3, a small case of using the cursor

This case has no practical significance, it is just to demonstrate the use of the cursor

First create a users Table

CREATE TABLE `users` (
  `u_id` int(4) NOT NULL,
  `u_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `u_age` int(4) DEFAULT NULL,
  PRIMARY KEY (`u_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Insert the following data

Create a stored procedure named select_age

BEGIN
    #Routine body goes here...
    declare i_user_id int;
    declare i_user_age int;
    declare cur_age cursor for select u_id,u_age from users;
    declare exit handler for not FOUND close cur_age;
    set @age_ji = 0;
    set @age_ou = 0;
    
    open cur_age;
    REPEAT
        fetch cur_age into i_user_id,i_user_age;
        if i_user_id%2 = 1 THEN
            set @age_ji = @age_ji + i_user_age; 
        else
            set @age_ou = @age_ou + i_user_age;
        end if;
    until 0 end REPEAT;
    close cur_age;
END

Call the stored procedure

call select_age();
select @age_ji,@age_ou;

Detailed explanation of the code:

1, first define two local variables i_user_id and i_user_age; used to store the columns of the table traversed by the cursor.

2, declare a cursor to traverse the u_id and u_age columns in the users table.

3, declare an exception and close the cursor when an exception occurs.

4, set two user variables to store the final results.

5, turn on the cursor.

6, write a loop body, the terminal condition is to traverse to the last column of the table.

7, capture the cursor and put the traversed content into two local variables.

8. By judging the parity of i_user_id, add i_user_age to the two user variables respectively.

9. Close the cursor.

4, Notes

Variables, custom exceptions, exception handling, and cursors are all defined through the declare keyword, and there are order requirements between them. Variables and custom exceptions must be placed first, followed by the cursor declaration, and finally the exception handling declaration.

The above is the detailed content of How to write the cursor in mysql. For more information, please follow other related articles on the PHP Chinese website!