Stored Procedures in MySQL

 

CREATION OF STORED PROCEDURES IN MySQL

---

1. AIM

To study and implement Stored Procedures in MySQL.

---

2. THEORY

✔ What is a Stored Procedure?

A Stored Procedure is a precompiled block of SQL statements stored in the database and executed on demand.

✔ Advantages of Stored Procedures

• Improves performance (precompiled)

• Ensures reusability of code

• Helps maintain data integrity

• Reduces network traffic

• Provides security by restricting direct table access

---

3. GENERAL SYNTAX (MySQL)

MySQL requires the DELIMITER command when creating procedures to avoid conflict with semicolons.

DELIMITER $$

CREATE PROCEDURE procedure_name (parameter_list)
BEGIN
    SQL statements;
END $$

DELIMITER ;

✔ Calling a Procedure

CALL procedure_name(arguments);

---

4. EXAMPLES 

---

⭐ Example 1: Procedure Without Parameters

▶ Create a table for demonstration

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2)
);

▶ Insert sample data

INSERT INTO employees VALUES 
(1, 'Alice', 50000),
(2, 'Bob', 45000),
(3, 'Charlie', 60000);

---

⭐ Procedure to Display All Employees

DELIMITER $$

CREATE PROCEDURE show_employees()
BEGIN
    SELECT * FROM employees;
END $$

DELIMITER ;

▶ Call the Procedure:

CALL show_employees();

---

⭐ Example 2: Procedure With IN Parameter

✔ Procedure to get salary of an employee

DELIMITER $$

CREATE PROCEDURE get_salary(IN emp_id INT)
BEGIN
    SELECT name, salary 
    FROM employees
    WHERE id = emp_id;
END $$

DELIMITER ;

▶ Call:

CALL get_salary(2);

---

⭐ Example 3: Procedure With OUT Parameter

✔ Procedure to get total number of employees

DELIMITER $$

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

DELIMITER ;

▶ Call:

CALL count_emp(@n);
SELECT @n AS total_employees;

---

⭐ Example 4: Procedure with INOUT Parameter

✔ Procedure to increase salary by a percentage

DELIMITER $$

CREATE PROCEDURE update_salary(INOUT sal DECIMAL(10,2))
BEGIN
    SET sal = sal + (sal * 0.10);  -- 10% increment
END $$

DELIMITER ;

▶ Call:

SET @s = 50000;
CALL update_salary(@s);
SELECT @s;

---

⭐ Example 5: Procedure Containing IF, ELSEIF, ELSE

DELIMITER $$

CREATE PROCEDURE grade(IN marks INT)
BEGIN
    IF marks >= 90 THEN
        SELECT 'A Grade' AS grade;
    ELSEIF marks >= 75 THEN
        SELECT 'B Grade' AS grade;
    ELSEIF marks >= 60 THEN
        SELECT 'C Grade' AS grade;
    ELSE
        SELECT 'Fail' AS grade;
    END IF;
END $$

DELIMITER ;

▶ Call:

CALL grade(82);

---

⭐ Example 6: Procedure With Loop

DELIMITER $$

CREATE PROCEDURE print_numbers(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    numbers_loop: LOOP
        IF i > n THEN 
            LEAVE numbers_loop;
        END IF;
        SELECT i AS number;
        SET i = i + 1;
    END LOOP;
END $$

DELIMITER ;

▶ Call:

CALL print_numbers(5);

---

⭐ Example 7: Procedure That Inserts Data

DELIMITER $$

CREATE PROCEDURE add_employee(IN eid INT, IN ename VARCHAR(100), IN esalary DECIMAL(10,2))
BEGIN
    INSERT INTO employees VALUES(eid, ename, esalary);
END $$

DELIMITER ;

▶ Call:

CALL add_employee(4, 'David', 55000);

---

⭐ Example 8: Procedure With Error Handling (SIGNAL)

DELIMITER $$

CREATE PROCEDURE safe_insert(
    IN eid INT,
    IN ename VARCHAR(100),
    IN esalary DECIMAL(10,2)
)
BEGIN
    IF esalary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be negative';
    ELSE
        INSERT INTO employees VALUES(eid, ename, esalary);
    END IF;
END $$

DELIMITER ;

▶ Test it:

CALL safe_insert(5, 'Eve', -20000);

---

5. LAB PRACTICE QUESTIONS

1. Create a procedure to display all students from the student table.

2. Write a procedure that calculates total price of products.

3. Create a procedure that increases salary by a given percentage.

4. Write a procedure to delete a record based on ID.

5. Create a procedure to return highest salary using an OUT parameter.

6. Write a procedure to display even numbers up to N using LOOP.

---

6. RESULT

Stored procedures were successfully created, executed, and tested in MySQL.