Updating within varray Syntax
A collection is a data structure that acts like a list or a single-dimensional array.
Collections are, in fact, the closest you can get in the PL/SQL language to traditional arrays. This chapter will help you decide which of the three different types of collection (associative array, nested table, and VARRAY) best fits your program's requirements and show you how to define and manipulate those structures. Here are some of the ways I have found collections handy: To maintain in-program lists of data Most generally, I use collections to keep track of lists of data elements within my programs.
Yes, you could use relational tables or global temporary tables (which would involve many context switches) or delimited strings, but collections are very efficient structures that can be manipulated with very clean, maintainable code. To improve multirow SQL operations by an order of magnitude or more You can use collections in conjunction with FORALL and BULK COLLECT to dramatically improve the performance of multirow SQL operations.
To cache database information Collections are appropriate for caching database information that is static and frequently queried in a single session (or simply queried repeatedly in a single program) to speed up the performance of those queries. I have noticed over the years that relatively few developers know about and use collections.
A primary reason for this limited usage is that collections are relatively complicated. Three different types of collections, multiple steps involved in defining and using them, usage in both PL/SQL programs and database objects, more complex syntax than simply working with individual variables.
Collections are, in fact, the closest you can get in the PL/SQL language to traditional arrays. This chapter will help you decide which of the three different types of collection (associative array, nested table, and VARRAY) best fits your program's requirements and show you how to define and manipulate those structures. Here are some of the ways I have found collections handy: To maintain in-program lists of data Most generally, I use collections to keep track of lists of data elements within my programs.
Yes, you could use relational tables or global temporary tables (which would involve many context switches) or delimited strings, but collections are very efficient structures that can be manipulated with very clean, maintainable code. To improve multirow SQL operations by an order of magnitude or more You can use collections in conjunction with FORALL and BULK COLLECT to dramatically improve the performance of multirow SQL operations.
To cache database information Collections are appropriate for caching database information that is static and frequently queried in a single session (or simply queried repeatedly in a single program) to speed up the performance of those queries. I have noticed over the years that relatively few developers know about and use collections.
A primary reason for this limited usage is that collections are relatively complicated. Three different types of collections, multiple steps involved in defining and using them, usage in both PL/SQL programs and database objects, more complex syntax than simply working with individual variables.
Syntax Delete Statement
DELETE TABLE(SELECT <nested table name> FROM <parent table name> <table alias> WHERE <clause>) <nested table alias> WHERE <clause>;
Syntax for the Delete Statement
| Location 1 | The DELETE statement with the TABLE or the THE keyword |
| Location 2 | The SELECT statement to select a particular record from the parent table |
| Location 3 | The WHERE clause to select a particular row of the nested table |
