1 / 21

Collections Oracle Database PL/SQL 10g Programming

Collections Oracle Database PL/SQL 10g Programming. Chapter 6. Collections. Collection Types VARRAY Collections Nested Table Collections Associative Array Collections Collection API. Collections Collection Types: Definition. Collections are lists. Collections are ordered or unordered.

keita
Download Presentation

Collections Oracle Database PL/SQL 10g Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CollectionsOracle Database PL/SQL 10g Programming Chapter 6

  2. Collections • Collection Types • VARRAY Collections • Nested Table Collections • Associative Array Collections • Collection API Oracle Database PL/SQL 10g Programming (Chapter 6)

  3. CollectionsCollection Types: Definition • Collections are lists. • Collections are ordered or unordered. • Ordered lists are indexed by numbers. • Unordered lists are indexed by unique strings. • Collection elements can be: • Scalar variables • Compound variables Oracle Database PL/SQL 10g Programming (Chapter 6)

  4. CollectionsCollection Types: VARRAY • VARRAY collections are densely populated structures, indexed by sequential numbers. • VARRAY collections have an initial maximum size. • VARRAY collections can be used as column data types. Oracle Database PL/SQL 10g Programming (Chapter 6)

  5. CollectionsCollection Types: Nested Table • Nested tables are densely populated structures, indexed by sequential numbers. • Nested tables are have no maximum size. • Nested tables can be used as column data types. Oracle Database PL/SQL 10g Programming (Chapter 6)

  6. CollectionsCollection Types: Associative Arrays • Associative arrays are sparsely populated structures, indexed by unique numbers or strings. • Associative arrays have no maximum size. • Associative arrays cannot be used as column data types. Oracle Database PL/SQL 10g Programming (Chapter 6)

  7. CollectionsCollection Types: Multiset Operators • MULTISET EXCEPT This operator removes one set from another, like the SQL MINUS operator. • MULTISET INTERSECT This operator takes two sets and merges them into a new set that contains one copy of elements found in both original sets, like the SQL INTERSECT operator. • MULTISET UNION This operator takes two sets and merges them into a new set without eliminating duplicate values, like the UNION ALL operator. • SET This operator removes duplicates from a set and acts like the DISTINCT operator in a SQL statement. Oracle Database PL/SQL 10g Programming (Chapter 6)

  8. CollectionsVARRAY Collection Types: Rules • VARRAY data types can be defined: • As PL/SQL user-defined types. • As SQL collection data types of scalar variables. • As SQL collection data types of compound, object type, variables. • VARRAY data types require explicit construction. • VARRAY data types allocate space at construction or by calling the Oracle Collection API EXTEND methods to allocate space for an element or set of elements. Oracle Database PL/SQL 10g Programming (Chapter 6)

  9. CollectionsVARRAY Collection Types: SQL Declaration -- Declare VARRAY SQL data type. CREATE [OR REPLACE]TYPEnumber_list AS VARRAY(3) OF NUMBER; Oracle Database PL/SQL 10g Programming (Chapter 6)

  10. CollectionsVARRAY Collection Types: PL/SQL Declaration DECLARE -- Declare VARRAY PL/SQL data type. TYPEnumber_listIS VARRAY(3) OF NUMBER; -- Create a list without any allocated space. empty_scalar_list NUMBER_LIST := NUMBER_LIST(); -- Create a list of NULL values with two allocated space. null_scalar_list NUMBER_LIST := NUMBER_LIST(NULL,NULL); -- Create a list of values with three allocated space. value_scalar_list NUMBER_LIST := NUMBER_LIST(1,2,3); BEGIN … next_slide … END; / Oracle Database PL/SQL 10g Programming (Chapter 6)

  11. CollectionsVARRAY Collection Types: PL/SQL Assignment DECLARE … prior_slide … BEGIN empty_scalar_list.EXTEND; empty_scalar_list(1) := 1829; END; / Oracle Database PL/SQL 10g Programming (Chapter 6)

  12. CollectionsNested Table Collection Types: Rules • Nested table data types can be defined: • As PL/SQL user-defined types. • As SQL collection data types of scalar variables. • As SQL collection data types of compound, object type, variables. • Nested table data types require explicit construction. • Nested table data types allocate space at construction or by calling the Oracle Collection API EXTEND methods to allocate space for an element or set of elements. Oracle Database PL/SQL 10g Programming (Chapter 6)

  13. CollectionsNested Table Collection Types: SQL Declaration -- Declare VARRAY SQL data type. CREATE [OR REPLACE]TYPEnumber_list AS TABLE OF NUMBER; Oracle Database PL/SQL 10g Programming (Chapter 6)

  14. CollectionsNested Table Collection Types: PL/SQL Declaration DECLARE -- Declare VARRAY PL/SQL data type. TYPEnumber_listIS TABLE OF NUMBER; -- Create a list without any allocated space. empty_scalar_list NUMBER_LIST := NUMBER_LIST(); -- Create a list of NULL values with two allocated space. null_scalar_list NUMBER_LIST := NUMBER_LIST(NULL,NULL); -- Create a list of values with three allocated space. value_scalar_list NUMBER_LIST := NUMBER_LIST(1,2,3); BEGIN … next_slide … END; / Oracle Database PL/SQL 10g Programming (Chapter 6)

  15. CollectionsAssociative Array Collection Types: Rules • Associative Array data types can be defined as PL/SQL user-defined types. • Associative Array data types cannot be defined as SQL collection data types. • Associative Array data types do not require explicit construction. • Associative Array data types require element by element assignment, or bulk assignments. • Associative Array data types do not require explicit space allocation. • Associative Array data types can use the Oracle Collection API. Oracle Database PL/SQL 10g Programming (Chapter 6)

  16. CollectionsNested Table Collection Types: PL/SQL Usage DECLARE -- Declare VARRAY PL/SQL data type. TYPEnumber_listIS TABLE OF NUMBER INDEX BY BINARY_INTEGER; -- Declare a variable. empty_scalar_list NUMBER_LIST; BEGIN empty_scalar_list(1) := 1829; END; / Oracle Database PL/SQL 10g Programming (Chapter 6)

  17. CollectionsCOLLECTION API: Methods • COUNT a method that returns the number of elements in a collection. • DELETE(n) a method that takes a single formal parameter that is an index value, and it removes the element pointed to by the equivalent index value. • DELETE(n,m) a method that takes two formal parameter index values, and it removes a range of elements pointed to by the equivalent index value. • EXISTS(n) a method that takes one formal parameter index value, and returns TRUE if found in the collection and FALSE if not. If the collection is a null element structure, the method also returns FALSE. Oracle Database PL/SQL 10g Programming (Chapter 6)

  18. CollectionsCOLLECTION API: Methods • EXTEND a method that takes no formal parameter and extends space for one new element. • EXTEND(n) a method that takes one formal parameter, which designates how many spaces to extend the collection. • EXTEND(n,m) a method that takes two formal parameters; the first designates how many spaces to extend, and the second identifies an index to copy into the newly indexed spaces. • FIRST a method that takes no formal parameter and returns the first index value, this is the lowest number for numeric indexes and lowest value string for string indexes. • LAST a method that takes no formal parameter and returns the last index value by using opposite rules to the FIRST method. Oracle Database PL/SQL 10g Programming (Chapter 6)

  19. CollectionsCOLLECTION API: Methods • LIMIT a method that returns the highest allowed element number in a VARRAY collection. • NEXT(n) a method that takes a single formal parameter that is an index value, and it returns the next value in the collection. • PRIOR(n) a method that takes a single formal parameter that is an index value, and returns the prior indexed value in the collection. • TRIM a method that takes no formal parameter, and removes the highest subscripted value from a collection. • TRIM(n) a method that takes one formal parameter, which is an INTEGER; and it removes that number of subscripted values from the end of a collection. Oracle Database PL/SQL 10g Programming (Chapter 6)

  20. CollectionsCOLLECTION API: Exceptions • COLLECTION_IS_NULL • Raised when attempting to access a null collection. • NO_DATA_FOUND • Raised when attempting to access values that are not present in an initialized collection. • SUBSCRIPT_BEYOND_COUNT • Raised when attempting to access beyond the highest subscripted value. • SUBSCRIPT_OUTSIDE_LIMIT • Raised when attempting to access beyond a VARRAY index value limit. • VALUE_ERROR • Raised when attempting to cast an incorrect data type to the index type of the subscript. Oracle Database PL/SQL 10g Programming (Chapter 6)

  21. Summary • Collection Types • VARRAY Collections • Nested Table Collections • Associative Array Collections • Collection API Oracle Database PL/SQL 10g Programming (Chapter 6)

More Related