[ Pobierz całość w formacie PDF ]
.(Okay, maybe it's not real magic, but you're almost guaranteed to say "How didthey do that?" the first time you try to program this stuff and find yourself bewildered.)19.4.1 Initializing Collection VariablesWith an index-by table datatype, initialization is a non-issue.Simply declaring an index-by table variablealso initializes it, in an "empty" state.Then you can just assign values to subscripted table elements as youdesire.Index values (subscripts) can be almost any positive or negative integer.A program can even assignsubscripts to index-by tables arbitrarily, skipping huge ranges of subscripts without paying a memory orperformance penalty.[2][2] This sparseness makes it possible to use an index-by table as an in-memoryrepresentation of almost any database table which uses an integer primary key.(See Chapter10 for a discussion of this eminently useful technique.)The allocation scheme for nested tables and VARRAYs is different from that of index-by tables.First off, ifyou don't initialize one of these collections, it will be "atomically null," and any attempt to read or write anelement of an atomically null collection will generate a runtime error.For example:DECLARE/* The variable cool_colors is not initialized in its|| declaration; it is "atomically null."*/cool_colors Color_tab_t;BEGINIF cool_colors IS NULL THEN -- valid; will be TRUE.IF cool_colors(1) IS NULL THEN -- invalid.cool_colors(1) := 'BLUE'; -- invalid689[Appendix A] What's on the Companion Disk?You must initialize the collection before using it.There are three ways you can initialize a collection:"Explicitly, using a constructor"Implicitly, via a fetch from the database"Implicitly, via a direct assignment of another collection variableThere is no requirement that you initialize any particular number of elements in a collection.Zero, one, ormore are fine, and you can always add more values later.In particular, don't be confused by VARRAYs.Justbecause you specify a limit on the number of elements it can hold does not imply that you have to put thatmany elements in when you initialize it.19.4.1.1 Initializing with a constructorEarlier, we saw declarations that looked like this:my_favorite_colors Color_tab_t := Color_tab_t('PURPLE', 'GREEN');my_favorite_numbers Number_t := Number_t(42, 65536);Color_tab_t( ) is the constructor function supplied by Oracle when we created the Color_tab_t collection type.This function accepts an arbitrary number of arguments, as long as each argument is of the "proper"datatype -- which in this case is VARCHAR2(30), since our original type definition statement was thefollowing:CREATE TYPE Color_tab_t AS TABLE OF VARCHAR2(30);At initialization, Oracle allocates to the variable an amount of memory necessary to hold the values yousupply as arguments.Initialization both creates the "slots" for the elements and populates them.So, if I want to "fix" the earlier invalid example, I can simply initialize the variable:DECLAREcool_colors Color_tab_t := Color_tab_t('VIOLET'); -- initializeBEGINIF cool_colors(1) IS NULL THEN -- This is OK now!What do you suppose Oracle does with the following initialization?working_colors Color_tab_t := Color_tab_t();This is a way of creating an "empty" collection.Empty is a sort of enigmatic state in which the collection isnot atomically null but still has no data.Whenever you create such an empty collection, you'll need to"extend" the collection variable later when you want to put elements into it.(The EXTEND built-in isexplored later in this chapter.)19.4.1.2 Initializing implicitly during direct assignmentYou can copy the entire contents of one collection to another as long as both are built from the exact samedatatype.When you do so, initialization comes along "for free."Here's an example illustrating implicit initialization that occurs when we assign wedding_colors to be thevalue of earth_colors.19.4.1 Initializing Collection Variables 690[Appendix A] What's on the Companion Disk?DECLAREearth_colors Color_tab_t := Color_tab_t('BRICK', 'RUST', 'DIRT');wedding_colors Color_tab_t;BEGINwedding_colors := earth_colors;wedding_colors(3) := 'CANVAS';END;This code initializes wedding_colors and creates three elements that match those in earth_colors.These areindependent variables rather than pointers to identical values; changing the third element of wedding_colors to'CANVAS' does not have any effect on the third element of earth_colors.Note that assignment is not possible when datatypes are merely "type-compatible." Even if you have createdtwo different types with the exact same definition, the fact that they have different names makes themdifferent types.A collection variable cannot be assigned to another variable of a different datatype:DECLARETYPE Local_colors_t IS VARRAY(16) OF VARCHAR2(30);TYPE Remote_colors_t IS VARRAY(16) OF VARCHAR2(30);l_color Local_colors_t := Local_colors_t('THALO BLUE');r_color Remote_colors_t;BEGINr_color := l_color; -- invalidEND;This code will fail with the compile-time error "PLS-00382: expression is of wrong type," because r_colorand l_color are of different types.19.4.1.3 Initializing implicitly via fetchIf you use a collection as a type in a database table, Oracle provides some very elegant ways of moving thecollection between PL/SQL and the table.As with direct assignment, when you use FETCH or SELECTINTO to retrieve a collection and drop it into a collection variable, you get automatic initialization of thevariable.Collections can turn out to be incredibly useful!Although we mentioned this briefly in an earlier example, let's take a closer look at how you can read anentire collection in a single fetch.First, we want to create a table containing a collection and populate it with acouple of values:CREATE TABLE color_models (model_type VARCHAR2(12),colors Color_tab_t)NESTED TABLE colors STORE AS color_model_colors_tab;insert into color_modelsvalues ('RGB', Color_tab_t('RED','GREEN','BLUE'));insert into color_modelsvalues ('CYMK',Color_tab_t('CYAN','YELLOW','MAGENTA','BLACK'));Now we can show off the neat integration features [ Pobierz całość w formacie PDF ]