Working with PL/SQL Collections: Associative Arrays, Nested Tables, and VARRAYs
📤 Compartilhe este artigo com o link curto:
Storing and Manipulating Data Sets in Memory
In complex database routines, we often need to temporarily store sets of records or lists of values in memory for procedural processing, business rule validation, or batch manipulation before definitively persisting data to physical tables. To meet this need, PL/SQL provides composite data structures known as Collections.
In this article, we will detail the three types of collections supported by PL/SQL and when to use each to maximize the performance of your procedures and packages.
The Three Types of Collections in PL/SQL
The PL/SQL ecosystem offers three distinct collection structures, each with its own characteristics regarding indexing, size, and persistence:
- Associative Arrays (Index‑by Tables): Flexible collections indexed by integers or strings. They have no predefined size limit and can grow dynamically. They are ideal for fast key‑value lookups in memory.
- Nested Tables: One‑dimensional collections with no fixed initial size limit. They can be sparse (allowing deletion of middle elements and leaving gaps in indices) and can even be stored directly in columns of relational tables.
- VARRAYs (Variable‑Size Arrays): Arrays with a predefined maximum size declared at creation time. They guarantee that the order of elements is strictly maintained and are ideal for data sets whose maximum size is known in advance.
Practical example of declaring and using an Associative Array:
Native Manipulation Methods
PL/SQL provides built‑in functions and procedures (methods) that are extremely useful for inspecting and manipulating the state of collections at runtime:
- EXISTS(n): Returns TRUE if the element at position or key n exists in the collection.
- COUNT: Returns the total number of elements currently stored in the collection.
- FIRST and LAST: Return, respectively, the first and last index or key of the collection.
- EXTEND: Adds empty elements to the end of a Nested Table or VARRAY (required before populating collections that are not index‑by).
- DELETE: Removes elements from a collection (in Nested Tables, it can remove a specific range of indices).
Practical example populating a Nested Table with EXTEND:
Performance Best Practices
- Prefer Associative Arrays for fast local processing: If you only need an in‑memory dictionary within the procedure to cross‑reference data without persisting to tables, integer‑indexed associative arrays offer excellent performance.
- Be careful with PGA consumption: As with BULK COLLECT, avoid unnecessarily loading massive collections with hundreds of thousands of rows into procedural memory to avoid impacting the session PGA.
Conclusion
Mastering the use of collections and arrays in PL/SQL empowers the developer to structure complex, clean, and highly efficient procedural algorithms directly in the database layer.