Untitled

adityass

May 2nd, 2024

110

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

PL/SQL 2.27 KB | None | 0 0

raw download clone embed print report

-- 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;
/

Add Comment

Please, Sign In to add comment