mysql存储过程怎么写_mysql存储过程实践讲解

DELIMITER //
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    -- SQL语句
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT id, name, email FROM users;
END //
DELIMITER ;

CALL GetAllUsers();

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT id, name, email FROM users WHERE id = user_id;
END //
DELIMITER ;

CALL GetUserById(5);

DELIMITER //
CREATE PROCEDURE GetUserCount(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM users;
END //
DELIMITER ;

CALL GetUserCount(@count);
SELECT @count AS 用户总数;

DELIMITER //
CREATE PROCEDURE AddToCounter(INOUT counter INT, IN inc INT)
BEGIN
    SET counter = counter + inc;
END //
DELIMITER ;

SET @counter = 10;
CALL AddToCounter(@counter, 5);
SELECT @counter; -- 结果为 15

DELIMITER //
CREATE PROCEDURE InsertTestUsers(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= num DO
        INSERT INTO users(name, email) VALUES 
            (CONCAT('user_', i), CONCAT('user_', i, '@test.com'));
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL InsertTestUsers(100);

SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';

SHOW CREATE PROCEDURE 存储过程名;

DROP PROCEDURE IF EXISTS 存储过程名;