Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of traversing multi-table records and inserting into third-party tables on the basis of mysql stored procedures. It is very detailed and has a certain reference value. Interested friends must finish reading it!

Preface

Since I learned about stored procedures, I have never touched stored procedures again. This is the first stored procedure I wrote after graduation.

Because there are many kinds of devices in the project, they exist in different data tables, and the java corresponds to different java bean objects, so it is difficult to manage them uniformly. Recently, we are about to develop a functional module, that is, we need to deal with the equipment uniformly. In order to deal with all kinds of equipment conveniently in the future, we will design a set of programs to manage these projects from now on.

How to manage it uniformly?

If we had considered that the project would develop like this from the beginning of the project design, we should have extracted the common parent class and extracted all the common fields of all types uniformly, so that no matter how many modules were added, when we need to operate on the device, we only need to associate the unified parent class, the relationship between the data will be much clearer, and the development will be much easier.

What about the extraction now? How's the time?

It should be too late, because the project has grown and developed a lot of functions, once extracted now, there is too much need to modify, there is not so much time to complete the work.

Is there any other way?

I don't know how other people's projects deal with such problems, but as far as I think, I can learn the idea of the registry. There are a lot of software in the computer, and each software is the same. But when each software is installed, it will write a message to the registry to facilitate the computer's centralized management of the software. Do you feel that this plan is not bad?

What needs to be done in the project is: 1, add a registry object; 2, when adding devices, write public and important information into the registry, update and delete also have to synchronize the operation of the registry; 3, a very important job, what about the equipment information that has been added to the project? They do not have registration information, is it difficult to give up the management of them? Or delete and re-add? No! You just need to write a stored procedure to traverse the device information that already exists in the table and insert it into the registry.

All right, this is the beginning of the first stored procedure of my life, and here is my first stored procedure:

There must be all kinds of problems in the process of writing.

First of all, I don't remember what loop statements there are, so let's popularize science.

The first while cycle

While loop syntax:

While conditional DO

Cyclic body

End while

The second loop cycle

Loop loop syntax:

Loop_name:loop

If condition THEN-- leave the loop when the condition is satisfied

Leave loop_name;-- almost always ends the lecture with break.

End if

End loop

The third repeat cycle

Repeat loop syntax

Repeat

Cyclic body

Until conditional end repeat

Second, there is a problem, when looping, the last record is always executed twice.

At the beginning, I used the repeat loop body, thinking that it was executed because it had not been judged, which must be wrong. We should judge it first and then execute it, thinking that a different loop statement would be fine.

It used to be like this.

And then it changed to this.

There is nothing wrong with it, it was judged first and then done, but the result is still wrong. After studying it carefully, it is found that when the cursor goes to the last line, it is right to execute insert, but the tag value at this time is still normal and will enter the loop body again. At this time, when you go to fetch the next cursor, you will not get it, and the tag value will change, but the insert statement will still be executed. The next loop is not qualified, the loop that pops out, so it should be judged after the cursor moves down one line, such as adding an if judgment after the fetch statement, or moving the cursor first and then judging the insert, as follows

Finally, paste the code so that you can copy and paste it later. There is not a saying called "A big copy of the code all over the world, let's see if you can copy it". In fact, the daily development is just copy and paste.

BEGIN DECLARE dsi_id LONG; DECLARE dsi_name varchar (300); DECLARE dsi_areaid LONG; DECLARE dsi_orgzid LONG; DECLARE dsi_clazz varchar (300); declare no_more_departments integer DEFAULT 0 DECLARE cursor_employee CURSOR FOR SELECT dsi.id, dsi.inputname, dsi.deviceArea_id, sd.organization_id, "DeviceSwitchInfo" FROM tip_s_deviceswitchinfo dsi LEFT JOIN tip_s_systemdeviceinfo sd ON dsi.Device_id = sd.id UNION SELECT dsi.id, dsi.outputname, dsi.deviceArea_id, sd.organization_id, "DeviceSwitchOutPut" FROM tip_s_DeviceSwitchOutPut dsi LEFT JOIN tip_s_systemdeviceinfo sd ON dsi.Device_id = sd.id UNION SELECT dsi.id, dsi.analogname, dsi.deviceArea_id Sd.organization_id, "AnalogInputInfo" FROM tip_s_AnalogInputInfo dsi LEFT JOIN tip_s_systemdeviceinfo sd ON dsi.Device_id = sd.id UNION SELECT dsi.id, dsi.devicename, dsi.area_id, sd.organization_id, "SmartDeviceInfo" FROM tip_smart_deviceinfo dsi LEFT JOIN tip_s_systemdeviceinfo sd ON dsi.deviceid = sd.id DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments = 1; OPEN cursor_employee; FETCH cursor_employee INTO dsi_id, dsi_name, dsi_areaid, dsi_orgzid, dsi_clazz; WHILE! no_more_departments DO INSERT INTO tip_m_deviceregister (deviceId, NAME, area_id, orgz_id, deviceClass, active, version) VALUES (dsi_id, dsi_name, dsi_areaid, dsi_orgzid, dsi_clazz, 1, 1) FETCH cursor_employee INTO dsi_id, dsi_name, dsi_areaid, dsi_orgzid, dsi_clazz; END WHILE; CLOSE cursor_employee;END are all the contents of this article entitled "sample analysis of traversing multi-table records and inserting third-party tables into third-party tables based on mysql stored procedures". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow 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.