After I published my blog about Qualified Expressions, my dear friend Patrick Barel made a comment that my remarks about the Cursor For Loop was not at all what he expected to happen.
This is the code block that Patrick referred to.
10 for r in (select * from t) 11 loop 12 l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id 13 ,num2 => r.id)); 14 end loop;with these results
1 - 1 2 - 2 3 - 3A different way of looking at this code example might expect the outcome to be only one record, being the last one.
Each iteration would replace the complete Associative Array with a new instance of it.
So, there was another Puzzling thing in the original blogpost that I hadn't noticed before, probably because I wasn't looking for it. Why isn't the whole Associative Array replaced? It seems that this code would append values in the Associative Array and this was my objective. Strange indeed.
Anyhoo, probably the better way of working with Qualified Expressions to append values to the Associative Array, the index should be on the other side of the assignment:
10 for r in (select * from t) 11 loop 12 l_tbl (l_tbl.count + 1) := rec_t (num1 => r.id 13 ,num2 => r.id); 14 end loop;
When inserting the pointless conditions inside the For Loop, all values from the table are inserted into the Associative Array
10 for r in (select * from t) 11 loop 12 if r.yes_no = 'Y' 13 then 14 l_tbl (l_tbl.count + 1) := rec_t (num1 => r.id 15 ,num2 => r.id); 16 else 17 l_tbl (l_tbl.count + 1) := rec_t (num1 => r.id 18 ,num2 => r.id); 19 end if; 20 end loop;with all records from the table that get populated in the Associative Array:
1 - 1 2 - 2 3 - 3
No comments:
Post a Comment