Stored Procedures

Stored Procedures

DELIMITER //

CREATE PROCEDURE GetUserOrders(IN user_id INT, OUT order_count INT)
BEGIN
    SELECT COUNT(*) INTO order_count
    FROM orders
    WHERE orders.user_id = user_id;
END //

CREATE PROCEDURE CreateUser(IN p_name VARCHAR(100), IN p_email VARCHAR(255))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
    INSERT INTO users (name, email) VALUES (p_name, p_email);
    INSERT INTO profiles (user_id) VALUES (LAST_INSERT_ID());
    COMMIT;
END //

DELIMITER ;

-- Call
CALL GetUserOrders(1, @count);
SELECT @count;

CALL CreateUser('Alice', 'alice@example.com');

-- Drop
DROP PROCEDURE IF EXISTS GetUserOrders;