|
“An education isn't
how much you have committed to memory, or even how much you
know. It's being able to differentiate between what you do know
and what you don't.” Anatole France (1844 - 1924) |
Read
first then play the video:
PLS005(VIDEO)-Populating
table using PL/SQL
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
the iself directory.
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. 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 the iself 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 the iself 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.
|
“To follow, without
halt, one aim: There's the secret of success.” Anna Pavlova
(1885 - 1931) |
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.
|