-- Create a table
CREATE TABLE Employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100),
    emp_salary NUMBER
);

-- Create a sequence to generate employee IDs
CREATE SEQUENCE emp_id_seq START WITH 1 INCREMENT BY 1;

-- Create a trigger to automatically assign IDs to new employees
CREATE OR REPLACE TRIGGER emp_id_trigger
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
    :NEW.emp_id := emp_id_seq.NEXTVAL;
END;
/

-- Function to calculate annual salary
CREATE OR REPLACE FUNCTION calculate_annual_salary(p_monthly_salary IN NUMBER) RETURN NUMBER IS
    v_annual_salary NUMBER;
BEGIN
    v_annual_salary := p_monthly_salary * 12;
    RETURN v_annual_salary;
END calculate_annual_salary;
/

-- PL/SQL block with cursor, exception handling, and triggers
DECLARE
    v_total_salary NUMBER := 0;
    v_emp_count NUMBER := 0;
    
    -- Cursor declaration
    CURSOR emp_cursor IS
        SELECT emp_id, emp_name, emp_salary FROM Employees;
BEGIN
    -- Inserting sample data
    INSERT INTO Employees (emp_name, emp_salary) VALUES ('John', 5000);
    INSERT INTO Employees (emp_name, emp_salary) VALUES ('Jane', 6000);
    INSERT INTO Employees (emp_name, emp_salary) VALUES ('Alice', 5500);
    
    -- Implicit cursor loop to fetch employee details
    FOR emp_rec IN emp_cursor LOOP
        -- Displaying employee details
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.emp_id || ', Name: ' || emp_rec.emp_name || ', Monthly Salary: ' || emp_rec.emp_salary || ', Annual Salary: ' || calculate_annual_salary(emp_rec.emp_salary));
        
        -- Calculating total salary
        v_total_salary := v_total_salary + emp_rec.emp_salary;
        
        -- Incrementing employee count
        v_emp_count := v_emp_count + 1;
    END LOOP;
    
    -- Displaying total salary and employee count
    DBMS_OUTPUT.PUT_LINE('Total Salary: ' || v_total_salary);
    DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_emp_count);
    
    -- Exception handling
    IF v_emp_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'No employees found.');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No data found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/