In this section, we will explore real-world applications of PL/SQL through detailed case studies. These case studies will help you understand how PL/SQL can be used to solve complex problems, optimize performance, and integrate with other systems. Each case study will include a problem statement, a step-by-step solution, and a discussion of the results.

A retail company needs to manage its inventory efficiently. The system should track stock levels, reorder products when they fall below a certain threshold, and generate reports on inventory status.

1. Database Design:

   • Create tables for products, suppliers, and inventory transactions.
   • Define relationships between these tables.

   CREATE TABLE products (
       product_id NUMBER PRIMARY KEY,
       product_name VARCHAR2(100),
       supplier_id NUMBER,
       reorder_level NUMBER
   );
   
   CREATE TABLE suppliers (
       supplier_id NUMBER PRIMARY KEY,
       supplier_name VARCHAR2(100)
   );
   
   CREATE TABLE inventory_transactions (
       transaction_id NUMBER PRIMARY KEY,
       product_id NUMBER,
       transaction_date DATE,
       quantity NUMBER,
       transaction_type VARCHAR2(10)
   );
   

2. PL/SQL Procedures:

   • Create a procedure to add new inventory transactions.
   • Create a procedure to check stock levels and reorder products.

   CREATE OR REPLACE PROCEDURE add_inventory_transaction (
       p_product_id IN NUMBER,
       p_quantity IN NUMBER,
       p_transaction_type IN VARCHAR2
   ) IS
   BEGIN
       INSERT INTO inventory_transactions (transaction_id, product_id, transaction_date, quantity, transaction_type)
       VALUES (inventory_transactions_seq.NEXTVAL, p_product_id, SYSDATE, p_quantity, p_transaction_type);
   END;
   /
   
   CREATE OR REPLACE PROCEDURE check_and_reorder (
       p_product_id IN NUMBER
   ) IS
       v_quantity NUMBER;
       v_reorder_level NUMBER;
   BEGIN
       SELECT SUM(quantity) INTO v_quantity
       FROM inventory_transactions
       WHERE product_id = p_product_id;
   
       SELECT reorder_level INTO v_reorder_level
       FROM products
       WHERE product_id = p_product_id;
   
       IF v_quantity < v_reorder_level THEN
           -- Reorder logic here
           DBMS_OUTPUT.PUT_LINE('Reordering product ' || p_product_id);
       END IF;
   END;
   /
   

3. Triggers:

   • Create a trigger to automatically check stock levels after each transaction.

   CREATE OR REPLACE TRIGGER trg_after_inventory_transaction
   AFTER INSERT ON inventory_transactions
   FOR EACH ROW
   BEGIN
       check_and_reorder(:NEW.product_id);
   END;
   /
   

This case study demonstrates how PL/SQL can be used to manage inventory efficiently. The use of procedures and triggers ensures that stock levels are always monitored and products are reordered automatically when necessary. This approach minimizes manual intervention and reduces the risk of stockouts.

A company wants to track the performance of its employees. The system should record performance metrics, calculate performance scores, and generate performance reports.

1. Database Design:

   • Create tables for employees, performance metrics, and performance scores.

   CREATE TABLE employees (
       employee_id NUMBER PRIMARY KEY,
       employee_name VARCHAR2(100),
       department_id NUMBER
   );
   
   CREATE TABLE performance_metrics (
       metric_id NUMBER PRIMARY KEY,
       metric_name VARCHAR2(100)
   );
   
   CREATE TABLE performance_scores (
       score_id NUMBER PRIMARY KEY,
       employee_id NUMBER,
       metric_id NUMBER,
       score NUMBER,
       score_date DATE
   );
   

2. PL/SQL Procedures:

   • Create a procedure to add performance scores.
   • Create a procedure to calculate the average performance score for an employee.

   CREATE OR REPLACE PROCEDURE add_performance_score (
       p_employee_id IN NUMBER,
       p_metric_id IN NUMBER,
       p_score IN NUMBER
   ) IS
   BEGIN
       INSERT INTO performance_scores (score_id, employee_id, metric_id, score, score_date)
       VALUES (performance_scores_seq.NEXTVAL, p_employee_id, p_metric_id, p_score, SYSDATE);
   END;
   /
   
   CREATE OR REPLACE PROCEDURE calculate_average_score (
       p_employee_id IN NUMBER,
       p_average_score OUT NUMBER
   ) IS
   BEGIN
       SELECT AVG(score) INTO p_average_score
       FROM performance_scores
       WHERE employee_id = p_employee_id;
   END;
   /
   

3. Reports:

   • Create a report to display the average performance score for each employee.

   SET SERVEROUTPUT ON;
   DECLARE
       v_employee_id NUMBER;
       v_average_score NUMBER;
   BEGIN
       FOR rec IN (SELECT employee_id FROM employees) LOOP
           v_employee_id := rec.employee_id;
           calculate_average_score(v_employee_id, v_average_score);
           DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ' Average Score: ' || v_average_score);
       END LOOP;
   END;
   /
   

This case study illustrates how PL/SQL can be used to track and analyze employee performance. By recording performance metrics and calculating average scores, the company can gain valuable insights into employee performance and make informed decisions.

These case studies provide practical examples of how PL/SQL can be used to solve real-world problems. By understanding these examples, you can apply similar techniques to your own projects and improve your PL/SQL skills. In the next module, we will explore more advanced topics and best practices to further enhance your PL/SQL knowledge.