After Exception handling, the topic which we finished off with the last PL/SQL tutorial, collection is the most demanded topic on my social media. That is why our next series of tutorials will be based on PL/SQL collections. Starting with today.
The structure of PL/SQL collections consist of a cell with subscript called index. Data is stored into these cells and can be identified and accessed using the index number. This is again very similar to the structure of arrays, but unlike array PL/SQL Collections are strictly one-dimensional.
Persistent collection, as the name suggests, are those which physically store the collection structure with the data into the database and can be accessed again if needed. Whereas non-persistent collection only stores data and structure for one session.
On the basis of above categories collections are further divided into three types:
Nested Table – Nested tables are persistent collection which means they can be stored into the database and can be reused. Nested tables has no upper limits on rows thus they are unbounded collections. Nested tables are initially dense but can become sparse through deletion.
VARRAYs – Similar to Nested tables Variable-Sized Arrays are also persistent collections thus they can be created in database as well as PL/SQL block and can be reused. But unlike nested tables VARRAYs are bounded in nature which means that they can hold only a fixed amount of elements.
Associative Array – Unlike nested table and VARRAYs, associative arrays are non-persistent collections thus they cannot be stored into the database. Since they cannot be store hence they cannot be reused but they are available in PL/SQL block for the session. But similar to nested tables associative arrays are unbounded which means they also don’t have lower and upper limits on rows.
Dense & Sparse Collection. – Collections is said to be dense if all the rows between the first and the last are defined and given a value. And a collection in which rows are not defined and populated sequentially are called sparse collection.
That’s it for this tutorial. You can help others in learning as well as help me and my channel in growing by sharing this blog with your friends or on your social media.
You can now get the updates of our blogs, giveaways along with other interesting things as it happens right in your inbox just subscribe to our newsletter. Click here for the sign up form. Thanks & have a great day!
You can DOWNLOAD SQL script and presentation used in the Video and in this article.