task14.sql

CREATE DATABASE task14;
USE task14;

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    bonus DECIMAL(10,2)
);
--Stored Procedure: Insert Employee Data
--Procedure with Parameters
DELIMITER $$

CREATE PROCEDURE add_employee (
    IN p_name VARCHAR(100),
    IN p_dept VARCHAR(50),
    IN p_salary DECIMAL(10,2),
    IN p_bonus DECIMAL(10,2)
)
BEGIN
    INSERT INTO employees(emp_name, department, salary, bonus)
    VALUES(p_name, p_dept, p_salary, p_bonus);
END $$

DELIMITER ;
--Call Procedure
CALL add_employee('Vijay', 'IT', 50000, 5000);
CALL add_employee('Anita', 'HR', 45000, 3000);

SELECT * FROM employees;
--Stored Procedure with OUTPUT Parameter
--Get Employee Count
DELIMITER $$

CREATE PROCEDURE get_employee_count(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM employees;
END $$

DELIMITER ;
Call
CALL get_employee_count(@count);
SELECT @count;
--User Defined Function (UDF): Tax Calculation
✔ Function must return a value
Example: 10% tax on salary

DELIMITER $$

CREATE FUNCTION calculate_tax(sal DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN sal * 0.10;
END $$

DELIMITER ;
Use Function
SELECT emp_name, salary, calculate_tax(salary) AS tax
FROM employees;
--Function: Bonus + Salary Total
DELIMITER $$

CREATE FUNCTION total_income(sal DECIMAL(10,2), bon DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN sal + bon;
END $$

DELIMITER ;
Usage:

SELECT emp_name, total_income(salary, bonus)
FROM employees;

--Procedure with Error Handling
DELIMITER $$

CREATE PROCEDURE safe_insert_employee(
    IN p_name VARCHAR(100),
    IN p_dept VARCHAR(50),
    IN p_salary DECIMAL(10,2)
)
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'Error occurred during insert' AS message;
    END;

    INSERT INTO employees(emp_name, department, salary)
    VALUES(p_name, p_dept, p_salary);

    SELECT 'Insert successful' AS message;
END $$

DELIMITER ;