There are three types of collections: associative arrays (formerly known as index-by tables or PL/SQL tables), nested tables, and VARRAYs.
The following table compares these similar collection types:
1.17.1 Declaring a CollectionCollections are implemented as TYPEs. As with any programmer-defined type, you must first define the type; then you can declare instances of that type. The TYPE definition can be stored in the database or declared in the PL/SQL program. Each instance of the TYPE is a collection. The syntax for declaring an associative array is: TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY {BINARY_INTEGER | VARCHAR2 (size_limit)}; The syntax for a nested table is: [CREATE [OR REPLACE]] TYPE type_name IS TABLE OF element_type [NOT NULL]; The syntax for a VARRAY is: [CREATE [OR REPLACE]] TYPE type_name IS VARRAY | VARYING ARRAY (max_elements) OF element_type [NOT NULL]; The CREATE keyword defines the statement to be DDL and indicates that this type will exist in the database. The optional OR REPLACE keywords are used to rebuild an existing type, preserving the privileges. type_name is any valid identifier that will be used later to declare the collection. max_elements is the maximum size of the VARRAY. element_type is the type of the collection's elements. All elements are of a single type, which can be most scalar datatypes, an object type, or a REF object type. If the elements are objects, the object type itself cannot have an attribute that is a collection. Explicitly disallowed collection datatypes are BOOLEAN, NCHAR, NCLOB, NVARCHAR2, REF CURSOR, TABLE, and VARRAY. NOT NULL indicates that a collection of this type cannot have any null elements. However, the collection can be atomically null (uninitialized). 1.17.2 Initializing CollectionsInitializing an associative array is trivial simply declaring it also initializes it. Initializing a nested table or a VARRAY can be done in any of three ways: explicitly with a constructor, or implicitly with a fetch from the database or with a direct assignment of another collection variable. The constructor is a built-in function with the same name as the collection. It constructs the collection from the elements passed to it. The first example shows how you can create a nested table of colors and explicitly initialize it to three elements with a constructor: DECLARE TYPE colors_tab_t IS TABLE OF VARCHAR2(30); colors_tab_t('RED','GREEN','BLUE'); BEGIN The next example shows how you can create the nested table of colors and implicitly initialize it with a fetch from the database: -- Create the nested table to exist in the database. CREATE TYPE colors_tab_t IS TABLE OF VARCHAR2(32); -- Create table with nested table type as column. CREATE TABLE color_models (model_type VARCHAR2(12) ,colors color_tab_t) NESTED TABLE colors STORE AS color_model_colors_tab; -- Add some data to the table. INSERT INTO color_models VALUES('RGB',color_tab_t('RED','GREEN','BLUE')); INSERT INTO color_models VALUES('CYMK',color_tab_t('CYAN','YELLOW', 'MAGENTA' 'BLACK')); -- Initialize a collection of colors from the table. DECLARE basic_colors colors_tab_t; BEGIN SELECT colors INTO basic_colors FROM color_models WHERE model_type = 'RGB'; ... END; The third example shows how you can implicitly initialize the table via an assignment from an existing collection: DECLARE basic_colors Color_tab_t := Color_tab_t ('RED','GREEN','BLUE'); my_colors Color_tab_t; BEGIN my_colors := basic_colors; my_colors(2) := 'MUSTARD'; 1.17.3 Adding and Removing ElementsElements in an associative array can be added simply by referencing new subscripts. To add elements to nested tables or VARRAYs, you must first enlarge the collection with the EXTEND function, and then you can assign a value to a new element using one of the methods described in the previous section. Use the DELETE function to remove an element in a nested table regardless of its position. The TRIM function can also be used to remove elements, but only from the end of a collection. To avoid unexpected results, do not use both DELETE and TRIM on the same collection. 1.17.4 Collection Pseudo-FunctionsThere are several pseudo-functions defined for collections: CAST, MULTISET, and TABLE.
1.17.5 Collection MethodsThere are a number of built-in functions (methods) defined for all collections. These methods are called with dot notation: collection_name.method_name[(parameters)] The methods are listed in the following table:
The EXISTS function returns a BOOLEAN, and all other functions and procedures return BINARY_INTEGER except for collections indexed by VARCHAR2, which can return character strings. All parameters are of the BINARY_INTEGER type. Only EXISTS can be used on uninitialized nested tables or VARRAYs. Other methods applied to these atomically null collections will raise the COLLECTION_IS_NULL exception. DELETE and TRIM both remove elements from a nested table, but TRIM also removes the placeholder, while DELETE does not. This behavior may be confusing, because TRIM can remove previously DELETEd elements. Here is an example of some collection methods in use with an associative array: DECLARE TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64); continent_population population_type; howmany NUMBER; limit VARCHAR2(64); BEGIN continent_population('Australia') := 30000000; -- Create new entry continent_population('Antarctica') := 1000; -- Replace old value continent_population('Antarctica') := 1001; limit := continent_population.FIRST; DBMS_OUTPUT.PUT_LINE (limit); DBMS_OUTPUT.PUT_LINE (continent_population(limit)); limit := continent_population.LAST; DBMS_OUTPUT.PUT_LINE (limit); DBMS_OUTPUT.PUT_LINE (continent_population(limit)); END; / This example produces the following output: Antarctica 1001 Australia 30000000 Here is an example of some collection methods in use with a nested table: DECLARE TYPE colors_tab_t IS TABLE OF VARCHAR2(30); my_list colors_tab_t := colors_tab_t('RED','GREEN','BLUE'); element BINARY_INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE('my_list has ' ||my_list.COUNT||' elements'); my_list.DELETE(2); -- delete element two DBMS_OUTPUT.PUT_LINE('my_list has ' ||my_list.COUNT||' elements'); FOR element IN my_list.FIRST..my_list.LAST LOOP IF my_list.EXISTS(element) THEN DBMS_OUTPUT.PUT_LINE(my_list(element) || ' Prior= '||my_list.PRIOR(element) || ' Next= ' ||my_list.NEXT(element)); ELSE DBMS_OUTPUT.PUT_LINE('Element '|| element ||' deleted. Prior= '||my_ list.PRIOR(element) || ' Next= '||my_list.NEXT(element)); END IF; END LOOP; END; This example produces the output: my_list has 3 elements my_list has 2 elements RED Prior= Next= 3 Element 2 deleted. Prior= 1 Next= 3 BLUE Prior= 1 Next= 1.17.6 Collections and PrivilegesAs with other TYPEs in the database, you need the EXECUTE privilege on that TYPE in order to use a collection type created by another schema (user account) in the database. Note that Oracle9i Release 2 made it possible to use synonyms for user-defined TYPE names. 1.17.7 Nested Collections (Oracle9i)Nested collections are collections contained in members that are collections themselves. Nesting collections is a powerful way to implement object-oriented programming constructs within PL/SQL programs. For example: CREATE TYPE books IS TABLE OF VARCHAR2(64); CREATE TYPE our_books IS TABLE OF books; 1.17.8 Bulk BindsYou can use collections to improve the performance of SQL operations executed iteratively by using bulk binds. Bulk binds reduce the number of context switches between the PL/SQL engine and the database engine. Two PL/SQL language constructs implement bulk binds: FORALL and BULK COLLECT INTO. The syntax for the FORALL statement is: FORALL bulk_index IN lower_bound..upper_bound [SAVE EXCEPTIONS] sql_statement; bulk_index can be used only in the sql_statement and only as a collection index (subscript). When PL/SQL processes this statement, the whole collection, instead of each individual collection element, is sent to the database server for processing. To delete all the accounts in the collection inactives from the table ledger, do this: FORALL i IN inactives.FIRST..inactives.LAST DELETE FROM ledger WHERE acct_no = inactives(i); The default is for Oracle to stop after the first exception encountered. Use the keywords SAVE EXCEPTIONS to tell Oracle that processing should continue after encountering exceptions. The cursor attribute %BULK_EXCEPTIONS stores a collection of records containing the errors. These records have two fields, EXCEPTION_INDEX and EXCEPTION_CODE, which contain the FOR ALL iteration during which the exception was raised, as well as the SQLCODE for the exception. If no exceptions are raised, the SQL%BULK_EXCEPTION.COUNT method returns 0. For example: DECLARE TYPE NameList IS TABLE OF VARCHAR2(32); name_tab NameList := NameList('Pribyl' ,'Dawes','Feuerstein','Gennick' ,'Pribyl','Beresniewicz','Dawes','Dye'); error_count NUMBER; bulk_errors EXCEPTION; PRAGMA exception_init(bulk_errors, -24381); BEGIN FORALL indx IN name_tab.FIRST..name_tab.LAST SAVE EXCEPTIONS INSERT INTO authors (name) VALUES (name_tab(indx)); -- authors has pk index on name EXCEPTION WHEN others THEN error_count := SQL%BULK_EXCEPTIONS.COUNT; DBMS_OUTPUT.PUT_LINE('Number of errors is ' || error_count); FOR indx IN 1..error_count LOOP DBMS_OUTPUT.PUT_LINE('Error ' || indx || ' occurred during '||'iteration ' || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX); DBMS_OUTPUT.PUT_LINE('Error is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE)); END LOOP; END; / Number of errors is 2 Error 1 occurred during iteration 5 Error is ORA-00001: unique constraint (.) violated Error 2 occurred during iteration 7 Error is ORA-00001: unique constraint (.) violated The syntax for the BULK COLLECT INTO clause is: BULK COLLECT INTO collection_name_list; where collection_name_list is a comma-delimited list of collections, one for each column in the SELECT. Collections of records cannot be a target of a BULK COLLECT INTO clause. However, Oracle does support retrieving a set of typed objects and "bulk collecting" them into a collection of objects. The BULK COLLECT INTO clause can be used in SELECT INTO, FETCH INTO, or RETURNING INTO statements. For example: DECLARE TYPE vendor_name_tab IS TABLE OF vendors.name%TYPE; TYPE vendor_term_tab IS TABLE OF vendors.terms%TYPE; v_names vendor_name_tab; v_terms vendor_term_tab; BEGIN SELECT name, terms BULK COLLECT INTO v_names, v_terms FROM vendors WHERE terms < 30; ... END; The next function deletes products in an input list of categories, and the SQL RETURNING clause returns a list of deleted products: FUNCTION cascade_category_delete (categorylist clist_t) RETURN prodlist_t IS prodlist prodlist_t; BEGIN FORALL aprod IN categorylist.FIRST..categorylist.LAST DELETE FROM product WHERE product_id IN categorylist(aprod) RETURNING product_id BULK COLLECT INTO prodlist; RETURN prodlist; END; You can use the SQL%BULK_ROWCOUNT cursor attribute for bulk bind operations. It is like an associative array containing the number of rows affected by the executions of the bulk bound statements. The nth element of SQL%BULK_ROWCOUNT contains the number of rows affected by the nth execution of the SQL statement. For example: FORALL i IN inactives.FIRST..inactives.LAST DELETE FROM ledger WHERE acct_no = inactives(i); FOR counter IN inactives.FIRST..inactives.LAST LOOP IF SQL%BULK_ROWCOUNT(counter) = 0 THEN DBMS_OUTPUT.PUT_LINE('No rows deleted for '|| counter); END IF; END LOOP; You cannot pass SQL%BULK_ROWCOUNT as a parameter to another program, or use an aggregate assignment to another collection. %ROWCOUNT contains a summation of all %BULK_ROWCOUNT elements. %FOUND and %NOTFOUND reflect only the last execution of the SQL statement. |