Declare TABLE TYPE variables in a PL/SQL declare block.
Table variables are also known as index-by table or array. The table variable
contains one column which must be a scalar or record datatype plus a primary
key of type BINARY_INTEGER.
Syntax:
DECLARE TYPE type_name IS TABLE OF (column_type | variable%TYPE | table.column%TYPE [NOT NULL] INDEX BY BINARY INTEGER; -- Then to declare a TABLE variable of this type: variable_name type_name; -- Assigning values to a TABLE variable: variable_name(n).field_name := 'some text'; -- Where 'n' is the index value
Using TABLE variable Methods:
To execute these use the syntax
table_name[ (parameters)] EXISTS(n) Returns TRUE if nth element of the table exists. COUNT The number of elements (rows) in the plsql table FIRST First and Last index no.s in the table LAST returns NULL if table is empty PRIOR(n) Returns index no that preceeds n in the plsql table NEXT(n) Returns index no that succeeds n in the plsql table EXTEND(n,i) Append n copies of the 'i'th element to a plsql table i defaults to NULL n defaults to 1 TRIM(n) Remove n elements from the end of a plsql table n defaults to 1 DELETE(m,n) Delete elements in range m...n m defaults to = n n defaults to ALL elements
Although the type is called 'Table' they are more like advanced arrays. Although you cannot use SQL statements to manipulate a PL/SQL table, its primary key gives you array-like access to rows. Think of the key and rows as the index and elements of a one-dimensional array.
PL/SQL tables help you move bulk data. They can store columns or rows of Oracle data, and they can be passed as parameters. So, PL/SQL tables make it easy to move collections of data into and out of database tables or between client-side applications and stored subprograms. You can even use PL/SQL tables of records to simulate local database tables.
Examples:
DECLARE -- declare the table type TYPE MyTrip_table_type IS TABLE OF business_trips.bt_cost%Type INDEX BY BINARY INTEGER; --declare a TABLE variable of this type myTrips MyTrip_table_type; BEGIN myTrips(1) := 'Test Job'; UPDATE business_trips SET bt_cost = bt_cost * 1.2 WHERE bt_id_pk = myTrips(1) END /
Related: