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.