How to write the cursor in mysql

The method of cursor writing 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].

More related free learning recommendation: mysql tutorial (video)

mysql cursor Writing method:

1, what is a cursor

In a stored procedure or function, you can use the cursor to cycle through the result set . The following is my personal opinion, the cursor is similar to the auto-increment 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: The complete query statement, the column name in the query table (detailed explanation of the following cases).

2. Turn on the cursor

cursor_name: the cursor name at the time of declaration.

Format: OPEN cursor_name;

3, capture cursor

Format: FETCH cursor_name INTO var_name...; (…indicates that there can be more than one)

cursor_name: the cursor name at the time of declaration.

var_name: Custom variable name. (Detailed explanation of the following cases)

4. Close the cursor

Format: CLOSE cursor_name;

cursor_name: the cursor name at the time of declaration.

3, a small case of cursor use

This case has no practical significance, it is just to demonstrate the use of 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 called 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, i_user_age; used to store the columns of the table traversed by the cursor.

2. Declare a cursor and traverse the u_id and u_age columns in the users table.

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

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

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 by the declare keyword, and there is an order between them required. Variables and custom exceptions must be placed first, followed by the declaration of the cursor, and finally the declaration of exception handling.

The above is the detailed content of how to write the cursor in mysql, please pay attention to other related articles on 1024programmer.com for more!