Stored Procedures and Functions

# CHAPTER 21

Stored Procedures and Functions #

1. Introduction #

2. Learning Objectives #

• Understand the architectural benefits of Stored Procedures.

• Create a Stored Procedure using the DELIMITER command.

• Pass input parameters (IN) to a procedure.

• Create User-Defined Functions (UDF) for inline calculations.

• Execute a procedure using the CALL statement.

3. What is a Stored Procedure? #

• Security: You can grant an app permission to run the procedure without giving it permission to access the underlying tables!

• Performance: Because the procedure is pre-compiled by the MySQL engine, it executes faster than sending raw SQL strings over the network.

• Maintainability: "Don't Repeat Yourself." If the checkout logic changes, you update the database once, and all three apps (Web, iOS, Android) instantly use the new logic!

4. Creating a Stored Procedure #

-- Step 1: Change the delimiter from ; to //
DELIMITER //

-- Step 2: Create the Procedure
CREATE PROCEDURE GetHighValueCustomers()
BEGIN
    SELECT first_name, email 
    FROM users 
    WHERE total_spent > 1000;
END //

-- Step 3: Change the delimiter back to standard ;
DELIMITER ;

5. Executing the Procedure #

CALL GetHighValueCustomers();

6. Using Parameters (IN variables) #

DELIMITER //

-- We define an input parameter named p_department of type VARCHAR
CREATE PROCEDURE GetEmployeesByDept(IN p_department VARCHAR(50))
BEGIN
    SELECT first_name, salary 
    FROM employees 
    WHERE department = p_department;  -- We use the parameter here!
END //

DELIMITER ;

-- To use it:
CALL GetEmployeesByDept('IT');
CALL GetEmployeesByDept('Sales');

7. User-Defined Functions (UDF) #

*Let's build a function that calculates a 10% discount on any price passed to it.*

DELIMITER //

CREATE FUNCTION CalculateDiscount(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE discount_price DECIMAL(10,2);
    SET discount_price = price * 0.90;
    RETURN discount_price;
END //

DELIMITER ;

-- How to use the Function:
SELECT product_name, price, CalculateDiscount(price) AS sale_price 
FROM products;

8. Mini Project: The Transfer Funds Procedure #

DELIMITER //
CREATE PROCEDURE TransferFunds(IN sender_id INT, IN receiver_id INT, IN amount DECIMAL(10,2))
BEGIN
    START TRANSACTION;
    
    UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
    UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;
    
    COMMIT;
END //
DELIMITER ;

-- The web app just sends this:
CALL TransferFunds(1, 2, 500.00);

9. Common Mistakes #

• Forgetting the DELIMITER: If you attempt to write a multi-line procedure with standard semicolons without changing the delimiter first, MySQL will throw a syntax error on line 2.

• Putting too much logic in MySQL: While Procedures are powerful, MySQL is difficult to debug. Do not put massive amounts of String Manipulation or complex array processing inside the database. Use Python/PHP for heavy business logic, and use MySQL purely for data routing and transactions.

10. Best Practices #

• Parameter Naming: Always prefix your parameters (e.g., pdepartment or indepartment). If you name your parameter exactly the same as the column name (e.g., department), MySQL gets confused during the WHERE department = department clause and will return all rows!

11. Exercises #

1. 1. Write the CALL statement required to execute a procedure named DeleteInactiveUsers.

1. 2. Why is it necessary to change the DELIMITER when writing a Stored Procedure?

12. MCQ Quiz with Answers #

13. Interview Questions #

• Q: Describe a security scenario where granting a web application permission to CALL a Stored Procedure is vastly safer than granting it UPDATE and DELETE permissions on the underlying tables.

• Q: Explain the purpose of the DELIMITER // command when writing MySQL routines.

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #