Shulou(Shulou.com)06/01 Report--

Update the background of large quantities of data:

Users need to send VIP's Wechat logo to the user's ERP member file, the known stock data is about 50W lines of data, offline Wechat logo data we developed to provide openid and erpid csv files, erpid and offline member file id corresponding, need to update openid to the member file.

The number of updates is roughly divided into two steps

First, to import the data source we want to update into the database, we need to create a temporary table and pass the data into the temporary table

Write cursors to update the data in the temporary table with the data that needs to be updated

1. Import the data from the csv file into the database temporary table, and create the temporary table first.

Create table vip_openid (erpid number 10, openid varchar 200)

two。 After the temporary table is created, import the data from the cvs file into the table, where we select tools > text importer

Select the csv file to import and note that the table field of the csv file corresponds to the database table field

After selecting a file, the imported data will be previewed in the interface.

3. Click on the data of oracle, select the imported table, first select the user name, the table name under the user name, and then select the corresponding relationship between the table and the csv file table field. We select the import, and you can see the prompt that the import is complete.

4. The next step is to update the data association between the membership table and the temporary table. We first use the direct update method to update the data. This method only updates 5W data, which takes about 12 minutes:

So referring to the Baidu documentation, I wrote a cursor as follows

Declare cursor cur is-- declares cursors cur select B.openidMagneA.ID ROW_ID FROM C_CLIENT_VIP A, VIP_OPENID B WHERE A.ID=B.ERPID ORDER BY A.IDTIFICATION-finds the corresponding openid of ID from tables An and B, and sorts the array within the cursor and declares a variable BEGIN V_VOUNTER:=0 of type number. -- initialization variable value is 0 FOR ROW IN CUR LOOP-- traversal cursor UPDATE C_CLIENT_VIP A SET A.OPENID=ROW.OPENID WHERE A. ID; variable count VUNTERVUNTERVUNTERIDs 1-variable value per cycle + 1 IF (V_COUNTER > = 1000) THEN COMMIT; variables are updated 0-every 1000 rows are updated, the VCOUNTER value is 1000, the variable is submitted to the database and the variable is returned to zero, and the next 1000 rows update the END IF; END LOOP. COMMIT;END

About 48W rows of data are updated in 43 seconds.

Note that because the ID of the database membership table has an index, cursors are used quickly. If our multi-table associated fields do not have an index, the effect of using this method may not be obvious.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

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.