|
More Resources by
Google: |
|
|
|
|
Topics: Hands-On 06 - Populating table using
PL/SQL
In this
Hands-On, you create a table named "dept_stat". The table contains four
columns: department name (dname), total number of employees (total_emplno),
total salary of employees (total_sal), and average salary of employees (avg_sal).
And the department name should be a primary key.
Then write a PL/SQL block to populate the department statistics table (dept_stat) into the “dept_stat” table. Use and modify the “test_for_loop”
file from previous Hands-On. Save the file as “test_for_loop2” at the iself
directory.
1-
Create table.
2- INSERT statement in PL/SQL block
3- FOR LOOP statement.
4- COMMIT statement in PL/SQL block.
.
Go to “MS-DOS.”
Change directory to the iself directory. And login to “sqlplus” as
“iself/schooling.”
Create a table named "dept_stat".
It should contain four columns: department name, total number of employees,
total salary, and average salary. And define the department name as a primary
key.
>> create table dept_stat
(dname varchar2(20) primary key,
total_empno number(3),
total_sal number(8,2),
avg_sal number(8,2));
Table created.
Go to
“Notepad”
Open the
“test_for_loop” file from the iself directory.
Modify the
PL/SQL block to populate the department stat table.
>> declare
-- define department statistics
cursor c_ds is
select dname, count (*) ttemp,
begin
-- loop to read cursor record.
for v_ds in c_ds loop
-- insert into
dept_stat
insert into
dept_stat
values (v_ds.dname,
v_ds.ttemp,
end loop;
-- save the insert
transaction.
commit;
end;
/
In the “PL/SQL” body, insert each cursor record into the dept_stat table.
Save the insert transaction.
=
Save the file as “test_for_loop2”
at the iself directory.
=
Go to “sqlplus.”
Run the file.
>>
@test_for_loop2
Query the dept_stat table.
>> select * from dept_stat;
=
Now, you should practice this over and over, until you become a master at it.
Good Luck!