mysql 使用存储过程

时间：2023-05-20 12:00

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 语句来调用这个存储过程：