oracle table_type,Table Type

1)table type

1@@@@

@@@O10g

HR@ocm> ed

Wrote file afiedt.buf

1  DECLARE

2    TYPE my_dept_table IS TABLE OF departments%ROWTYPE

3        INDEX BY BINARY_INTEGER;

4    my_dept     my_dept_table;

5    dept_id     PLS_INTEGER;

6  BEGIN

7    FOR i IN 1 .. 7 LOOP

8     dept_id :=

9         CASE i

10         WHEN 1 THEN 10

11         WHEN 2 THEN 20

12         WHEN 3 THEN 50

13         WHEN 4 THEN 60

14         WHEN 5 THEN 70

15         WHEN 6 THEN 90

16         WHEN 7 THEN 110

17         ELSE NULL

18         END;

19     SELECT * INTO my_dept(i)

20     FROM departments

21     WHERE    department_id= dept_id;

22    END LOOP;

23  ----Print the result as the following:

24    FOR i IN 1 .. 7 LOOP

25     DBMS_OUTPUT.put_line('Department_name is: '||RPAD(my_dept(i).department_name,17)

26     ||' Department_id is: '|| RPAD(my_dept(i).department_id,5)

27     ||' Manager_id is: ' ||RPAD(my_dept(i).manager_id,5));

28    END LOOP;

29* END;

HR@ocm> /

Department_name is: Administration    Department_id is: 10    Manager_id is: 200

Department_name is: Marketing          Department_id is: 20    Manager_id is: 201

Department_name is: Shipping          Department_id is: 50    Manager_id is: 121

Department_name is: IT              Department_id is: 60    Manager_id is: 103

Department_name is: Public Relations  Department_id is: 70    Manager_id is: 204

Department_name is: Executive          Department_id is: 90    Manager_id is: 100

Department_name is: Accounting          Department_id is: 110   Manager_id is: 205

PL/SQL procedure successfully completed.