Introduction
PL/SQL applications typically consist of SQL statements intermixed with procedural logic to process data retrieved from the database. If compiled as a stored procedure, your PL/SQL code will reside on the server, an ideal place for programs that require intensive database interaction. Having said that, anytime a software application links up with a database, there is a performance price to be paid. Not only that, programs that continually switch off between code and SQL can become quite complex. PL/SQL collections can address some of these concerns.
Why Collections?
Just about all modern programming languages provide support for collections. A collection can be loosely defined as a group of ordered elements, all of the same type that allows programmatic access to its elements through an index. Commonly used collection types used in the programming world include arrays, maps, and lists.
Storing elements in a collection can provide a number of advantages. For starters, collections can help to simplify code. If you need to process a number of items of a similar type, storing these items in a collection will allow you to loop through each element with ease, referencing each one by an index. In addition, most languages define collection types that contain built-in methods to operate on the collection. Probably the biggest advantage a collection can provide is improved application performance. Developers utilize collections to 'cache' static data that needs to be regularly accessed. This results in reduced calls to a database.
Oracle Collections
Oracle provides three types of PL/SQL collections viz. Associative arrays, nested tables and Varrays. Each type of collection is described in the below section.
Associative Array
Associative arrays are set of key value pairs where each key is unique and is used to locate corresponding value in the array. The key can be integer or a string.
Associative arrays represent data set of arbitrary size with fast lookup of individual element without knowing its position within the array and without having to loop through all array elements. As associative arrays are intended for temporary data storage rather than storing persistent data, they cannot use with SQL statements such as INSERT and SELECT INTO. They can make persistent for a life of database session by declaring the type in package and assigning values in package body.
Varrays
The Varray is short for Variable Array. A Varray stores elements of the same type in the order in which they are added. The number of elements in a Varray must be known at the time of its declaration. In other words, a Varray has a fixed lower and upper bounds, making it most similar to collection types from other programming languages. Once it is created and populated, each element can be accessed by a numeric index.
Nested Table
Nested Tables, like the Varray, can be stored in a relational table as well as function as a PL/SQL program variable. Unlike Varray, nested table require no size specification. In other words, they are unbound.
When to Use What?
Varray
• Use to preserve ordered list.
• Use when working with a fixed set, with a known number of entries.
• Use when you need to store in the database and operate on the Collection as a whole.
Nested Table
• Use when working with an unbounded list that needs to increase dynamically.
• Use when you need to store in the database and operate on elements individually.
Associative Array
• Use when there is no need to store the Collection in the database. Its speed and indexing flexibility make it ideal for internal application use.
Conclusion
Oracle PL/SQL is not a difficult language to learn. However, like all good programming languages, there are many things we can do to maximize efficiency and minimize complexity. Given PL/SQL's power to interact with the database, it can be tempting to simply to fall into the habit of making excessive database calls to do our work. Collections can help you build simpler, faster Oracle database applications, the goal of every good PL/SQL developer.
PL/SQL applications typically consist of SQL statements intermixed with procedural logic to process data retrieved from the database. If compiled as a stored procedure, your PL/SQL code will reside on the server, an ideal place for programs that require intensive database interaction. Having said that, anytime a software application links up with a database, there is a performance price to be paid. Not only that, programs that continually switch off between code and SQL can become quite complex. PL/SQL collections can address some of these concerns.
Why Collections?
Just about all modern programming languages provide support for collections. A collection can be loosely defined as a group of ordered elements, all of the same type that allows programmatic access to its elements through an index. Commonly used collection types used in the programming world include arrays, maps, and lists.
Storing elements in a collection can provide a number of advantages. For starters, collections can help to simplify code. If you need to process a number of items of a similar type, storing these items in a collection will allow you to loop through each element with ease, referencing each one by an index. In addition, most languages define collection types that contain built-in methods to operate on the collection. Probably the biggest advantage a collection can provide is improved application performance. Developers utilize collections to 'cache' static data that needs to be regularly accessed. This results in reduced calls to a database.
Oracle Collections
Oracle provides three types of PL/SQL collections viz. Associative arrays, nested tables and Varrays. Each type of collection is described in the below section.
Associative Array
Associative arrays are set of key value pairs where each key is unique and is used to locate corresponding value in the array. The key can be integer or a string.
Associative arrays represent data set of arbitrary size with fast lookup of individual element without knowing its position within the array and without having to loop through all array elements. As associative arrays are intended for temporary data storage rather than storing persistent data, they cannot use with SQL statements such as INSERT and SELECT INTO. They can make persistent for a life of database session by declaring the type in package and assigning values in package body.
Varrays
The Varray is short for Variable Array. A Varray stores elements of the same type in the order in which they are added. The number of elements in a Varray must be known at the time of its declaration. In other words, a Varray has a fixed lower and upper bounds, making it most similar to collection types from other programming languages. Once it is created and populated, each element can be accessed by a numeric index.
Nested Table
Nested Tables, like the Varray, can be stored in a relational table as well as function as a PL/SQL program variable. Unlike Varray, nested table require no size specification. In other words, they are unbound.
When to Use What?
Varray
• Use to preserve ordered list.
• Use when working with a fixed set, with a known number of entries.
• Use when you need to store in the database and operate on the Collection as a whole.
Nested Table
• Use when working with an unbounded list that needs to increase dynamically.
• Use when you need to store in the database and operate on elements individually.
Associative Array
• Use when there is no need to store the Collection in the database. Its speed and indexing flexibility make it ideal for internal application use.
Conclusion
Oracle PL/SQL is not a difficult language to learn. However, like all good programming languages, there are many things we can do to maximize efficiency and minimize complexity. Given PL/SQL's power to interact with the database, it can be tempting to simply to fall into the habit of making excessive database calls to do our work. Collections can help you build simpler, faster Oracle database applications, the goal of every good PL/SQL developer.
No comments:
Post a Comment