mysql 使用存储过程-mysql教程-PHP中文网

mysql 使用存储过程

王林

发布： 2023-05-20 10:25:37

原创

1422人浏览过

mysql 是当前最普及的一种关系型数据库管理系统，其使用存储过程是为了更好地维护和管理 sql 语句和程序的执行。存储过程是一种预编译的 sql 代码块，可以将常用的 sql 语句封装起来，在多次执行时提高效率，降低运行时的资源占用。

MySQL 中存储过程的使用

1、创建存储过程

从根本上来说，创建一个存储过程就是编写一个 SQL 语句块，以及在系统数据库中保存这个代码块。下面是一个具体的例子：

CREATE PROCEDURE `procedure_name` (`parameter_list`)
BEGIN
    -- SQL 语句块
END

登录后复制

其中，procedure_name 是存储过程的名称，paramter_list 是存储过程的参数列表，可以包括入参、出参，也可以不包含参数。

例如，我们创建一个获取用户信息的存储过程：

CREATE PROCEDURE `get_user_info`(user_id INT)
BEGIN
    SELECT * FROM `user` WHERE `user_id` = user_id;
END

登录后复制

在存储过程的 SQL 语句块中，我们可以使用 MySQL 支持的所有 SQL 语句，如 SELECT、INSERT、UPDATE、DELETE 等，还可以使用控制流语句，例如 IF、WHILE、LOOP 等，从而实现更复杂的数据逻辑处理。

2、调用存储过程

创建存储过程之后，我们可以通过 CALL 语句来调用存储过程，并向其传递参数：

CALL procedure_name(param1, param2, ...)

登录后复制

例如，我们可以按如下方式调用上面定义的 get_user_info 存储过程：

CALL `get_user_info`(1);

登录后复制

3、删除存储过程

存了个图

视频图片解析/字幕/剪辑，视频高清保存/图片源图提取

查看详情

如果随着时间的推移，一个存储过程已经不再需要，可以使用 DROP PROCEDURE 语句删除它：

DROP PROCEDURE `procedure_name`;

登录后复制

例如：

DROP PROCEDURE `get_user_info`;

登录后复制

4、存储过程实例

我们来看一个完整的存储过程实例。假设我们有一个 score 表，存储了学生的考试成绩，而我们需要计算出每个学生的总分、平均分，并按照总分从高到低排列。这时可以使用存储过程来实现：

CREATE PROCEDURE `calc_stu_score`()
BEGIN
     DECLARE `total_score` INT;
     DECLARE `avg_score` FLOAT;
     DECLARE `stu_id` INT DEFAULT 0;
     DECLARE `stu_name` VARCHAR(255);
     DECLARE `total` INT DEFAULT 0;
    DECLARE `cursor_stu_id` CURSOR FOR SELECT `stu_id` FROM `score` GROUP BY `stu_id`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;

    DROP TEMPORARY TABLE IF EXISTS `tmp_score`;
    CREATE TEMPORARY TABLE `tmp_score` (
        `stu_id` INT NOT NULL,
        `stu_name` VARCHAR(255) NOT NULL,
        `total_score` INT NOT NULL,
        `avg_score` FLOAT NOT NULL,
        PRIMARY KEY (`stu_id`)
    );

    OPEN cursor_stu_id;

    stu_loop: LOOP
        FETCH cursor_stu_id INTO stu_id;
        IF finished = TRUE THEN 
            LEAVE stu_loop;
        END IF;

        SELECT `name` INTO stu_name FROM `student` WHERE `stu_id` = stu_id;

        SELECT SUM(`sorce`), COUNT(*) INTO total_score, total FROM `score` WHERE `stu_id` = stu_id;

        SET avg_score = total_score / NULLIF(total, 0);

        INSERT INTO `tmp_score` (`stu_id`, `stu_name`, `total_score`, `avg_score`) VALUES (stu_id, stu_name, total_score, avg_score);

    END LOOP;

    CLOSE cursor_stu_id;

    SELECT * FROM `tmp_score` ORDER BY `total_score` DESC;

    DROP TEMPORARY TABLE `tmp_score`;

END

登录后复制

在这个存储过程中，我们首先定义了一些需要使用的变量，包括 total_score 表示某个学生的总分，avg_score 表示某个学生的平均分，stu_id 表示某个学生的编号，stu_name 表示某个学生的姓名，还有一个临时表 tmp_score。

然后我们使用了 DECLARE CURSOR 语句声明了一个游标变量 cursor_stu_id，用于查询学生表中的学生编号。在循环中，我们根据这个学生编号查询成绩表，计算出该学生的总分和平均分，并将其保存到临时表中。最后，我们使用 SELECT 语句查询临时表，按照总分从高到低排序，并在最后使用 DROP TABLE 语句删除临时表。

最后，我们可以使用 CALL 语句来调用这个存储过程：

CALL `calc_stu_score`();

登录后复制

总结

存储过程是 MySQL 中一种非常强大的工具，它可以大大简化我们的数据库操作过程，并提高数据库的性能和安全性。在实际应用中，我们可以使用存储过程来完成很多复杂的数据处理操作，从而更好地满足业务需求，提高效率，降低成本。

以上就是mysql 使用存储过程的详细内容，更多请关注php中文网其它相关文章！