10.9 How do I…Achieve array functionality with PL/SQL Index-By tables? Steps1. Run SQL*Plus and connect as the WAITE user account. The code in the CHP10_32.SQL file and the resulting output is shown in Figure 10.32. The PL/SQL block contained in the file uses a FOR loop to define a PL/SQL table and fill it with data. When the table is filled, a second FOR loop moves through the table, displaying the values contained.
Lines 1 through 5 contain the declarative section of the PL/SQL block. The TYPE statement started in line 2 creates a datatype called ARRAY_TYPE, which defines a table of numbers. Line 4 presents the required INDEX BY BINARY_INTEGER clause to create the index for the table. Table declarations must use BINARY_INTEGER indexes. Line 5 declares a variable of the table type defined in the previous lines. Lines 6 through 14 contain the execution section of the block. The FOR loop contained in lines 7 through 9 populates the first 10 values of the table with a calculated value. The FOR loop contained in lines 10 through 13 loops through the table, displaying the values contained in the records.
When the block is executed, the PL/SQL table is created and populated with elements. When the table is populated, the values of the table are displayed within SQL*Plus.\
2. Run CHP10_33.SQL, which has a PL/SQL block that declares a cursor and uses the columns returned by the cursor as a basis for defining a user-defined TABLE type. The code and output are shown in Figure 10.33.
Lines 1 through 8 contain the declarative section of the PL/SQL block. Line 2 defines a cursor to query the DEPT10 table. The TYPE statement in line 3 creates a new datatype called DEPT_TYPE. Line 4 specifies that the new datatype is a table of records corresponding with the columns returned by the cursor. The INDEX BY BINARY_INTEGER clause in line 5 is necessary to define an Index-By table. Line 6 declares a record variable of the cursor type that will serve as a placeholder for fetching rows from the cursor. Line 7 declares a variable of the newly defined table type. Lines 9 through 16 contain the execution section of the block. The FOR loop contained in lines 10 through 15 fetches all rows from the DEPT10 table. A row is first fetched from the DEPT10 table into the record variable in line 10. Line 11 shows how records of the same type can be directly assigned without using a dot notation. The Index-By table is populated with rows returned by the cursor through the cursor type record variable as the mediator. After each row is fetched from the database table into the Index-By table, the column values contained in the Index-By table are displayed in SQL*Plus, using the DBMS_OUTPUT.PUT_LINE procedure.
When the block is executed, the PL/SQL Index-By table is initialized and populated with elements. As the table becomes populated, the values of columns are displayed in SQL*Plus.
How It Works
Index-By tables are similar to one-dimensional arrays and are referenced like arrays of records. Index-By tables can be created using any of the standard PL/SQL datatypes. In PL/SQL version 2.3 and later, you can even use record variables in PL/SQL tables.
To use an Index-By table, a datatype for the table must first be defined. When the datatype is defined, variables of the new datatype can be declared. Steps 1 and 2 show how you can use an Index-By table like an array. A datatype is defined in the declarative section of the block with the TYPE statement. Step 1 defines a table to contain values of the NUMBER datatype, whereas step 2 defines a table to contain records of a cursor type. Any built-in datatype or user-defined datatype can be specified in the TABLE OF clause.
In contrast to record types, Index-By tables cannot contain a table or a record with composite fields. The INDEX BY BINARY_INTEGER clause is required in all Index-By table definitions. When the table variable is declared, it can be referenced like an array by specifying an index value in parentheses to represent an element in the table. Elements of Index-By tables are initialized with null values automatically during declaration.
Comments
Array processing is a common need in procedural languages. PL/SQL Index-By tables can be used like one-dimensional arrays within your code. If your requirements include two-dimensional arrays, this requires considerably more effort. You can define a table type based on a column using the %TYPE attribute, based on a database table or cursor using the %ROWTYPE attribute, or by using a RECORD type. If you are running PL/SQL version 2.3 or later, additional support of record variables in PL/SQL tables lets you create array-type structures to represent records returned by a query. Refer to How-To 10.9 for more information on creating variables of user-defined record types. PL/SQL tables can be passed as parameters to subprograms. Other new datatypes such as Varrays and Nested Tables are addressed in Chapter 16.