Hi All,
Im just started learning SQL and PL/SQL. This is my understanding about cursors and collections:
Cursors: Oracle brings data from DB to a memory by cursors either explicit or implicit (Is RAM the memory here ? how it stores millions of rows from a table? Is the RAM of a Server going to be that large ??). Then we can go over each record by a loop. Instead of going to Cursor memory area each time, Cursor FOR LOOP gets 100 records each time to its local variable.
Where as in collections we can BULK COLLECT entire result set from an Implicit/Explicit cursor in to a Collection which is a local variable to PL/SQL and we can limit the number of records by LIMIT (useful if we have less memory). As collections are local, accessing them will be faster. We can perform calculations and store data back to collections. We have BULK FORALL for collections.
So, Collections have upper hand in everything when compared to Cursors. Only advantage of cursors I can think of is its simplicity in declaring, where as in collections we need to declare a record, type and a variable.
What are the other advantages of Cursors and where do we use them ?
Thanks
Dinesh