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;