Simple examples of creating stored procedures, triggers and using cursors in Navicat (pictures and text)

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

2. Stored procedure <br /> Write a stored procedure to insert data into the users table. The creation process 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 data in the users table as follows:

The entire stored procedure has been written. Of course, this is just a very simple example and is only for reference.

3. Triggers

Before writing the trigger, we first clear the users data

truncate table users;

Now we have two tables. What I need to do is, when I insert data into users, number will also change accordingly:

For example, there is an initial data 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 increase by 1, which means the number of users is recorded.

Let's implement this little functionality.

Right-click the users table and select 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 magically find that the data in the number table has also changed. Try it yourself!

ps: stored procedures need to be executed by programmers themselves, while triggers, as the name suggests, are automatically triggered.

4. Use of cursor <br /> What I want to do now is to add 100 to the student_ID field. I will use this example to simply demonstrate the use of cursors.

Create a stored procedure. Refer to the above steps for the creation method. 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 the cursor/*
 02000 One of the following exceptions occurred: 
 The result of a SELECT INTO statement or a subquery of an INSERT statement is an empty table. 
 The number of rows identified within the searched UPDATE or DELETE statement is zero. 
 The cursor position referenced in the FETCH statement is after the last row of the result table.


 */
 declare CONTINUE HANDLER FOR SQLSTATE '02000' set tmp = 0; 


 OPEN cur; -- Open the cursor FETCH cur INTO tmp; -- Move the cursor down one step WHILE (tmp != 0)
 DO 
 select tmp; -- Print out tmp and you will find that tmp is like a pointer. It points to the first row at the beginning. When the cursor moves one step, it points to the next row of records UPDATE users
 SET student_ID = tmp + 100
 WHERE student_ID = tmp;
 FETCH cur INTO tmp;
 END WHILE;

 CLOSE cur; -- Close the cursor END

Execute the above stored procedure and you will find that the data in users has changed as you wish.