Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to write the cursor in mysql, the article is very detailed, has a certain reference value, interested friends must read it!

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

How to write cursor in mysql:

1 What is a cursor?

Within a stored procedure or function, you can loop through the result set using the cursor. Here's my opinion. Cursors are like incrementing variable i in java loops.

2. Use of Cursors

Cursor usage involves three steps:

1, declaration cursor

Format: DECLARE cursor_name CURSOR FOR select_statement;

cursor_name: cursor name, set by the user himself, it is best to see the name.

select_statement: complete query statement, query column name in the table (detailed explanation of later cases).

2. Open the cursor

cursor_name: The cursor name at the time of declaration.

Format: OPEN cursor_name;

3. Capture the cursor

Format: FETCH cursor_name INTO var_name...;（... There can be several.)

cursor_name: The cursor name at the time of declaration.

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

4. Close the cursor

Format: CLOSE cursor_name

cursor_name: The cursor name at the time of declaration.

3. Small cases of cursor use

This example has no practical significance, 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 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 column of the table traversed by the cursor.

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

Declare an exception and close the cursor when an exception occurs.

Set two user variables to store the final result.

5. Open the cursor.

6, write a loop body, the final 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, i_user_age is added to 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 are order requirements between them. Variables and custom exceptions must come first, then the cursor declaration, and finally the exception handling declaration.

The above is how to write all the contents of mysql cursor, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

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.