MySQL — Stored Procedures

Basic Stored Procedure

DELIMITER //
CREATE PROCEDURE GetAllCustomers()
BEGIN
    SELECT * FROM customers;
END //
DELIMITER ;

-- Call it
CALL GetAllCustomers();

Procedure with Parameters

DELIMITER //
CREATE PROCEDURE GetCustomersByCity(IN city_name VARCHAR(100))
BEGIN
    SELECT * FROM customers WHERE city = city_name;
END //
DELIMITER ;

CALL GetCustomersByCity('New York');

IN, OUT, INOUT Parameters

DELIMITER //
CREATE PROCEDURE UpdateSalary(
    IN emp_id INT,
    IN raise_pct DECIMAL(5,2),
    OUT new_salary DECIMAL(10,2)
)
BEGIN
    UPDATE employees SET salary = salary * (1 + raise_pct / 100) WHERE id = emp_id;
    SELECT salary INTO new_salary FROM employees WHERE id = emp_id;
END //
DELIMITER ;

CALL UpdateSalary(1, 10, @result);
SELECT @result;

Variables

DELIMITER //
CREATE PROCEDURE CountCustomers()
BEGIN
    DECLARE total INT DEFAULT 0;
    SELECT COUNT(*) INTO total FROM customers;
    SELECT total AS total_customers;
END //
DELIMITER ;

Conditional Logic

DELIMITER //
CREATE PROCEDURE ClassifyScore(IN score INT)
BEGIN
    IF score >= 90 THEN SELECT 'A' AS grade;
    ELSEIF score >= 80 THEN SELECT 'B' AS grade;
    ELSEIF score >= 70 THEN SELECT 'C' AS grade;
    ELSE SELECT 'F' AS grade;
    END IF;
END //
DELIMITER ;

Loops

DELIMITER //
CREATE PROCEDURE CountToTen()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 10 DO
        SELECT i;
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

Dropping Procedures

DROP PROCEDURE IF EXISTS GetAllCustomers;

Mini Practice

Write SQL code that:

1. Creates a stored procedure with no parameters
2. Creates a procedure with IN and OUT parameters
3. Uses IF/ELSEIF inside a procedure
4. Drops a procedure

Up Next

Continue with Functions — custom functions that return values.