Real-World Examples

1. Data Validation Using Triggers

Triggers are ideal for enforcing business rules and validating data before it's inserted or updated in the database.

Scenario:

Ensure that no employee is inserted into the employees table with a salary less than 10,000.

Code Example:

plsql
Copy code
CREATE OR REPLACE TRIGGER trg_validate_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
   IF :NEW.salary < 10000 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Salary must be at least 10,000.');
   END IF;
END;
/

Explanation:

• This BEFORE trigger checks the salary value before an INSERT or UPDATE operation.
• If the salary is less than 10,000, it raises a custom error message.

2. Generating Reports Using Cursors

Cursors can be used to fetch and process multiple rows of data for generating detailed reports.

Scenario:

Generate a report of employees with their names and salaries who earn more than 50,000.

Code Example:

plsql
Copy code
DECLARE
   CURSOR emp_cursor IS
      SELECT first_name, last_name, salary FROM employees WHERE salary > 50000;
   v_first_name employees.first_name%TYPE;
   v_last_name employees.last_name%TYPE;
   v_salary employees.salary%TYPE;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Employee Report');
   DBMS_OUTPUT.PUT_LINE('-------------------------');
   OPEN emp_cursor;
   LOOP
      FETCH emp_cursor INTO v_first_name, v_last_name, v_salary;
      EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name || ': ' || v_salary);
   END LOOP;
   CLOSE emp_cursor;
END;
/

Explanation:

• The cursor emp_cursor fetches employees with salaries greater than 50,000.
• The DBMS_OUTPUT.PUT_LINE is used to print the report.

3. Automating Business Logic with Procedures

Procedures allow encapsulating business rules and automating tasks.

Scenario:

Automate the process of giving a 10% bonus to all employees in a specific department.

Code Example:

plsql
Copy code
CREATE OR REPLACE PROCEDURE give_bonus (p_department_id IN NUMBER) IS
BEGIN
   UPDATE employees
   SET salary = salary * 1.1
   WHERE department_id = p_department_id;

   DBMS_OUTPUT.PUT_LINE('Bonus applied successfully to department: ' || p_department_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;
/

Execution:

plsql
Copy code
BEGIN
   give_bonus(10);
END;
/

Explanation:

• The procedure give_bonus updates the salary of employees in the specified department.
• It includes exception handling for errors during the update.

4. Exception Handling in Real-Life Scenarios

Robust error handling ensures smooth operation in unexpected situations.

Scenario:

Handle errors gracefully during a fund transfer between two accounts.

Code Example:

plsql
Copy code
CREATE OR REPLACE PROCEDURE transfer_funds (
   p_from_account IN NUMBER,
   p_to_account IN NUMBER,
   p_amount IN NUMBER
) IS
   v_balance NUMBER;
BEGIN
   -- Check balance
   SELECT balance INTO v_balance FROM accounts WHERE account_id = p_from_account;
   IF v_balance < p_amount THEN
      RAISE_APPLICATION_ERROR(-20002, 'Insufficient balance in the source account.');
   END IF;

   -- Deduct from source account
   UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from_account;

   -- Add to destination account
   UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to_account;

   DBMS_OUTPUT.PUT_LINE('Transfer successful.');
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Error: Account not found.');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Execution:

plsql
Copy code
BEGIN
   transfer_funds(101, 102, 500);
END;
/

Explanation:

• Validates balance and performs the fund transfer.
• Handles specific (NO_DATA_FOUND) and general (WHEN OTHERS) exceptions.

5. Implementing a Simple Inventory Management System

PL/SQL can be used to implement systems like inventory management for tracking stock levels.

Scenario:

Track stock levels and alert if stock for any item falls below the minimum threshold.

Step 1: Create the Tables

plsql
Copy code
CREATE TABLE inventory (
   item_id NUMBER PRIMARY KEY,
   item_name VARCHAR2(50),
   stock_quantity NUMBER,
   minimum_stock NUMBER
);

Step 2: Insert Sample Data

plsql
Copy code
INSERT INTO inventory VALUES (1, 'Laptop', 50, 10);
INSERT INTO inventory VALUES (2, 'Mouse', 100, 20);

Step 3: Create a Procedure to Restock

plsql
Copy code
CREATE OR REPLACE PROCEDURE restock_item (
   p_item_id IN NUMBER,
   p_additional_stock IN NUMBER
) IS
BEGIN
   UPDATE inventory
   SET stock_quantity = stock_quantity + p_additional_stock
   WHERE item_id = p_item_id;

   DBMS_OUTPUT.PUT_LINE('Item restocked successfully.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;
/

Step 4: Create a Trigger to Check Stock Levels

plsql
Copy code
CREATE OR REPLACE TRIGGER trg_check_stock
AFTER UPDATE ON inventory
FOR EACH ROW
BEGIN
   IF :NEW.stock_quantity < :NEW.minimum_stock THEN
      DBMS_OUTPUT.PUT_LINE('Alert: Stock for ' || :NEW.item_name || ' is below minimum level.');
   END IF;
END;
/

Execution:

plsql
Copy code
BEGIN
   -- Restock an item
   restock_item(1, 20);

   -- Update stock to a low level
   UPDATE inventory SET stock_quantity = 5 WHERE item_id = 2;
END;
/

Explanation:

• The restock_item procedure adds stock for an item.
• The trg_check_stock trigger alerts if the stock falls below the minimum level.

Summary of Scenarios

These scenarios demonstrate the practical use of PL/SQL for implementing real-world business logic, data processing, and error handling effectively.