When you declare an associative array using a string as the key, the declaration must use a VARCHAR2, STRING, or LONG type. If you need to change these settings during the session, make sure to set them back to their original values before performing further operations with these kinds of associative arrays. For example, changing the NLS_COMP or NLS_SORT initialization parameters within a session might cause methods such as NEXT and PRIOR to raise exceptions. If settings for national language or globalization change during a session that uses associative arrays with VARCHAR2 key values, the program might encounter a runtime error. How Globalization Settings Affect VARCHAR2 Keys for Associative Arrays You can make them persistent for the life of a database session by declaring the type in a package and assigning the values in a package body. For simple temporary storage of lookup data, associative arrays let you avoid using the disk space and network operations required for SQL tables.īecause associative arrays are intended for temporary data rather than storing persistent data, you cannot use them with SQL statements such as INSERT and SELECT INTO. It is like a simple version of a SQL table where you can retrieve values based on the primary key. Howmany := continent_population(continent_population.LAST) Īssociative arrays help you represent data sets of arbitrary size, with fast lookup for an individual element without knowing its position within the array and without having to loop through all the array elements. Returns the value corresponding to the last key, in this Returns 'Australia' as that comes last alphabetically. Returns 'Antarctica' as that comes first alphabetically. Howmany := country_population('Greenland') Ĭontinent_population('Australia') := 30000000 Ĭontinent_population('Antarctica') := 1000 - Creates new entryĬontinent_population('Antarctica') := 1001 - Replaces previous value Looks up value associated with a string The built-in function NEXT lets you iterate over all the subscripts of a nested table, even if the sequence has gaps.Įxample 5-1 Declaring Collection Types DECLARE TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64) Ĭountry_population('Greenland') := 100000 - Creates new entryĬountry_population('Iceland') := 750000 - Creates new entry You can delete elements from a nested table using the built-in procedure DELETE. Initially, nested tables are dense, but they can become sparse (have nonconsecutive subscripts). Nested tables might not have consecutive subscripts, while arrays are always dense (have consecutive subscripts). The size of a nested table can increase dynamically however, a maximum limit is imposed. Nested tables do not have a declared number of elements, while arrays have a predefined number as illustrated in Figure 5-1. Nested tables differ from arrays in two important ways: That gives you array-like access to individual rows. When you retrieve a nested table from the database into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. Oracle stores the rows of a nested table in no particular order. Within the database, nested tables are column types that hold sets of values. You can model multi-dimensional arrays by creating nested tables whose elements are also nested tables. You can think of them as one-dimensional arrays with no declared number of elements. PL/SQL nested tables represent sets of values. Nested tables and varrays can also be attributes of object types. To look up data that is more complex than single values, you can store PL/SQL records or SQL object types in collections. You can pass collection variables as parameters to stored subprograms. You can define collection types in a procedure, function, or package. To use collections in an application, you define one or more PL/SQL types, then define variables of those types. They can be stored and retrieved through SQL, but with less flexibility than nested tables.Īlthough collections have only one dimension, you can model multi-dimensional arrays by creating collections whose elements are also collections. You can define equivalent SQL types, allowing varrays to be stored in database tables. They use sequential numbers as subscripts. Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
Nested tables hold an arbitrary number of elements. These are similar to hash tables in other programming languages. Associative arrays, also known as index-by tables, let you look up elements using arbitrary numbers and strings for subscript values.
0 kommentar(er)
