Create a stored procedure, trigger, and a simple instance (image and text) using a cursor in navicat.

Create a stored procedure, trigger, and a simple instance (image and text) using a cursor in navicat.

1. Create a table
First, create two tables (users table and number table). The specific design is as follows:

2. Stored Procedure
Write a stored procedure and insert data into the users table. The creation procedure is as follows:

The Code is as follows:

BEGIN #Routine body goes here... declare n bigint; set n = 201121029684; while n <= 201121029694 do insert into users(student_ID) values(n); set n = n + 1; end while;END

After executing the stored procedure, you can see the following data in the users table:

The entire stored procedure is completed. Of course, this is just an extremely simple example and is only for reference.

3. triggers

Before writing a trigger, we first clear the users data.

truncate table users; 

Now we have two tables. What I want to do is, When I insert data into users, the number also changes accordingly:

For example, an initial data entry in the number table, id = 1, num = 0;

When I insert a piece of data into users, the num FIELD IN THE number table is triggered to add 1, that is, the number of records.

Next we will implement this small feature.

Right-click the users table and select the design table.

Select trigger options

begin update number set num = (select count(*) from users) ;end

After saving, add new data to the users table, and check the data in number. You will find that the data in the number table has also changed. Try it on your own!

Ps: the stored procedure needs to be executed by the programmer. The trigger, as the name suggests, is automatically triggered.

4. Use a cursor
What I want to do now is to add the student_ID field to 100. This example shows how to use the cursor.

Create a stored procedure. For more information about how to create a stored procedure, see the preceding steps. The Stored Procedure Code is as follows:

BEGIN # Routine body goes here... declare tmp bigint default 0; declare cur cursor for select student_ID FROM users; -- Define a CURSOR/* 02000 one of the following exceptions occurs: the results of the select into statement or the INSERT statement subquery are empty tables. The number of lines identified in the UPDATE or DELETE statement is zero. The cursor referenced in the FETCH statement is placed after the last row of the result table. */Declare continue handler for sqlstate '000000' set tmp = 0; OPEN cur; -- OPEN the cursor FETCH cur INTO tmp; -- the cursor goes down one step WHILE (tmp! = 0) DO select tmp; -- print the tmp, and you will find that tmp is like a pointer, pointing to the first row at the beginning, the cursor takes a step, point to the next record, UPDATE users SET student_ID = tmp + 100 WHERE student_ID = tmp; FETCH cur INTO tmp; end while; CLOSE cur; -- CLOSE the cursor END

When you execute the above stored procedure, you will find that the data in users has changed as you wish.