PL/SQL TABLES

PL/SQL tables are PL/SQL’s way of providing arrays. Arrays are like tem-porary

tables in memory and thus are processed very quickly. It is impor-tant

for you to realize that they are not database tables, and DML

statements cannot be issued against them. This type of table is indexed

by a binary integer counter (it cannot be indexed by another type of

number) whose value can be referenced using the number of the index.

Remember that PL/SQL tables exist in memory only, and therefore don’t

exist in any persistent way, disappearing after the session ends.

A PL/SQL TABLE DECLARATION

There are two steps in the declaration of a PL/SQL table. First, you must

define the table structure using the TYPE statement. Second, once a table

type is created, you then declare the actual table.

FOR EXAMPLE

DECLARE

-- Table structure definition

TYPE NameType IS TABLE OF

Customer.name_name%TYPE

INDEX BY BINARY_INTEGER;

-- Create the actual table

CnameTab NameType;

KnameTab NameType;

BEGIN

NULL; -- ...

END;

REFERENCING AND MODIFYING PL/SQL

TABLE ROWS

In order to specify a particular row in a PL/SQL table, you must name the

table and the index.

Syntax: <table_name> (<index_value>)

The datatype of the index value must be compatible with the

BINARY_INTEGER datatype. You assign values to a row using the stan-dard

assignment operator.

Referencing a nonexistent row raises the NO_DATA_FOUND exception.

FOR EXAMPLE

SET SERVEROUTPUT ON;

DECLARE

CURSOR c_cname IS

SELECT cust_name, Cust_no, ROWNUM

FROM customers

ORDER BY cust_name;

TYPE type_cname_tab IS TABLE OF customers.cust_name%TYPE

INDEX BY BINARY_INTEGER;

tab_cname type_cname_tab;

v_cname_counter NUMBER := 0;

BEGIN

FOR r_cname IN c_cname

LOOP

v_cname_counter := v_cname_counter + 1;

tab_cname(v_cname_counter):= r_cname.cust_name;

END LOOP;

FOR i_cname IN 1 .. v_cname_counter

LOOP

DBMS_OUTPUT.PUT_LINE('Here is a last name: '|| Tab_cname(i_cname));

END LOOP;

END;

PL/SQL TABLE ATTRIBUTES

Here are seven PL/SQL table attributes you can use to gain information

about a PL/SQL table or to modify a row in a PL/SQL table:

• DELETE—Deletes rows in a table.

• EXISTS—Return TRUE if the specified entry exists in the table.

• COUNT—Returns the number of rows in the table.

• FIRST—Returns the index of the first row in the table.

• LAST—Returns the index of the last row in the table.

• NEXT—Returns the index of the next row in the table after the

specified row.

• PRIOR—Returns the index of the previous row in the table be-fore

the specified row.

PL/SQL table attributes are used with the following syntax:

<table_name>.<attribute>

If you declare a PL/SQL table named t_customer then you get a rowcount

for the table as follows:

v_count := t_customer.count;

The DELETE and EXISTS attributes function differently than the other attributes.

These two generally operate on one row at a time, so you must

add the following syntax:

<TableName>.<attribute>(<IndexNumber>[,<IndexNumber>])

t.customer.delete deletes all rows from the t_customer table, whereas

t_customer.delete(15) deletes the fifteenth row of the t_customer table.

Likewise, t_customer.exists(10) returns a value of true if there is a one-hundredth row and a value of false if there is not.

The EXISTS attribute can be used to determine if a particular index value

exists in the PL/SQL table or not.

FOR EXAMPLE

DECLARE

TYPE t_czip_type IS TABLE OF

customers.post_code%TYPE

INDEX BY BINARY_INTEGER;

t_czip t_czip_type;

v_czip_index BINARY_INTEGER;

BEGIN

t_czip(11203) := ‘nyc’;

t_czip(11201) := ‘Brkl’;

t_czip(49341) := ‘SF’;

BEGIN

v_czip_index := t_czip.first;

LOOP

DBMS_OUTPUT.PUT_LINE(t_czip(v_czip_index));

EXIT WHEN v_czip_index = t_czip.LAST;

v_czip_index := t_czip.NEXT(v_czip_index);

END LOOP;

RAISE NO_DATA_FOUND;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

DBMS_OUTPUT.PUT_LINE

(‘The last zipcode has been reached.’

);

END;

END;