|
Populating Table using PL/SQL
Hands-On
introduction
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
table statistics into the �dept_stat� table.
Statistics
Information:
The
Department Number,
The
total number of employees in each
department,
The
total salary paid in each
department, and
The
average salary paid in each
department.
Use and modify
the �test_for_loop� file from previous
Hands-On. Save the file as �test_for_loop2�
at your directory.
Go to
�MS-DOS.� Change directory to your
directory. And login to �sqlplus� as
�oracle/learning.� 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. In the
�PL/SQL� body, insert each cursor record
into the dept_stat table. Save the inserted
transactions.
Create a table
SQL> CREATE
TABLE dept_stat
(dname
VARCHAR2(20) primary key,
total_empno
NUMBER(3),
total_sal
NUMBER (8,2),
avg_sal NUMBER
(8,2));
You should not
have any error when creating the table.
Populating a
table using PL/SQL block
Go to
�Notepad� and open the �test_for_loop� file
from your directory. Modify the PL/SQL block
to populate the department stat table.
(Notepad)
DECLARE
-- define
department statistics
cursor c_ds is
SELECT dname,
count (*) ttemp,
SUM(sal) ttsal,
AVG(sal) avsal
FROM dept d,
emp e
WHERE d.deptno
= e.deptno
GROUP BY dname;
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,
v_ds.ttsal,
v_ds.avsal);
END LOOP;
-- save the
insert transaction.
commit;
END;
/
Save a PL/SQL
block
Save the file
as �test_for_loop2� at your directory.
Run a PL/SQL
block
Go to �sqlplus.�
Run the file.
SQL> @test_for_loop2
Query the
dept_stat table.
SQL> select * from dept_stat;
Now, you
should have your populated records. Validate
the results.
Questions:
Q: Create a
table named "dept_stat". The table should
have four columns: department name (dname),
total number of employees (total_empno),
total salary of employees (total_sal), and
average salary of employees (avg_sal). And
the department name should be a primary key.
The following are its columns, datatypes and
index constraint:
dname
VARCHAR2(20) primary key
total_empno
NUMBER(3)
total_sal
NUMBER (8,2)
avg_sal NUMBER
(8,2)
Q: Write a
PL/SQL block to populate the department
table statistics into the �dept_stat� table.
Statistics
Information:
The
Department Number,
The
total number of employees in each
department,
The
total salary paid in each
department, and
The
average salary paid in each
department.
|
