Chapter 6. Index-by Tables

Oracle8 introduced the term collection to describe three new datatypes: index-by tables, nested tables, and variable arrays. These collections are quite similar and are really just conceptual riffs on the old PL/SQL table. We’ll examine each of these new types in the next three chapters.

The index-by table (or the structure formerly known as a PL/SQL table) is PL/SQL’s answer to an array. Like an array, an index-by table consists of homogeneous elements (meaning that all the elements have the same datatype) that are indexed by an integer (hence the name index-by table). Unlike arrays in other languages, PL/SQL index-by tables are one-dimensional, sparse (meaning that there can be “gaps” between elements), and unconstrained (meaning that the number of elements can grow). This chapter tests your understanding of these extremely useful structures.

Beginner

6-1.

What two steps are required before you can work with an index-by table?

6-2.

Can index-by tables be declared with indexes other than BINARY_INTEGERs?

6-3.

Consider this definition of an index-by table TYPE:

DECLARE
   TYPE number_tabtype IS TABLE OF NUMBER
      INDEX BY BINARY_INTEGER;

Which of the following index-by table declarations is valid?

  1. number_tabtype salary_list;

  2. salaries, commissions number_tabtype;

  3. acres_clearcut number_tabtype;

6-4.

Consider this definition of an index-by table TYPE and table:

DECLARE
   TYPE number_tabtype IS TABLE OF NUMBER
      INDEX BY BINARY_INTEGER;
   acres_clearcut number_tabtype;

Which of the following assignments is valid?

  1. acres_clearcut.15 := 1000000;
  2. acres_clearcut(15) := 1000000;
  3. acres_clearcut (15, 1000000);
  4. acres_clearcut.ASSIGN (15, 1000000);

6-5.

True or false?

  1. Index-by tables are capable of handling only a single dimension of information.

  2. Different rows in an index-by table can hold different types of information. Row 1, for example, might contain another index-by table, while row 2 contains a string.

  3. Index-by tables can be stored in the database as columns in a table.

6-6.

What types of data can be stored in index-by tables?

6-7.

What is the difference between an index-by table and a PL/SQL table?

6-8.

True or false?

  1. There are no upper or lower bounds on the integer values you can use as row numbers.

  2. Row numbers must be positive.

  3. You must always specify a row number as an integer.

6-9.

True or false?

  1. When first declared, an index-by table contains no defined rows.

  2. The default value for a row in an index-by table is NULL.

  3. Each row in an index-by table contains the same type of data.

  4. If you try to access (read) a row in a table that is not yet defined, PL/SQL raises the VALUE_ERROR exception.

6-10.

How many elements does an index-by table contain when it is created?

6-11.

Can an index-by table element be set back to undefined once you have assigned it a value?

6-12.

What error is raised when you execute the following PL/SQL code:

DECLARE
   TYPE names_type IS TABLE OF VARCHAR2(100)
      INDEX BY BINARY_INTEGER;
   all_my_cousins names_type;
BEGIN
   all_my_cousins (POWER (2, 32)) := 'George Washington';
END;
/

6-13.

True or false?

  1. Once you have defined a table TYPE, you can declare more than one index-by table from that TYPE.

  2. The table TYPE and all tables declared from that type must be defined in the same PL/SQL block or scope.

  3. Since BINARY_INTEGER is the only valid index type for an index-by table, you can omit the INDEX BY clause.

Intermediate

6-14.

Consider the following table declarations:

-- table of numbers
TYPE number_table_type IS TABLE OF number
  INDEX BY BINARY_INTEGER;
number_table number_table_type;

-- table of empnos
TYPE empno_table_type IS TABLE OF employee.employee_id%TYPE
  INDEX BY BINARY_INTEGER;
empno_table empno_table_type;

-- table of employee table rows
TYPE employee_table_type IS TABLE of employee%ROWTYPE
  INDEX BY BINARY_INTEGER;
employee_table employee_table_type;

-- table of records of employee numbers
TYPE employee_record IS RECORD
  (empno employee.employee_id%TYPE );
TYPE employee_record_table_type IS TABLE OF employee_record
  INDEX BY BINARY_INTEGER;
employee_record_table employee_record_table_type;

Complete the following chart by supplying the command that assigns the value “100” to element number 100. For the tables of records, assign the value to the empno field.

Table Name

Syntax

Number_table

 

Empno_table

 

Employee_table

 

Employee_record_table

 

6-15.

Oracle offers a set of “methods” (different in syntax usage from functions) to help you work with collections. Identify the method associated with each description:

  1. Returns TRUE if an element has been defined or FALSE if it has not.

  2. Returns the number of elements in an index-by table. It is 0 when the table is initially defined.

  3. Returns the number of the first element in the index-by table. It returns NULL if no elements are defined.

  4. Returns the number of the last element in the index-by table. It returns NULL if no elements are defined.

  5. Returns the number of the next lowest element to the one specified. If there are no lower elements it returns NULL. It also returns NULL if no elements are defined.

  6. Returns the number of the next highest element to the one specified. If there are no higher elements, it returns NULL. It also returns NULL if no elements are defined.

  7. Removes (undefines) elements in the index-by table.

6-16.

What can you do to ensure that index-by table elements are never set to NULL?

6-17.

Write a block to retrieve employee numbers from the employee table and put them into an index-by table sequentially, starting with row 0.

6-18.

Write a block to retrieve employee last names from the employee table and put them into an index-by table nonsequentially, using the employee ID number as the index-by table row. Why would you want to use the employee ID number as the row number?

6-19.

Nesting index-by table methods, an often underutilized feature, is sometimes required to solve complex problems. Here is a simple example of nested calls to index-by table methods. What will the output look like?

DECLARE
  TYPE number_table_type IS TABLE OF number
    INDEX BY BINARY_INTEGER;
  number_table number_table_type;
  v_element PLS_INTEGER;

BEGIN
  number_table(1)  := 3;
  number_table(2)  := 2;
  number_table(3)  := 1;
  v_element := number_table.PRIOR
     (number_table.NEXT(number_table.FIRST));
  DBMS_OUTPUT.PUT_LINE(
     'Element ' || v_element || ' ' ||
     'Value '   || number_table(v_element));
END;

6-20.

What are the two ways to delete all entries from an index-by table?

6-21.

What is the output of the following block?

DECLARE
  TYPE a_table_type IS TABLE OF VARCHAR2(30)
    INDEX BY BINARY_INTEGER;
  a_table a_table_type;

BEGIN
  IF a_table IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('The table is NULL?'),
  ELSE
    DBMS_OUTPUT.PUT_LINE('The table is NOT NULL!'),
  END IF;
END;

6-22.

Rewrite the following code into a single line:

IF the_table.FIRST IS NULL /* table is empty */
THEN
  the_table(1) := 2067;
ELSE
  the_table(the_table.LAST + 1) := 2067;
END IF;

Expert

6-23.

My son keeps track of every year’s favorite birthday presents in a database table. To display the contents of that table, he loads them into an index-by table, using the year as the row number:

/* Filename on web page: presents.sql */
CREATE TABLE birthday (
   party_date DATE,
   fav_present VARCHAR2(100));

INSERT INTO birthday VALUES
   ('01-OCT-92', 'TEENAGE MUTANT NINJA TURTLE'),
INSERT INTO birthday VALUES
   ('01-OCT-98', 'GAMEBOY POKEMON'),

DECLARE
   TYPE name_tt IS TABLE OF birthday.fav_present%TYPE
      INDEX BY BINARY_INTEGER;
   the_best name_tt;
BEGIN
   FOR rec IN (
      SELECT TO_NUMBER (TO_CHAR (party_date, 'YYYY')) indx,
             fav_present
        FROM birthday)
   LOOP
      the_best (rec.indx) := rec.fav_present;
   END LOOP;

That’s all well and good, but what he really wants to do is display those favorite presents and savor the moments of his recent past. The following loops demonstrate different ways he could see this information. Which is the best approach to take and what is wrong with each of the others?

  1. FOR indx IN the_best.FIRST .. the_best.LAST
LOOP
   DBMS_OUTPUT.PUT_LINE (the_best(indx));
END LOOP;
  2. IF the_best.COUNT > 0
THEN
   FOR indx IN the_best.FIRST .. the_best.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE (the_best(indx));
   END LOOP;
END IF;
  3. IF the_best.COUNT > 0
THEN
   FOR indx IN the_best.FIRST .. the_best.LAST
   LOOP
      IF the_best.EXISTS (indx)
      THEN
         DBMS_OUTPUT.PUT_LINE (the_best(indx));
      END IF;
   END LOOP;
END IF;
  4. /* assume indx is declared as PLS_INTEGER */
indx := the_best.FIRST;
LOOP
   EXIT WHEN indx IS NULL;
   DBMS_OUTPUT.PUT_LINE (the_best(indx));
   indx := the_best.NEXT (indx);
END LOOP;

6-24.

The salary_ grouping package creates a matrix grouping of employees and salary ranges. The package is used as follows:

DECLARE
  v_range salary_grouping.v_range_type;
BEGIN
  v_range(1) :=  20000;
  v_range(2) :=  30000;
  v_range(3) :=  75000;
  v_range(4) := 100000;
  v_range(5) := 125000;
  salary_grouping.build_range(v_range);
  salary_grouping.show_range(v_range);
END;

This example produces the following output, showing that three employees make more than $30,000 and less than or equal to $75,000, and two employees make more than $75,000 and less than or equal to $100,000:

20000  30000  75000 100000 125000
0      0      3     2      0

Here is a sample header for the package:

CREATE OR REPLACE PACKAGE salary_grouping
IS
  /* Type and table to store the salary groupings */
  TYPE v_range_type IS TABLE OF NUMBER
     INDEX BY BINARY_INTEGER;
  v_range_table v_range_type;

  /* Calculate the groupings  */
  PROCEDURE build_range ( p_range IN OUT v_range_type );

  /* Procedure to display the calculated groupings */
  PROCEDURE show_range ( p_range IN v_range_type );

  -- the number of categories defined
  v_num_categories PLS_INTEGER;
  -- the longest category (for display)
  v_maxlen         PLS_INTEGER;

END salary_grouping;

Write the code for the package body.

6-25.

Wouldn’t it be great to sort the contents of an index-by table? Here is the specification of such a package for sorting VARCHAR2(30) index-by tables:

CREATE OR REPLACE PACKAGE sort_routines
IS
  -- global types for sorting
  TYPE v_vc230_array_type IS TABLE OF VARCHAR2(30)
    INDEX BY BINARY_INTEGER;

  /* Overloaded sort procedures */
  PROCEDURE sort_table(
     p_array IN OUT v_vc230_array_type,
     p_duplicates BOOLEAN := FALSE );

END sort_routines;

The p_duplicates flag denotes whether duplicate values are to be included in the sorted array or not; the default is that duplicates are to be left out of the sorted version of the table.

Write the code for the package body.

6-26.

Index-by tables can support bidirectional cursors that allow navigation forward and backward through rows in a table (Oracle does not support true bidirectional cursor access, at least up through Oracle8i Release 8.1).

Here is a package header that implements simplified bidirectional cursor capabilities for the employee table:

CREATE OR REPLACE PACKAGE employee_cursor IS
  -- record of the employee table declared
  -- globally for all to see
  TYPE employee_record IS RECORD (
     empno     employee.empno%TYPE,
     empname   employee.empname%TYPE,
     empsalary employee.empsalary%TYPE );

  -- load the employee records into memory
  FUNCTION load_rows RETURN PLS_INTEGER;

  -- function to move to the next row
  FUNCTION next_row RETURN BOOLEAN;

  -- function to move to the previous row
  FUNCTION prev_row RETURN BOOLEAN;

  -- function to get the values for the current row
  FUNCTION get_row RETURN employee_record;

  -- function to go to a specific row
  FUNCTION go_row ( p_row PLS_INTEGER ) RETURN BOOLEAN;

END employee_cursor;

Here is an example of using this package:

DECLARE
  v_num_employees PLS_INTEGER;
  v_employee employee_cursor.employee_record;

BEGIN
  -- load the employees into memory
  v_num_employees := employee_cursor.load_rows;

  -- display the first employee
  IF v_num_employees > 0 THEN
    v_employee := employee_cursor.get_row;
    DBMS_OUTPUT.PUT_LINE(v_employee.empno);
  END IF;

  -- loop through the rest and display them
  LOOP
    EXIT WHEN NOT employee_cursor.next_row;
    v_employee := employee_cursor.get_row;
    DBMS_OUTPUT.PUT_LINE(v_employee.empno);
  END LOOP;

  -- wait a minute, who was that second one again?
  IF employee_cursor.go_row(2) THEN
    v_employee := employee_cursor.get_row;
    DBMS_OUTPUT.PUT_LINE(v_employee.empno);
  END IF;

END;

Write the code for the package body.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.226.82.78