everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

PL/SQL

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22
<< Previous

Chapter # 04

Next >>


 

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.

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.