|
SQL>
SQL> create table department
2 ( dept_id number(2),
3 dept_name varchar2(14),
4 no_of_emps varchar2(13)
5 )
6 /
Table created.
SQL>
SQL> INSERT INTO department VALUES (10, 'ACCOUNTING', 'NEW YORK');
1 row created.
SQL> INSERT INTO department VALUES (20, 'RESEARCH', 'DALLAS');
1 row created.
SQL> INSERT INTO department VALUES (30, 'SALES', 'CHICAGO');
1 row created.
SQL> INSERT INTO department VALUES (40, 'OPERATIONS', 'BOSTON');
1 row created.
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 CURSOR all_depts IS
3 SELECT dept_id, dept_name FROM department ORDER BY dept_name;
4
5 TYPE dept_id IS TABLE OF department.dept_id%TYPE;
6 TYPE dept_name IS TABLE OF department.dept_name%TYPE;
7
8 dept_ids dept_id;
9 dept_names dept_name;
10 inx1 PLS_INTEGER;
11 BEGIN
12 OPEN all_depts;
13 FETCH all_depts BULK COLLECT INTO dept_ids, dept_names;
14 CLOSE all_depts;
15
16 FOR inx1 IN 1..dept_ids.count LOOP
17 dept_names(inx1) := UPPER(dept_names(inx1));
18 DBMS_OUTPUT.PUT_LINE (dept_ids(inx1) ||' ' || dept_names(inx1));
19 END LOOP;
20
21 FORALL x IN dept_ids.first..dept_ids.last
22 UPDATE department
23 SET dept_name = dept_names(x)
24 WHERE dept_id = dept_ids(x);
25 END;
26 /
10 ACCOUNTING
40 OPERATIONS
20 RESEARCH
30 SALES
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table department;
Table dropped.
SQL> --
|