Updating tables within cursor loop
The SELECT FOR UPDATE statement allows you to lock the records in the cursor result set.
You are not required to make changes to the records in order to use this statement.
I ran the data multiple times in multiple hours of the day (as system can have variable load in different hours of the day) to get the best average figure for the analysis.
DECLARE this Student Student%ROWTYPE; CURSOR Maths_Student IS SELECT * FROM Student WHERE SID IN (SELECT SID FROM Take WHERE CID = ’CS145’) FOR UPDATE; BEGIN OPEN Maths_Student; LOOP FETCH Maths_Student INTO this Student; EXIT WHEN (Maths_Student%NOTFOUND); IF (this Student.
Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.By extending SQL, PL/SQL offers a unique combination of power and ease of use.In addition to static SQL discussed in this chapter, PL/SQL also supports dynamic SQL, which enables you to execute SQL data definition, data control, and session control statements dynamically.😛 The two scenario DELETEs we are talking today are: 1) What does your logical mind say? Since my training days, almost a decade ago, we were told that DELETE within LOOP is never acceptable. Mark by Parallel Cursor technique and DELETE outside the LOOP.Later on, as I started understanding the concept of Index and Table scan, I started believing that, DELETING within LOOP in parallel cursor with the target INDEX known, would be better in the view of Performance.