iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

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

 

Lesson 04

“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:

   PLS-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 the loop when no value

    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.

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.