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.
转载于:https://blog.51cto.com/majesty/991901