PL/SQl tables were introduced in Oracle 7.PL/SQL tables are similar to arrays in C.By using PL/SQL Tables it is possible to create a collection of items all of the same type indexed by an integer.
eg:TYPE name IS TABLE OF varchar2INDEX BY BINARY_INTEGER;
INDEX By Tables composed of two components 1. Primary key of data type BINARY_INTEGER 2. Column of scalar or record data type.
Since this table is unconstrained it can increase its size dynamically.
Syntax: TYPE type_name IS TABLE OF {column_type | variable type | table.column type } [NOT NULL] | table. ROWTYPE [INDEX BY BINARY_INTEGER]; identifier type_name;
for eg: To declare an INDEX BY TABLE to store names.
TYPE enam_table_type IS TABLE OF employees.name TYPE INDEX BY BINARY_INTEGER; ename_table ename_table_type;
To Refer the INDEX BY TABLE: INDEX_BY_TABLE_NAME(primary_key_value) where primary_key_value belongs to type BINARY_INTEGER
For eg: We have already have the ename_table which is of type ename_table_type. To refer the 5th row in this table we can use ename_table(5).
The methods associated with this index by table are: EXISTS COUNT FIRST and LAST PRIOR NEXT TRIM DELETE
- Pl/sql is in memory representation of a table. - It is just a data type. ( Collection type) - It may store any number of rows from table.
Here is a simple example
CREATE OR REPLACE PROCEDURE TEST_PROC AS TYPE EMP_TYPE IS TABLE OF EMP ROWTYPE; EMP_VAR EMP_TYPE; BEGIN SELECT * BULK COLLECT INTO EMP_VAR FROM EMP; FOR I IN EMP_VAR.FIRST..EMP_VAR.LAST LOOP DBMS_OUTPUT.PUT_LINE(' EMPLOYEE ' ||EMP_VAR(I).ENAME || ' SALARY IS '|| EMP_VAR(I).SAL); END LOOP; END;
