OraclePLSQL--TYPE-TABLE Declare PL/SQL ---Scenario: We want to extract SSN of manager and worker from our database.

TYPE-TABLE   Declare PL/SQL

There are two steps required for define the tables in declare in pl/sql

1-A user defined table  i.e CustmName and this  difnes the data type of the data element  as well as the data type of the index.

2- The second step references the user-defined type declared in the first step and declares one or more instances of this type (orderList and addressList).

DECLARE

    TYPE CustmName

      IS TABLE OF customer.name%TYPE

      INDEX BY PLS_INTEGER;

     orderList        CustmName;

     addressList      CustmName;

…

Scenario;

We want to extract SSN of manager and worker from our database.

DECLARE

TYPE EmpSSNarray

IS TABLE OF employee.ssn%TYPE

INDEX BY PLS_INTEGER;

ManagementList       EmpSSNarray;

WorkerList           EmpSSNarray;

BEGIN

SELECT superssn

INTO ManagementList(1)

FROM employee

WHERE superssn IS NOT NULL

AND ROWNUM <=1;

SELECT superssn

INTO ManagementList(2)

FROM employee

WHERE superssn IS NOT NULL

AND ROWNUM <=1

AND superssn <> ManagementList(1);

SELECT essn

INTO WorkerList(1)

FROM works_on

WHERE hours IS NOT NULL

AND ROWNUM <=1

AND essn NOT IN (ManagementList(1), ManagementList(2));

SELECT essn

INTO WorkerList(2)

FROM works_on

WHERE hours IS NOT NULL

AND ROWNUM <=1

AND essn NOT IN (ManagementList(1), ManagementList(2), WorkerList(1));

dbms_output.put_line ('Managers are: ' || ManagementList(1) || ' ' ||

ManagementList(2));

dbms_output.put_line ('Workers are: ' || WorkerList(1) || ' ' ||

WorkerList(2));

END;

anonymous block completed

Managers are: 333445555 987654321

Workers are: 123456789 453453453