Create a stored procedure and trigger

Create a stored procedure and trigger

 

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:

[SQL]View plaincopy

1. Begin
2. # Routine body goes here...
3. Declare n bigint;
4. Set n = 201121029684;
5. While n <= 201121029694
6. Do
7. Insert into users (student_id) values (N );
8. Set n = n + 1;
9. End while;
10. 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.

[SQL]View plaincopy

1. 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

Code:

[SQL]View plaincopy

1. Begin
2. UPDATE Number
3. Set num = (select count (*) from users );
4. 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

First, clear the data in the users table (of course, you can do it without clearing it), and then fill in the data in it, as shown in:

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:

[SQL]View plaincopy

1. Begin
2. # Routine body goes here...
4. Declare TMP bigint default 0;
5. Declare cur cursor for select student_id from users; -- defines the cursor
6. /*
7. 02000 one of the following exceptions occurs:
8. The results of the select into statement or the insert statement subquery are empty tables.
9. The number of lines identified in the update or delete statement is zero.
10. The cursor referenced in the fetch statement is placed after the last row of the result table.
13. */
14. Declare continue handler for sqlstate '200' set TMP = 0;
17. Open cur; -- open the cursor
18. Fetch cur into TMP; -- move the cursor down
21. While (TMP! = 0)
22. Do
23. Select TMP; -- print the TMP, and you will find that TMP is like a pointer. It points to the first row at the beginning, and the cursor points to the next row of records.
26. Update users
27. Set student_id = TMP + 100
28. Where student_id = TMP;
29. Fetch cur into TMP;
30. End while;
32. Close cur; -- close the cursor
33. End

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

Of course, this function can be solved directly through loops. Here I will simply show the usage of the cursor, which is conducive to a perceptual knowledge of the cursor.

That's all ~

Create a stored procedure and trigger