Using Navicat to create stored procedures, triggers, and simple instances using cursors

Creating stored procedures and triggers

1. Build a table

First, two tables (the Users table and the number table) are built, specifically designed as:

2. Stored Procedures

Write a stored procedure that inserts data into the users table, creating the following process:

The code is as follows:

[SQL]View Plaincopy

2. BEGIN
4. #Routine body goes here ...
6. declare n bigint;
8. set n = 201121029684;
10. While N <= 201121029694
12. Do
14. INSERT INTO users (student_id) values (n);
16. Set n = n + 1;
18. End While;
20. END

After executing the stored procedure, you can see the data in the Users table as follows:

The entire stored procedure is written, which is, of course, a very simple example and is for reference only.

3. Trigger

Before we write the trigger, we'll erase the data from users.

[SQL]View Plaincopy

1. Truncate table users;

Now that we have two tables, the thing I'm going to do is that when I insert data into users, the number changes as well:

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

When I insert a piece of data into users, it triggers the NUM field in the Number table to add 1, which is how many users are logged.

Let's implement this little feature.

Right-click the Users table, select 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 then look at the data in number, you will magically find that the data in the number table has changed, try it yourself!

PS: Stored procedures require programmers to execute their own, triggers, as the name implies, automatically triggered.

4. Cursor use

First, I'll empty the data in the Users table (of course not emptying it), and then populate it with data, as shown in:

What I want to do now is to add 100 to the student_id field, and this example simply shows the use of a downstream label.

Create a stored procedure that references the steps above. The stored procedure code is as follows:

[SQL]View Plaincopy

2. BEGIN
4. #Routine body goes here ...
8. declare tmp bigint default 0;
10. declare cur CURSOR for SELECT student_id from users; --Defining cursors
12. /*
14. 02000 one of the following exceptions occurred:
16. The result of the subquery for the SELECT into statement or INSERT statement is an empty table.
18. The number of rows identified in the UPDATE or DELETE statement of the search is zero.
20. The cursor position referenced in the FETCH statement is after the last row in the result table.
26. */
28. declare CONTINUE HANDLER for SQLSTATE ' 02000 ' set tmp = 0;
34. OPEN cur; --Open cursor
36. FETCH cur into tmp; --the cursor goes down one step
42. while (tmp! = 0)
44. Do
46. Select tmp; --Print tmp out, you will find that TMP is like a pointer, starting at the first line, the cursor one step, pointing to the next row of records
52. UPDATE Users
54. SET student_id = tmp +
56. WHERE student_id = tmp;
58. FETCH cur into tmp;
60. END while;
64. CLOSE cur; --Close cursor
66. END

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

Of course, this function directly with the loop can be solved, I am just a simple demonstration of the use of a downstream standard, conducive to the cursor has a perceptual knowledge.

That ' s all~

Original: http://blog.csdn.net/cqnuztq/article/details/9735245

Using Navicat to create stored procedures, triggers, and simple instances using cursors