Stored Procedures and Functions

50 min

Stored procedures are precompiled SQL statements stored in the database that can be executed by name. They can accept input parameters, return output parameters, and return multiple result sets. Stored procedures are compiled once and stored in the database, improving performance for repeated execution. Understanding stored procedures enables you to encapsulate business logic in the database. Stored procedures are supported by most major databases (MySQL, PostgreSQL, SQL Server, Oracle).

Stored procedures can accept parameters (IN, OUT, INOUT) and return multiple result sets, making them powerful for complex operations. IN parameters pass values into the procedure. OUT parameters return values from the procedure. INOUT parameters do both. Stored procedures can execute multiple SQL statements, use variables, control flow (IF, WHILE, etc.), and handle errors. Understanding stored procedure capabilities enables you to build sophisticated database logic.

User-defined functions return a single value and can be used in SQL statements like built-in functions. Functions are different from stored procedures: they return a single value, can be used in SELECT statements, and are typically used for calculations or transformations. Understanding functions enables you to create reusable logic. Functions can be scalar (return single value) or table-valued (return table) depending on the database.

Stored procedures improve performance and security by reducing network traffic, enabling parameterized queries (preventing SQL injection), and allowing execution plan caching. Network traffic is reduced because only the procedure name and parameters are sent, not the entire SQL. Parameterized queries prevent SQL injection attacks. Execution plans can be cached and reused. Understanding these benefits helps you decide when to use stored procedures.

Stored procedures can encapsulate complex business logic and ensure consistency across applications. By centralizing logic in the database, multiple applications can use the same logic, ensuring consistency. Stored procedures can enforce business rules, validate data, and perform complex calculations. Understanding stored procedures as encapsulation tools helps you design maintainable systems. However, business logic in the database can make applications less portable.

Best practices include using stored procedures for complex, frequently executed operations; using parameterized procedures to prevent SQL injection; documenting procedures thoroughly; testing procedures carefully; and understanding the trade-offs of database logic vs application logic. Understanding stored procedures and functions enables you to build robust, efficient database applications.

Key Concepts

Stored procedures are precompiled SQL statements stored in the database.
Stored procedures can accept parameters and return multiple result sets.
User-defined functions return single values and can be used in SQL.
Stored procedures improve performance and security.
Stored procedures encapsulate business logic in the database.

Learning Objectives

Master

Creating stored procedures with parameters
Using control flow in stored procedures
Creating user-defined functions
Understanding when to use procedures vs functions

Develop

Database programming thinking
Understanding business logic placement
Designing maintainable database applications

Tips

Use stored procedures for complex, frequently executed operations.
Use parameterized procedures to prevent SQL injection.
Document stored procedures thoroughly.
Test procedures carefully before deploying to production.

Common Pitfalls

Putting too much business logic in stored procedures, reducing portability.
Not using parameters, creating SQL injection vulnerabilities.
Not handling errors in stored procedures, causing unexpected failures.
Not documenting procedures, making maintenance difficult.

Summary

Stored procedures are precompiled SQL statements stored in the database.
Stored procedures can accept parameters and return multiple result sets.
Functions return single values and can be used in SQL statements.
Understanding stored procedures enables robust database applications.
Stored procedures improve performance and security.

Exercise

Create a stored procedure to process a new order with validation.

DELIMITER //
CREATE PROCEDURE ProcessOrder(
    IN p_CustomerID INT,
    IN p_ProductID INT,
    IN p_Quantity INT
)
BEGIN
    DECLARE v_StockQuantity INT;
    DECLARE v_UnitPrice DECIMAL(10,2);
    DECLARE v_OrderID INT;
    
    -- Check stock availability
    SELECT StockQuantity, Price INTO v_StockQuantity, v_UnitPrice
    FROM Products WHERE ProductID = p_ProductID;
    
    IF v_StockQuantity >= p_Quantity THEN
        -- Create order
        INSERT INTO Orders (CustomerID, TotalAmount)
        VALUES (p_CustomerID, p_UnitPrice * p_Quantity);
        
        SET v_OrderID = LAST_INSERT_ID();
        
        -- Add order item
        INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice)
        VALUES (v_OrderID, p_ProductID, p_Quantity, v_UnitPrice);
        
        -- Update stock
        UPDATE Products 
        SET StockQuantity = StockQuantity - p_Quantity
        WHERE ProductID = p_ProductID;
        
        SELECT 'Order processed successfully' as Result, v_OrderID as OrderID;
    ELSE
        SELECT 'Insufficient stock' as Result, NULL as OrderID;
    END IF;
END //
DELIMITER ;

Stored Procedures and Functions