Oracle PLSQL Language- P21

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

61
lượt xem 5
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'oracle plsql language- p21', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle PLSQL Language- P21

What USER_DEPENDENCIES SELECT name, type database FROM user_dependencies objects are WHERE referenced_name = 'FOO_T'; dependent on Foo_t? 18.6.2 SQL*Plus "Describe" Command If you're like me and don't like to type any more than necessary, you'll appreciate a wonderful enhancement that Oracle has provided for the describe command in SQL*Plus. It will report not only the attributes of an object type, but also the methods and their arguments. To illustrate: SQL> desc pet_t Name Null? Type ------------------------------- -------- ---- TAG_NO NUMBER(38) NAME VARCHAR2(60) ANIMAL_TYPE VARCHAR2(30) SEX VARCHAR2(1) PHOTO BINARY FILE LOB VACCINATIONS VACCINATION_LIST_T OWNER REF OF PERSON_T METHOD ------ MEMBER FUNCTION SET_TAG_NO RETURNS PET_T Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NEW_TAG_NO NUMBER IN METHOD ------ MEMBER FUNCTION SET_PHOTO RETURNS PET_T Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- FILE_LOCATION VARCHAR2 IN MEMBER PROCEDURE PRINT_ME Although the formatting could be improved, this is much easier than SELECTing the equivalent information from the data dictionary. 18.6.3 Schema Evolution Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Let's say that you have created an object type and you need to make a change to its definition. What do you do? The answer is that it depends -- on whether you have used the type, and on what type of change you want to make. Precious few modifications are easy; the rest will probably age you prematurely. Consider the implications of where you have used the type: q Type has no dependencies. Using CREATE OR REPLACE, you can change the object type to you heart's content. Or drop and recreate it; who cares? Life is good. q Type is used only in PL/SQL modules. In this case, since you don't have to rebuild any dependent tables, life is still easy. Oracle will automatically recompile dependent PL/SQL modules the next time they are called. q Type is used in one or more tables. Consider what would be a simple change to a relational table: adding a column. If you try to add a column to an object table, you get an "ORA-22856 cannot add columns to object tables." The "Action" for this message says we need to "Create a new type with additional attributes, and use the new type to create an object table. The new object table will have the desired columns." Your frustrations are beginning. OK, if you want to add an attribute, you're out of luck. What about methods? Oracle8.0 does include an ALTER TYPE statement that allows you to recompile an object specification or body. It also allows you to add new methods. It is extremely limited, however; it does not allow you to add or remove attributes, nor does it allow you to modify the quantity or datatypes of existing method arguments. The basic syntax is: Form I ALTER TYPE [ BODY ] type_name COMPILE [ SPECIFICATION | BODY ]; which does not solve our problem, or: Form II ALTER TYPE [ BODY ] type_name REPLACE ; Using Form II, we can, in fact, add an entirely new method to an object type, even if there are dependencies on the type. In the case of changing a method's specification (or deleting a method) in object type Foo_t which is implemented in table foo, you would think that export/import would work, using something like: 1. Export the foo table. 2. Drop the foo table. 3. CREATE OR REPLACE TYPE Foo_t with the new definition. 4. Import the foo table. But alas, it doesn't work, because when you CREATE OR REPLACE the type, it actually assigns a new OID to the type, and the import fails with IMP-00063 when it sees that the OID is different. Huh? What do you mean, "assigns a new OID to the type?" For reasons apparently having to do with facilitating certain operations in the Oracle Call Interface (OCI), object types themselves have an OID. See for yourself -- you can easily retrieve them from the USER_TYPES data dictionary view. Neither can you "CREATE new_object_table AS SELECT ... FROM old_object_table." Even if you could, the REFs wouldn't match up to the OIDs of the new table. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
It's even worse if you want to make any serious modifications to an object type and you have a dependency on the type from other types or tables. You cannot drop and recreate a parent object table unless you drop the child object types and object tables first. So maybe you could: 1. Create new object types and tables. 2. Somehow populate new from the old. 3. Drop the old object tables and types. 4. Rename the new types and object tables to the old names. It is not obvious to me how to do the second step in a way that will preserve REFs to the type. The only way I see to do it in a guaranteed fashion is to rely on relational primary and foreign keys for tuple identification. That is, your schema will include not only REFs but also equivalent foreign keys. Then, when your OIDs change because you have rebuilt an object table, you can update all the REFs to that object table using foreign key values. Not a pretty picture. Also, you cannot rename object types (number 4 above); attempting to do so fails with "ORA-03001: unimplemented feature." WARNING: Requiring the dropping of all dependent types and objects before altering a type is not going to endear the Oracle objects option to the average database administrator (or to anyone else, for that matter). Object schema evolution is a significant area where Oracle could make a lot of improvements. Previous: 18.5 Modifying Oracle PL/SQL Next: 18.7 Making the Persistent Objects Programming, 2nd Edition Objects Option Work 18.5 Modifying Persistent Book Index 18.7 Making the Objects Objects Option Work The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 18.6 Object Chapter 18 Next: 19. Nested Tables Housekeeping Object Types and VARRAYs 18.7 Making the Objects Option Work This stuff isn't designed to be easy for the beginner, and the complexities are more than syntax-deep. In addition to the operational limitations we have discussed, the act of "thinking objects" is not a trait that comes naturally to programmers schooled in database or structured approaches. But if you feel intimidated, take heart from this advice: "There may be an OO revolution, but that does not mean you have to make the change all at once. Instead, you can incorporate what you know worked before, and bring in the best that OO has to offer, a little at a time as you understand it."[16] [16] See Rick Mercer and A. Michael Berman, "Object-Oriented Technology and C++ in the First Year: Ten Lessons Learned." Presented at the Northeastern Small College Computing Conference, April 18- 20, 1996, and on the web at http://www.rowan.edu/ ~berman/tenLessons/paper.htm. If object technology is such a challenge, what is it that drives many organizations to consider object approaches in the first place? The overriding interest of managers seems to be their desire to reuse rather than reinvent the software needed to run their businesses.[17] In industries whose automation needs are not satisfied by off-the-shelf solutions, IS managers are continuously squeezed by the need to deliver more and more solutions while maintaining their legacy code, all while attempting to keep costs under control. [17] See Ivar Jacobson, "Reuse in Reality: The Reuse-Driven Software-Engineering Business." Presented at Object Expo Paris, available at http://www.rational.com/ support/techpapers/objex_ivar.pdf. It may not be obvious from our examples just how the objects option is going to facilitate reuse, particularly given Oracle8.0's lack of inheritance and difficulties with schema evolution. Indeed, the benefits of an object approach do not automatically accrue to the practitioner; large systems, in particular, must exhibit other characteristics.[18] Achieving reuse requires careful planning and deliberate execution. [18] See Grady Booch, Object-Oriented Analysis and Design with Applications, Addison-Wesley 1994. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Experts recommend not attempting object approaches just because someone says they are cool or because everyone else is doing it. Without a financial and time commitment to understanding, and without taking advantage of a different programming model, you are not likely to get much benefit, and yours will join the landscape of projects that didn't deliver. Yes, object approaches can be a way to do more with less. In fact, computer industry pundits assert that "componentware" is becoming the dominant form of software, and that application development is evolving into a process of wiring together components -- whether built in-house or procured -- rather than developing software from scratch. These components are typically built using object design (to specify the component's interfaces, and what it will and won't do) and object-oriented programming languages. The game isn't over, though; we need look only as close as the nearest desktop computer to see both the benefits and the perils of componentware. Windows DLLs, for example, allow module sharing and dynamic loading, but lack a superstructure for managing multiple versions. Other component models exist (CORBA, ActiveX, COM, JavaBeans) in varying states of industry acceptance. Almost certainly, Oracle Corporation will be adding needed features such as inheritance and schema evolution tools to their objects option. One day, objects may even be a standard part of the server. Until the technology matures, early adopters will enjoy the pleasures of finding workarounds, and will gain a deeper appreciation of features that appear later in the product. Previous: 18.6 Object Oracle PL/SQL Next: 19. Nested Tables Housekeeping Programming, 2nd Edition and VARRAYs 18.6 Object Housekeeping Book Index 19. Nested Tables and VARRAYs The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 18.7 Making the Chapter 19 Next: 19.2 Creating the Objects Option Work New Collections 19. Nested Tables and VARRAYs Contents: Types of Collections Creating the New Collections Syntax for Declaring Collection Datatypes Using Collections Collection Pseudo-Functions Collection Built-Ins Example: PL/SQL-to-Server Integration Collections Housekeeping Which Collection Type Should I Use? In PL/SQL Version 2, Oracle introduced the TABLE datatype as a way of storing singly dimensioned sparse arrays in PL/SQL. Known as the "PL/SQL table," this structure is thoroughly documented in Chapter 10, PL/SQL Tables. PL/SQL8 introduces two new collection structures that have a wide range of new uses. These structures are nested tables and variable-size arrays (VARRAYs). Like PL/ SQL tables, the new structures can also be used in PL/SQL programs. But what is dramatic and new is the ability to use the new collections as the datatypes of fields in conventional tables and attributes of objects. While not an exhaustive implementation of user-defined datatypes, collections offer rich new physical (and, by extension, logical) design opportunities for Oracle practitioners. In this chapter we'll include brief examples showing how to create and use collection types both in the database and in PL/SQL programs. We'll also show the syntax for creating collection types. We'll present the three different initialization techniques with additional examples, and we'll discuss the new built-in "methods," EXTEND, TRIM, and DELETE, for managing collection content. This chapter also contains an introduction to the new "collection pseudo-functions" that Oracle8 provides to deal with nonatomic values in table columns. Although we can't cover every aspect of SQL usage, the examples will give you a sense of how important -- and useful -- these new devices can be, despite their complexity. We also include a reference section that details all of the built-in methods for collections: for each we'll show its specification, an example, and some programming considerations. The chapter concludes with a brief discussion of which type of collection is most appropriate for some common situations. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
19.1 Types of Collections Oracle now supports three types of collections: q PL/SQL tables are singly dimensioned, unbounded, sparse collections of homogeneous elements and are available only in PL/SQL (see Chapter 10). These are now called index-by tables. q Nested tables are also singly dimensioned, unbounded collections of homogeneous elements. They are initially dense but can become sparse through deletions. Nested tables are available in both PL/SQL and the database (for example, as a column in a table). q VARRAYs, like the other two collection types, are also singly dimensioned collections of homogeneous elements. However, they are always bounded and never sparse. Like nested tables, they can be used in PL/SQL and in the database. Unlike nested tables, when you store and retrieve a VARRAY, its element order is preserved. Using a nested table or VARRAY, you can store and retrieve nonatomic data in a single column. For example, the employee table used by the HR department could store the date of birth for each employee's dependents in a single column, as shown in Table 19.1. Table 19.1: Storing a Nonatomic Column of Dependents in a Table of Employees Id (NUMBER) Name (VARCHAR2) Dependents_ages (Dependent_birthdate_t) 10010 Zaphod Beeblebrox 12-JAN-1763 4-JUL-1977 22-MAR-2021 10020 Molly Squiggly 15-NOV-1968 15-NOV-1968 10030 Joseph Josephs Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
10040 Cepheus Usrbin 27-JUN-1995 9-AUG-1996 19-JUN-1997 10050 Deirdre Quattlebaum 21-SEP-1997 It's not terribly difficult to create such a table. First we define the collection type: CREATE TYPE Dependent_birthdate_t AS VARRAY(10) OF DATE; Now we can use it in the table definition: CREATE TABLE employees ( id NUMBER, name VARCHAR2(50), ...other columns..., Dependents_ages Dependent_birthdate_t ); We can populate this table using the following INSERT syntax, which relies on the type's default constructor to transform a list of dates into values of the proper datatype: INSERT INTO employees VALUES (42, 'Zaphod Beeblebrox', ..., Dependent_birthdate_t( '12-JAN-1765', '4-JUL-1977', '22-MAR-2021')); One slight problem: most of us have been trained to view nonatomic data as a design flaw. So why would we actually want to do this? In some situations (for those in which you don't need to scan the contents of all the values in all the rows), theoreticians and practitioners alike consider nonatomic data to be perfectly acceptable. Even the conscience of the relational model, Chris Date, suggests that relational domains could contain complex values, including lists.[1] Some database designers have believed for years that the large percentage of nonatomic data inherent in their applications demands a nonrelational solution. [1] See Hugh Darwen and C. J. Date, "The Third Manifesto," SIGMOD Record, Volume 24 Number 1, March 1995. Setting aside theoretical arguments about "natural" data representations, Oracle collections provide a dramatic advantage from an application programmer's perspective: you can pass an entire collection Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
between the database and PL/SQL using a single fetch. This feature alone could have significant positive impact on application performance. As we've mentioned, within PL/SQL both nested tables and VARRAYs are ordered collections of homogeneous elements. Both bear some resemblance to the PL/SQL Version 2 table datatype, the elder member of the "collection" family. The new types are also singly dimensioned arrays, but they differ in areas such as sparseness (not exactly), how they're initialized (via a constructor), and whether they can be null (yes). One chief difference between nested tables and VARRAYs surfaces when we use them as column datatypes. Although using a VARRAY as a column's datatype can achieve much the same result as a nested table, VARRAY data must be predeclared to be of a maximum size, and is actually stored "inline" with the rest of the table's data. Nested tables, by contrast, are stored in special auxiliary tables called store tables, and there is no pre- set limit on how large they can grow. For this reason, Oracle Corporation says that VARRAY columns are intended for "small" arrays, and that nested tables are appropriate for "large" arrays. As we've mentioned, the old Version 2 table datatype is now called an index-by table , in honor of the INDEX BY BINARY_INTEGER syntax required when declaring such a type. Despite the many benefits of the new collection types, index-by tables have one important unique feature: initial sparseness. Table 19.2 illustrates many of the additional differences among index-by tables and the new collection types. Table 19.2: Comparing Oracle Collection Types Characteristic Index-By Table Nested Table VARRAY Dimensionality Single Single Single Usable in SQL? No Yes Yes Usable as column No Yes; data stored Yes; data stored "in datatype in a table? "out of line" (in line" (in same table) separate table) Uninitialized state Empty (cannot be null); Atomically null; Atomically null; elements undefined illegal to reference illegal to reference elements elements Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Initialization Automatic, when declared Via constructor, Via constructor, fetch, assignment fetch, assignment In PL/SQL, elements BINARY_INTEGER Positive integer Positive integer referenced via between 1 and between 1 and (-2,147,483,647 .. 2,147,483,647 2,147,483,647 2,147,483,647) Sparse? Yes Initially, no; after No deletions, yes Bounded? No Can be extended Yes Can assign value to Yes No; may need to No; may need to any element at any EXTEND first EXTEND first, and time? cannot EXTEND past upper bound Means of extending Assign value to element with Use built-in EXTEND (or a new subscript EXTEND TRIM), but only up procedure (or to declared TRIM to maximum size condense), with no predefined maximum Can be compared for No No No equality? Retains ordering and N/A No Yes subscripts when stored in and retrieved from database? The inevitable question is: Which construct should I use? This chapter reviews some examples of the new collections and offers some suggestions in this area. The short answer: q Nested tables are more flexible than VARRAYs for table columns. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
q VARRAYs are best when you need bounded arrays that preserve element order. q Index-by tables are the only option that allows initial sparseness. q If your code must run in both Oracle7 and Oracle8, you can use only index-by tables. We'll revisit these suggestions in more detail at the end of the chapter. Before diving in, though, let's review a few of the new terms: Collection A term which can have several different meanings: r A nested table, index-by table, or VARRAY datatype r A PL/SQL variable of type nested table, index-by table, or VARRAY r A table column of type nested table or VARRAY Outer table A term referring to the "enclosing" table in which you have used a nested table or VARRAY as a column's datatype Inner table The "enclosed" collection that is implemented as a column in a table; also known as a "nested table column" Store table The physical table which Oracle creates to hold values of the inner table Unfortunately, the term "nested table" can be a bit misleading. A nested table, when declared and used in PL/SQL, is not nested at all! It is instead fairly similar to an array. Even when you use a nested table as a table column, in Oracle 8.0 you can only nest these structures to a single level. That is, your column cannot consist of a nested table of nested tables. "Variable-size array" is also a deceptive name; one might assume, based on the fact that it is supposed to be "variable size," that it can be arbitrarily extended; quite the opposite is true. Although a VARRAY can have a variable number of elements, this number can never exceed the limit that you define when you create the type. Previous: 18.7 Making the Oracle PL/SQL Next: 19.2 Creating the Objects Option Work Programming, 2nd Edition New Collections 18.7 Making the Objects Book Index 19.2 Creating the New Option Work Collections The Oracle Library Navigation Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 19.1 Types of Chapter 19 Next: 19.3 Syntax for Collections Nested Tables and Declaring Collection VARRAYs Datatypes 19.2 Creating the New Collections There are two different ways of creating the new user-defined collection types: 1. You can define a nested table type or VARRAY type "in the database" using the CREATE TYPE command, which makes the datatype available to use for a variety of purposes: columns in database tables, variables in PL/SQL programs, and attributes of object types. 2. You can declare the collection type within a PL/SQL program using TYPE ... IS ... syntax. This collection type will then be available only for use within PL/SQL. Let's look at a few examples that illustrate how to create collections. 19.2.1 Collections "In the Database" 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. There is no good analogy for this command in Oracle7; it represents new functionality in the server. If we wanted to create a nested table datatype for variables that will hold lists of color names, we'll specify: CREATE TYPE Color_tab_t AS TABLE OF VARCHAR2(30); This command stores the type definition for the Color_tab_t nested table in the data dictionary. Once created, it can serve as the datatype for items in at least two different categories of database object: q A "column" in a conventional table q An attribute in an object type Defining 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. For example: CREATE TYPE Color_array_t AS VARRAY (16) OF VARCHAR2(30); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Type Color_array_t has an upper limit of 16 elements regardless of where it is used. 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. To show something other than a table of scalars, let's look at an example of a VARRAY of objects. Here we define an object type that will contain information about documents: CREATE TYPE Doc_t AS OBJECT ( doc_id INTEGER, name VARCHAR2(512), author VARCHAR2(60), url VARCHAR2(2000) ); We can then define a collection type to hold a list of these objects: CREATE TYPE Doc_array_t AS VARRAY(10) OF Doc_t; In this case, we've chosen to make it a variable-size array type with a maximum of ten elements. Another useful application of collections is in their ability to have elements which are REFs (reference pointers) to objects in the database. That is, your collection may have a number of pointers to various persistent objects (see Chapter 18, Object Types, for more discussion of REFs). Consider this example: CREATE TYPE Doc_ref_array_t AS TABLE OF REF Doc_t; 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. NOTE: While Oracle 8.0.3 allows you to create homogeneous collections, in some cases we might want to build heterogeneous collections. It would be useful to be able to define a type like the following: CREATE TYPE Generic_ref_t AS TABLE OF REF ANY; -- not in 8.0.3 This could allow you to make collections that hold references to more than one type of object in your database ... or, if OID's are globally unique, each REF could point to any object in any database on your entire network!1 19.2.1.1 Collection as a "column" in a conventional table Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
In the following case, we are using a nested table datatype as a column. When we create the outer table personality_inventory, we must tell Oracle what we want to call the "out of line" store table: CREATE TABLE personality_inventory ( person_id NUMBER, favorite_colors Color_tab_t, date_tested DATE, test_results BLOB) NESTED TABLE favorite_colors STORE AS favorite_colors_st; The NESTED TABLE ... STORE AS clause tells Oracle that we want the store table for the favorite_colors column to be called favorite_colors_st. 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. The only path by which you can read or write its attributes is via the outer table. (See Section 19.5, "Collection Pseudo-Functions" for 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. As you would expect, if you use a VARRAY as a column rather than as a nested table, no store table is required. Here, the colors collection is stored "in line" with the rest of the table: CREATE TABLE birds ( genus VARCHAR2(128), species VARCHAR2(128), colors Color_array_t ); 19.2.1.2 Collection as an attribute of an object type 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. (See Chapter 18 for more information about Oracle object types.) CREATE TYPE Auto_spec_t AS OBJECT ( make VARCHAR2(30), model VARCHAR2(30), available_colors Color_tab_t ); Because 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 CREATE TYPE ... AS OBJECT statement. When the time comes to implement the type as, say, an object table, you could do this: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CREATE TABLE auto_specs OF Auto_spec_t NESTED TABLE available_colors STORE AS available_colors_st; This statement requires a bit of explanation. When you create a "table of objects," Oracle looks at the object type definition to determine what columns you want. 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. So the phrase ...NESTED TABLE available_colors STORE AS available_colors_st says that you want the available_colors column to have a store table named available_colors_st. 19.2.2 Collections in PL/SQL Whether you use a predefined collection type or declare one in your program, using it requires that you declare a variable in a separate step. This declare-type-then-declare-variable motif should be familiar to you if you have ever used an index-by table or a RECORD type in a PL/SQL program. 19.2.2.1 Collection variables Using the collection types we've declared above, the following shows some legal declarations of PL/ SQL variables: DECLARE -- A variable that will hold a list of available font colors font_colors Color_tab_t; /* The next variable will later hold a temporary copy of || font_colors. Note that we can use %TYPE to refer to the || datatype of font_colors. This illustrates two different || ways of declaring variables of the Color_tab_t type. */ font_colors_save font_colors%TYPE; -- Variable to hold a list of paint colors paint_mixture Color_array_t; But there is no reason you must use only types you have created in the database. You can declare them locally, or mix and match from both sources: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DECLARE /* As with Oracle7 index-by tables, you can define || a table datatype here within a declaration section... */ TYPE Number_t IS TABLE OF NUMBER; /* ...and then you can use your new type in the declaration || of a local variable. The next line declares and initializes || in a single statement. Notice the use of the constructor, || Number_t(value, value, ...), to the right of the ": =" */ my_favorite_numbers Number_t := Number_t(42, 65536); /* Or you can just refer to the Color_tab_t datatype in the || data dictionary. This next line declares a local variable || my_favorite_colors to be a "nested" table and initializes it || with two initial elements using the default constructor. */ my_favorite_colors Color_tab_t := Color_tab_t ('PURPLE', 'GREEN'); BEGIN /* Once the local variables exist, usage is independent of whether || they were declared from local types or from types that live in || the data dictionary. */ my_favorite_colors(2) := 'BLUE'; -- changes 2nd element to BLUE my_favorite_numbers(1) := 3.14159; -- changes first element to pi END; This code also illustrates default constructors, which are special functions Oracle provides whenever you create a type, that serve to initialize and/or populate their respective types. A constructor has the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
same name as the type, and accepts as arguments a comma-separated list of elements. 19.2.2.2 Collections as components of a record Using a collection type in a record is very similar to using any other type. You can use VARRAYs, nested tables, or index-by tables (or any combination thereof) in RECORD datatypes. For example: DECLARE TYPE toy_rec_t IS RECORD ( manufacturer INTEGER, shipping_weight_kg NUMBER, domestic_colors Color_array_t, international_colors Color_tab_t ); RECORD types cannot live in the database; they are only available within PL/SQL programs. Logically, however, you can achieve a similar result with object types. Briefly, object types can have a variety of attributes, and you can include the two new collection types as attributes within objects; or you can define a collection whose elements are themselves objects. 19.2.2.3 Collections as module parameters Collections can also serve as module parameters. In this case, you cannot return a user-defined type that is declared in the module itself. You will instead use types that you have built outside the scope of the module, either via CREATE TYPE or via public declaration in a package. /* This function provides a pseudo "UNION ALL" operation on || two input parameters of type Color_tab_t. That is, it creates an || OUT parameter which is the superset of the colors of the two || input parameters. */ CREATE PROCEDURE make_colors_superset (first_colors IN Color_tab_t, second_colors IN Color_tab_t, superset OUT Color_tab_t) AS working_colors Color_tab_t := Color_tab_t(); element INTEGER := 1; which INTEGER; BEGIN /* Invoke the EXTEND method to allocate enough storage || to the nested table working_colors. */ Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
working_colors.EXTEND (first_colors.COUNT + second_colors.COUNT); /* Loop through each of the input parameters, reading their || contents, and assigning each element to an element of || working_colors. Input collections may be sparse. */ which := first_colors.FIRST; LOOP EXIT WHEN which IS NULL; working_colors(element) := first_colors(which); element := element + 1; which := first_colors.NEXT(which); END LOOP; which := second_colors.FIRST; LOOP EXIT WHEN which IS NULL; working_colors(element) := second_colors(which); element := element + 1; which := second_colors.NEXT(which); END LOOP; superset := working_colors; END; As a bit of an aside, let's take a look at the loops used in the code. The general form you can use to iterate over the elements of a collection is as follows: 1 which := collection_name.FIRST; 2 LOOP 3 EXIT WHEN which IS NULL; 4 -- do something useful with the current element... 5 which := collection_name.NEXT(which); 6 END LOOP; This works for both dense and sparse collections. The first assignment statement, at line 1, gets the subscript of the FIRST element in the collection; if it's NULL, that means there are no elements, and we would therefore exit immediately at line 3. But if there are elements in the collection, we reach line 4, where the program will do "something useful" with the value, such as assign, change, or test its value for some purpose. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The most interesting line of this example is line 5, where we use the NEXT method on the collection to retrieve the next-higher subscript above "which" on the right-hand side. In the event that a particular subscript has been DELETEd, the NEXT operator simply skips over it until it finds a non- deleted element. Also in line 5, if NEXT returns a NULL, that is our cue that we have iterated over all of the collection's elements, and it's time to exit the loop when we get back to line 3. You might also ask why we should use the local variable working_colors in the example above? Why not simply use the superset parameter as the working variable in the program? As it turns out, when we EXTEND a nested table, it must also read the table. So we would have to make superset an IN OUT variable, because OUT variables cannot be read within the program. It's better programming style to avoid using an IN OUT variable when OUT would suffice -- -and more efficient, especially for remote procedure calls. 19.2.2.4 Collections as the datatype of a function's return value In the next example, the programmer has defined Color_tab_t as the type of a function return value, and it is also used as the datatype of a local variable. The same restriction about datatype scope applies to this usage; types must be declared outside the module's scope. CREATE FUNCTION true_colors (whose_id IN NUMBER) RETURN Color_tab_t AS l_colors Color_tab_t; BEGIN SELECT favorite_colors INTO l_colors FROM personality_inventory WHERE person_id = whose_id; RETURN l_colors; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; This example also illustrates a long-awaited feature: the retrieval of a complex data item in a single fetch. This is so cool that it bears repeating, so we'll talk more about it later in this chapter. How would you use this function in a PL/SQL program? Since it acts in the place of a variable of type Color_tab_t, you can do one of two things with the returned data: 1. Assign the entire result to a collection variable 2. Assign a single element of the result to a variable (as long as the variable is of a type compatible with the collection's elements) The first option is easy. Notice, by the way, that this is another circumstance where you don't have to Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.