NESTED TABLE available_colors STORE AS available_colors_st When it discovers that one of the object type's attributes, available_colors, is in fact a nested table, Oracle treats this table in a way similar to the examples above in other words, it wants to know what to name the store table. When you create a "table of objects," Oracle looks at the object type definition to determine what columns you want. This statement requires a bit of explanation. When the time comes to implement the type as, say, an object table, you could do this: (Seeįor more information about Oracle object types.)īecause there is no data storage required for the object type, it is not necessary to designate a name for the companion table at the time we issue the In this example, we are modeling automobile specifications, and each Auto_spec_t object will include a list of manufacturer's colors in which you can purchase the vehicle.
Here, the colors collection is stored "in line" with the rest of the table:ġ9.2.1.2 Collection as an attribute of an object type Section 19.5, "Collection Pseudo-Functions"įor a few examples of doing so.) You cannot even specify storage parameters for the store table it inherits the physical attributes of its outermost table.Īs you would expect, if you use a VARRAY as a column rather than as a nested table, no store table is required. The only path by which you can read or write its attributes is via the outer table.
You cannot directly manipulate data in the store table, and any attempt to retrieve or store data directly into favorite_colors_st will generate an error. STORE AS clause tells Oracle that we want the store table for the favorite_colors column to be called favorite_colors_st. NESTED TABLE favorite_colors STORE AS favorite_colors_st When we create the outer table personality_inventory, we must tell Oracle what we want to call the "out of line" store table: In the following case, we are using a nested table datatype as a column. or, if OID's are globally unique, each REF could point to any object in any database on your entire network!1ġ9.2.1.1 Collection as a "column" in a conventional table This could allow you to make collections that hold references to more than one type of object in your database. It would be useful to be able to define a type like the following:ĬREATE TYPE Generic_ref_t AS TABLE OF REF ANY - not in 8.0.3 While Oracle 8.0.3 allows you to create homogeneous collections, in some cases we might want to build heterogeneous collections. This statement says "create a user-defined type to hold lists of pointers to document objects." You can use a nested table of REFs as you would any other nested table: as a column, as an attribute in an object type, or as the type of a PL/SQL variable. Consider this example:ĬREATE TYPE Doc_ref_array_t AS TABLE OF REF Doc_t That is, your collection may have a number of pointers to various persistent objects (see REFs (reference pointers) to objects in the database. In this case, we've chosen to make it a variable-size array type with a maximum of ten elements.Īnother useful application of collections is in their ability to have elements which are We can then define a collection type to hold a list of these objects:ĬREATE TYPE Doc_array_t AS VARRAY(10) OF Doc_t Here we define an object type that will contain information about documents: To show something other than a table of scalars, let's look at an example of a VARRAY of objects. While these examples use VARCHAR2, collections can also consist of other primitive datatypes, object types, references to object types, or (in PL/SQL only) PL/SQL record types. Type Color_array_t has an upper limit of 16 elements regardless of where it is used. For example:ĬREATE TYPE Color_array_t AS VARRAY (16) OF VARCHAR2(30) Once created, it can serve as the datatype for items in at least two different categories of database object:ĭefining a VARRAY datatype is similar to defining a nested table, but you must also specify an upper bound on the number of elements collections of this type may contain. This command stores the type definition for the Color_tab_t nested table in the data dictionary. If we wanted to create a nested table datatype for variables that will hold lists of color names, we'll specify:ĬREATE TYPE Color_tab_t AS TABLE OF VARCHAR2(30) There is no good analogy for this command in Oracle7 it represents new functionality in the server. Before you can define a database table containing a nested table or VARRAY, you must first create the collection's datatype in the database using the CREATE TYPE statement.
0 kommentar(er)
