If your cursor logic has a “FOR UPDATE” clause, please leave it in place as it locks the records in the Resultset for the update (until next COMMIT or ROLLBACK) and you will open up your code for unnecessary race conditions by replacing that with FOR LOOP (like two people reserving the same seat on an airplane!).
hate don’t like OPEN/FETCH/CLOSE
Back in the day, the most commonly used method to loop through and process “a small number of records” in PL/SQL was to use the WHILE/OPEN/FETCH/CLOSE explicit cursor operations.
Not only is the syntax cryptic and cumbersome, it can also lead to errors if you miss something really simple inside your loop.
The easier way to do this is using implicit cursors. By the way, this is a fully working anonymous block.
BEGIN FOR v_Rec IN (SELECT * FROM My_Table) LOOP DBMS_OUTPUT.PUT_LINE(v_Rec.Client_Id); END LOOP; END;
Here are a few things to note above
- V_REC variable is not even declared. PL/SQL implicitly declared it for
- There is no explicit CURSOR definition. Inline SQL was used
- The looping logic is as straightforward as it can get.
Please note that this mechanism is good only when you want to loop through a small number of records. There are other ways to do the same if you will work with a large number of records like using set based SQL statements and by using BULK operations.