PL/SQL Table Update

You update a PL/SQL table in a very similar way as you do for inserting data into a PL/SQL table. If you have already inserted a row number 102 in the emp_table, then you can update the same row with the following statement:

emp_table(102) := `Jack Michaels';

This statement will update the contents of row 102 with the new value in the assignment statement.

PL/SQL Table Delete

Because the PL/SQL table is not stored in the database, the DELETE and DROP TABLE commands are limited in their effectiveness. PL/SQL does allow you to nullify and delete rows of a table, but you cannot drop a PL/SQL table.

The following command is an example of removing all the rows from a PL/SQL table using the Oracle DELETE built-in procedure:

emp_tab.DELETE;

The next example deletes the 2333rd record from the employee table:

emp_tab(2333);

This example deletes the first ten thousand guest records from the guest table:

guest_tab(0, 10000);

Likewise, the following statement will nullify the 100th record in the guest table:

guest_tab(100);

You can also assign an empty table to an existing table. This will make the existing table (one with rows) empty, as shown in the following example:

/* Statement will empty out the guest table   */

guest_tab := empty_tab;

Displaying the Contents of a PL/SQL Table
As with database tables, you will want to display the rows of a PL/SQL table. One of the easiest ways to view PL/SQL table rows is with the DBMS_OUTPUT public package. This package is supplied with your Oracle RDBMS.

The DBMS_OUTPUT package has a procedure named PUT LINE, which will output a single line of data to the output buffer. This buffer is usually your screen. The following example is a portion of code that you can include in your PL/SQL block to display the rows of a PL/SQL table. The code shown in Listing 9.7 will display the first 50 rows of the table.

INPUT
Listing 9.7. Using the DBMS_OUTPUT package to display the contents of a table.

1: BEGIN
2: for row_indicator in 1 .. 50
3: LOOP
4: dbms_output.put_line (emp_table (row_indicator);
5: END LOOP;
6: END;

ANALYSIS In the example in Listing 9.7, several assumptions are made. First, I assumed that the table begins with row number one. Secondly, I assumed that the first 50 rows are sequentially numbered.

Both assumptions might not be true. You can adjust or customize your display block to suit your local needs.

PL/SQL Table Functions

PL/SQL provides a handful of useful built-in functions for use with PL/SQL tables. These functions are as follows:

• count—The count function returns the number of rows current in the PL/SQL table. The following line will return the number of rows in the employee table:

  counter := emp_tab.count;
  

  
  The specification for the function used in the preceding line is

  FUNCTION count RETURN INTEGER;
  

• exists—This function returns the value of true if the specific row is present in the table. Otherwise, false is returned. The following example will evaluate to true if the guest table contains a third row:

  IF guest_tab.exists(3) THEN...
  

• The specification for the function used in the preceding line is

  FUNCTION exists (index in INTEGER) RETURN boolean;
  

• first—This built-in function will return the value of the lowest index present in the PL/SQL table. The following will return the index value for the first row in the employee table:

  first_row_value :=emp_tab.first;
  

  
  The specification for the function used in the preceding line is

  FUNCTION first RETURN INTEGER;
  

• last—This function will return the value of the last index in the PL/SQL table. The following example will return the value of the last index in the guest table:

  last_row_value := guest_tab.last;
  

  
  The specification for the function used in the preceding line is

  FUNCTION last RETURN INTEGER;
  

• next—The next built-in function returns the value of the index that is the next largest index value. Take note of the syntax shown in the following example for the next built-in function:

  next_index := guest_tab.next (current_index);
  

  
  The specification for the function used in the preceding line is

  FUNCTION next RETURN INTEGER;
  

• prior—The prior function will return the value of the index that is immediately prior to the current row. This is shown in the following example:

  prior_index := guest_tab.prior  (current_index);
  

  
  The specification for the function used in the preceding line is

  FUNCTION prior RETURN INTEGER;
  

Nested Tables

Oracle8 introduces a new collection type known as a nested table. A nested table is an item of the type TABLE and is similar to a single-dimension array that has no upper limit; that is, a nested table is unbounded. Data stored in a nested table is not stored in any particular order. However, when the data is retrieved into variables, the rows are ordered consecutively, based on their subscripts.

The Syntax for Declaring a Nested Table

TYPE type_name IS TABLE OF element_type
INDEX BY BINARY_INTEGER;

In this syntax, type_name is the type specifier used later in the declaration. element_type is any valid PL/SQL datatype except for:

• BOOLEAN
• NCHAR
• NCLOB
• NVARCHAR2
• REF CURSOR
• TABLE
• VARRAY

The INDEX BY clause is optional.