Oracle PL/SQL Program: Employee Investment Database in 3NF with Procedure and Cursor

Introduction

Learn how to design an Employee–Investment database in 3NF and implement PL/SQL Procedure and Cursor in Oracle. This guide is beginner-friendly and perfect for practical exams and viva preparation.

Problem Statement

Consider the following entities and their relationships:

Employee(emp_id, emp_name, address)
Investment(inv_no, inv_name, inv_date, inv_amount)

Relation between Employee and Investment is One-to-Many.
Constraints:

• Primary key must be defined.
• inv_amount should be greater than 0.

Tasks:

1. Write a procedure which will display details of employees invested amount in “Mutual Fund”.
2. Write a cursor which will display date wise investment details.

Logic

Step 1: Database Design in 3NF

To achieve Third Normal Form (3NF):

• Each table has a primary key.
• No partial dependency.
• No transitive dependency.
• One-to-Many relationship implemented using foreign key (emp_id in Investment table referencing Employee).

Step 2: Procedure Logic

• Join Employee and Investment tables.
• Filter records where inv_name = ‘Mutual Fund’.
• Display output using DBMS_OUTPUT.PUT_LINE.

Step 3: Cursor Logic

• Create explicit cursor.
• Join Employee and Investment tables.
• Sort records using ORDER BY inv_date.
• Fetch and display records in loop.

Source Code

1️⃣ Create Employee Table

CREATE TABLE Employee(
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    address VARCHAR2(100)
);

2️⃣ Create Investment Table

CREATE TABLE Investment(
    inv_no NUMBER PRIMARY KEY,
    emp_id NUMBER REFERENCES Employee(emp_id),
    inv_name VARCHAR2(50),
    inv_date DATE,
    inv_amount NUMBER(10,2) CHECK(inv_amount > 0)
);

3️⃣ Procedure to Display Mutual Fund Investments

CREATE OR REPLACE PROCEDURE display_mutual_fund_investments AS
BEGIN
    FOR rec IN(
        SELECT e.emp_id,e.emp_name,e.address,
               i.inv_amount,i.inv_date
        FROM Employee e
        JOIN Investment i ON e.emp_id=i.emp_id
        WHERE i.inv_name='Mutual Fund'
        ORDER BY e.emp_id
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE('Emp ID: '||rec.emp_id||
                             ' Name: '||rec.emp_name||
                             ' Address: '||rec.address||
                             ' Amount: '||rec.inv_amount||
                             ' Date: '||TO_CHAR(rec.inv_date,'DD-MON-YYYY'));
    END LOOP;
END;
/

Execute Procedure

SET SERVEROUTPUT ON;
EXEC display_mutual_fund_investments;

4️⃣ Cursor to Display Date Wise Investment Details

DECLARE
    CURSOR datewise_invest_cur IS
        SELECT e.emp_name,e.address,
               i.inv_name,i.inv_amount,i.inv_date
        FROM Employee e
        JOIN Investment i ON e.emp_id=i.emp_id
        ORDER BY i.inv_date;

    rec datewise_invest_cur%ROWTYPE;
BEGIN
    OPEN datewise_invest_cur;
    LOOP
        FETCH datewise_invest_cur INTO rec;
        EXIT WHEN datewise_invest_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Emp: '||rec.emp_name||
                             ' Address: '||rec.address||
                             ' Investment: '||rec.inv_name||
                             ' Amount: '||rec.inv_amount||
                             ' Date: '||TO_CHAR(rec.inv_date,'DD-MON-YYYY'));
    END LOOP;
    CLOSE datewise_invest_cur;
END;
/

5️⃣ Insert Sample Data

INSERT INTO Employee VALUES(1,'Alice','Mumbai');
INSERT INTO Employee VALUES(2,'Bob','Delhi');

INSERT INTO Investment VALUES(101,1,'Mutual Fund',TO_DATE('2025-01-10','YYYY-MM-DD'),5000);
INSERT INTO Investment VALUES(102,1,'FD',TO_DATE('2025-02-15','YYYY-MM-DD'),10000);
INSERT INTO Investment VALUES(103,2,'Mutual Fund',TO_DATE('2025-01-12','YYYY-MM-DD'),7000);

Sample Output

After executing procedure:

Emp ID: 1 Name: Alice Address: Mumbai Amount: 5000 Date: 10-JAN-2025
Emp ID: 2 Name: Bob Address: Delhi Amount: 7000 Date: 12-JAN-2025

After executing cursor:

Emp: Alice Address: Mumbai Investment: Mutual Fund Amount: 5000 Date: 10-JAN-2025
Emp: Bob Address: Delhi Investment: Mutual Fund Amount: 7000 Date: 12-JAN-2025
Emp: Alice Address: Mumbai Investment: FD Amount: 10000 Date: 15-FEB-2025

Viva Questions with Answers

1. What is 3NF?
   3NF ensures there is no transitive dependency and all non-key attributes depend only on the primary key.
2. What is a Procedure?
   A procedure is a stored PL/SQL block that performs a specific task and may not return a value.
3. What is a Cursor?
   A cursor is used to fetch multiple rows from a query result one by one.
4. What is a Foreign Key?
   A foreign key is a column that creates a relationship between two tables by referencing the primary key of another table.
5. Why is CHECK constraint used?
   CHECK constraint ensures that a column satisfies a specific condition, such as inv_amount > 0.