A few days ago, I wrote an oracle procedure to extract useful data from many irrelevant Excel files and insert them into a table in the form of a single record, I used two nested cursors in procedure to traverse and judge the results. After procedure is executed, there is no problem and the results can be imported successfully, that is, the execution time of procedure is unstable, it can be as fast as 12 S, and as slow as 40 s. It took a long time to get depressed. After two hours, I finally found out that this sentence had caused me a disaster ~~~~ Execute Immediate ' Select ' | V _ Monthly Report column name | ' From V _ original monthly report view where rownum = 1 and Monthly Report No. = ''' | V_ybbh | ''' And reserved column 2 is not null ' Into V _ temporary column;
Execute Immediate ' Select ' | V _ Monthly Report column name | ' From (select V _ original monthly report view. *, rownum as JN No. From V _ original monthly report view where monthly report No. = ''' | V_ybbh | ''' And reserved column 2 is not null) Where JN No. = 2 ' Into V _ temporary Column 2;
I used the statement execute immediate result into variable to dynamically execute the SQL statement in the cursor. This statement seriously affects the performance speed and execution speed. The execution speed is good once, but the speed will change when the loop speed is more than 8000 times. slow and unstable ....... it seems that the performance loss of using dynamic value assignment statements in a large number of data cursor loops is huge ....
