Smitha25

 Profile Answers by Smitha25 

• Mar 26th, 2008

 

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 varchar2 INDEX BY BINARY_INTEGER;

tonyrobert

 Profile Answers by tonyrobert 

• Mar 27th, 2008

 

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

  

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 29th, 2008

 

- 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;