《Oracle PL/SQL开发指南》学习笔记20——集合（Collections）（章节回顾、测试）

集合平时用的不多，有机会再来精修。

对象类型（变长数组和表集合）（Object Types: Varray and Table Collections）

Review Section
This section has described the following points about SQL collection types:
1. SQL collections are defined as schema-level object types.
2. SQL collections require construction, which you do by calling the type name with a list of actual parameters that map to the definition of the object type's attributes.
3. SQL collections with a base scalar data type are Attribute Data Types (ADTs), while collections of object types are user-defined types (UDTs).
4. SQL collections have a base type, and it can be either a scalar or composite data type; and a SQL collection is a multilevel collection when its base composite data type is also a collection.
5. SQL collections can be function and procedure formal parameters and function return types in both SQL and PL/SQL operating contexts.
6. A varray collection is defined with a fixed size, while a table collection is not constrained by a maximum size value.
7. The varray always has a sequential or densely populated index.
8. The table collection starts with a sequential or densely populated index, but it is possible to delete elements from the collection, which creates gaps in the sequence of index values, potentially making the index sparsely populated.

 

关联数组（Associative Arrays）

Review Section
This section has described the following points about PL/SQL associative arrays:
1. Associative arrays are defined as PL/SQL-only data types.
2. Associative arrays must be assigned elements one at a time.
3. Associative arrays with a base scalar data type are Attribute Data Types (ADTs).
4. Associative arrays with a base composite data type are PL/SQL user-defined types (UDTs).
5. Composite type associative arrays with a base record type must be assigned one record at a time or one record element at a time, and those with a base object type must be assigned a constructed object type.
6. Associative arrays have a sparsely populated index, which may be numeric (negative, positive, or zero integer) values or string index values.

 

Oracle Collection API

Review Section
This section has described the following points about the Oracle Collection API:
1.  The Oracle Collection API simplifies working with all three types of Oracle collections.
2. The methods of the Oracle Collection API are functions or procedures; some only work with one or two of the collection types, while others perform differently based on the type of collection.
3. The LIMIT function only works with varray collections, and it captures the maximum number of elements allowed in the collection.
4.The overloaded EXTEND procedure only works with varray and table collections, and it allocates space before you can assign values.
5. The EXISTS function lets you check whether an element has been allocated memory.
6. The DELETE procedure lets you remove an element from a collection.
7. The COUNT function returns the number of elements in a varray or table collection. The COUNT and LIMIT function can return the same number for a varray collection, but only when the varray collection is full.
8. The NEXT, PRIOR, FIRST, and LAST functions let you traverse sparsely populated index lists by painlessly skipping gaps in the sequence.
9. The TRIM procedure lets you deallocate space from varray and table collections.

 

测验（Mastery Check）

The mastery check is a series of true-or-false and multiple-choice questions that let you confirm
how well you understand the material in the chapter. You may check Appendix I for answers to
these questions.

True or False:
1. SQL varray collections can only be used in a SQL context.
False. You can use a varray collection in a SQL scope or a PL/SQL scope.
2. Table collections can be used in a SQL context or a PL/SQL context.
True. You can use a table collection in a SQL scope or a PL/SQL scope.
3. Associative arrays can be used only in a PL/SQL context.
True. You can only use a table collection in a PL/SQL scope.
4. A table collection can hold a record or object type as its a composite base data type.
True. You can only use a table collection in a PL/SQL scope that refers to a composite record structure. There are limits on how you assign elements to the collection because object types and record structures aren’t interchangeable.
5. A varray has a fixed number of elements when you define it.
True. You define any varray (or varying array) with a maximum number of elements.
6. A varray or table of a scalar variable is an Attribute Data Type (ADT).
True. A varray or table of a scalar variable is an ADT and returns a single column_value pseudocolumn when translated by the TABLE function in a query.
7. A varray or table of a composite data type is a user-defined type (UDT).
True. A varray or table of a composite data type is a UDT and returns a list of column names that map to the user-defined object type when translated by the TABLE function in a query.
8. A LIMIT function from the Oracle Collection API only works with table collections.
False. A LIMIT function from the Oracle Collection API only works with a varray (or varying array) data type.
9. A BULK COLLECT statement can work with a table collection of object types.
False. A BULK COLLECT statement can only work with a table of scalar values or a table
of PL/SQL record structures.

10. The TABLE function lets you consume a varray or table collection as an ordinary SQL result set.
True. A TABLE function translates a collection into an aggregate result set, which is the form of any query’s result set.

Multiple Choice:
11. Which of the following is a densely populated index in an Oracle varray or table collection? (Multiple answers possible)
A. A sequence of negative integers without any gaps in the sequence of integers
B. A sequence of positive integers starting at a number of your choosing without any gaps in the sequence of integers
C. A sequence of positive integers starting at 1 without any gaps in the sequence
D. A sequence of letters without any gaps in the sequence of integers
E. A sequence of positive integers starting at 1 with some gaps in the sequence of integers
B is correct. Only a sequence of integers without gaps and starting at 1 is considered densely populated.
12. Which of the following support string indexes? (Multiple answers possible)
A. PL/SQL tables
B. Table collections
C. Varray collections
D. Associative arrays
E. Java ArrayList classes
D is correct. Only associative arrays support string indexes.
13. Which of the following is a sparsely populated index in an Oracle varray or table collection?
(Multiple answers possible)
A. A sequence of negative integers without any gaps in the sequence of integers
B. A sequence of positive integers starting at a number of your choosing without any gaps in the sequence of integers
C. A sequence of positive integers starting at 1 without any gaps in the sequence
D. A sequence of letters without any gaps in the sequence of integers
E. A sequence of positive integers starting at 1 with some gaps in the sequence of integers
D and E are correct. Letters implicitly have gaps even when they don’t, because they’re letters, not numbers, which makes them sparsely populated indexes. Any sequence of integers with gaps is sparsely populated.
14. Which of the following are boundary elements of collections? (Multiple answers possible)
A. The index value returned by the FIRST function
B. The index value returned by the COUNT function
C. The index value returned by the LIMIT function

D. The index value returned by the LAST function
E. All of the above
A, B, and D are correct. The FIRST function returns the lowest boundary element. The LAST function returns the highest boundary element. The COUNT function also returns the highest boundary element (when the index is numeric) because collections use 1-based numbering.
15. Which of the following collections work in SQL and PL/SQL contexts? (Multiple answers possible)
A. Varray collections of scalar data types
B. Varray collections of record data types
C. Table collections of scalar data types
D. Table collections of object data types
E. All of the above
A, C, and D are correct. A varray or table collection can work in SQL and PL/SQL contexts when the base data type is a scalar or object data type.