Stored Procedures and Functions

Stored procedures and functions are pre-compiled collections of SQL statements stored within the database. They promote code reusability, reduce network traffic by minimizing data transfer, and enhance security by encapsulating business logic at the database layer.

Key Distinctions

• Stored Procedures: Do not necessarily return a value. They can perform complex operations including transaction management.
• Stored Functions: Must return a single value and are typically used within SQL expressions.

Procedure Syntax and Execution

When defining procedures containing multiple statements, the default delimiter (semicolon) must be changed temporarily to allow the block to compile correctly.

DELIMITER //
CREATE PROCEDURE GetDepartmentStats()
BEGIN
    SELECT department_id, COUNT(*) as employee_count 
    FROM employees 
    GROUP BY department_id;
END //
DELIMITER ;

To invoke the procedure:

CALL GetDepartmentStats();

Variables and Control Structures

Local variables are declared using DECLARE and assigned using SET or SELECT ... INTO.

DELIMITER //
CREATE PROCEDURE AnalyzeSalary(IN emp_id INT)
BEGIN
    DECLARE avg_salary DECIMAL(10,2);
    DECLARE salary_status VARCHAR(20);

    SELECT AVG(salary) INTO avg_salary FROM employees;

    IF avg_salary > 5000 THEN
        SET salary_status = 'High';
    ELSE
        SET salary_status = 'Standard';
    END IF;

    SELECT avg_salary, salary_status;
END //
DELIMITER ;

Looping Mechanisms

MySQL supports WHILE, REPEAT, and LOOP constructs for iterative processing.

-- WHILE loop example: Summation
DELIMITER //
CREATE PROCEDURE CalculateSum(OUT total_sum INT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE accumulator INT DEFAULT 0;

    WHILE counter <= 100 DO
        SET accumulator = accumulator + counter;
        SET counter = counter + 1;
    END WHILE;

    SET total_sum = accumulator;
END //
DELIMITER ;

Cursors

Cursors allow row-by-row processing of result sets, which is useful for complex data migration or reporting tasks.

DELIMITER //
CREATE PROCEDURE ArchiveHighEarners()
BEGIN
    DECLARE v_emp_id INT;
    DECLARE v_salary DECIMAL(10,2);
    DECLARE finished INT DEFAULT 0;

    DECLARE emp_cursor CURSOR FOR 
        SELECT id, salary FROM employees WHERE salary > 80000;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO v_emp_id, v_salary;
        IF finished = 1 THEN
            LEAVE read_loop;
        END IF;

        INSERT INTO employee_archive (emp_id, salary) VALUES (v_emp_id, v_salary);
    END LOOP;

    CLOSE emp_cursor;
END //
DELIMITER ;

Database Triggers

Triggers are database objects automatically invoked in response to specific events (INSERT, UPDATE, DELETE) on a table. They are useful for maintaining audit trails and enforcing data integrity.

Trigger Types and Aliases

Implementation Example

The following example creates an audit trail whenever a product price is updated.

CREATE TABLE product_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    old_price DECIMAL(10,2),
    new_price DECIMAL(10,2),
    changed_at DATETIME
);

DELIMITER //
CREATE TRIGGER log_price_change
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price != NEW.price THEN
        INSERT INTO product_audit (product_id, old_price, new_price, changed_at)
        VALUES (OLD.id, OLD.price, NEW.price, NOW());
    END IF;
END //
DELIMITER ;

Transaction Management

A transaction represents a logical unit of work that must be executed as a whole. It adherers to ACID properties: Atomicity, Consistency, Isolation, and Durability.

Basic Transaction Commands

-- Start a new transaction
START TRANSACTION;

-- Perform data operations
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Save changes permanently
COMMIT;

-- Or revert to the previous state if an error occurs
-- ROLLBACK;

Transaction Isolation Levels

Isolation levels define the degree to which transactions must be isolated from each other. Lower levels increase concurrency but risk data anomalies.

To check or set the global isolation level:

SELECT @@GLOBAL.TX_ISOLATION;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;