|
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.
|
